1

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.

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
Jon M
  • 457
  • 1
  • 6
  • 11
  • You aren't running MyISAM tables are you? Quit doing that and convert them to InnoDB. :) Then add `--single-transaction` to your `mysqldump` and watch your backup performance improve dramatically. – Michael Hampton Mar 26 '13 at 12:45
  • Only use single-transaction when you have only InnoDB tables. As Michael states, it will greatly improve performance. – daemonofchaos Mar 26 '13 at 18:20
  • We have both InnoDB and some MyISAM tables at the moment. Is there a negative impact to using --single-transaction where MyISAM tables are present or does it not affecting them? – Jon M Mar 27 '13 at 10:27

1 Answers1

2

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 !!!

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • Is there a way to check without enabling binlogging? Essentially, I'm thinking of looking at counters for data transferred of queries or similar. – Ztyx Mar 08 '16 at 16:52