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