Mysql – How to estimate binlog size and whether binlogs should be on a separate disk

loggingMySQLmysql-replicationraid

We are about to setup MySQL Master-Slave replication on our production database. We intend to run backups from the slave (at the moment our master lags out for 15 mins every day whilst backups are run, taking our website offline).

  • We run an average of 800 qps
  • 95%+ of queries are SELECT statements

How can I estimate how fast the bin log will grow? What other information would be needed?

Our MySQL master database server currently has 4x 15k SAS disks in RAID 10.

Should I consider adding a separate RAID 1 (2 disks) array to hold the bin logs? I am concerned that writing to the bin log will slow down reads & writes to the main mysql data store.

Best Answer

There are two ways to figure that out

EXAMPLE #1

Supposed the binary logs are defined as follows:

[mysqld]
log-bin=mysql-bin

Go into the OS and do this:

cd /var/lib/mysql
ls -lt --time=access --time-style=+"%s" mysql-bin.* | awk '{print $7,$6}'

This will show you the binary logs and their respective UNIX timestamps

Simply subtract one timestamp from the previous one

ls -l mysql-bin.0*
-rw-rw---- 1 mysql mysql 1073754057 Mar 25 18:27 mysql-bin.000630
-rw-rw---- 1 mysql mysql 1074289363 Mar 25 22:16 mysql-bin.000631
-rw-rw---- 1 mysql mysql 1073746572 Mar 26 07:20 mysql-bin.000632
-rw-rw---- 1 mysql mysql  747478886 Mar 26 11:31 mysql-bin.000633

ls -l --time=access --time-style=+"%s" mysql-bin.* | awk '{print $7,$6}'
mysql-bin.000630 1364250429
mysql-bin.000631 1364264182
mysql-bin.000632 1364296842
mysql-bin.000633 1364311833

What can you compute ?

  • The time for mysql-bin.000631 is 13753 sec (1364264182 - 1364250429), 3:49:53
  • The time for mysql-bin.000632 is 32660 sec (1364296842 - 1364264182), 9:04:20
  • The time for mysql-bin.000633 is 14991 sec (1364311833 - 1364296842), 4:09:51

You would have to script this computations for each binlog.

EXAMPLE #2

If you want to examine just the binary log alone, you can do this:

mysqlbinlog mysql-bin.000632 > binlogsql.txt
T1=`head -20 binlogsql.txt | grep "SET TIMESTAMP" | head -1 | sed 's/=/ /' | sed 's/\// /' | awk '{print $3}'`
T2=`tail -20 binlogsql.txt | grep "SET TIMESTAMP" | tail -1 | sed 's/=/ /' | sed 's/\// /' | awk '{print $3}'`
(( TT = T2 - T1 ))
echo ${TT}

Look what it does:

[root@*** mysql]# mysqlbinlog mysql-bin.000632 > binlogsql.txt
[root@*** mysql]# T1=`head -20 binlogsql.txt | grep "SET TIMESTAMP" | head -1 | sed 's/=/ /' | sed 's/\// /' | awk '{print $3}'`
[root@*** mysql]# T2=`tail -20 binlogsql.txt | grep "SET TIMESTAMP" | tail -1 | sed 's/=/ /' | sed 's/\// /' | awk '{print $3}'`
[root@*** mysql]# (( TT = T2 - T1 ))
[root@*** mysql]# echo ${TT}
32660
[root@*** mysql]#

EPILOGUE

You can calculate the number of seconds each binlog takes to generate and do your number crunching from there

Give it a Try !!!

Related Topic