質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

Q&A

解決済

1回答

1368閲覧

innoDBで大量selectに伴う「The total number of locks exceeds the lock table size」の回避方法を知りたい

kazunii_ac

総合スコア20

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

0グッド

3クリップ

投稿2018/12/23 11:36

解決したいこと

innoDBで、下記エラーが出ています。これを回避したいです。

The total number of locks exceeds the lock table size

実行しているSQL文

SQL文は下記のような感じです。

SQL

1replace INTO tbl_a( 2 str1 3 , str2 4 , updateDatetime 5 , updateNum 6 ) 7 select distinct 8 str1 9 , str2 10 , now() as updateDatetime 11 , 1 as updateNum 12 FROM 13 tbl_b

tbl_bは巨大なテーブルです。phpmyadminの表示で5億5千万レコード、テーブルサイズは727GBです。

上記SQL文のdistinct結果は、おそらく1~2GB程度になるハズです。

現在の innodb_buffer_pool_size の設定は、512MBです。

tbl_aはカラの状態で実行しています。
※直前でtruncateしています。

innodb_buffer_pool_size のサイズは、物理メモリ環境を考えると10GBくらいまで増やせますが、上記のtbl_bは現在の10倍くらいまで肥大化する想定のため、 innodb_buffer_pool_size の調整だけでは解決しきれないように感じています。

また、上記SQL文が走っている間、tbl_a、tbl_bともに、他のスレッドからinsert・update・delete等が実行されないことはアプリケーション上で保証できます。つまり、行ロックの機能自体が、今回の私のニーズとしては不要です。

考えていること

「The total number of locks exceeds the lock table size」でググると、

  • innoDBでのselectでは行ロックが行われる
  • 行ロックの際にメモリが使われる
  • 行ロックのためにメモリが必要なので、必要なぶんだけ innodb_buffer_pool_size を増やせ

というような記述のあるサイトが多くあります。
それに対して、私は下記のように考えています。

  1. そもそも、私のニーズとしては行ロックが不要。メモリを極力使わない形で上記SQLを実行できないだろうか?
  2. innodb_buffer_pool_size は10GBまでは増やせるけど、それ以上はコスト上難しい。

何らかの形で、極力、innodb_buffer_pool_size を増やさずに、上記SQL文を正常実行できるような手段はないでしょうか。

知識のある方、ご回答をよろしくお願い致します!

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

tbl_aのユニーク制約次第でしょうけど
単純にbl_bにlimitをつかって分割して処理するのも効果はあると思います
またreplaceは所詮削除して追加する処理なので先に削除してからあとから追加してはどうでしょう?

いずれにしろ途中状態でアクセスされるとデータに不整合がおきますので
メンテナンス状態にしてテーブルロックしてしまうなり、アクセスをとめさせてから
別テーブルに一度仮データをつくっておいて、tbl_aをtruncateして一気に流したほうが
よいかもしれません。

投稿2018/12/25 02:35

yambejp

総合スコア114583

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kazunii_ac

2018/12/28 03:42

ご回答ありがとうございます! ちょっといま試行錯誤しています。「メンテナンス状態」というのがよくわからないのですが、ひとまず「LOCK IN SHARE MODE」をSQLに付与して試してみています。結果が出たらまたご報告します!
kazunii_ac

2018/12/29 13:21

「LOCK IN SHARE MODE」では回避不能でした。 「メンテナンス状態にする」とは、具体的にはどのようなコマンドを打つのでしょうか?お教え頂けたら嬉しいです。よろしくお願い致します!
yambejp

2019/01/07 03:28

ここでいう「メンテナンス状態にする」とは、UIがwebベースであれば httpdを止めて他のユーザーからのアクセスを回避することを指したつもりです replaceはとにかくやめて削除するところからでしょうね その後insertしてください
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問