mysql open table cahce uses some regardless of hit ration.
Inro: discussion about table open cahe and ope files limit for memory and faster response.
should i reduce mysql open table cache size ?
first les check utilization by analaying hit ratio.
( Open_tables / table_open_cache ) = 100%
SHOW OPEN TABLES lists the non-TEMPORARY tables that are currently open in the table cache
352 row
mysql> SHOW OPEN TABLES;
+——————–+—————————————–+——–+——- ——+
| Database | Table | In_use | Name_l ocked |
+——————–+—————————————–+——–+——- ——+
| rrb | usgwp_actionscheduler_claims | 0 |
to find out tables
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
+———-+
| COUNT(*) |
+———-+
| 1429 |
+———-+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE ‘%open%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| have_openssl | YES |
| innodb_open_files | 300 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 10000 |
| table_open_cache | 100 |
| table_open_cache_instances | 16 |
+—————————-+——
Problem: mysql open table file cache hit ratio: 5%
4000/85K
Max opentables 4919
open fileslimit: 10000
at
cat /etc/security/limits.conf
nginx soft nofile 10000
nginx hard nofile 30000
www-data soft nofile 20000
www-data hard nofile 50000
mysql soft nofile 20000
mysql hard nofile 50000
root soft nproc 1000000
root hard nproc 100000
root soft nofile 100000
root hard nofile 100000
Open file limit : 10% of available memory. bytes
also change number of active process per mysql.
connections = 30000+ if
fs file max system ide limit
cat /proc/sys/fs/file-max
2097152 (2 lakhs
cat /etc/sysctl.conf
# Increase size of file handles and inode cache
fs.file-max = 2097152 // linux file descriptor limit
This file is used during the boot process
check existing files limit
ps aux | grep mysql
653859
Max open files 10000 10000
after restart mysql
check mysql.service
reload /etc/security/limits.conf
before
after
+—————————-+——–+
| Variable_name | Value |
+—————————-+——–+
| have_openssl | YES |
| innodb_open_files | 58000 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 300000 |
| table_open_cache | 58000 |
| table_open_cache_instances | 16 |
find the total number of tables in mysql server
+———-+
| COUNT(*) |
+———-+
| 1010 |
+———-+
1 row in set (0.01 sec)
Table_open_cache = total_tables*Threads_connected
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set (0.00 sec)
+——————————+——-+
| Variable_name | Value |
+——————————+——-+
| Delayed_insert_threads | 0 |
| Mysqlx_worker_threads | 2 |
| Mysqlx_worker_threads_active | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 4 |
| Threads_running | 2 |
+——————————+——-+
8 rows in set (0.00 sec)
+——————————————————-+———————+
| Variable_name | Value |
+——————————————————-+———————+
| Aborted_connects | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 216 |
| Locked_connects | 0 |
| Max_used_connections | 4 |
| Max_used_connections_time | 2021-07-30 03:07:36 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 116 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+——————————————————-+———————+
22 rows in set (0.00 sec)
+———————————————–+——————–+
| Variable_name | Value |
+———————————————–+——————–+
| character_set_connection | utf8mb4 |
| collation_connection | utf8mb4_0900_ai_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| mysqlx_connect_timeout | 30 |
| mysqlx_max_connections | 100 |
| performance_schema_session_connect_attrs_size | 512 |
+———————————————–+——————–+
11 rows in set (0.01 sec)
| thread_handling | one-thread-per-connection |
| thread_stack | 286720
show global variables like ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 2000 |
+————————+——-+
1 row in set (0.00 sec)
table_open_cache_instances:
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| table_open_cache_instances | 16 |
+—————————-+——-+
1 row in set (0.00 sec)
+——————–+
| @@open_files_limit |
+——————–+
| 30000 |
+——————–+
1 row in set (0.00 sec)
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
TimeoutSec=0
LimitNOFILE = 30000
Restart=on-failure
RestartPreventExitStatus=1
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| have_openssl | YES |
| innodb_open_files | 300 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 2000 |
| table_open_cache | 100 |
| table_open_cache_instances | 16 |
+—————————-+——
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 450 |
+————————+——-
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘open_files_limit’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 1024 |
+——————+——-+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE ‘%open%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| have_openssl | YES |
| innodb_open_files | 300 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 1024 |
| table_open_cache | 100 |
| table_open_cache_instances | 16 |
+—————————-+——-+
6 rows in set (0.00 sec)
mysql> SHOW Status LIKE ‘%open%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Com_ha_open | 0 |
| Com_show_open_tables | 0 |
| Innodb_num_open_files | 287 |
| Mysqlx_cursor_open | 0 |
| Mysqlx_expect_open | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_table_definitions | 335 |
| Open_tables | 96 |
| Opened_files | 0 |
| Opened_table_definitions | 1 |
| Opened_tables | 18 |
| Replica_open_temp_tables | 0 |
| Slave_open_temp_tables | 0 |
| Table_open_cache_hits | 1 |
| Table_open_cache_misses | 18 |
| Table_open_cache_overflows | 18 |
+—————————-+——-+
17 rows in set (0.00 sec)
mysql>