0

I'm really confused. We have a process - admittedly inefficient, and I'm fixing it - that runs about a quarter million tiny update queries. These finished on the master server, which is on MySQL 5.0, in half an hour; the newly-upgraded MySQL 5.5 slave has been working on them for six hours. The key seems to be "query end" - each one spends over a tenth of a second in this state on the slave, which is really dragging things down as 10 queries a second means, well... six hours. The master spends less than .06 seconds in the entire query, and this one is spending .13 seconds (89% of the query) in "query end".

Did 5.5 change something that my 5.0 configuration is interfering with? I'm at my wits end, as this is really starting to slow down some reports we have that are inefficient like this. I will change the reports, but I also want to find out what went wrong.

Before you ask: The 5.5 slave is still doing everything in MyISAM, so that hasn't changed. In fact, the configuration is generally identical to what the other, still 5.0, slave has, and that slave also finished in half an hour.

Andrew
  • 5,095
  • 6
  • 42
  • 48
  • http://stackoverflow.com/questions/3722510/why-does-this-simple-mysql-insert-query-take-occasionally-so-long – vearutop Jun 27 '12 at 21:07
  • Thanks for the link, but unfortunately none of the responses seem relevant to me. – Andrew Jun 27 '12 at 21:20
  • Is this a 5.0 vs a 5.5 issue or is it really a master vs slave issue? insert/updates have different runtime characteristics for these. – TerryE Jun 28 '12 at 11:23
  • The fact that it used to be perfectly fast, yes, it's a 5.0 vs 5.5 issue. – Andrew Jun 28 '12 at 12:33
  • Ive been batteling with the same problem on 5.6. Ive tried changing pretty much everything in flush/commit settings and all different fixes found on stackoverflow/internet. I still face the problem. Every 5 minute the insert will lock for about 160 seconds on a simple insert. – Mad Dog Tannen May 13 '13 at 11:46

1 Answers1

0

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.

Andrew
  • 5,095
  • 6
  • 42
  • 48