MySQL table lock status and number of opened files

In the MySQL database, we need to adjust some system parameters according to the database status. The following describes how to adjust the MySQL table lock and the number of opened files for your reference.

Open_files)

 
 

  
  
Mysql> show global status like 'open _ files ';
  
  
+ --------------- + ------- +
  
  
| Variable_name | Value |
  
  
+ --------------- + ------- +
  
  
| Open_files | 1410 |
  
  
+ --------------- + ------- +
  
  
Mysql> show variables like 'open _ files_limit ';
  
  
+ ------------------ + ------- +
  
  
| Variable_name | Value |
  
  
+ ------------------ + ------- +
  
  
| Open_files_limit | 4590 |
  
  
+ ------------------ + ------- +
  
  
Suitable settings: Open_files/open_files_limit * 100%
 
 

MySQL table locks

 
 

  
  
mysql> show global status like 'table_locks%';  
  
  
+-----------------------+-----------+  
  
  
| Variable_name | Value |  
  
  
+-----------------------+-----------+  
  
  
| Table_locks_immediate | 490206328 |  
  
  
| Table_locks_waited | 2084912 |  
  
  
+-----------------------+-----------+ 
 
 

Table_locks_immediate indicates the number of MySQL table locks to be released immediately, and Table_locks_waited indicates the number of MySQL table locks to wait. If Table_locks_immediate/Table_locks_waited> 5000, InnoDB engine is recommended, because InnoDB is a row lock and MyISAM is a MySQL table lock, InnoDB is better for highly concurrent writes. In the example, the server Table_locks_immediate/Table_locks_waited = 235, and MyISAM is enough.

MySQL query cache Variables

How to enable MySQL slow Query

Mysql multi-Table query implementation

How to deal with MySQL function creation Problems

Connection between MySQL stored procedures and databases