[MySQL]ERROR 1206: The total number of locks exceeds the lock table size

INSERT 文でエラー発生

MySQL 5.0 で少し重めの INSERT 文を走らせていると、“ERROR 1206 (HY000): The total number of locks exceeds the lock table size” に出くわした。

MySQL のログには次のように出力されていた。

110929 12:41:38 InnoDB: WARNING: over 67 percent of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 8 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.
110929 12:41:41 [ERROR] Got error 147 when reading table './database_name/table_name

エラーログから buffer pool size の問題とわかる。

このブログを参考に innodb_buffer_pool_size を 大きくしてその場を凌ぐ。

MySQL 公式マニュアル

buffer pool size に関して MySQL マニュアルでは “7.9.1. The InnoDB Buffer Pool”  に記載がある。

general guideline として

Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

また innodb_buffer_pool_size オプションについては

If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.

このオプションの値を大きくすれば、ディスクI/Oを減らし、よりオンメモリーで処理するようになり、パフォーマンスが良くなるよ、ということらしい。

オプションはアルファベット順に並んでいるわけでないにもかかわらず、このオプションが先頭にあるということは、それだけこの値が重要ということだろう。

漢のチューニング

@nippondanji さんのエキスパートのためのMySQL[運用+管理]トラブルシューティングガイド には以下の記載がある。

§1-4-5(P.52)でキャッシュやアダプティブ・ハッシュ・インデックスと絡めて簡単に innodb_buffer_pool_size オプションの説明をし、§1-6-5(P.96)でサイジングのガイドラインを与えている。
InnoDB だけを利用しているのであれば、innodb_buffer_pool_size はマシンのメモリからOSが消費するメモリとセッションが消費するメモリを引いたそのほとんどをわりふるように書かれている。

今回の問題のあったシステム

今回自分が経験したシステムは、つい最近システム構築したばかりなのに、MySQL 5.0 系列をインストールしており、そのデフォルト値 8MB のままだった。(メモリは大量ではないがそれなりに積んである)

mysql> show global variables like ‘innodb%’;
+———————————+————————+
| Variable_name | Value |
+———————————+————————+

| innodb_buffer_pool_size | 8388608 |

チューニングされていないままサービスインしちゃっているのはいろいろな理由があるのだけど、それは今後のマネージメントの改善ポイントということで。

Advertisements
Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: