毎日暑い…。というか蒸し暑い。何年か前にベトナムとカンボジアをバックパッカー旅行したときを思い出す。日本の気候が亜熱帯ってか東南アジアっぽくなってきたような…昔はこんな頻繁にゲラリ豪雨とか降らなかったよねー?温暖化ってやつ?日本の植生とか変わるんじゃねーのかな…。
えーと、おれがよく見ている技術ブログの一つにPercona社のMySQL Performance Blogがある。そのブログに先日、「Why is the ibdata1 file continuously growing in MySQL?」という記事が投稿された。内容はInnoDBのibdata1の肥大化とその解消方法に関するもの。ibdata1の肥大化を解消する手段は、ダンプをとってDBを作り直してあげないと治らないということは多くのInnoDBユーザが知っていることだと思うけど、おれもInnoDBを触り始めたころは、「気がついたらibdata1ってのがなんかでかくなってる…なんだこれ…」と思ったりして調べたりした。どうやらPercona社には、この誰もが直面するであろう疑問(?)に関する質問が多く寄せられるようで、投稿された記事にはこれに対する回答、InnoDBの仕組みの説明、問題の原因の特定手法、分析に便利なツールの紹介、解決手法が書かれていた。説明がわかりやすかったので、引用&意訳をしてみる。



以下、引用と意訳。

Why is the ibdata1 file continuously growing in MySQL?

We receive this question about the ibdata1 file in MySQL very often in Percona Support.
The panic starts when the monitoring server sends an alert about the storage of the MySQL server – saying that the disk is about to get filled.
After some research you realize that most of the disk space is used by the InnoDB’s shared tablespace ibdata1. You have innodb_file_per_table enabled, so the question is:
What is stored in ibdata1?

なぜibdata1の容量は増え続けるのか?

我々はPercona Supportにibdata1に関する質問をよくもらう。パニックの開始は監視サーバがMySQLサーバのストレージ関連のアラートを送ってきたときだ。ディスク容量が少なくなってきた、と。

調査すると、InnoDBの共有テーブルスペースibdata1がほとんどのディスク容量を使っていることがわかる。innodb_file_per_tableは有効になっている。そして思う「ibdata1には何が入っているのか?」

What is stored in ibdata1?

When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data:
– data dictionary aka metadata of InnoDB tables
– change buffer
– doublewrite buffer
– undo logs
Some of them can be configured on Percona Server to avoid becoming too large. For example you can set a maximum size for change buffer with innodb_ibuf_max_size or store the doublewrite buffer on a separate file with innodb_doublewrite_file.
In MySQL 5.6 you can also create external UNDO tablespaces so they will be in their own files instead of stored inside ibdata1. Check following documentation link.

ibdata1には何が入っているのか?

innodb_file_per_tableを有効にしていると、テーブルのデータはそれぞれのテーブルスペースに格納される。しかし共有テーブルスペースには次のようなInnoDBの内部データが格納されている。

  • データディクショナリ(InnoDBテーブルのメタデータ)
  • チェンジバッファ
  • ダブルライトバッファ
  • UNDOログ
Percona Serverでは肥大化を回避するための設定を施すことができる。例えば、innodb_ibuf_max_sizeによって、チェンジバッファの最大サイズを設定でき、innodb_doublewrite_fileによって、ダブルライトバッファを別ファイルに格納することもできる。
MySQL5.6においては、UNDOテーブルスペースもまた外出しにすることができる。詳細はこちらのリンクを参照のこと(http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-undo-tablespace)

What is causing the ibdata1 to grow that fast?

Usually the first command that we need to run when there is a MySQL problem is:

SHOW ENGINE INNODB STATUS\G

That will show us very valuable information. We start checking the TRANSACTIONS section and we find this:

—TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

This is the most common reason, a pretty old transaction created 14 days ago. The status is ACTIVE, that means InnoDB has created a snapshot of the data so it needs to maintain old pages in undo to be able to provide a consistent view of the database since that transaction was started. If your database is heavily write loaded that means lots of undo pages are being store.
If you don’t find any long-running transaction you can also monitor another variable from the INNODB STATUS, the “History list length.” It shows the number of pending purge operations. In this case the problem is usually caused because the purge thread (or master thread in older versions) is not capable to process undo records with the same speed as they come in.

ibdata1の肥大化を早めているのは何か?

MySQLに問題が発生したときに我々が最初に使うコマンドはこちらである。

1
SHOW ENGINE INNODB STATUS\G

このコマンドはとても有用な情報を表示してくれる。TRANSACTIONSセクションをチェックすると、次のような表示が見つかる。

1
2
3
4
---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id &gt;= 36F, sees &lt; 36F

かなり古いトランザクションが14日前(1256288秒前)に作られている。そしてステータスがACTIVEである。これは、トランザクションが開始されたことによって、一貫性を提供するために古いページをUNDOに保持しておく必要があるので、InnoDBがデータのスナップショットを作ったということ意味する。もし、データベースに大量の書き込みが行われているのであれば、多くのUNDOページが格納されるということを意味する。

長時間実行されているトランザクションが見つからないのであれば、INNODB STATUSのHistory list lengthを見る。この値はペンディングされているパージ処理の数を表している。この場合、大抵の問題はパージスレッド(古いバージョンではマスタースレッド)が、UNDOレコードが増える速度とと同じ速度で処理できないことが原因である。

How can I check what is being stored in the ibdata1?

Unfortunately MySQL doesn’t provide information of what is being stored on that ibdata1 shared tablespace but there are two tools that will be very helpful. First a modified version of innochecksum made by Mark Callaghan and published in this bug report.
It is pretty easy to use:

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

It has 19272 UNDO_LOG pages from a total of 20608. That’s the 93% of the tablespace.
The second way to check the content of a tablespace are the InnoDB Ruby Tools made by Jeremy Cole. It is a more advanced tool to examine the internals of InnoDB. For example we can use the space-summary parameter to get a list with every page and its data type. We can use standard Unix tools to get the number of UNDO_LOG pages:

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

Altough in this particular case innochecksum is faster and easier to use I recommend you to play with Jeremy’s tools to learn more about the data distribution inside InnoDB and its internals.
OK, now we know where the problem is. The next question:

ibdata1に格納されているものをチェックする方法

残念ながら、MySQLはibdata1に格納されている情報を提供しない。しかしながら、便利な2つのツールがある。1つはinnochecksumの修正されたバージョン。Mark Callaghanがバグレポートで公開している。使用方法は非常に簡単。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

全20608ページ中、19272のUNDOログがあり、テーブルスペースの93%を占めていることがわかる。

2つめの方法は、Jeremy Cole(※)作のInnoDB Ruby Toolsを使うことだ。これはさらに深くInnoDBの内部を調査するツール。例えば、space-summaryパラメータで、全ページとそのデータ型のリストを取得できる。UNDOログのページ数は、次のようにして標準のUNIXコマンドで取得できる。

1
# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l

このケースにおいてはinnochecksumの方が簡単で早いが、InnoDBの内部構造をさらに学ぶためにJeremyのツールを使うことをお勧めする。

さて、問題の個所を特定したところで、次の質問である。どうやって解決するか?

※ 訳とは関係ないんだけど、どっかで見たことある顔だと思ったら、swap insanityとNUMAに関する記事を書いていた人だった。この記事にもお世話になった。

How can I solve the problem?

The answer to this question is easy. If you can still commit that query, do it. If not you’ll have to kill the thread to start the rollback process. That will just stop ibdata1 from growing but it is clear that your software has a bug or someone made a mistake. Now that you know how to identify where is the problem you need to find who or what is causing it using your own debugging tools or the general query log.
If the problem is caused by the purge thread then the solution is usually to upgrade to a newer version where you can use a dedicated purge thread instead of the master thread. More information on the following documentation link.

問題の解決手法

答えは簡単。クエリをコミットする。できないなら、ロールバックを開始するためにスレッドをkillする必要がある。これによってibdata1の肥大化が止まると、だれかがミスをしたか、クエリを発行しているプログラムにバグがあることが明らかになる。さて、ここからはデバッグツールや一般クエリログによって、何が/誰が問題の原因だったかを特定できる。

もし、原因がパージスレッドなら、専用のパージスレッドが使えるバージョン(※)にアップグレードすることによって解決できる。詳細はこちらのリンクを参照。

※ 上記リンク先参照。MySQL5.5以降、ってかInnoDB1.1以降。このあたりからパージスレッドが独立した。

Is there any way to recover the used space?

No, it is not possible at least in an easy and fast way. InnoDB tablespaces never shrink… see the following 10-year old bug report recently updated by James Day (thanks):
When you delete some rows, the pages are marked as deleted to reuse later but the space is never recovered. The only way is to start the database with fresh ibdata1. To do that you would need to take a full logical backup with mysqldump. Then stop MySQL and remove all the databases, ib_logfile* and ibdata* files. When you start MySQL again it will create a new fresh shared tablespace. Then, recover the logical dump.

逼迫されてしまったストレージ容量を治す方法

ない。少なくとも、簡単に素早く治すのは不可能。InnoDBのテーブルスペースは小さくならない。この10年前のバグレポートを参照のこと…10-year old bug report(最近、James Dayによってアップデートされた。Thanks)。

いくつかの行を削除すると、ページは再利用のために”削除された”としてマーキングされる。しかし削除されたスペースは回復しない。治す唯一の方法は、新しいibdata1を作る。手順は次の通り。

  • mysqldumpでフルダンプを取得する
  • MySQLを停止する
  • 全部のデータベースとib_logfile*とibdata*を削除する。
  • MySQLを起動する(新しいibdata1が作られる)
  • フルダンプを流し込む

Summary

When the ibdata1 file is growing too fast within MySQL it is usually caused by a long running transaction that we have forgotten about. Try to solve the problem as fast as possible (commiting or killing a transaction) because you won’t be able to recover the wasted disk space without the painfully slow mysqldump process.
Monitoring the database to avoid these kind of problems is also very recommended. Our MySQL Monitoring Plugins includes a Nagios script that can alert you if it finds a too old running transaction.

まとめ

MySQLにおいて、ibdata1が急速に肥大化するのは、長時間実行されているトランザクションが原因である。この問題をできるだけ早く解決するには、コミットするかトランザクションのkillを試す。そうしないと、非常に遅いmysqldumpからのリストアをする羽目になり、これをやらないと使ってしまったディスク容量は回復しない。

この手の問題を回避するためのデータベース監視を強く推奨する。我々のMySQL Monitoring Pluginsは長時間実行されているトランザクションを見つけたらアラートを飛ばしてくれるnagiosプラグインがあるよ。

 

InnoDBの仕組み、問題の特定の手法、便利ツールの紹介、解決方法の説明。そして最後、宣伝で終わるのはさすが。

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <img localsrc="" alt="">

Set your Twitter account name in your settings to use the TwitterBar Section.