It is easy for MySQL replication to become bottleneck when Master server is not seriously loaded and the more cores and hard drives the get the larger the difference becomes, as long as replication
remains single thread process. At the same time it is a lot easier to optimize your system when your replication runs normally – if you need to add/remove indexes and do other schema changes you probably would be looking at some methods involving replication if you can’t take your system down. So here comes the catch in many systems – we find system is in need for optimization when replication can’t catch up but yet optimization process we’re going to use relays on replication being functional and being able to catch up quickly.
So the question becomes how can we estimate replication capacity, so we can deal with replication load before slave is unable to catch up.
Need to replication capacity is not only needed in case you’re planning to use replication to perform system optimization, it is also needed on other cases. For example in sharded environment you may need to schedule downtime or set object read only to move it to another shard. It is much nicer if it can be planned in advance rather than done on emergency basics when slave(s) are unable to catch up and application is suffering because of stale data. This especially applies to Software as Service providers which often have very strict SLA agreements with their customers and which can have a lot of data per customer so move can take considerable amount of time.
So what is replication capacity I call replication capacity the ability to replicate the master load. If replication is able to replicate 3 times the write load from the master without falling behind I will call it replication capacity of 3. When used with context of applying binary logs (for example point in time recovery from backup) replication capacity of 1 will mean you can reply 1 hour worth of binary logs within 1 hour. I will call “replication load” the inverse of replication capacity – this is basically what percentage of time the replication thread was busy replicating events vs staying idle.
Note you can speak about idle replication capacity, when box does not do anything else as well as loaded replication capacity when the box serves the normal load. Both are important. You care about idle replication capacity when you have no load on the slave and need it to catch up or when restoring from backup, the loaded replication capacity matters during normal operation.
So we defined what replication capacity is. There is however no tools which can tell us straight what replication capacity is for the given system. It also tends to float depending on the load similar as loadavg metrics. Here are some of the ways to measure it:
1) Use “UserStats” functionality from Google patches, which is now available in Percona Server and MariaDB. This is the probably the easiest and most accurate approach but it
does not work in Oracle MySQL Server. set userstat_running=1 and run following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql> select * from information_schema.user_statistics where user="#mysql_system#" \G *************************** 1. row *************************** USER: #mysql_system# TOTAL_CONNECTIONS: 1 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 446 BUSY_TIME: 74 CPU_TIME: 0 BYTES_RECEIVED: 0 BYTES_SENT: 63 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 0 ROWS_UPDATED: 127576 TABLE_ROWS_READ: 4085689 SELECT_COMMANDS: 0 UPDATE_COMMANDS: 119127 OTHER_COMMANDS: 89557 COMMIT_TRANSACTIONS: 90259 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 1 LOST_CONNECTIONS: 0 ACCESS_DENIED: 0 EMPTY_QUERIES: 0 1 row in set (0.00 sec) |
In this case CONNECTED_TIME is 446 second, out of this replication thread was busy (BUSY_TIME) 74 seconds which means replication capacity is 446/74 = 6
You normally would not like to measure it from the start but rather take the difference in these counters every 5 minutes or other interval of your choice.
2) Use full slow query log and mk-query-digest. This method is great for one time execution especially as it comes together with giving you the list of queries which load replication
the most. It however works only with statement level replication. You need to set long_query_time=0 and log_slave_slow_statements=1 for this method to work.
Get the log file which will include all queries MySQL server ran with their times and run mk-query-digest with filter to only check queries from replication thread:
mk-query-digest slow-log –filter ‘($event->{user} || “”) =~ m/[SLAVE_THREAD]/’ > /tmp/report-slave.txt
In the report you will see something like this as a header:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # 475s user time, 1.2s system time, 80.41M rss, 170.38M vsz # Current date: Mon Jul 19 15:12:24 2010 # Files: slow-log # Overall: 1.22M total, 1.27k unique, 558.56 QPS, 0.37x concurrency ______ # total min max avg 95% stddev median # Exec time 819s 1us 92s 669us 260us 120ms 93us # Lock time 28s 0 166ms 23us 49us 192us 25us # Rows sent 4.27k 0 325 0.00 0 1.04 0 # Rows exam 30.88M 0 1.28M 26.48 0 3.07k 0 # Time range 2010-07-19 14:35:53 to 2010-07-19 15:12:22 # bytes 350.99M 5 1022.34k 301.01 719.66 5.75k 124.25 # Bytes sen 1.94M 0 9.42k 1.67 0 110.38 0 # Killed 0 0 0 0 0 0 0 # Last errn 34.11M 0 1.55k 29.26 0 185.83 0 # Merge pas 0 0 0 0 0 0 0 # Rows affe 875.19k 0 17.55k 0.73 0.99 25.61 0.99 # Rows read 2.20M 0 14.83k 1.88 1.96 24.68 1.96 # Tmp disk 4.15k 0 1 0.00 0 0.06 0 # Tmp table 14.19k 0 2 0.01 0 0.14 0 # Tmp table 8.30G 0 2.01M 7.12k 0 117.75k 0 # 0% (5k) Filesort # 0% (5k) Full_join # 0% (7k) Full_scan # 0% (10k) Tmp_table # 0% (4k) Tmp_table_on_disk |
There is a lot of interesting you can find out from this header but in relation to replication capacity – you can get replication load, which is same as “concurrency” figure (0.37x) The concurrency as reported by mk-query-digest is sum of query execution time vs time range the log file covers. In this case as we know there is only one replication thread it will be same as replication load. This gives us replication capacity of 1/0.37 = 2.70
This method should work with original MySQL Server in theory, though I have not tested it. Some versions had log_slave_slow_statements unreliable and also you may need to adjust regular expression for finding users replication thread uses.
3) Processlist Polling This method is simple – the Slave thread has different status in Show Processlist depending on if it processes query or simply waiting. By pooling processlist frequently (for example 10 times a second) we can compute the approximate percentage the thread was busy vs idle. Of course running processlist very aggressively can be an overhead especially if it is busy system with a lot of connections
1 2 3 4 5 6 7 8 9 | mysql> show processlist; +--------+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ | 801812 | system user | | NULL | Connect | 2665 | Waiting for master to send event | NULL | | 801813 | system user | | NULL | Connect | 0 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | | 802354 | root | localhost | NULL | Query | 0 | NULL | show processlist | +--------+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) |
4) Slave Catchup/Binlog Application method. We can just get the spare server with backups restored on it and apply binary log to it. If 1 hour worth of binary logs applies for 10 minutes we have replication capacity of 6. The challenge of course having spare server around and it is quite labor intensive. At the same time it can be good measurement to take during backup recovery trials when you’re doing this activity anyway. Using this way you can also measure “cold” vs “hot” replication capacity as well as how long replication warmup takes. It is very typical for servers with cold cache to perform a lot slower then they are warmed up. Measuring times for each binary log separately should give you these numbers.
The less intrusive process which can be done in production (especially if you have slave which is used for backups/reporting etc) is to stop the replication for some time and when see how long it takes to catch up. If you paused replication for 10 minutes and it took 5 minutes to catch up your replication capacity will be 3 (not 2) because you not only had to process the events for outstanding 10 minutes but also for these 5 minutes it took to catch up. The formula is (Time_Replication_Paused+Time_Took_To_Catchup)/Time_Took_To_Catchup.
So how much of replication capacity do you need in the healthy system ? It depends a lot on many things including how fast do you need to be able to recover from backups and how much your load variance is. A lot of systems have special requirements on the time it takes to warmup too (there are different things you can do about it too). First I would measure replication capacity on 5 minute intervals (or something similar) because it tends to vary a lot. When I would suggest to ensure the loaded replication capacity is at least 3 during the peak load and 5 during the normal load. This applies to normal operational load – if you push heavy ALTER TABLE through replication they will surely get your replication capacity down for their duration.
One more thing about these methods – methods 1,2,3 work well only if replication capacity is above 1, so system is caught up. If it is less than 1, so the master writes more binary logs than slave can process they will show number close to 1. the method 4 however with work even if replication can’t ever catch up – If 1 hour worth of binary logs takes 2 hours to apply, your replication capacity is 0.5.
Hi Everyone,
Perhaps a bit off topic, but I want to know if there are any alternatives to mysql replication.
Since I’m on a shared host that does allow replication, I wondered if there are any other methods to get databases in sync ( besides from dumping and exporting data).
Looking forward for your answers 🙂
Hi,
The MySQL Built in replication is the most common way to do it. The Continuent Tungsten Replicator is the most known alternative product. There is Galera replication (synchronous) in development. DRBD is somethat replication, if you need it for high availability – replication happens on disk level and you can’t start MySQL on the “slave” until Master is down.
I’m trying to estimate the slave load using the UserStats method. But it doesn’t reflect the reality. Does it work well with row-level replication?
My testcase:
STOP SLAVE SQL_THREAD;
SELECT SLEEP(600);
START SLAVE;
Then i watch the counters while the slave is catching up, but I see only a slight progress, like 1 second of BUSY_TIME in 5 minutes while the slave is working. I see really high CPU utilization in htop for mysql, so it’s really working hard to apply the relay log events.
I forgot to mention the version, it’s Percona Server 5.5.18-55-log