If I were you, I would switch all data to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING.
While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by key_buffer_size), only caches index pages from .MYI files. It never caches data pages. It has an official maximum of 4GB in 32-bit Systems. 8GB is best maximum for 64-bit.
The InnoDB Buffer Pool caches the data and index pages. Depending on the server your have, you can cache up to the entire dataset in RAM. You can tune InnoDB for up to 80% RAM and 10% for DB Conenctions, and leave 10% for the OS. This is true even for different operating systems.
I have recommended these things for Drupal customers with marvelous success. It applies to Wordpress just as well. I have provided DB support for clients with WordPress. Same improvements.
You can always configure memory for InnoDB more effectively that you can more MyISAM. There is always a way to tweek InnoDB to suit your performance needs. As your data grows, it will eventually become a requirement.
UPDATE 2011-11-21 11:44 EST
If your complete dataset is small enough, you could execute a SELECT query on every table you have right after mysql starts up.
For all tables that are InnoDB and/or MyISAM, run this query:
SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
SELECT
engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine IN ('InnoDB','MyISAM')) B
USING (table_schema,table_name)
WHERE
B.table_schema NOT IN ('information_schema','mysql')
AND A.index_type <> 'FULLTEXT'
ORDER BY
table_schema,table_name,index_name,seq_in_index
) A
GROUP BY
table_schema,table_name,index_name
) AA
ORDER BY
engine DESC,db,tb
;
This will output every possible SELECT query you need to run that will summon all indexes to be referenced. Place this query in a file called /root/MakeSelectQueriesToLoad.sql. Run the script and collect the output /root/SelectQueriesToLoad.sql. Finally, run it:
mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
mysql -u... -p... < /root/SelectQueriesToLoad.sql
This will definitely preload all index pages into the InnoDB Buffer Pool and MyISAM Key Cache. If all your data is InnoDB, make two changes:
- replace
WHERE engine IN ('InnoDB','MyISAM') with WHERE engine='InnoDB'
- replace
CONCAT('SELECT ',ndxcollist,' FROM ', with CONCAT('SELECT * FROM ',
This will also populate more data pages into the InnoDB Buffer Pool.
FINAL NOTE : Make sure the InnoDB Buffer Pool is large enough to hold all your InnoDB Data
Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores
Here are the settings you should be using
innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.
innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.
Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB
Best Answer
MyISAM or InnoDB? Depending on which, you need to see the appropriate cache buffer. If you had 16GB of RAM, you could set that so that the table will (eventually) be entirely in RAM. That would move it from being I/O bound (as it probably is now) to being CPU bound. At that point, an old, slow, cheap, spinning drive will be almost as good as SSDs.
Can you parallelize the algorithm? If so, split it into as many programs (connections) as you have cores. That will (assuming CPU-bound) give you a nice speed-up.
Let's see the main query. And the SHOW CREATE TABLE for any table(s) involved. There may be simple ways to speed it up.
(I estimate that a table of 8.4GB is about the 96th percentile.)