Hi Bruce, Thanks again for your advice.
I have control over the client access so it a possibility to do what Heikki is suggesting, additionally I have to do it to a master and slave, I could always try it on one and if it fails then go down the dump and import route, this way I wouldn't lose data. We had similar issues with character sets also when upgrading but we got those resolved. Regarding the file size issue, we are on a 32-bit system running redhat AS3, we already have idb files in excess of 21Gb, I'm not sure what the limit is though if any ? Just one thought about the shared space, do you think it would be possible to back up all the current shared data files along with the iblog files, change the my.cnf file to use a single ibdata file of 2 gig, then try to start it up. If it did fail how about reverting the my.cnf and restoring the original ibdata and iblog files, would it still work after restoring the original files ? With this I could test Heikki idea without the pssibility of losing data. Cheers, Marvin. -----Original Message----- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: 25 July 2005 17:55 To: Marvin Wright; mysql@lists.mysql.com Subject: Re: Correct way to use innodb_file_per_table? On Jul 25, 2005, at 5:33 AM, Marvin Wright wrote: > Hi, > > Thanks for your reply. > > I've only just moved all tables to there own table space so that I can > put certain databases on different disks. > Right now my shared tablespace does not hold any databases. > I'm aware that I still need the shared table space but I don't need > 200gb now, I just want to decrease it down to 10Gb. > > It seems a bit daft that I still have to dump all tables even when > they are in their own tablespace. I guess this is because the table > definitions are still stored in the shared space. > > Marvin. > Hi! These are good questions... Heikki once told me that if there is no activity going on AND the innodb status page shows nothing being processed AND everything is up to date according to the innodb status page, you could (in theory) shutdown mysql and bring it back with a new shared table space under these circumstances... That is going to require that every connection to the database server be idle, or better still shut off... Depending on how your machines access your database server that may be easy or hard to do... We had some character set issues to work on and were (are - it's an ongoing project) needing to do a dump and an import to do the move from 4.0 to 4.1 at the same time... So we didn't actually try and bounce a server into a smaller shared table space live... I have total control over my client connections to the database server and can easily prevent them from connecting with a hardware load balancer, and I'm still not sure I would want to try that though. Hint if you are going the dump and import route... The fastest way to dump and for sure the fastest way to import is to use mysqldump -- tab=/var/tmp/somewhere and use mysqlimport to import the tab delimited data... using --tab on the dump creates two files for each table.. an sql file with the create table statement, and a txt file with the tab delimited data... We create our databases using cat /var/ tmp/somewhere/*sql | mysql ourDatabase, and then use mysqlimport ourDatabase /var/tmp/somewhere/*.txt - mysql import is smart enough to insert data into tables matching the filename, it's the fastest way to do the whole dump and import thing by a lot. On the issue of how much shared space, Heikki told me 200Mbytes would be far more than we would need if everything is innodb_file_per_table... but as my old file space was made with 2000M files I just kept ibdata01 and commented out the rest of the line.... certainly haven't any issues with the 2Gbyte shared table space, I would think 10G would be overkill (I think my 2G is overkill). The only other area we discovered was an issue is that if you are running a 32 bit file system there is likely to be a a problem on any table that needs more file space than the file system will give a single file. The solutions here are to use a 64 bit file system which doesn't care so much, or create a larger shared table space and turn off innodb_file_per_table and alter the table to innodb (even if it is already innodb, altering it like this will recreate it new). turn on innodb_file_per_table again and that table will stay in the shared table space, the rest will be in their own files. the main problem here is that once the file reached the OS limit InnoDB thought the table was full(which technically it was)... so Innodb's autoextending files don't know how to launch a second file once the File system's upper limit has been reached. Best Regards, Bruce ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ********************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
