List:General Discussion« Previous MessageNext Message »
From:Kent Ho Date:October 17 2012 1:45pm
Subject:Unexpected gradual replication log size increase.
View as plain text  
Hi,

I have a Mysql replicate setup running for a while over 6 months and recent we had an
outage.  We fix it, bought the server back up and we spotted something peculiar and
worrying.   The replication logs are growing in size, all of a sudden on Tuesday 9th
Oct based on clues from monitoring graphs.  No changes recorded at the time that
might have triggered it.

The application using this database is Zenoss.  There are no surge of events or
activitiy AFAIK of in Zenoss and working as normal.  There's no extra load or work
added to the server.  CPU & load average are normal.  It's just that the IO
write to disk are increasing suddenly, creeping up slowly.

Next try find out why?  I'm not a mysql guru.  I've found a mysql bin log
analyser here:-
http://scale-out-blog.blogspot.co.uk/2010/01/whats-in-your-binlog.html
run it against the logs.

We noticed "Max. Event Bytes" are increasing approx. almost exactly by 58,590 bytes per
hour.

for i in `ls -1 mysql-bin.*` ; do ls -l $i ; /opt/zends/bin/mysqlbinlog $i |
./binlog-analyze.pl -q | grep "Max. Event Bytes" ; done
-rw-rw---- 1 zenoss zenoss 1076965696 Oct 17 01:07 mysql-bin.000210
Max. Event Bytes  :        10129066
-rw-rw---- 1 zenoss zenoss 1082897654 Oct 17 01:34 mysql-bin.000211
Max. Event Bytes  :        10156066
.
.
-rw-rw---- 1 zenoss zenoss 1075538619 Oct 17 07:14 mysql-bin.000224
Max. Event Bytes  :        10495366

On 15th Oct 22:05 GMT mysql-bin.000155 Max. Event Bytes reading was 8,553,166.
40 hours later today 17th Oct 14:00 GMT mysql-bin.000240 Max. Event Bytes reading is
10,896,766.

On 15th bin log growth rate was around 16MB per hour, today 50Mb per hour and increasing.
 Before 9th the bin log was constant size, it take several days to fill a 1Gb bin
log file.  Now I get about 72 files or 72Gb logs per day.   Now I've put in a
cron job to purge anything older then 12 hours and soon need to reduce that further at
the current rate.

What could be causing this?   All other atrributes on the system are same as it was
weeks or months ago.

I'm running Mysql "Ver 8.42 Distrib 5.5.15, for linux2.6 on x86_64" on RHEL5.6 Linux xxx
2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux.

Please let me know if need any further infomation, I'll grab and post.

Any help/info are highly appreciated.

Thanks in advance.

Kent.
Thread
Unexpected gradual replication log size increase.Kent Ho17 Oct
  • RE: Unexpected gradual replication log size increase.Rick James17 Oct
RE: Unexpected gradual replication log size increase.Kent Ho18 Oct
  • Re: Unexpected gradual replication log size increase.Dehua Yang18 Oct