List:General Discussion« Previous MessageNext Message »
From:Kent Ho Date:October 18 2012 9:37am
Subject:RE: Unexpected gradual replication log size increase.
View as plain text  
Thanks for the replies.

 After examining the logs carefully. We found several devices sending snmp traps to the
application making it composing large sql statements to mysql. Statment over a meg on
size on 1 line. We disabled those devices and the problems have gone away.

 Thanks.

 Kent.
----- Original Message -----
From: Rick James
Sent: 10/17/12 04:50 PM
To: Kent Ho, mysql@stripped, replication@stripped
Subject: RE: Unexpected gradual replication log size increase.

 Check that server_id is different between Master and Slave(s). Check other settings
relating to replication. > -----Original Message----- > From: Kent Ho
[mailto:kent@stripped] > Sent: Wednesday, October 17, 2012 6:45 AM > To:
mysql@stripped; replication@stripped > Subject: Unexpected gradual
replication log size increase. > > 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. > > -- > MySQL General Mailing List > For list archives:
http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql

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