在MySQL數據庫中,我們需要根據數據庫的狀態調整一些系統參數,下面為您介紹的是MySQL表鎖情況和文件打開數的調整方法,供您參考。
文件打開數(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 |
- +------------------+-------+
- 比較合適的設置:Open_files / open_files_limit * 100%
MySQL表鎖情況
- mysql> show global status like 'table_locks%';
- +-----------------------+-----------+
- | Variable_name | Value |
- +-----------------------+-----------+
- | Table_locks_immediate | 490206328 |
- | Table_locks_waited | 2084912 |
- +-----------------------+-----------+
Table_locks_immediate表示立即釋放MySQL表鎖數,Table_locks_waited表示需要等待的MySQL表鎖數,如果Table_locks_immediate / Table_locks_waited > 5000,最好采用InnoDB引擎,因為InnoDB是行鎖而MyISAM是MySQL表鎖,對於高並發寫入的應用InnoDB效果會好些。示例中的服務器Table_locks_immediate / Table_locks_waited = 235,MyISAM就足夠了。
MySQL查詢緩存變量
MySQL慢查詢的啟用方法
Mysql多表查詢的實現
MySQL創建函數問題的處理方法
MySQL存儲過程和數據庫的聯系