The actual procedure of opening files in the MySQL database

The following articles mainly describe how the MySQL database correctly calculates the actual operation flow of the number of opened files. Everyone knows that every time a MyISAM table is opened, two file descriptors must be used for it, so let's verify how the MySQL database calculates the number of opened files. From Manual 6.4.8.HowMySQLOpensandClosesTables

The following articles mainly describe how the MySQL database correctly calculates the actual operation flow of the number of opened files. Everyone knows that every time a MyISAM table is opened, two file descriptors must be used for it, so let's verify how the MySQL database calculates the number of opened files. Test from the manual 6.4.8. How MySQL Opens and Closes Tables can be

The following articles mainly describe how the MySQL database correctly calculates the actual operation flow of the number of opened files. Everyone knows that every time a MyISAM table is opened, two file descriptors must be used for it, so let's verify how the MySQL database calculates the number of opened files.

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

We can see that a total of eight tables are opened, and each table has two file descriptors of the MySQL database, 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/mysql.sock 
  
  
mysqld 24349 mysql 22u unix 0x00000102378ff980 4244128 /home/mysql/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.

The above content introduces how to calculate the number of opened files in the MySQL database.