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

What is the impact of mysql table_open_cache? in view of this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Tencent Cloud once asked this question before, but in fact, it seems that this table_open_cache is nothing.

Let's take a look at what kind of existence table_open_cache really is.

First of all, let's take a test and conduct a stress test through sysbench. On the basis of the same data, all the other parameters remain the same, just change the table_open_cache, and then we see what changes are made in the overall stress test data.

Testing method and environment

1 tested database 8G, 4 core memory disk SSD 300MB per second

Test table_open_cache value change

1 4096

2 8192

3 1024

4 96

1 4096

Result

Transactions: 193526 (806.21 per sec.)

Read/write requests: 3483840 (14513.26 per sec.)

Total time taken by event execution: 72006.8461s

Response time:

Min: 6.27ms

Avg: 372.08ms

Max: 1866.33ms

Approx. 95 percentile: 604.04ms

Change table_open_cache to 8192

Transactions: 188719 (786.11 per sec.)

Read/write requests: 3397113 (14150.74 per sec.)

Total time taken by event execution: 72013.6051s

Response time:

Min: 6.57ms

Avg: 381.59ms

Max: 1534.00ms

Approx. 95 percentile: 630.08ms

Transactions: 189366 (788.85 per sec.)

Read/write requests: 3408744 (14199.90 per sec.)

Total time taken by event execution: 72006.3405s

Response time:

Min: 5.48ms

Avg: 380.25ms

Max: 1433.75ms

Approx. 95 percentile: 619.79ms

When using the same pressure test command, the system reports an error directly.

We have to reduce the number of simultaneous threads to 200.

Transactions: 174108 (725.36 per sec.)

Read/write requests: 3134050 (13056.84 per sec.)

Total time taken by event execution: 48001.7575s

Response time:

Min: 5.70ms

Avg: 275.70ms

Max: 1528.54ms

Approx. 95 percentile: 472.15ms

From the simple test above, we can get the simplest result.

The performance of table_open_cache and your system has an impact on your system when its size changes, and smaller values will degrade your system as a whole. The excessive setting does not improve the performance of the system, or even a small drop in event execution.

It is estimated that someone here will ask, how to set this value, I have never touched him, and there has been no problem.

In fact, the official gave a method to set this value. The official suggestion is that the threads * N = table_open_cache of your system connection here, the number of connections can already be obtained through the usual connection, but N this data, you can only estimate the number of tables queried each time. To be honest, this is not easy to control and lock. How can I know that on average a connection accesses several tables at a time, of course, if you have a golden rule? A SQL can't produce JOIN at most three times, which is easier to do.

An average of 1000 connections * 3 = 3000, so the default 4096 is still rich. In most companies that use MYSQL, how many people can tell how many tables MYSQL accesses each time.

Is there any way to see if my table_open_cache is enough? there is a general formula.

Table cache hit rate = table_open_cache*100/Opened_tables

According to this formula, let's look at the hit rate of different table_open_cache.

96mm 100max 739812 = 0.0129

1024 inch 100amp 739812 = 0.13

4096 * 100max 739812 = 0.55

8192 dollars 100max 739812 = 1.1

As you can see from the numbers above, it might be better to set table_open_cache to 6140. Of course, it is estimated that there will not be much difference between the performance gap and 4096.

Someone may jump out, if it's so troublesome, I'll set it up a little bigger.

As a result, the stress test cannot be carried out, that is, too large and too small settings will cause the system to fail to work properly.

WHY, the authorities have given an explanation.

Of course, there is also a parameter linkage setting open_file_limit for table_open_cache, so when adjusting your table_open_cache, you also need to adjust your open_file_limit.

Open_files_limit= Table_open_cache*2

Therefore, the parameters of the system are ring-related and influence each other.

This is the end of the answer to the question about the impact of mysql table_open_cache. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.