High Disk IO, How to mitigate?

Question

I'm a programmer, not a DBA. Be gentle :)

Overview

• InnoDB, MySQL
• mod_perl script, persistent connections
• script called every 20 seconds by thousands of users

Problem

• High Disk IO (presumably caused by updates[?]) slows everything down, creating a huge bottleneck.

Queries

1. UPDATE [single table] SET refreshTime to current timestamp, with two same table checks in the WHERE clause
2. SELECT COUNT(*) [four table join, with indexes], and a bunch of ANDs in the WHERE clause (still pretty simple)
3. SELECT a,b [four table join, same four tables], and a bunch of ANDs in the WHERE clause (also pretty simple)

Query cache is on.

Solutions?

• I'm not a DBA, but I suspect that it's possible to have a table in RAM that periodically (every 10 seconds?) updates onto disk, and in the event of a catastrophic failure, will automatically populate the RAM table from the disk table upon restart, but I have no idea if it's actually possible, if it's the best solution or what other options there are out there.
• Any thoughts or suggestions? Again, I'm a programmer so if someone either knows someone who does this for a fee or can point me to very specific resources, I'd be very appreciative.

~~~~~~~

CREATE TABLE `openInvitations` (
`id` int(99) NOT NULL auto_increment,
`createTime` timestamp NULL default NULL,
`repAcceptTime` timestamp NULL default NULL,
`rep_id` varchar(64) NOT NULL default '',
`repRefreshTime` timestamp NULL default NULL,
`customer_macAddr` varchar(14) NOT NULL default '',
`customerRefreshTime` timestamp NULL default NULL,
`stage` char(1) NOT NULL default 'P',
`parent` varchar(25) default NULL,
`reason` varchar(64) default NULL,
PRIMARY KEY  (`rep_id`,`customer_macAddr`),
UNIQUE KEY `id` (`id`),
KEY `customer_macAddr` (`customer_macAddr`),
CONSTRAINT `openInvitations_ibfk_1` FOREIGN KEY (`rep_id`) REFERENCES `rep` (`id`),
CONSTRAINT `openInvitations_ibfk_2` FOREIGN KEY (`customer_macAddr`) REFERENCES `customer` (`macAddr`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where; Using index
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ALL     customer_macAddr    NULL    NULL    NULL    5258    Using where

After fixing query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ref     customer_macAddr    customer_macAddr    16  const   1   Using where

Answer 1

As DTest pointed out, provide more information about your problem.

Regarding caching, you could possibly increase your innodb buffer pool size to allow more data and indexes to be cached in memory. If you have frequent updates, you may not benefit from the query cache and might be better off using that RAM for innodb buffer pool.

If you want to put all your data and indexes in RAM, then MySQL Cluster might be your answer.

EDIT
It looks like your SELECT statements are using proper indexes. Could you provide an explain plan for your update statement? You will have to rewrite it as a SELECT statement to do that. Do a SELECT * FROM same table and with same where clause as your UPDATE statement.

Answer 2

The issue was a result of not including a key in one of my OR statements in the UPDATE.

Hat tip to "dabest1" for helping me find the answer.