Problem: 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)