mysqlでメモリ使用量が増えつづける現象が起こって、最終的にはOOM Killerに殺されてサービスが一時停止するという状況が発生して困っていました。

前提条件

• mysql8
• 数年間運用しているシステム
• mysqlのfulltextインデックスの検索を使っている。

状況

• mysqlのfulltextインデックスの検索が非常に遅くなっている。以前は遅くなかった。
• 高負荷状態で、mysql で、SHOW PROCESSLIST を確認すると FULLTEXT initialization になっているプロセスがある。ということは、全文検索の初期化処理で時間がかかっている。
• fulltextインデックスのあるテーブルの更新頻度がかなり高い状態で、何年も運用している。
• mysqlのメモリ使用量が増えていき、空いてる領域を全て確保する勢い。swapは常に占領してしまう。mysqlが落ちるとswapも開放される。

対応

最初はmysqlの設定やら、メモリアロケーションをOSのmallocではなくtcmallocを使うようにしたりしましたが、抜本的な効果は見られませんでした。

InnoDBでは、deleteした行は消されないのでフラグメント化してしまうため、optimizeが必要になるようです。 https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html しかし、今回問題になったテーブルはdeleteはそれほど高頻度には実行されないテーブルでした。updateは頻繁に行なわれています。

optimize tableは、テーブルをロックすることがあるそうなので、同様の効果があり停止時間が短かくなりそうな、新しいテーブルを作ってデータを投入した後に、リネームする方法を取りました。例として対象のテーブルが posts だった場合のsqlは以下です。

-- 同じスキーマでテーブルを作成する。
create table posts_new like posts;
insert into posts_new select * from posts;

-- 名前を変更する。
rename table posts to posts_old, posts_new to rooms;

前後で、fulltextの検索を行なったところ、最大160倍のパフォーマンス改善がありました。改善というより、元の検索速度に戻っただけというのが実情のようですが。

追記(2024-01-13)

上記の対応で一時的な改善をしていたのですが、その後も度々メモリ使用量が増加する現象は起こっていました。

その後問題が発生していたシステムに対して、別の理由から全文検索を行なわないようにする仕様変更が入りました。全文検索を行なわなくなってからは、メモリ使用量が増加しつづける現象は発生していません。

全文検索に関して使い方が適切でないなど何かしらの問題があったと推測されますが、別のmysql8のサーバーでは問題が発生していなかったりしたので、mysql8のバージョンによって引き起こされている可能性もあるかも、と思いました。可能であれば、mysql8のバージョンアップを行なうというのは試した方が良いかもしれません。