Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  KayakJim 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? –  Jenkz Mar 27 '13 at 10:27
add comment

1 Answer 1

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.