>>> Any reason you're still using MyISAM tables? Innodb is almost as >>> fast >>> or much much faster than MyISAM in nearly every way these days. >> >> Can multiple processes be utilized for mysql like they are for >> apache2? Perhaps not since it's a database? > > Mysql is multithreaded and spawns a thread for each connection. Try a > ps -efL and you should see a number of Mysql threads. However that is part > of the problem with MyISAM. It throws a giant table lock blocking all other > threads until the SQL statement is complete. Innodb uses row locks which > allows the other threads to use the table. > > As far as moving to Innodb tables it's actually easy, but with a > number of caveats. I'd lower your Apache max clients, tweak my.cnf, and runs > some load tests before getting deep into Mysql. When you're ready I'd go > about this way.
apache MaxClients has been lowered to 50 which is a shame because I have 30+ separate images on each of my pages and that number can not be reduced. This means I may not be able to serve more than 1 full page at a time. > 1. Make backups first. > 2. See if you have any full text fields. Tables with full text fields will > have to remain MyISAM. Many of my tables have one or more fields defined as TEXT out of laziness. Should I instead come up with an appropriate char(N) declaration for each? Can N go as high as necessary? > 3. Dump your database out to text. If it's not a huge amount of data I'd > just vi it and change the ENGINE to Innodb. Then import the whole thing as a > new database. If you have a lot of data, I'd dump the schema with -d edit, > import schema, then dump your data with no create statements and finally > import the data into the new database. > 4. Point your staging code to the new database and test > 5. Plan a maintenance window to do all the above and take the site offline > while you reimport the data to be Innodb > 6. take the RAM you gave to key_buffer and give it to innodb. Storage > engines do not share buffers in Mysql. OK, just leave key_buffer at the default 16M? - Grant
