8

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
Improve this question
4
  • 3
    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. Commented Nov 7, 2011 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. Commented Nov 7, 2011 at 22:57
  • +1 for fixing it yourself :) Sometimes, explaining the problem in detail can show you where your own errors are! Commented Nov 8, 2011 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 :) Commented Nov 8, 2011 at 8:28

2 Answers 2

Reset to default
7

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.

Improve this answer
5
  • 3
    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! Commented Nov 8, 2011 at 0:52
  • 3
    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? Commented Nov 8, 2011 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. Commented Nov 8, 2011 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? :) Commented Nov 9, 2011 at 20:08
  • @Disco, go ahead and create your answer with the explanation and mark it as correct. I'm ok with that. Commented Nov 9, 2011 at 22:25
5

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.