Mysql – High Disk IO, How to mitigate

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