Continuing on the series of blog posts about MySQL High Availability, today we will talk about stale reads and how to overcome this issue.
The Problem
Stale reads is a read operation that fetches an incorrect value from a source that has not synchronized an update operation to the value (source Wiktionary).
A practical scenario is when your application applies INSERT or UPDATE data to your master/writer node, and has to read it immediately after. If this particular read is served from another server in the replication/cluster topology, the data is either not there yet (in case of an INSERT) or it still provides the old value (in case of an UPDATE).
If your application or part of your application is sensitive to stale reads, then this is something to consider when implementing HA/load balancing.
How NOT to fix stale reads
While working with customers, we have seen a few incorrect attempts to fix the issue:
SELECT SLEEP(X)
The most common incorrect approach that we see in Percona support is when customers add a sleep between the write and the read. This may work in some cases, but it’s not 100% reliable for all scenarios, and it can add latency when there is no need.
Let’s review an example where by the time you query your slave, the data is already applied and you have configured your transaction to start with a SELECT SLEEP(1). In this case, you just added 1000ms latency when there was no need for it.
Another example could be when the slave is lagging behind for more than whatever you configured as the parameter on the sleep command. In this case, you will have to create a login to keep trying the sleep until the slave has received the data: potentially it could take several seconds.
Reference: SELECT SLEEP.
Semisync replication
By default, MySQL replication is asynchronous, and this is exactly what causes the stale read. However, MySQL distributes a plugin that can make the replication semi-synchronous. We have seen customers enabling it hoping the stale reads problem will go away. In fact, that is not the case. The semi-synchronous plugin only ensures that at least one slave has received it (IO Thread has streamed the binlog event to relay log), but the action of applying the event is done asynchronously. In other words, stale reads are still a problem with semi-sync replication.
Reference: Semisync replication.
How to PROPERLY fix stale reads
There are several ways to fix/overcome this situation, and each one has its pros and cons:
1) MASTER_POS_WAIT
Consists of executing a SHOW MASTER STATUS right after your write, getting the binlog file and position, connecting on a slave, and executing the SELECT MASTER_POS_WAIT function, passing the binlog file and position as parameters. The execution will block until the slave has applied the position via the function. You can optionally pass a timeout to exit the function in case of exceeding this timeout.
Pros:
- Works on all MySQL versions
- No prerequisites
Cons:
- Requires an application code rewrite.
- It’s a blocking operation, and can add significant latency to queries in cases where a slave/node is too far behind.
Reference: MASTER_POS_WAIT.
2) WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
Requires GTID: this is similar to the previous approach, but in this case, we need to track the executed GTID from the master (also available on SHOW MASTER STATUS).
Pros:
- Works on all MySQL versions.
Cons:
- Requires an application code rewrite.
- It’s a blocking operation, can add significant latency to queries in cases where a slave/node is too far behind.
- As it requires GTID, it only works on versions from 5.6 onwards.
Reference: WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
3) Querying slave_relay_log_info
Consists of enabling relay_log_info_repository=TABLE and sync_relay_log_info=1 on the slave, and using a similar approach to option 1. After the write, execute SHOW MASTER STATUS, connect to the slave, and query mysql.slave_relay_log_info , passing the binlog name and position to verify if the slave is already applying a position after the one you got from SHOW MASTER STATUS.
Pros:
- This is not a blocking operation.
- In cases where the slave is missing the position you require, you can try to connect to another slave and repeat the process. There is even an option to fail over back to the master if none of the slaves have the said position.
Cons:
- Requires an application code rewrite.
- In cases of checking multiple slaves, this can add significant latency.
Reference: slave_relay_log_info.
4) wsrep-sync-wait
Requires Galera/Percona XtraDB Cluster: Consists of setting a global/session variable to enforce consistency. This will block execution of subsequent queries until the node has applied all write-sets from it’s applier queue. It can be configured to trigger on multiple commands, such as SELECT, INSERT, and so on.
Pros:
- Easy to implement. Built-in as a SESSION variable.
Cons:
- Requires an application code rewrite in the event that you want to implement the solution on per session basis.
- It’s a blocking operation, and can add significant latency to queries if a slave/node is too far behind.
Reference: wsrep-sync-wait
5) ProxySQL 2.0 GTID consistent reads
Requires MySQL 5.7 and GTID: MySQL 5.7 returns the GTID generated by a commit as part of the OK package. ProxySQL with the help of binlog readers installed on MySQL servers can keep track of which GTID the slave has already applied. With this information + the GTID received from the OK package at the moment of the write, ProxySQL will decide if it will route a subsequent read to one of the slaves/read nodes or if the master/write node will serve the read.
Pros:
- Transparent to the application – no code changes are required.
- Adds minimal latency.
Cons:
- This still a new feature of ProxySQL 2.0, which is not yet GA.
Referece: GTID consistent reads.
Conclusions
Undesirable issues can arise from adding HA and distributing the load across multiple servers. Stale reads can cause an impact on applications sensitive to them. We have demonstrated various approaches you can use to overcome them.