10/22(金) 追記
この記事で解説している内容について解説する勉強会を開催することとなりました。以下のconnpassよりお申し込みください。
pixiv.connpass.com
pixivではサービスの成長に伴い、気に入った作品に対して付けることができるブックマークの総数が急速に増加しており、ユーザーの皆様に滞りなくサービスを提供し続けるためブックマークに関するデータベース(以後DB)の負荷対策が必要になりました。
2021年2月より対策を行うプロジェクトを発足し、先日ユーザーの皆様にこれまでお掛けしたいくつかのご不便の解消についてお知らせ することができました。
今回はこのブックマークDBに関する負荷対策プロジェクトについて技術的な観点からご紹介いたします。
pixivのブックマークについて
pixivでは気に入った作品に対して♡をすることで利用できるブックマーク機能があります。
ブックマークすることでお気に入りの作品を見返したり、クリエイターへ好きの気持ちを伝えることが可能です。
ブックマークはpixivに投稿できるイラスト*1 ・小説作品に対して行うことができますが、最も量が多いのがイラストブックマークです。近年サービスの成長やレコメンド機能の改善によって伴って急速に利用数が増加しています。
イラストのブックマークされた回数*2 は現在135億を突破し、月間およそ3億のペースで増加しています。
pixivではブックマークデータをMySQL(InnoDB)で管理しています。
pixivは用途ごとにDBを分割しており、イラスト関連のテーブルを収めたDB・小説関連のテーブルを収めたDBといった多数のDB系統を運用しています。その一つの系統としてブックマークDBと呼称している系統があります。
ブックマークDBでは
イラストブックマーク
ユーザーブックマーク（フォロー・フォロワー）
マイピク
を主に扱っており、現在(2021年10月)ピーク時で秒間200~300query/secのINSERT、(mainと3台のreplicaの計4台)30,000~35,000query/secのSELECTを捌いており、以下のスペックです。
CPU: Intel Xeon Silver 4110 * 2
メモリ: 1TB
ディスク: Intel SSD D3-S4510 7.68TB
MySQLバージョン: 5.7
DBファイルサイズ: 3.4TB
pixivはブックマークをサービス上非常に重要な機能と考えており、ブックマーク数やフォロー数についてユーザー毎の上限を設けるといった制限を極力しないよう運営してきました。
サービス提供開始からの歴史もあり、ヘビーユーザーの中にブックマーク数が100万を超えるユーザーも存在しています。
ブックマークDBの問題について
pixivは2007年9月にサービスインし今年14周年を迎えました。
www.pixiv.co.jp
ブックマーク機能はサービスの最初期から存在しているため、現在のサービス規模を考慮に含めていないテーブルスキーマがありました。そのため近年の急なブックマークの増加に伴い、特に多数のブックマークを持っているケースで非常に重いクエリが発行されることがあり問題になりました。
具体的な例を上げると、非公開・公開のイラストブックマークを持っているか正確に判定するクエリや特定のタグがついたブックマークを抽出するクエリがあります。
改善前のイラストブックマークテーブルのスキーマ
※本番スキーマを一部簡略化しています
CREATE TABLE `pix_bookmark_illust` (
`bookmark_id` bigint AUTO_INCREMENT COMMENT ' ブックマークID ' ,
`bookmark_illust_user_id` int COMMENT ' ブックマークしたユーザID ' ,
`bookmark_illust_illust_id` int COMMENT ' ブックマークされたイラストID ' ,
`bookmark_illust_comment` varchar COMMENT ' ブックマークコメント ' ,
`bookmark_illust_restrict` tinyint COMMENT ' ブックマーク公開設定(0=公開, 1=非公開) ' ,
`bookmark_illust_add_date` timestamp COMMENT ' ブックマーク時刻 ' ,
`bookmark_tag01` varchar ,
`bookmark_tag02` varchar ,
`bookmark_tag03` varchar ,
`bookmark_tag04` varchar ,
`bookmark_tag05` varchar ,
`bookmark_tag06` varchar ,
`bookmark_tag07` varchar ,
`bookmark_tag08` varchar ,
`bookmark_tag09` varchar ,
`bookmark_tag10` varchar ,
`bookmark_illust_delflg` tinyint COMMENT ' ブックマーク削除フラグ ' ,
PRIMARY KEY (`bookmark_id`),
KEY `bookmark_illust_illust_id` (`bookmark_illust_illust_id`),
KEY `bookmark_illust_user_id` (`bookmark_illust_user_id`,`bookmark_id`),
KEY `user_id_illust_id` (`bookmark_illust_user_id`,`bookmark_illust_illust_id`),
) ENGINE=InnoDB;
改善前の非公開判定クエリ
SELECT 1
FROM `pix_bookmark_illust`
FORCE INDEX (`bookmark_illust_user_id`)
WHERE
`bookmark_illust_user_id` = :user_id
AND `bookmark_illust_restrict` = :bookmark_illust_restrict_private
LIMIT 1
改善前のタグ絞り込みクエリ
SELECT
`bookmark_id`,
`bookmark_illust_illust_id`
FROM `pix_bookmark_illust`
FORCE INDEX (`bookmark_illust_user_id`)
WHERE
`bookmark_illust_user_id` = :user_id
AND `bookmark_illust_restrict` = :bookmark_illust_restrict
AND (
bookmark_tag01 = :bookmark_tag
OR bookmark_tag02 = :bookmark_tag
OR bookmark_tag03 = :bookmark_tag
…(tag10まで続く)
)
ORDER BY `bookmark_id` DESC
OFFSET :offset
LIMIT :limit
上記クエリは利用しているindexからわかるように、ユーザーに紐づくブックマーク数が増加することによってrowsが増え、それにより大きなDiskReadを発生させる問題があります。
またブックマークに付けることができる10個のタグがそれぞれのカラムに存在するため、検索のために効果的なindexを貼ることが困難ですし、将来的にタグを10個以上増やすといったこともできません。
ブックマークの増加に伴ってこれら効率の悪いクエリの発行によって発生するSlowQuery*3 量は増加しており対策が必要でした。
ブックマークDBのSlowQuery量の変化
ブックマークDBはこれまでこの状況を緩和するためにDBの物理メモリ量を増やしてきました、しかし1TB搭載の現在からさらに増加させることは調達の費用・時間コストさらに長期的に見てこのコストが指数的に増加していく点から現実的ではなく、根本的な対処を行うと決定しました。
対処するにあたっては
過去につくって表層だけ整えてきたサーバサイド実装の掘り返し
追加するindexの内容やそれについての検証
100億レコードを超えるテーブルの構造変更
等が必要です。
ピクシブ株式会社ではインフラチームと開発チームは体制上分かれていますが、今回は双方の密な連携が必要であったため、チームに準ずる特定課題への対処の組織としてピクシブで運用している「タスクフォース」を作りインフラメンバーと開発メンバーで対処にあたりました。
具体的な対策内容
複数の手段のあわせ技で対策しました。
論理削除廃止・index追加・ブックマークタグのテーブル分割
SlowQuery増加の問題はindexで効果的な絞り込みができていなかったことに大半が起因します。そのため効果的にindexを利用できるようにすることが目標になりました。
これを実現するため、主に以下3点を行いました。
論理削除フラグを廃止し物理削除化
公開設定を扱う絞り込みクエリに適用できるindexの追加
イラストブックマークタグ絞り込みをindexを用いて行うため、タグデータのテーブル分割
当初ブックマークについてはデータ量の多さなどから中長期を見据えMySQL以外への移行(pixivで検索に用いているSolrの利用や分散DBの採用等)も案に出ましたが、不確実性が大きいため、まずは正攻法でイラストブックマークタグを別テーブルとして分離し、タグの絞り込みはそのテーブルから行うことを検討しました。
初めにブックマークで行っていた論理削除を物理削除に変更する対応を、以下理由から行いました。
論理削除用のカラムは今後テーブル設計をする際に余計な要素になる
ディスク性能が上がった昨今においては物理削除のコストは許容出来る
その上で以下を考慮し新しいテーブル設計をしました。
クエリ発行量が非常に多いため、単一テーブルで完結する構造にする (テーブルJOINを避ける)
イラストブックマークタグが設定されていないレコードについても最小コストで抽出出来るようにする
その結果以下のテーブル構造が完成しました。
変更後のイラストブックマークテーブルのスキーマ
※本番スキーマを一部簡略化しています
CREATE TABLE `pix_bookmark_illust` (
`bookmark_id` bigint AUTO_INCREMENT COMMENT ' ブックマークID ' ,
`bookmark_illust_user_id` int NOT NULL COMMENT ' ブックマークしたユーザID ' ,
`bookmark_illust_illust_id` int COMMENT ' ブックマークされたイラストID ' ,
`bookmark_illust_comment` varchar COMMENT ' ブックマークコメント ' ,
`bookmark_illust_restrict` tinyint COMMENT ' ブックマーク公開設定(0=公開, 1=非公開) ' ,
`bookmark_illust_add_date` timestamp COMMENT ' ブックマーク時刻 ' ,
`bookmark_illust_untagged_flg` tinyint COMMENT ' ブックマーク未分類フラグ(0=タグがついている, 1=タグがない) ' ,
PRIMARY KEY (`bookmark_id`),
KEY `bookmark_illust_illust_id` (`bookmark_illust_illust_id`),
KEY `bookmark_illust_user_id_restrict_id_illust_id` (`bookmark_illust_user_id`,`bookmark_illust_restrict`,`bookmark_id`,`bookmark_illust_illust_id`),
KEY `bookmark_illust_user_id` (`bookmark_illust_user_id`,`bookmark_id`),
KEY `user_id_illust_id` (`bookmark_illust_user_id`,`bookmark_illust_illust_id`),
KEY `user_id_restrict_untagged_flg_id_illust_id` (`bookmark_illust_user_id`,`bookmark_illust_restrict`,`bookmark_illust_untagged_flg`,`bookmark_id`,`bookmark_illust_illust_id`),
KEY `user_id_restrict_untagged_flg_illust_id_id` (`bookmark_illust_user_id`,`bookmark_illust_restrict`,`bookmark_illust_untagged_flg`,`bookmark_illust_illust_id`,`bookmark_id`)
) ENGINE=InnoDB;
変更点
論理削除フラグ、ブックマークタグカラムを削除
ブックマークしたユーザーについてさらに公開設定で絞り込めるようにするindexを追加
ブックマークタグがついているかどうかを示すカラムを追加し、indexを追加
新しく作成したイラストブックマークタグテーブルのスキーマ
CREATE TABLE `pix_bookmark_illust_tag` (
`bookmark_id` bigint COMMENT ' ブックマークID ' ,
`bookmark_illust_user_id` int COMMENT ' ブックマークしたユーザーID ' ,
`bookmark_illust_illust_id` int COMMENT ' ブックマークされたイラストID ' ,
`bookmark_illust_restrict` tinyint COMMENT ' ブックマーク公開設定(0=公開, 1=非公開) ' ,
`bookmark_illust_tag_name` varchar COMMENT ' ブックマークタグ ' ,
PRIMARY KEY (`bookmark_id`,`bookmark_illust_tag_name`),
KEY `user_id_restrict_tag_name_id_illust_id` (`bookmark_illust_user_id`,`bookmark_illust_restrict`,`bookmark_illust_tag_name`,`bookmark_id`,`bookmark_illust_illust_id`),
KEY `user_id_restrict_tag_name_illust_id_id` (`bookmark_illust_user_id`,`bookmark_illust_restrict`,`bookmark_illust_tag_name`,`bookmark_illust_illust_id`,`bookmark_id`)
) ENGINE=InnoDB;
ポイント
元々のブックマークテーブルにあったtag01~tag10についてをこのテーブルで再現
JOINが不要になるようにクエリで必要なカラム(ユーザID等)をこのテーブルにも配置
テーブル構造が固まった後は、この構造で本当に問題ないのかを確認するため、サービス環境で実際に発行されているクエリを取得し、テストクエリを作成・検証環境で実行し並列で高速に捌くことができることを検証しました。
その結果、テーブル構造の変更によってこれからの運用が可能であることが分かりテーブル構造の変更を行うことにしました。
テーブル構造を変更することにより、諸問題を解消することができることが分かりましたが、100億以上のレコードを持つブックマーク関連テーブルに対する1スレッドでのALTER TABLEは現実時間で完了しないため、特別な対応が必要でした。
ALTERは大まかに以下の手順で擬似的に実現できます。
ユーザーリクエストを受けていないDBを用意しレプリケーションを追従させておく
レプリケーションを停止
移行したい構造を持ったテーブルを ${table_name}_newのような名前で新規に作成
一定区間に切りながらデータを旧テーブルから並列にSELECTし新テーブルにINSERTするスクリプトを実行 旧テーブルをdropし、新テーブルをrenameして置き換え
レプリケーションを再開させ追従
新しいテーブル構造を持ったDBファイルができるのでこれを本番のreplicaに配布
replicaの1台をmainに昇格(旧mainはパージ)
区間を切りながら並列にSELECTしINSERTして新しいデータを作成することはALTER文より高速なので現実時間で構造変更ができます。
イラストブックマークの場合、新しいテーブルのデータを作成するスクリプトに1週間。作業の中で新旧テーブルの内容がズレていないかといった確認や本番DBのデータ置き換え作業等を含めると一回約1ヶ月を要しました。
この擬似的なALTERをベースとし、必要に応じてトリガー やデータを補正するためのバッチスクリプトを組み合わせ、pixivを無停止でテーブル構造の変更を行いました。
テーブル構造の変更を行ったことで、様々なクエリをindexを効かせて効果的に捌く事が出来るようになりました。先に例示した一部ケースで非常に重くなってしまうクエリなどは下記のように変化しました。
先のケースでは一部クエリで本番実行すると非常に重く障害に至る可能性がありましたが、改善後のクエリはユーザーのブックマークが増加しても高速に応答出来るようになりました。
改善後の非公開判定クエリ
SELECT 1
FROM `pix_bookmark_illust`
FORCE INDEX (`user_id_restrict_untagged_flg_id_illust_id`)
WHERE
`bookmark_illust_user_id` = :user_id
AND `bookmark_illust_restrict` = :bookmark_illust_restrict_private
LIMIT 1
改善後のタグ絞り込みクエリ
SELECT
`bookmark_id`,
`bookmark_illust_illust_id`
FROM `pix_bookmark_illust_tag`
FORCE INDEX (`user_id_restrict_tag_name_id_illust_id`)
WHERE
`bookmark_illust_user_id` = :user_id
AND `bookmark_illust_restrict` = :bookmark_illust_restrict
AND `bookmark_illust_tag_name` = :bookmark_illust_tag_name
ORDER BY `bookmark_id` DESC
OFFSET :offset
LIMIT :limit
今回新しいタグテーブルの作成や、既存のイラストブックマークテーブルにindexを新たに追加しまた。それによりDBのファイルサイズ増加と、indexの更新コストが増えたことで、index更新時のロック待ちによる競合が悪化する懸念がありました。
事前にindexあり・なしで更新の負荷が問題ないか検証を行い、問題ないことを確認した上で一部本番サーバに適用を行いましたが、本番環境では定期的にデータ削除を行うバッチ処理が走っており、その直後からロック待ち状況を表しているSemaphoresの値が悪化していることに気づきました。
このままではロック待ちが大量に発生することで、実行クエリが滞留し障害になる可能性もあるため改善に向けた調査・対応を行うことにしました。
詳細については紆余曲折ありここに記載しきれないので割愛しますが、以下の対応を実施しました。
“SHOW ENGINE INNODB STATUS” コマンドを実行し ”btr0sea.cc” 内の処理で大量にロック待ちが発生していることを確認
MySQLのソースコードから該当処理を調査し、適応ハッシュインデックスにおけるindex更新処理でロックを取っていることが判明
適応ハッシュインデックスにおけるヒット率を確認し、ヒット率が低いことを確認した上で、適応ハッシュインデックス(innodb_adaptive_hash_index)の無効化を実施
適応ハッシュインデックスを無効化した後、どのぐらいロック待ちが改善したかは以下グラフを見ていただければ歴然だと思います。
今回のブックマークDBにおいては適応ハッシュインデックスはヒット率が低く、逆に更新コストにおけるロック待ちの悪化というデメリットの方が大きかったため無効化を行いました。
しかし環境によっては有効化していたほうがクエリの高速化やディスクIOを抑えられるというメリットもあります。適応ハッシュインデックスはデフォルト有効なので、ヒット率やロック待ち状況などを総合的に判断して有効・無効の判断を行うことになるかと思います。
ブックマークDBのSemaphoresの変化
アプリケーションコードのリファクタリング・全発行クエリの列挙と見直し
DB負荷に対処する上でアプリケーションでどのようなクエリが何を目的として発行されているのかを把握することは本質的な改善を行う上で重要です。重いクエリでもプロダクト側としてみると重要ではないクエリで、軽いクエリに置き換えられたり、そもそもの機能の要件に対してアプローチできたりするからです。
しかしpixivにおいてブックマーク関連実装は最初期に作られ、ほとんど手をいれずに必要になった段階で外側を整えてきた結果、内部実装は混沌とした状態になっていました。全く同じようなクエリがあったり、必要ないカラムを取得していたり等です。
ただ幸いにも、pixivはモノレポジトリ構成を採用しておりコードは1レポジトリだけにあり、DBへのアクセス方法は1つ(内製のPDOラッパーを利用)で、またpixiv以外からブックマークDBへの直接アクセスを行うサービスはありませんでした。
実装を整理する銀の弾丸はないので、地道にリファクタリングを繰り返し、不要なSELECT *を廃止し必要なカラムだけを取得するようにし、適切に関数分割と統合を行い、最終的にブックマークDBへの全クエリをスプレッドシートにまとめ整理しました。取得するカラム・絞り込む条件・ソート等の観点で類似のものをまとめたクエリ数はイラストブックマークで約30種類でした。
これらクエリをインフラチームと開発チームで確認しindexを検討し対応しました。
大きな更新処理の非同期化
pixivはブックマークタグを書き換える機能 を持っています。
これは既存のブックマークに付けたタグを一括して別のタグ名に変更する(必要であればマージ)処理ですが、ブックマークに多数同じタグを付けている場合大量レコードのUPDATEを発生させる問題が従来からありました。
今回のイラストブックマークタグの書き換え機能では最大で50万行程度の更新が見込まれました。
ユーザー起因処理で一度に50万のレコードをアップデートすることは現実的ではないので、今回pixivではこれらのユーザーリクエストをキュー化し非同期に毎秒一定数を更新できるようにしました。
pixivではこれまでこの手の処理に対して都度DBに作ったキューテーブルとJenkinsを用いたバッチ実行で実装していましたが、リトライ等まで考えると都度実装の手間が大きいという問題がありました。今回GoogleCloudPlatformのCloudTasks をタスクコントローラーとして用い、pixiv側のDBと連携して一つの大きな処理を複数に分割して実行できる処理基盤をpixivに実装しました。
これによりブックマークタグの書き換えだけでなく、pixivで大量のデータ更新を免れない要件を持つ機能に対して容易にDB更新量を一定に抑えたうえで実現することができるようになりました。
結果
諸々の対応の結果、6月のindexの追加、9月のタグ絞り込みに関するテーブル分割の完了により、ブックマークDBのSlowQueryの数を最終的にほぼ0にすることができました。
また対応以前は出ていたSlowQueryの大半について処理時間が数秒~数十秒かかる物だったのですが、対応後にわずかに出ているSlowとなるクエリは1秒未満のものとなっています。
クエリの応答時間はpixivのブックマークページ等の表示時間に直結するので、場合によっては表示に時間を要していたページも高速に表示されるようになりました。
ブックマークDBのSlowQuery量の変化
そしてブックマーク数の増加が特段の懸念にならない状態になったので、サービス上で多数のブックマークを持っている場合にやむなく行っていた以下の制限を解消しました。
イラストブックマークタグ絞り込み
イラストブックマークタグ書き換え
今回の取り組みによって過去困難であったブックマークの根本的な問題に対処し、ユーザーの皆様にこれまでお掛けしたいくつかのご不便の解消ができました。
pixivはこれからも機能の改善に努めてまいります。
あわせてよみたい
