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.

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran.

We recently updated one of our slaves to MySQL 5.5, from MySQL 5.0, as a test run before we do it on our master. We're still running everything in MyISAM because we needed it up and running before attempting to do InnoDB optimization. The thing seems to be working fast enough, except for this process. The other slave, with identical hardware, finished it virtually in real time; this slave, however, is running over 2 hours behind because of it.

I'm curious, does anyone know if this type of thing (tons of tiny, simple updates) is slower in MySQL 5.5 for MyISAM in general? The settings between the two servers are as close as settings between a 5.0 and 5.5 can be, so it doesn't necessarily appear to be configuration. So while I can fix it by not having the horrible process, I'm worried that this is indicative of a deeper problem with our server. Should I be, or is it all kosher?

Edit: The problem appears to only be with large numbers of small queries. I just ran one and it was in the "query end" state for .133 seconds, or 89% of the query time. Only being able to run ten queries per second on a single thread seems pretty bad. Does 5.5 change things such that I need to modify my cnf file?

share|improve this question
    
It really sounds like there's a deeper problem elsewhere. I've setup many MySQL 5.5.x hosts replicating MM & MS with millions of reads & writes per hour and not had replication lag > 2 sec. Keep in mind, if the slave is taking reads while replicating, the replication will be blocked until the table locks are released...because the table engine is MyISAM. –  randy melder Jun 27 '12 at 15:37
    
It's not, right now this machine is purely a backup, no other writes or reads. Everything else seems to run fine but it seems to stumble on these large numbers of tiny queries. Oh well, I'm getting rid of that and will get InnoDB set up soon. –  Andrew Jun 27 '12 at 15:39
    
Same hardware & my.cnf tuning??? Very strange. I'd suggest running sar on the slave and see if there's a hardware bottleneck. –  randy melder Jun 27 '12 at 17:42
    
The only thing that changed was upgrading from RHEL 4 to 5, and MySQL 5.0 to 5.5... I've narrowed the problem down, I think to query end: It spends 89% of its time in query end, which lasts .13 seconds. Over a tenth of a second per query means these million queries will take a very long time to complete. –  Andrew Jun 27 '12 at 18:44
    
You said the cnfs are as close as possible, all the same could you post a diff of the "good" and "bad" slaves cnfs. Are you sure the systems are configured the same despite having the same hardware? That is same file systems, same specked disks and raid configurations? –  atxdba Jun 28 '12 at 6:59
show 1 more comment

2 Answers 2

Disabling the binary log on the slave fixed it. This makes me worry about when we upgrade the master to 5.5, but for now things are working much much better.

Edit: The issue was sync_binlog; now that it's set to zero, everything's happy.

share|improve this answer
    
I mentioned this in my answer before. –  RolandoMySQLDBA Sep 28 '12 at 21:32
    
When sync_binlog is not zero it forces a flush of the binlogs to disk ahead of the OS. I hope my answer did not make matters worse for you. –  RolandoMySQLDBA Sep 28 '12 at 21:34
    
Well ... the thing is, I don't mind if the slaves lose a second of replication, we can resync them if necessary. But it was really, REALLY slowing them down. But we're upgrading the master this weekend and I'm going to keep sync_binlog at 0 because we simply can't have that kind of slowdown. Any idea WHY we would have that kind of slowdown? If it's a widespread thing then you'd think there'd be remedies, but if it's just us then I don't know why. –  Andrew Sep 28 '12 at 21:37
add comment

If the binlog was the root of the problem, then I would look at

  • SHOW VARIABLES LIKE 'innodb%';
  • The disk config (RAID, SSD, write cache, etc)
  • SHOW VARIABLES LIKE 'sync_binlog';
share|improve this answer
    
innodb would be irrelevant, I hope, at this point because I have InnoDB disabled on that slave. The disk config is no different from how it was on 5.0, but at this moment does not involve SSDs or RAID. As for sync_binlog, it is set to '1'. –  Andrew Jul 2 '12 at 13:16
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.