0

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?

6
  • 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.
    – randomx
    Jun 27, 2012 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, 2012 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.
    – randomx
    Jun 27, 2012 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, 2012 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, 2012 at 6:59

2 Answers 2

1

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.

3
  • I mentioned this in my answer before. Sep 28, 2012 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. Sep 28, 2012 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, 2012 at 21:37
0

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';
1
  • 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, 2012 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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