66170


どうしても問題が解決できなく、ここで皆様のお知恵を拝借したく投稿しております。

現在MySQL（Ver 14.14 for linux）を使っています。ある一つの大きなテーブルを、小さなテーブルに分割したいのですが、ERROR 1206
(HY000): The total number of locks exceeds the lock table
sizeが出てしまい、それの対処方法をお尋ねしたいです。

具体的には、

[code]

    select count(*) from TABLE1 where DATE = '2019' and MODE = 1;
    
[/code]

では

[code]

    +----------+
    | count(*) |
    +----------+
    |  9659724 |
    +----------+
    
[/code]

がデータの分割対象であり、

[code]

    select count(*) from TABLE1 where DATE = '2018' and MODE = 1 and SIGNATURE1 = 'option1';
    
[/code]

では、

[code]

    +----------+
    | count(*) |
    +----------+
    |   620984 |
    +----------+
    
[/code]

のデータが分割対象です。

行ったことは、以下の2つのコマンドを用いて、新しいtableを２つ作成しました。

[code]

    1. create table smalltable_2019 as select * from TABLE1 where DATE = '2019' and MODE = 1;
    2. create table smalltable_2018 as select * from TABLE1 where DATE = '2018' and MODE = 1 and SIGNATURE1 = 'option1';
    
[/code]

結果としては、1のコマンドは問題なく新しいテーブルが作成できましたが、2のコマンドがエラーを発生します。  
2のエラーは「ERROR 1206 (HY000): The total number of locks exceeds the lock table
size」です。

webで対処方法を調べて、「innodb_buffer_pool_size=20G」をmy.cnfの中に追記しましたが、まだ同じエラーが出てしまいます。

質問ですが、  
1\. innodb_buffer_pool_size以外の方法でThe total number of locks exceeds the lock
table sizeへの対処はありますでしょうか？  
2\.
smalltable_2019はsmalltable_2018に比べて１０倍のデータを含んでいるのに問題なく作成される。しかし、なぜより小さなテーブル(smalltable_2018)を作成するのに、エラーが出るのかがわかりません。

もし解決方法がわかりましたら、ご教授をお願いします。  
よろしくお願いします。


CC BY-SA 4.0
2020-05-01T01:41:58.817
66167
2020-05-01T02:41:49.297
2020-05-01T02:30:51.020
19110
11048
question
mysql
MySQLのThe total number of locks exceeds the lock table size問題への対処方法


<https://stackoverflow.com/q/10253482/3090068>

例えば上記のスレッドをみていると、当該 `ERROR 1206 (HY000): The total number of locks exceeds the
lock table size`
のエラーが発生するのは、裏で行ロックされるレコードがバッファプールの上に存在する必要があり、それがバッファプールに乗り切らなくなったから、だと推察できます。

今回のケースですと、「更新(create)に伴う select」ですので、 repeatable read 以上の consistency だと、
select 分の内部クエリは、 shared lock をそれぞれの行に対してかけることになります。

MySQL が更新時にかける行ロックは、実際にセレクトされたレコードだけではなく、 MySQL が走査(スキャン)した全てのレコードにかかります。
`explain` を行うとどのような走査が行われているかの確認ができます。

`1. create table smalltable_2019 as select * from TABLE1 where DATE = '2019'
and MODE = 1;`

が実行できていることから、おそらく少なくとも `DATE` 列については index が貼ってあるかな、と思います。

例えば、 `DATE` の index が今回のそれぞれの create 文の select において実行されていた、とすると、 1 番の create
文と 2番の create 文でロックされる行の総数は、それぞれ `DATE = 2018` と `DATE = 2019`
を満たす行の総数になります。おそらく、 `DATE = 2019` の総数の方が多いのではないかな、と思っています。

一番手っ取り早い方法は、２つ目のクエリでも全てのロックするレコードがバッファプールに乗るように、バッファプールのサイズをあげることです。`innodb_buffer_pool_size=20G`
を記載した、とのことですが、これがデータベースに反映されていることは確認されましたでしょうか。

メモリが足りないなどの理由でこれが実現できない場合としては、 `DATE`, `MODE`, `SIGNATURE1`
に対する複合インデックスを作成すると、おそらく走査の範囲が絞られて、今回のクエリは実行できるようになるかと思っています。


CC BY-SA 4.0
2020-05-01T02:41:49.297
66170
2020-05-01T02:41:49.297
754
66167
answer