この記事は最後に更新してから1年以上経過しています。
説明
大きく育ったデータベースを整理するため、古めのリビジョンとそれに紐づいたカスタムフィールドデータを削除。と思ったら「The total number of locks exceeds the lock table size」というエラーが発生して削除できなかった。
カスタムフィールドのデータ量
サイトによってカスタムフィールドの利用状況はまちまちだが、カスタムフィールドを多用しているとそのデータ量は侮ることはできない。その一例がこちら。
mysql> SELECT
-> table_name,
-> LPAD(FORMAT(table_rows, 0),11,' ') AS `rows`,
-> LPAD(FORMAT(FLOOR(data_length/1024), 0),11,' ') AS `data[KB]`,
-> LPAD(FORMAT(FLOOR(index_length/1024), 0),11,' ') AS `index[KB]`,
-> LPAD(FORMAT(FLOOR((data_length+index_length)/1024), 0),11,' ') AS `total[KB]`
-> FROM information_schema.tables
-> WHERE table_schema=DATABASE() AND table_name LIKE 'wp_post%'
-> ORDER BY table_name DESC;
+-------------+-------------+-------------+-------------+-------------+
| table_name | rows | data[KB] | index[KB] | total[KB] |
+-------------+-------------+-------------+-------------+-------------+
| wp_posts | 106,846 | 36,416 | 23,696 | 60,112 |
| wp_postmeta | 9,741,635 | 1,403,904 | 664,576 | 2,068,480 |
+-------------+-------------+-------------+-------------+-------------+
リビジョンを含む投稿データがwp_postsテーブルに、カスタムフィールドデータがwp_postmetaテーブルに格納されている。投稿データとスタムフィールドデータを比較すると、データ件数・データ量ともかなり大きい。試しに最終更新日が2020年1月1日より古いリビジョンとそのカスタムフィールドの件数を調べてみる。
mysql> SELECT FORMAT(COUNT(*),0) FROM wp_posts WHERE post_type='revision' AND post_modified<'2020-01-01';
+--------------------+
| FORMAT(count(*),0) |
+--------------------+
| 33,203 |
+--------------------+
mysql> SELECT FORMAT(COUNT(*),0) FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type='revision' AND post_modified<'2020-01-01');
+--------------------+
| FORMAT(count(*),0) |
+--------------------+
| 4,327,714 |
+--------------------+
古めのリビジョンとカスタムフィールドデータが全体の1/3前後を占めていることがわかる。
ということで、カスタムフィールドデータ側から削除しようと思い、次のクエリーを実行する。
mysql> DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type='revision' AND post_modified<'2020-01-01');
ここで待つこと数十分。次のようなエラーメッセージが表示され、データは削除できなかった。
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
ネットで検索してみると、クエリーの実行中「innodb_buffer_pool_size」で指定したメモリーが不足し、エラーになったことがわかった。さっそく現在の「innodb_buffer_pool_size」の設定を確認すると、次のように「8192K（8M）バイト」だった。
mysql> SELECT @@innodb_buffer_pool_size/1024;
+--------------------------------+
| @@innodb_buffer_pool_size/1024 |
+--------------------------------+
| 8192.0000 |
+--------------------------------+
削除しようとしたカスタムフィールドデータ量を（インデックス込みで）計算すると「918,920.68 = 2,068,480 / 9,741,635 * 4,327,714」（単位はKB）。圧倒的に足りてない。。。
仕方ないので、削除するカスタムフィールドデータを減らすことに。
mysql> SELECT FORMAT(COUNT(*),0) FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type='revision' AND post_modified<'2018-01-01');
+--------------------+
| FORMAT(count(*),0) |
+--------------------+
| 309,828 |
+--------------------+
データ件数は1/10未満になったが、データ量は「65,787.0082 = 2,068,480 / 9,741,635 * 309,828」と、まだ大幅にオーバーしている。
innodb_buffer_pool_sizeを大きくする
もっと最終更新日の期間を絞っていけばいずれは削除できるようになるが、何十回もクエリーを実行するのは避けたい。そこで今度は「innodb_buffer_pool_size」の指定を増やしてみる。「innodb_buffer_pool_size」の指定は、my.cnf（my.ini）に記述されている。エディターでmy.cnf（my.ini）を開き、「innodb_buffer_pool_size」の「8M」を「128M」に変更した（あわせて「innodb_log_file_size」も増やす）。
[mysqld]
中略
innodb_buffer_pool_size=128M
中略
innodb_log_file_size=512M
ファイルを保存したら「mysqld」を再起動。あらためてカスタムフィールドを削除する。
mysql> DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type='revision' AND post_modified<'2018-01-01');
数分待ってようやく問題なく削除できたので、最終更新日を半年分ずつ変更しながらクエリーを繰り返す。カスタムフィールド側が終わったら、リビジョン側も同じ期間分削除する。
mysql> DELETE FROM wp_posts WHERE post_type='revision' AND post_modified<'2020-01-01';
ここでwp_postsとwp_postmetaテーブルを確認する。
+-------------+-------------+-------------+-------------+-------------+
| table_name | rows | data[KB] | index[KB] | total[KB] |
+-------------+-------------+-------------+-------------+-------------+
| wp_posts | 72,741 | 36,144 | 22,624 | 58,768 |
| wp_postmeta | 6,538,742 | 1,526,016 | 716,512 | 2,242,528 |
+-------------+-------------+-------------+-------------+-------------+
どちらのテーブルもデータ件数は減っている。一方のデータ量は、wp_postsテーブルは少し減っているが、wp_postmetaテーブルはむしろ増えている。このことを検索すると、「OPTIMIZE TABLE」クエリーを実行することで改善できるようだ。さっそく試してみる。
mysql> OPTIMIZE TABLE wp_posts;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+-------------------------------------------------------------------+
| wplocal.wp_posts | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| wplocal.wp_posts | optimize | status | OK |
+------------------+----------+----------+-------------------------------------------------------------------+
mysql> OPTIMIZE TABLE wp_postmeta;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+-------------------------------------------------------------------+
| wplocal.wp_postmeta | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| wplocal.wp_postmeta | optimize | status | OK |
+---------------------+----------+----------+-------------------------------------------------------------------+
今回使用した開発環境のMySQLは「optimize」をサポートしておらず、テーブルが再構築されたようだ。あらためてwp_postsとwp_postmetaテーブルを確認する。
+-------------+-------------+-------------+-------------+-------------+
| table_name | rows | data[KB] | index[KB] | total[KB] |
+-------------+-------------+-------------+-------------+-------------+
| wp_posts | 73,519 | 26,160 | 13,408 | 39,568 |
| wp_postmeta | 6,105,420 | 957,424 | 363,152 | 1,320,576 |
+-------------+-------------+-------------+-------------+-------------+
どちらのテーブルもデータ量が大きく減少した。
今回はプラグインを使用せず、古めのリビジョンデータを削除してみた。何となキリがよかったので「最終更新日が2020年1月1日より古いもの」としたが、もう少し期間を広げればデータ量を1GBは節約できる。投稿数が万件単位のサイトなら、リビジョンデータの整理を定期的に実施した方がよさそうだ。
最終更新 : 2022年05月16日 17:00
お勧め
add_shortcode（2018年5月27日 更新）
void add_shortcode( string $tag, mixed $func )
ショートコード（独自タグ）を追加する。ショートコードは、投稿記事内でテキスト内容がない[tag]や、テキストを内包する[tag]テキスト[/tag]の書式で使用できる独自タグのこと。標準の状態では、 the_content関数によって表示する直前のフィルター処理内でパラメータ$funcで指定した関数・メソッドが実行される。
get_user_setting（2022年1月31日 更新）
mixed get_user_setting( string $name [ , string $default = false ] )
ユーザーインターフェイス設定を取得する。
have_posts（2018年5月27日 更新）
bool have_posts( )
次の投稿データが存在するかを調べる。
in_category（2018年5月27日 更新）
bool in_category( mixed $category [ , mixed $post = null ] )
投稿情報が指定したカテゴリーに属しているか調べる。
add_feed（2024年6月24日 更新）
string add_feed( string $feedname, callable $callback )
フィードを追加する。