How can we solve the problem that the primary database cannot be read from the database in a timely manner after inserting records, and how to avoid this problem from the architecture? I have seen a new version database table on the Internet, then, use mysqlproxy to check whether the data is up-to-date and then route it to the master database or slave database. is this solution feasible? How do I perform this operation? How to solve the problem that records cannot be read from the database in a timely manner after the primary database is inserted, and how to avoid this problem from the architecture
I have seen a table for creating a version database on the Internet, and then use mysql proxy to judge whether the data is up-to-date and then route it to the master database or slave database. is this solution feasible? How do I perform this operation? Reply content:
From the scenario you described, you need to ensure that the written data can be read on the slave machine after the host is written. that is to say, you needStrong Consistency in master-slave architecture.
The physical latency between the host and the slave is uncontrollable and unavoidable. However, it is relatively simple to satisfy such strong consistency: When writing data to the host, confirm that the update has been synchronized to the slave, and then return the write operation. All mainstream databases support this full synchronization mode. Some people have mentioned the MySQL Semi-sync feature (officially supported since MySQL5.6, and unofficial patches can be considered in earlier versions) based on this principle.
However, this synchronization mode is generally not recommended. Obviously, if the write operation has to wait for the update synchronization to complete, it will definitely significantly affect the performance, unless you do not care about the performance.
The key to the problem is that the master-slave architecture is used for data error tolerance.High AvailabilitySolution, rather than processingHigh concurrency pressure. It aims to enable the standby server to immediately overhead the standby server after it is ready, rather than letting the standby server share the concurrency pressure. The full synchronization mechanism is only used to ensure that the data of the host will not be lost after the host is used, rather than to ensure the consistency when the data is read from the slave machine. Therefore, I do not advocate that you use the method of reading data from the slave to share the concurrency pressure.
The solution is not to try to solve concurrent read operations at the database layer, or at least not at the database layer of the master-slave architecture. A distributed cache such as redis should be built on the database layer, which is dedicated to solving this problem. Its performance is certainly far higher than reading data from the slave.
Distributed Cache also has some limitations, for example, it cannot fully support transaction processing. It depends on your application scenario. For general Internet applications, distributed cache can be considered because of high concurrency pressure but no need to support transactions. For applications that strictly require high consistency such as banks, there is generally no requirement for write latency (the latency is acceptable for several hours, and data can only be written without errors), and the full synchronization mode can be applied.
In addition, it is not recommended that you use the version database to identify the latest version and then route it to the host or slave server separately. This will cause serious pollution to the application layer code.
MySQL replication
In my experience, the most important thing is to try to avoid full running of any mysql server.
Therefore, the real solution is precise capacity planning to scale out in a timely manner. Do not rely on master-slave synchronization, for example, the multi-master solution. Master-slave synchronization latency is inevitable, not a problem. The key is to see the specific business, what problems are caused by synchronization latency, and then solve them.
A simple example
Assume that a forum is a master-slave database. I will refresh the page immediately after sending a post because the post is read. if the latency is high, the system will prompt 404 ---- The Post does not exist, this is a problem. we also need to assume that the user's tolerance is to see their new content, and other people's new content can have a delay (in fact, the delay is a very small unit of time ).
There are several solutions to this assumption:
1. data reading operations after updating data are performed from the default to the master database;
2. use the cache to insert new data. last_id will be returned, assembled into data, and cached to the front end. Read the id data from the cache. The solution mentioned by the subject is very unreliable.
However, mysql-proxy has almost no contact with me. it is not certain whether it can implement the appeal function. even if it does, it is not recommended to use it for this purpose, production environments are not recommended on the official website.
For master-slave latency, my experience is as follows:
When the master database can process the business, it should be placed in the master database. only disaster recovery and backup are performed from the master database, and statistical report jobs with low real-time requirements;
In general, wait for a while. operations such as restarting the database cannot be solved. it will take longer to roll back and redo large transactions.
- Cannot be solved with a delay of N days
Redo slave.
Why is the delay of N days? is it because of the single thread of the slave database?
I feel that most of them use the binlog_format of mixed on the master database. due to some restrictions, it is not possible to copy data in row mode based on statement.
If the current SQL statement is a full table scan, the full table scan is performed multiple times when it is uploaded to the slave.
Show proceslist on slave to check what the current system user is executing. this is the problematic SQL statement. If the pos point remains unchanged, you can go to the binlog corresponding to the master database to check what is executed.
- When latency occurs, view the cpu and disk status of the current slave.
Generally, if there is no other business in the slave database, it is already the limit that the cpu runs full of one core because of a single thread. If the disk I/O is full, check whether other processes or mysql threads have affected the disk (for example, the slave database is running dump or a large SQL statement ), you can also try to adjust the io parameters on the slave.
- The slave database RAID card must be set to write back.
I am deeply affected by this. I checked it for a few months and found out why my SSD io performance is so bad.
If batch dml operations are not processed, there is usually a delay. we recommend that you perform low-latency operations and adjust the batch operations. one dml row contains 10000 rows, sleep for a while, and then dml 10000 rows.
The specific number of rows and sleep must be determined based on the business, so that the slave database is not delayed.
- If there is a regular short delay, increase the hardware configuration of the slave database, such as sata SSD and pcie.
- Latency monitoring is in place. you can use pt-heart-beat to accurately monitor the latency value and promptly discover and view it.
- For versions later than 5.5, semi-synchronous replication can be considered to solve the problem caused by a small amount of latency, but the performance loss of tps is high.
- Upgrade mysql 5.7 to multi-thread replication, which perfectly solves the slave database latency caused by single-thread replication.
Full synchronization is a very expensive and complex operation. if the load is large, it is almost impossible to complete it. Therefore, the smart way is to adjust the upper-layer logic to avoid this need. Redis technology can be used,
Sina WeiboRedis technology is widely used. What is different from Memcached is that redis periodically writes updated data to the disk or writes modifications to the append record file, and implements master-slave synchronization. This requires assuming the Redis server. For details, see
Yang Haichao, chief DBA of Sina NetworkVideo and PPT http://blog.nosqlfan.com/html/2692.html
. Add a nosql layer in the middle
You can try to use the active replication technology to solve the problem of replication latency between MySQL master and slave databases. for example, Twitter also specially developed the middleware gizzard for replication and partitioning (twitter/gizzard · GitHub
).
Although active replication solves the latency problem of passive replication, it also brings about a new problem, that is, data consistency.
Delay can be improved and cannot be eliminated.
You can look at this: Percona, http://www.percona.com/
It may meet your requirements...
