How does MySQL calculate the number of opened files _ MySQL

How does MySQL calculate the number of opened files each time a MyISAM table is opened, two file descriptors are required. let's verify how MySQL calculates the number of opened files.

I. test

We can see from the manual "6.4.8. How MySQL Opens and Closes Tables" that every time a MyISAM table is opened, we need two file descriptors for verification.

1. restart mysqld

/Etc/init. d/mysql restart

2. check that several files are opened.

Lsof | grep/home/mysql
...
Mysqld 24349 mysql 5u unix 0x000001041e8de040 4244009/home/mysql. sock
Mysqld 24349 mysql 6u REG 2048 30425188/home/mysql/host. MYI
Mysqld 24349 mysql 7u REG 8, 33 0 30425189/home/mysql/host. MYD
Mysqld 24349 mysql 8u REG 2048 30425153/home/mysql/user. MYI
Mysqld 24349 mysql 9u REG 8, 33 892 30425155/home/mysql/user. MYD
Mysqld 24349 mysql 10u REG 8, 33 5120 30425126/home/mysql/db. MYI
Mysqld 24349 mysql 11u REG 3080 30425148/home/mysql/db. MYD
Mysqld 24349 mysql 12u REG 8, 33 4096 30425154/home/mysql/tables_priv.MYI
Mysqld 24349 mysql 13u REG 8, 33 0 30425157/home/mysql/tables_priv.MYD
Mysqld 24349 mysql 14u REG 8, 33 4096 30425143/home/mysql/columns_priv.MYI
Mysqld 24349 mysql 15u REG 8, 33 0 30425156/home/mysql/columns_priv.MYD
Mysqld 24349 mysql 16u REG 8, 33 4096 30425127/home/mysql/procs_priv.MYI
Mysqld 24349 mysql 17u REG 8, 33 0 30425136/home/mysql/procs_priv.MYD
Mysqld 24349 mysql 18u REG 8, 33 1024 30425173/home/mysql/servers. MYI
Mysqld 24349 mysql 19u REG 8, 33 0 30425174/home/mysql/servers. MYD
Mysqld 24349 mysql 20u REG 8, 33 2048 30425182/home/mysql/event. MYI
Mysqld 24349 mysql 21u REG 8, 33 0 30425183/home/mysql/event. MYD
...

We can see that a total of eight tables are opened, and each table has two file descriptors, which seems to be correct.

3. check the status result again.
Mysql> show global status like 'open _ % ';
+ ------------------------ + ------- +
| Variable_name | Value |
+ ------------------------ + ------- +
| Open_files | 17 |
| Open_streams | 0 |
| Open_table_definitions | 15 |
| Open_tables | 8 |
| Opened_files | 52 |
| Opened_tables | 15 |
+ ------------------------ + ------- +

4. flush tables and check again

Mysql> flush tables;
Mysql> show global status like 'open _ % ';
+ ------------------------ + ------- +
| Variable_name | Value |
+ ------------------------ + ------- +
| Open_files | 1 |
| Open_streams | 0 |
| Open_table_definitions | 0 |
| Open_tables | 0 |
| Opened_files | 52 |
| Opened_tables | 15 |
+ ------------------------ + ------- +
Lsof | grep/home/mysql
...
Mysqld 24349 mysql 5u unix 0x000001041e8de040 4244009/home/mysql. sock
Mysqld 24349 mysql 22u unix 0x00000102108ff980 4244128/home/mysql. sock
...

We can see that after the flush operation, all file descriptors are released.

The test shows that the other opened file descriptor is used by the slow query log.

If a large number of MyISAM tables exist, pay special attention to whether the number of opened files exceeds the limit.

II. Principles

Next, take a closer look at the parameters related to the maximum number of files:

Table_cache (The new version is changed to table_open_cache) The number of cached open tables.

Open_files_limit If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit ().

If this value is 0 then mysqld will reserve max_connections * 5 or max_connections + table_cache * 2 (whichever is larger)

Number of files.

If open_files_limit is not set to 0, the result calculated by the setrlimit () function prevails. if it is set to 0, the actual value is max_connections * 5 or max_connections + table_cache * 2.

Therefore, to solve the problem that the number of opened files exceeds the limit, you also need to combine the system kernel limits (ulimit-n), mysqld's own limits (open_files_limit), and the number of table caches (table_open_cache) and other factors.

However, in actual tests, we found that this was not the case. open_files_limit adopted the kernel's maximum limit, not the above calculation results.

1. View kernel restrictions

Ulimit-n

65535

2. modify my. cnf restrictions

Vi/etc/my. cnf
...
Open_files_limit = 10000
...

3. restart mysqld

/Etc/init. d/mysql restart

4. View Results

Mysql> show global variables like '% open % ';
| Open_files_limit | 65535 |
| Table_open_cache | 1000 |

5. check if open_files_limit is not set.

Vi/etc/my. cnf
...
# Open_files_limit = 10000
...
Restart
/Etc/init. d/mysql restart
View
Mysql> show global variables like '% open % ';
| Open_files_limit | 65535 |
| Table_open_cache | 1000 |

At this time, the calculation results are as follows:

| Max_connections | 100 |

| Table_open_cache | 1000 |

To calculate:

Max_open_files_1 = max_connections + table_cache * 2 = 100 + 1000*2 = 2100

Max_open_files_2 = max_connections * 5 = 100x5 = 500

6. try to modify ulimit:

Unlimit-n 5000
Vi/etc/my. cnf
...
Open_files_limit = 10000
...
/Etc/init. d/mysql restart
Mysql> show global variables like '% open % ';
| Open_files_limit | 10000 |
| Table_open_cache | 1000 |

Open_files_limit is larger than the maximum number of kernels. Therefore, use open_files_limit as the standard.

Vi/etc/my. cnf
...
# Open_files_limit = 10000
...
/Etc/init. d/mysql restart
Mysql> show global variables like '% open % ';
| Open_files_limit | 5000 |
| Table_open_cache | 1000 |

As you can see, it becomes the new kernel's maximum limit.

As you can see, the results are completely different from those described in the document and written in the mysql source code. it seems that the pre-compiled version is not reliable in some places :(

The above tests are the same in mysql 5.1.23-rc (pre-compiled), mysql-5.1.24-rc (self-compiled) and 5.0.45 (pre-compiled.

Uname-
Linux s1.yejr.com 2.6.9-55. ELsmp #1 SMP Fri Apr 20 16:36:54 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
Mysql> select version ();
+ --------------- +
| Version () |
+ --------------- +
| 5.1.23-rc-log |
+ --------------- +