On 7/21/2011 2:50 PM, Grant wrote:
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.
1. Make backups first.
2. See if you have any full text fields. Tables with full text fields
will have to remain MyISAM.
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.
You can alter tables in place, but it locks them for the duration. If
you site is small and low traffic you could get away with it, but
testing with a copy of your site database is better.
kashani
