WL#9703: Defaults change table_open_cache=4000
Affects: Server-8.0 — Status: Complete
Information on the table cache and settings related to it is described in https://dev.mysql.com/doc/refman/5.7/en/table-cache.html From docs: The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. 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. Make sure that your operating system can handle the number of open file descriptors implied by the table_open_cache setting. If table_open_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable. You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started: mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 2741 | +---------------+-------+ If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table cache size. End from docs. The number of open tables stored in the cache, is a function of the number of tables in the schema and the number of users using the tables. It should not be set to a value that is higher than the OS "number of open file descriptors" can handle. Wrt memory consumption per cache entry, this depends on the SE, and table structure e.g. if row buffers allocated for the table are big (e.g. close to the limit) you can easily get around 200K per table instance in the cache, like we see in BUG#86279 (lots of medium-sized varchar fields). In most situations the actual memory consumed per entry is a much smaller. The search in the cache generally uses hash-lookup. For DROP DATABASE a linear scan is done. Considerations for finding a good default value for table_open_cache: So the default value for table_open_cache should be set as high as possible, but consider OS resource constraints on memory usage and OS limit of open file descriptors. What to think about: 1. The default OS limit for the number of open file descriptors differs for the MySQL supported platforms. The value should not exceed any of these OS specific platform limits. 2. The MySQL usage of open file descriptors depends on the number of connections and the number of tables that are opened. Multiple connections opening the same table, still requires an open file descriptor per user. 3. The SEs can handle use of file descriptors differently. For MyISAM 2 file descriptors per table are opened (MYD/MYI). TODO, see how many InnoDB uses. 4. How many tables are opened simultaneously affect the number of needed open file descriptors. The number of connections that access tables simultaneously also affect the number of needed open file descriptors. In addition these are SE dependent. For MyISAM you will then have the following: a) A schema with 2K tables which are all kept open due to schema structure, and 1 connection will require: 2k handles for MYD + 2K*1 handles for MYI b) A schema with 14 table being accessed by 151 connections (default MAX_CONNECTION) will require: 14 handles for MYD + 14*151 handles for MYI 5. The memory used per TABLE instance is dependent on the table structure. It is hard for users to understand how to calculate this. In corner cases, we have seen 200K per TABLE instance, but generally the size is much smaller.
Change default value of table_open_cache: Old default value : 2000 New default value : 4000
Change default value of table_open_cache: Old default value : 2000 New default value : 4000 The default value of table_definition_cache variable is autosized and determined at runtime from the value of the table_open_cache by the formula : default_value= min
(400 + table_cache_size / 2, 2000); The change in the default value of table_open_cache will change the default value of table_definition_cache too from 1400 to 2000. Doc links: - https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_open_cache - https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache Mechanism and trade-offs: -------------------------- The TABLE cache is handled by a cache manager. Table cache manager creates instances of table cache. https://dev.mysql.com/doc/refman/5.7/en/server-system-ariables.html#sysvar_table_open_cache_instances Each thread is assigned a cached by mod division thread id by number of cache instances. The problem with this approach is that two caches instances can have same TABLE cached. Each cache instance maintains two linear lists of tables, used and free. When opening the table first time, it is added to used table list. When table is closed (at the end of every statement, it is moved to free list). The list is linear and traversing time increases linearly with the increase in cache size. - The cache manager and cache itself do not take big memory. Server initialiation will not consume extra memory. As tables are opened and cached, the memory size will increase. Average one TABLE takes around 2000 bytes to 4000 bytes. It might vary significantly depending on the table structure. The memory consumption will increase with the increase in cache size.
Test and Result files changed in due to change in default value: mysql-test/r/table_definition_cache_functionality.result mysql-test/r/table_open_cache_functionality.result mysql-test/suite/sys_vars/r/table_definition_cache_basic.result mysql-test/suite/sys_vars/r/table_open_cache_basic.result mysql-test/suite/sys_vars/t/table_definition_cache_basic.test mysql-test/suite/sys_vars/t/table_open_cache_basic.test mysql-test/t/table_definition_cache_functionality.test mysql-test/t/table_open_cache_functionality.test
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.