MySQL performance problem with replication

From: Thomas Hurst <tom.hurst_at_clara.net>
Date: Thu, 2 Aug 2007 04:56:03 +0100
For the past month or so we've been testing CURRENT with MySQL 5.1.20 as
one of our replicated slaves.

Performance has been very good, for the most part; easily comparable to
Linux and Solaris, and finally something we can seriously look at
running in production.  Many thanks to everyone who's helped make this
happen!

The one problem we have noticed is with replication.   This graph shows
it quite well:

  http://voi.aagh.net/db5.rbsov-daily-cpu.png

You can see a steady rise in system time over a 9 hour period until it's
so high replication falls behind and it drops out of load.  The sudden
drop after many hours of being pegged with a single CPU is me doing a
STOP SLAVE;START SLAVE -- system time returns to normal, and all is
well.

It's easier to see in top(1); after an hour or so, the replication
thread will be using about 5% more CPU than expected, and this will rise
slowly until SHOW PROCESSLIST shows it spending most of its time in
'init' and top's IO display shows 100,000+ VCSW (when it's normally rare
to see it pass 20,000).  ktrace does not show much; a couple of times
I've seen ~10 umtx ops repeating in groups rather a lot (when 2 would be
more normal) but this isn't particularly repeatable.

I've verified this behavior with ULE (2.0 and 3.0 + ulehtt+topology
patches), and 4BSD, however it doesn't occur on Linux 2.6.20.4 or in
another CURRENT machine with MySQL 4.1.  I had planned on testing with
libthread, but sadly the system I was testing on has now been replaced
with Linux.

Some numbers: 2 * Opteron 285 (dual core 2.6GHz), 16GB memory, 24 SAS
disks in RAID10 with gmirror+gstripe, running UFS2.  We see about 80
UPDATE and 180 INSERTs per second, with >10 million row InnoDB and
MyISAM tables (and most of those MyISAM tables in a 900 million row
MERGE).

I'll be migrating our other CURRENT test MySQL server to 5.1 for further
testing shortly.

-- 
Thomas 'Freaky' Hurst
    http://hur.st/
Received on Thu Aug 02 2007 - 01:56:06 UTC

This archive was generated by hypermail 2.4.0 : Wed May 19 2021 - 11:39:15 UTC