Shulou(Shulou.com)06/01 Report--

The following mainly brings you how to deal with mysql slave database delay caused by no primary key in ROW mode. I hope these contents can bring you practical use. This is also the main purpose of this article when I edit mysql slave database delay caused by no primary key in ROW mode. All right, don't talk too much nonsense, let's just read the following.

Scene:

MySQL-5.6.30, master-slave architecture, read-only the SQL thread card of the slave library remained untouched for more than two hours in a transaction. Show processlist found that the slave library did not have connections and slow query statements at that time.

Show open TABLES where In_use > 0; found that a table is locked as follows:

Mysql > show open TABLES where In_use > 0 +-+ | Database | Table | In_use | Name_locked | +-+ | cxx | | t_post_xxxxxx | 1 | 0 | +-+ |

Conclusion

There is no thread in the slave library, indicating that the locked table is locked by statements synchronized from the master library, which should be caused by the large transaction operation of the master library on this table.

Analysis.

1. Through the position determined by show slave status to analyze the binlog of the main library, it is found that a large number of statements to delete t_post_xxxxxx have been generated.

two。 Check the slow query log and find delete from t_post_xxxxxx

3. Looking at the table structure of tweepostxxxxxxx in the main library, we found that there was no primary key and no index.

4.select count (*) from tasking postcards xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

5. The truth is clear, no primary key directly delete delete full table will generate more than 200,000 delete statements in binlog, no primary key synchronization to slave database need to perform more than 200,000 full table scans, 20W*20W=400 more than 100 million, scary!

When 6.MySQL synchronizes, it uses the primary key to search for rows that need to be modified (or some secondary indexes).

Solution

1. Increase primary key

two。 Communicate with R & D to change delete from t_post_xxxxxx to truncate table t_post_xxxxxx.

To sum up

Due to the lack of unified database online platform and code review mechanism, some non-standard code and database design are running in production. It is recommended to use a standardized database launch platform, which automatically discovers database design and database launch script problems. There will inevitably be omissions in human flesh launch, and automated operation and maintenance is imperative. The R & D team should use code specification checking tools to avoid non-standard code online.

For the above about the table in ROW mode without primary keys caused by mysql slave database delay how to deal with, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.