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@lists.mysql.com, replicat...@lists.mysql.com 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:k...@graffiti.net] > Sent: Wednesday, October 17, 2012 6:45 AM > To: mysql@lists.mysql.com; replicat...@lists.mysql.com > 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 inc! reasing 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 gr! owth 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
