Can MySQL Replication catch up

So replication was recently improved in MySQL 5.6. However, people are still using 5.1 and 5.5 so some of those improvements will have to wait to hit the real world.

I recently helped move in this direction with a geo-located replication solution. One part of the country had a MySQL 5.1 server and the other part of the country had a new MySQL 5.6 server installed.

After dealing with the issues of getting the initial data backup from the primary to the secondary server (took several hours to say the least), I had to decide could replication catch up and keep up. The primary server had some big queries and optimization is always a good place to start. I had to get the secondary server pulling and applying as fast as I could first though.

So here are a few things to check and keep in mind when it comes to replication. I have added some links below that help support my thoughts as I worked on this.

Replication can be very I/O heavy. Depending on your application. A blog site does not have that many writes so the replication I/O is light, but a heavily written and updated primary server is going to lead to a replication server writing a lot of relay_logs and binary_logs if they are enabled. Binary logs can be enabled on the secondary to allow you to run backups or you might want this server to be a primary to others.

I split the logs onto a different data partition from the data directory.
This is set in the my.cnf file - relay-log

The InnoDB buffer pool was already set to a value over 10GB. This was plenty for this server.
The server was over 90,000 seconds behind still.

So I started to make some adjustments to the server and ended up with these settings in the end. Granted every server is different.

mysql> select @@sync_relay_log_info\G
*************************** 1. row ***************************
@@sync_relay_log_info: 0
1 row in set (0.08 sec)

mysql>  select @@innodb_flush_log_at_trx_commit\G
*************************** 1. row ***************************
@@innodb_flush_log_at_trx_commit: 2
1 row in set (0.00 sec)

mysql> select @@log_slave_updates\G
*************************** 1. row ***************************
@@log_slave_updates: 0

mysql> select @@sync_binlog\G
*************************** 1. row ***************************
@@sync_binlog: 0
1 row in set (0.00 sec)

mysql> select @@max_relay_log_size\G
*************************** 1. row ***************************
@@max_relay_log_size: 268435456

 I turned binary logging off as I monitored different settings and options to help replication catch up. It took a while. Some of the settings you see above may or may not have been applied as I worked within this time frame. Yet it did catch up to 0 seconds back. Now you might notice that a lot of these settings above relate in and around the binary logging.  So I ran a little test. So, I restarted and enabled the bin logs. I checked in on the server later and found it 10,000+ seconds behind.  So I once again restarted and disabled the bin logs.  It caught up (0 seconds behind) with the primary server in under 15 minutes. I used Aurimas' tool as I watched it catch up as well. If you have not use it before, it is a very nice and handy tool.

What this all means is the primary server must be ACID compliant. With this set up you are also depending on the OS for cache and clean up. This is server will be used as a primarily read server to feed information to others. It also means that yes, geo-located replication can stay up to date with a primary server.

What if you need to stop the slave, will it still catch up quickly?

How and why do you stop the slave is my first response.  You should get into the habit of using  STOP SLAVE SQL_THREAD; instead of STOP SLAVE;  This allows the relay logs to continue to gather data and just not apply it to your primary server.  So if you can take advantage of that it will help reduce the time it takes for you to populate the relay logs later. 

Some additional reading for you:

• http://www.mysqlperformanceblog.com/2011/07/29/reasons-for-mysql-replication-lag/
• http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
• http://www.mysqlperformanceblog.com/2012/08/29/heres-a-quick-way-to-foresee-if-replication-slave-is-ever-going-to-catch-up-and-when/

How to tune the MySQL Server

This blog post is part of blog series

• MySQL Server Tuning Question
• How to Identify MySQL Slow Queries and related concerns
• How to tune the MySQL Server 

To continue now with the server itself:

Well besides just saying do what Peter says, lets review examples of how they can solve this themselves.

For starters, you can compare you my.cnf file against the Q&A version that is available for free via Percona.  It this an ideal solution? No, but it will allow you to take a fresh look at your configuration file after you answers all of their questions, via their configuration wizard.

• https://tools.percona.com/wizard

innodb_buffer_pool_size –

select @@innodb_buffer_pool_size;

Setting the innodb_buffer_pool_size is by far one of the most important settings for a MySQL InnoDB database.Some good reading on this topic is below:

• http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
• http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

You need to keep in mind that there is not a "one size fits all query" for setting the innodb_buffer_pool_size.  We do have some common guidelines and suggestions that have surfaced over the years (seen below) and we also have several different theories about the best options.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;


SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;


It is safe to say that if you have the default 8M set as your innodb_buffer_pool_size  variable then this should be one of the first settings you adjust.

Basically, allow this to have all of the available memory you can afford to give it. This of course takes into account several factors, like what else needs to allocate memory on the server ?  Keep in mind that Disk I/O is important to performance and the more memory you allow this variable to have the less disk I/O  should be required for table access.

Let us also consider this....
Since a general recommendation is to set the innodb_log_file_size at  25 % of the buffer pool size we should evaluate what we have for the innodb_log_file_size per Baron's concepts then look to see how the relates to the innodb_buffer_pool_size.

For example, following the logic from Baron's post:

> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)

1 row in set (59.99 sec)

Log sequence number 3991367755
1 row in set (0.01 sec)

> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour  |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
|         1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
|           1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)



Now when you compare these results to the "guidelines"

SELECT CONCAT(ROUND(KBS/POWER(1024,
    -> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
    -> SUBSTR(' KMG',IF(PowerOf1024<0,0,
    -> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
    -> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
    -> WHERE engine='InnoDB') A,
    -> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G                                  |
+-------------------------------------+


You get a different result.   While Baron's check is dependent on the time frame it is executed within (which is why you should check this during peak traffic)  it can give you a more realistic view on YOUR traffic and use.  I would look into setting the  innodb_buffer_pool_size to 2G not 1G in this simple example.

SET @sequence1=3982683217;
SET @sequence2=3991367755;
select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;



innodb_log_file_size–

select @@innodb_log_file_size;

I have found that I enjoy Baron's concepts on how to set the innodb_log_file_size.  Remember to execute his suggestions during peak times to get true readings. The larger the size of these files the better performance with large data sets but nothing comes for free, you will increase the recovery times. Increased recovery times might not sound like a major concern to some, until it is a revenue dependent database that you are waiting and watching process forever.  A general recommendation is to set it at  25 % of the buffer pool size.

select ( @@innodb_buffer_pool_size * .25 )

• http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
• http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/

innodb_log_buffer_size –

select @@innodb_log_buffer_size; 

I recall I once had this set on a server to innodb_log_buffer_size=128M.  Was it a good choice? No it means I wanted memory when I likely did not have to. It was a very write heavy database but this setting was very likely to high. Focus on the defaults first and then double it ( 8MB - 16MB ) max.


innodb_additional_mem_pool_size–

select @@innodb_additional_mem_pool_size;

Peter addresses this setting in his blog post. You can address it and evaluate options after other settings have been addressed in my opinion.


innodb_flush_log_at_trx_commit–

select @@innodb_flush_log_at_trx_commit;

> set GLOBAL innodb_flush_log_at_trx_commit =2; 
Peter addresses this setting in his blog post as well. This is a commonly overlooked variable and worth testing performance improvement by adjusting from 0 - 2. Keep in mind the risks.


thread_cache –

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
|                  50 |
+---------------------+

• http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 4     |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 962   |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
|                 99.5842 |
+-------------------------+



Keep an eye on this status : show status like 'threads_created';
If it climbs then you have it set too low and it needs to be raised.


query_cache_size  –

select ((@@query_cache_size / 1024) / 1024);

The permissible values are multiples of 1024.

• http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_size

Pay attention to this setting the more read dependent you application becomes.  I used to run with it set to key_buffer=16M and I could and likely should have doubled that.


key_buffer_size –

select @@key_buffer_size;

With MySQL switching to InnoDB as the default storage engine it is likely some we could see fewer and fewer tables created as MyISAM. That is not a fact just an opinion. The key_buffer_size will very important to you if you do utilize MyISAM tables.

Review Shlomi Noach's post to help you find which easily which tables which engine type.
"See per table size (almost exactly as presented in INFORMATION_SCHEMA)" -- Shlomi Noach.

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+


table_cache –

show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 4096  |
| table_open_cache           | 4096  |
| table_open_cache_instances | 1     |
+----------------------------+-------+


According to the manual this is a good formula to help determine the table_definition_cache size.

SELECT 400 + (@@table_open_cache / 2);

> SHOW status like '%Opened_tables%';


table_open_cache_instances
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores, the default is 1.


Hopefully this exposes the variables and settings that need to be reviewed to help you get the best from your MySQL database.