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
mysql> show global variables like ‘table_open_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 10000 |
+——————+——-+
1 row in set (0.00 sec)
after restart mysql
cat /proc/658658/limits
Max open files 30000 30000 files
check mycnf setting
nano /etc/mysql/my.cnf
table_open_cache = 100000
open_files_limit = 50000
restart mysql
service mysql restart
ps aux | grep mysql
658893
cat /proc/659295/limits
Max open files 30000 30000 files
check mysql.service
cat /etc/systemd/system/multi-user.target.wants/mysql.service
LimitNOFILE = 30000
Max open files 300000 300000 files
nano /etc/systemd/system/multi-user.target.wants/mysql.service
root@localhost:~# systemctl daemon-reload
service mysql restart
cat /proc/659295/limits
Max open files 300000 300000 files
Note: mycnf was only 10000 but edited systemd as 3 lkhs.
systctl -p to reload sysctl
pam d to reload etc/security without reboot.
reload /etc/security/limits.conf
edit pam files add session required
use ulimit command set untill restart
before
mysql> show global variables like ‘%open%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| have_openssl | YES |
| innodb_open_files | 4919 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 10000 |
| table_open_cache | 4919 |
| table_open_cache_instances | 16 |
+—————————-+——-+
6 rows in set (0.00 sec)
after
mysql> show global variables like ‘%open%’;
+—————————-+——–+
| 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 |
+—————————-+——–+
| 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 |
able_open_cache | 58000 set in mycnf
Table_Cache should always – well mostly anyway – be significantly bigger than the total number of tables in the server.
beware the high memory usage unnecessary due to files limit
find the total number of tables in mysql server
Find out total tables of your databases
Run:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
+———-+
| COUNT(*) |
+———-+
| 1010 |
+———-+
1 row in set (0.01 sec)
+———-+
| COUNT(*) |
+———-+
| 1010 |
+———-+
1 row in set (0.01 sec)
show global status like ‘%Threads_connected%’;
Table_open_cache = total_tables*Threads_connected
mysql> show global status like ‘%Threads_connected%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set (0.00 sec)
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set (0.00 sec)
open_files_limit= table_open_cache*2 = 2x
open_files_limit= table_open_cache*1 = x
1010
show global status like ‘%Thread%’;
mysql> show global status like ‘%Thread%’;
+——————————+——-+
| 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 |
+——————————+——-+
| 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)
show global status like ‘%conn%’;
mysql> show global status like ‘%conn%’;
+——————————————————-+———————+
| 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 |
+——————————————————-+———————+
| 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)
show global variables like ‘%conn%’;
mysql> show global variables like ‘%conn%’;
+———————————————–+——————–+
| 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)
+———————————————–+——————–+
| 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)
show global variables like ‘%Thread%’;
thread_cache_size | 9 |
| thread_handling | one-thread-per-connection |
| thread_stack | 286720
| thread_handling | one-thread-per-connection |
| thread_stack | 286720
show global variables like ‘table_definition_cache’;
mysql> show global variables like ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 2000 |
+————————+——-+
1 row in set (0.00 sec)
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 2000 |
+————————+——-+
1 row in set (0.00 sec)
table_definition_cache=currentvalue+table_open_cache/2
2000+1010/2= 2505
table_open_cache_instances:
default 1 it shoulbe bigger than one and upto cores.
show global variables like ‘table_open_cache_instances’;
mysql> show global variables like ‘table_open_cache_instances’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| table_open_cache_instances | 16 |
+—————————-+——-+
1 row in set (0.00 sec)
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| table_open_cache_instances | 16 |
+—————————-+——-+
1 row in set (0.00 sec)
Reducing mysql open file limit due to high memory usage
mysql> select @@open_files_limit;
+——————–+
| @@open_files_limit |
+——————–+
| 30000 |
+——————–+
1 row in set (0.00 sec)
+——————–+
| @@open_files_limit |
+——————–+
| 30000 |
+——————–+
1 row in set (0.00 sec)
cat /etc/systemd/system/multi-user.target.wants/mysql.service
[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
Max open files 300000 300000 files
nano /etc/systemd/system/multi-user.target.wants/mysql.service
changed to 2000
root@localhost:~# systemctl daemon-reload
mysql> show global variables like ‘%open%’;
+—————————-+——-+
| 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 |
+—————————-+——-+
| 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 |
+—————————-+——
mysql> show global variables like ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 450 |
+————————+——-
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 450 |
+————————+——-
mysql set global table_definition_cache=100;
SET GLOBAL table_definition_cache=100;
mysql> SET GLOBAL table_definition_cache=100;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
