古めのリビジョンを削除した時のメモ

説明

大きく育ったデータベースを整理するため、古めのリビジョンとそれに紐づいたカスタムフィールドデータを削除。と思ったら「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

お勧め

is_admin(2011年6月23日 更新)

bool is_admin( )
リクエストページが管理者ページ(/wp-admin/以下すべて)か調べる。

get_term_by(2020年8月28日 更新)

mixed get_term_by( string $field , mixed $value , string $taxonomy [ , string $output = OBJECT [ , string $filter = 'raw' ] ] )
ID、名前、スラッグを指定してカテゴリー・タグ情報を取得する。

remove_shortcode(2018年5月27日 更新)

void remove_shortcode( string $tag )
ショートコードを削除する。

wp_get_original_image_url(2019年11月19日 更新)

string | false wp_get_original_image_url( int $attachment_id )
オリジナルの画像ファイルのURLを取得する。

get_html_split_regex(2018年5月27日 更新)

string get_html_split_regex( )
HTML要素分割用の正規表現パターンを取得する。