Many a times batch jobs are written to do the following. In case of MySQL some of these can cause Master-Slave Replication lags -
1. Need to update a lot of rows
2. Need to select rows for modification
3. Need to insert and select
4. Need to execute batch jobs on master
Some of the ways to overcome this are detailed below.
1. Need to update a lot of rows
Solution -
We know MySQL Replication is single threaded. We should avoid any long running write query to prevent replication stream clog. One way to do this - Chop the query
Example -
If we need to update 10,000 rows then instead of doing this in the SAME query we can do this in say 10 Small and Short queries
2. Need to select rows for modification
If there is considerable effort needed to select rows for modification – spread it out and have separate select and update queries. In such case slave will only need to run UPDATE and not make Slave to do more work than it needs to do.
Example:
UPDATE posts SET spam=1 WHERE body LIKE "%cheap rolex%"; TO
SELECT id FROM posts WHERE body LIKE "%cheap rolex%";
UPDATE posts SET spam=1 WHERE id IN (list of ids)
3. Need to insert and select
Solution -
Best solution is to avoid INSERT … SELECT going through replication for reasons like long query time, waste of execution on slave, etc.
Other alternative is - Piping data through application is the best solution in many cases and is quite easy – it is trivial to write the function which will take SELECT query and the table to which to store its result set and use in your application in all cases when you need this functionality.
4. Need to execute batch jobs on master
We should not overload the replication. Quite typically we see replication lagging when batch jobs are running. These can load master significantly during their run time and make it impossible for slave to run the same load through single thread.
Solution -
i) Space out and slow down the batch jobs (such as adding sleep calls) to ensure there is enough breathing room for replication thread.
ii) Use controlled execution of batch job – If we can check the Slave lag every so often and pause if it becomes too large. This is a bit more complicated approach but it saves from adjusting the Sleep behavior to keep the progress fast enough and at the same time keep replication from lagging.
Refer -
http://www.mysqlperformanceblog.com/2008/09/22/fighting-mysql-replication-lag/
http://www.mysqlperformanceblog.com/2007/10/12/managing-slave-lag-with-mysql-replication/
1. Need to update a lot of rows
2. Need to select rows for modification
3. Need to insert and select
4. Need to execute batch jobs on master
Some of the ways to overcome this are detailed below.
1. Need to update a lot of rows
Solution -
We know MySQL Replication is single threaded. We should avoid any long running write query to prevent replication stream clog. One way to do this - Chop the query
Example -
If we need to update 10,000 rows then instead of doing this in the SAME query we can do this in say 10 Small and Short queries
2. Need to select rows for modification
If there is considerable effort needed to select rows for modification – spread it out and have separate select and update queries. In such case slave will only need to run UPDATE and not make Slave to do more work than it needs to do.
Example:
UPDATE posts SET spam=1 WHERE body LIKE "%cheap rolex%"; TO
SELECT id FROM posts WHERE body LIKE "%cheap rolex%";
UPDATE posts SET spam=1 WHERE id IN (list of ids)
3. Need to insert and select
Solution -
Best solution is to avoid INSERT … SELECT going through replication for reasons like long query time, waste of execution on slave, etc.
Other alternative is - Piping data through application is the best solution in many cases and is quite easy – it is trivial to write the function which will take SELECT query and the table to which to store its result set and use in your application in all cases when you need this functionality.
4. Need to execute batch jobs on master
We should not overload the replication. Quite typically we see replication lagging when batch jobs are running. These can load master significantly during their run time and make it impossible for slave to run the same load through single thread.
Solution -
i) Space out and slow down the batch jobs (such as adding sleep calls) to ensure there is enough breathing room for replication thread.
ii) Use controlled execution of batch job – If we can check the Slave lag every so often and pause if it becomes too large. This is a bit more complicated approach but it saves from adjusting the Sleep behavior to keep the progress fast enough and at the same time keep replication from lagging.
Refer -
http://www.mysqlperformanceblog.com/2008/09/22/fighting-mysql-replication-lag/
http://www.mysqlperformanceblog.com/2007/10/12/managing-slave-lag-with-mysql-replication/
No comments:
Post a Comment