1. Find current value of open_tables and opened_tables

mysql> show global status like 'open%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Open_files | 1583 |
| Open_streams | 0 |
| Open_table_definitions | 1400 |
| Open_tables | 2000 |
| Opened_files | 2619222 |
| Opened_table_definitions | 110583 |
| Opened_tables | 482099 |
+--------------------------+---------+
7 rows in set (0.00 sec)

2. Find out Table cache hit rate

Table cache hit rate = table_open_cache*100/Opened_tables. 
= 2000*100/482099
= 0.41%

In general it should be more than 50%. So you need to increase value of table_open_cache, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.

At this stage you are almost sure table_open_cache system variable is not tuned properly.

Now you have to optimize MySQL table_open_cache and find out perfect value for this. To find tuned value of table_open_cache value follow the steps:

1. Find out total tables of your database

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
+----------+
| COUNT(*) |
+----------+
| 211 |
+----------+
1 row in set (0.05 sec)
Find threads currently connected to your database.

mysql> show global status like '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 6 |
+-------------------+-------+
1 row in set (0.00 sec)

It would be best if you take threads connected at busiest time of your database or take several times at different time and make an average.

3. Calculate the tune value of table_open_cache and set it

Table_open_cache = total_tables*Threads_connected
= 211*6
= 633

As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated. Because too big value of this variable has some other side effects. So the formula becomes

Table_open_cache = total_tables*Threads_connected*.50

4. Along with table_open_cache you should also tune open_files_limit system variable.

In general it is 2x of table_open_cache.

open_files_limit= Table_open_cache*2

open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.

*Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.

Go to your Mysql configuration file (in linux it is /etc/my.cnf) and set the table_open_cache and open_files_limit

nano /etc/my.cnf

add

table_open_cache=633
open_files_limit=65535

5. LimitNOFILE

nano /etc/systemd/system/multi-user.target.wants/mysql.service

Add line Ubuntu/Debian

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=65536

OR

Centos/RHEL/Fedora

nano /etc/systemd/system/multi-user.target.wants/mysql.service

add line

LimitNOFILE=65536

Reload and Restart

systemctl daemon-reload 
service mysql restart

6. Restart the MySQL ( In Linux it is like)

systemctl daemon-reload
systemctl restart mysql

or old Ubuntu 14.04/ Debian 7/ CentOS 62020

/etc/init.d/mysqld restart
or
/etc/init.d/mysql restart

The database I have taken has 211 tables so the value of table_open_cache is little big. For your case it may be significantly small.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.