MySQLメモリ設定（exceeds the lock table sizeエラー対応）
- 2014年12月10日
- サーバ構築
“The total number of locks exceeds the lock table size”エラーが発生した場合は”innodb_buffer_pool_size”を大きくします。
“/etc/my.cnf”で、”innodb_buffer_pool_size”を大きく設定します。以下の行を追加します。
innodb_buffer_pool_size = 1536M
ですが、エラー回復しませんでした。
では、現在の設定を確認します。
SHOW VARIABLES LIKE “innodb_%_size”;
をコマンドして設定を確認してください。
【変更前】
|
1 2 3 4 5 6 7 8 9 10 |
SHOW VARIABLES LIKE "innodb_%_size"; +---------------------------------+---------+ | Variable_name | Value | +---------------------------------+---------+ | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | +---------------------------------+---------+ |
“/etc/my.cnf”で、”innodb_buffer_pool_size”を“[mysqld]”配下に記載していませんでした。
（以下の”/etc/my.cnf”の設定ファイルのとおり変更する）
【変更後】
|
1 2 3 4 5 6 7 8 9 10 |
SHOW VARIABLES LIKE "innodb_%_size"; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 3221225472 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 1073741824 | +---------------------------------+------------+ |
現在の/etc/my.cnfの設定内容を掲載します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Yama Add Start default-character-set=utf8 skip-character-set-client-handshake innodb_buffer_pool_size = 3G innodb_log_file_size = 1G max_connections = 101 default-time-zone='+9:00' [mysqld_safe] timezone = UTC log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
【参照】
mysqlをinnodb_buffer_pool_sizeとinnodb_log_file_sizeで速くする
*****************************【innodb_log_file_sizeについて】**************************
MySQLはUPDATE等の各種処理する時に一時的にログをメモリにためるようです。
ですので、以下のサイズを大きくすると処理が速くなるようです。
innodb_log_file_size = 1G
“/etc/my.cnf”で”innodb_log_file_size”を変更します。
ただ、4Gbyteを超えると以下のエラーが発生します。
InnoDB: Error: combined size of log files must be < 4 GB 32Bitサーバで発生するようですが、他の要因でも発生するかもしれません。 "/etc/my.cnf"で"log-error=/var/log/mysqld.log"にログが無いか確認する。 発生した場合は4Gbyte以下に設定する。 （"innodb_buffer_pool_size"と"innodb_log_file_size"の合計値で4Gbyteを超えないこと） *****************************【max_connectionsについて】************************** 必要なメモリ量は、【スレッドバッファ * コネクション数】のようです。 ですので、スレッドバッファとコネクション数を確認して必要に応じて変更が必要です。 【確認方法】 (1)スレッドバッファ
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select @@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH as GLOBAL_BUFFER_SIZE, @@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE as THREAD_BUFFER_SIZE, @@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb \G; |
(2)コネクション数
|
1 |
select @@GLOBAL.MAX_CONNECTIONS; |
で確認できます。私の場合はスレッドバッファ(THREAD_BUFFER_SIZE)は約11MByteで、コネクション数は151でした。(以下の結果参照) (1)スレッドバッファの確認結果
|
1 2 3 4 5 6 7 8 |
*************************** 1. row *************************** GLOBAL_BUFFER_SIZE: 18886656 THREAD_BUFFER_SIZE: 11010040 TOTAL_MEMORY_SIZE: 1681402696 TOTAL_MEMORY_SIZE_kb: 1641994.8203 TOTAL_MEMORY_SIZE_mb: 1603.51056671 TOTAL_MEMORY_SIZE_gb: 1.565928287804 1 row in set (0.00 sec) |
(2)コネクション数の確認結果
|
1 2 3 4 5 6 7 |
mysql> select @@GLOBAL.MAX_CONNECTIONS; +--------------------------+ | @@GLOBAL.MAX_CONNECTIONS | +--------------------------+ | 151 | +--------------------------+ 1 row in set (0.00 sec) |
ということで、1661Mbyte(1.6Gbyte)必要になるのでコネクション数を101に削減しました。
"/etc/my.cnf"のところに以下の行を追加してください。
max_connections = 101
これで、完了です。エラーが発生しなくなりました。最終的に"/etc/my.cnf"は以下を追記したことになります。
参考：
5分で出来るMySQLのお手軽チューニング
5分でできる、MySQLのメモリ関係のチューニング！
【MySQL】総メモリー使用量を算出するSQL作ってみた
