Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
Hi Disco, welcome to Database Administrators. To start with, you'll need to provide the EXPLAIN output on your select queries and the SHOW CREATE TABLE updateTable for us to help with. It's probably an indexing issue. –  Derek Downey Nov 7 '11 at 22:30
    
Thanks. I've attached the info requested above. If you need the EXPLAIN for the UPDATE or anything else, please let me know. –  Disco Nov 7 '11 at 22:57
    
+1 for fixing it yourself :) Sometimes, explaining the problem in detail can show you where your own errors are! –  Dave Rix Nov 8 '11 at 8:27
1  
I'd suggest adding your own answer, showing the old and new versions of the queries, then in a couple of days you can accept your own answer, and then this question won't keep coming up on the "unanswered" lists :) –  Dave Rix Nov 8 '11 at 8:28
add comment

2 Answers

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.

share|improve this answer
    
Thanks, done. See above. –  Disco Nov 8 '11 at 0:31
2  
By George I think that found it. :) I think it's a bug in the query itself, which although it works, is updating several thousand more rows than necessary. Will run overnight and update in the morning. Thank you! –  Disco Nov 8 '11 at 0:52
2  
It is much better. The issue was that I didn't have a key in one of my ORs. What's the DBA StackExchange community etiquette here? Do I mark this answer as "correct", or answer my own question and mark that as correct? –  Disco Nov 8 '11 at 15:42
    
Good! I'm still new here as well, so I don't know the proper etiquette. This is a question to ask on DBA meta site. –  dabest1 Nov 8 '11 at 18:10
    
How about if you edit your answer to say to check the OR in the update, and I'll mark it as correct? :) –  Disco Nov 9 '11 at 20:08
show 1 more comment
up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.