D
D
Dmitry Tarasov2020-04-29 13:16:53
MySQL
Dmitry Tarasov, 2020-04-29 13:16:53

How to properly set up table_open_cache in mysql 8?

Server configuration.
4 cpu, 8 gb memory, 15 gb ssd disk.
mysql 8, centos 8.1.1911.
Only 114 tables and all InnoDB, the whole database takes 1.4 GB.

my.cnf
mysqltuner

I decided to install mysqltuner and check if everything is ok with my settings.
The first time something changed, something removed. Initially, the settings were not made by me and they seemed ok to me, but the database itself could eventually eat all the RAM and clean the memory with a script, because as it ended, the system admins said that it was ok.

Next, I commented out some of them and it became much less memory.
Remained in mysqltuner to change these directives.
Variables to adjust:
join_buffer_size (> 8.0M, or always use indexes with JOINs)
table_open_cache (> 4000)
join_buffer_size I decided that it is necessary to rewrite queries using indexes, and then increase or decrease.

I don't like table_open_cache, more specifically Table cache hit rate: 19% (3K open / 16K opened)

Go to mysql
site table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

And I don’t quite understand it, that is, I understand it this way, the maximum number of tables that you use in one query.
That is, if I have a query with 10 table joins, then my N is 10 ?

Total: I have 151 max connections and 151 * 10 = 1510.
table_open_cache = 1510 Is this normal? Did I understand correctly?
Why do I have 4000 now and still the database goes to disk and reads the tables again, bypassing the cache?
As for file reading limits, /usr/lib/systemd/system/mysqld.service is set to LimitNOFILE = 10000
ulimit -Hn
4096

What am I not understanding? How should I do it right and how much table_open_cache should I actually have?
Or maybe it's worth it to score at all? mysqltuner is not objective? But why then is a lot of reading from the disk and not the cache?

And what other directives I have configured may not be correct? Or which affect table_open_cache incorrectly?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question