Shulou(Shulou.com)05/31 Report--

Editor to share with you how to solve the problem of inconsistency in simulating MySQL self-addition through shell scripts. I hope you will gain something after reading this article. Let's discuss it together.

MySQL's self-adding problem is actually very interesting. After restarting the database, it will be calculated in the way of max (id) + 1. This seemingly awkward implementation has been criticized in the early version, but it was not solved in MySQL 5.7. finally, it was released in 8.0. it is planned to be fixed in this version.

Restart will bring potential problems such as self-increment, and if you do not restart, there may also be self-increment inconsistency. And the two parameters table_definition_cache and table_open_cache are still closely related.

What is the main reason? to quote the explanation of the Ali database kernel team (https://www.kancloud.cn/taobaomysql/monthly/67171): on the one hand, the self-increment of the InnoDB table is stored in the table object, and the table object is placed in the cache. If there are too many tables to put all in the cache, the old table will be replaced, and the replaced table will be reopened the next time it is used. For self-incrementing columns, this process is similar to instance restart, requiring select max (id) + 1 to calculate self-increment.

The cache size of table objects is controlled by the table_definition_cache system variable, with a minimum value of 400. another system variable related to table caching is table_open_cache, which controls the cache size of tables opened by all threads, which is cached in the server layer.

After looking at the environment of 5.6.14, I found that this value has risen to 500, and in MySQL 5.7, it has risen to 1400, which shows that a lot of efforts have been made in this regard.

Parameter values of MySQL 5.6.14

# mysqladmin var | grep table_open_cache

| | table_open_cache | 256 |

| | table_open_cache_instances | 1 |

# mysqladmin var | grep table_definition_cache

| | table_definition_cache | 500 |

Parameter values in MySQL 5.7are as follows:

Mysql > show variables like 'table_definition_cache'

| | Variable_name | Value |

| | table_definition_cache | 1400 | |

Mysql > show variables like 'table_open_cache'

| | Variable_name | Value |

| table_open_cache | 2000 | Ali's classmate has given the pseudo code of testcase, so I will implement the following, and give the shell version of the test script.

First, we can simulate the baseline of the test and change both variables to 400.

SET GLOBAL table_definition_cache = 400

SET GLOBAL table_open_cache = 400

Then use the following shell script, and if you look at it carefully, the script logic is simple.

Generate 500tables, then insert a piece of data, modify the self-incremented values, and then query the data in the table so that the data can be brushed out, wait a little, and see the results of show create table.

For i in {1..500}

Do

Mysql test_new

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.