Re: [gentoo-user] mysqld invoked oom-killer

[kashani]

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