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

• UPDATE [single table] SET refreshTime to current timestamp, with two same table checks in the WHERE clause

• SELECT COUNT(*) [four table join, with indexes], and a bunch of ANDs in the WHERE clause (still pretty simple)

• 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_

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.

StackExchange Database Administrators Q#7652, answer score: 7