Around the World

すとぶろ

MySQL運用が長くなってきたときに気をつけたい2つのポイント

この記事はCyberAgent エンジニア Advent Calendar 2015の18日目の記事です。 記事のアップが大幅に遅延してしまったことをお詫び致します。。。

www.adventar.org

今回はMySQLの運用が長くなってきたときによく起こりがちな問題と対策を2つ記します。

増加するデータ容量

運用が長くなればなるほど、おおよその場合データ容量は増え続けます。 さらにInnoDBの場合、レコードをDeleteしてもデータ容量自体は減りません。 Deleteしたあとはテーブルをオプティマイズしましょう。

> DELETE from sample_table WHERE update_datetime < '2014-12-24 00:00:00' LIMIT 100000;
> ALTER TABLE sample_table ENGINE=InnoDB;

削除件数が少なかったりレコード件数が少ないテーブルであればLIMITはいらないかもしれないですが、多いテーブルの場合は件数を絞り、間にsleepを入れるなどして削除していくと負荷に優しいです。 ALTER文も必要に応じてpt-online-schema-changeを用いましょう。

strsk.hatenablog.com

重複したインデックス

最初は必要がなかったけど、ソートする要件が加わったためORDER BY狙いのインデックスを作成したり、カラム追加に伴ってインデックスを作成したりといったことはよくあると思います。 それが重なっていくと、テーブル内に重複したインデックスが存在する可能性が高くなり、メモリの利用効率が悪くなります。 起こりうる変化は予め対策すべきですが、すべてを予期することは困難ですし、ハズレた場合は無駄も多くなります。

そんなときは、pt-duplicate-key-checkerの出番です。 https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html

$ pt-duplicate-key-checker  u=xxx,p=xxx --database=sample_db

# A software update is available:
#   * The current version for MySQL Community Server (GPL) is 5.6.24.

# ########################################################################
# sample_db.friend
# ########################################################################

# user_id is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `user_id` (`user_id`,`request_user_id`),
#   PRIMARY KEY (`user_id`,`request_user_id`),
# Column types:
#     `user_id` int(11) not null
#     `request_user_id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `sample_db`.`friend` DROP INDEX `user_id`;

# ########################################################################
# sample_db.gift
# ########################################################################

# idx_userId_status is a left-prefix of idx_useridStatusUpddatetime
# Key definitions:
#   KEY `idx_userId_status` (`user_id`,`status`) USING BTREE,
#   KEY `idx_useridStatusUpddatetime` (`user_id`,`status`,`upd_datetime`),
# Column types:
#     `user_id` int(11) not null
#     `status` mediumint(8) unsigned default null
#     `upd_datetime` datetime not null
# To remove this duplicate index, execute:
ALTER TABLE `sample_db`.`gift` DROP INDEX `idx_userId_status`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes   18895972132
# Total Duplicate Indexes  48
# Total Indexes            1286

実行すると、重複したインデックスと削除するためのALTER文が出力されます。 このサンプルでは重複したインデックスが48件も検出されています。これはすぐにでも削除したいところです。 削除については同じく、pt-online-schema-changeを利用するなり、計画的に削除しましょう。

最後に

他にも運用していると起こりうる問題はいろいろありますが、とりあえず今回はこの2点だけお伝えしました。