Re: Help make my big new server fast

[Rick James]

You need to keep key_buffer_size for the other tables (perhaps cut it in half).
innodb_buffer_pool_size -- make it 30% of available RAM (for now).

It might be better to go for the latest 5.1, including the "plugin". There are some performance improvements in the plugin. 5.5 has even more improvements, but it is only "RC" (Release Candidate), not "GA" (Generally Available).

Every table should (not 'must') have a PRIMARY KEY. If you have secondary keys, it helps to have a short PRIMARY KEY. Create a dummy AUTO_INCREMENT if necessary.

InnoDB tables will be stored in PRIMARY KEY order. (MyISAM tables are not.)

InnoDB has its own set of mutexes (separate from MyISAM), but they still need some cleanup. I have not heard of even the latest 5.5 doing well with 24 cores.

I prefer AUTOCOMMIT = 0 and to be explicit with BEGIN...COMMIT. Put BEGIN...COMMIT around every sequence of queries ('transaction') that need to be 'atomic'. Check for errors after every operation, including COMMIT. If an error occurs, program the appropriate action. Often it is advisable to ROLLBACK and loop back to retry the transaction. (But don't get stuck in a loop.)

Even the cleanest of code can stumble over 'deadlocks' if you have multiple threads hitting the same table. (See ROLLBACK, above.) MyISAM avoids such by locking the whole table(s) around each query. InnoDB locks rows as needed.

It is OK to JOIN MyISAM and InnoDB tables in a single query.