condition fanout filterで爆死
これは MySQL Casual Advent Calendar 2017 の Day13 の記事です。
5.6から5.7にバージョンアップした際に実行計画絡みでトラブった事を書きます。 タイトルにあるcondition fanout filterとはMySQL5.7から追加されたOptimizer Switchで、条件句での絞り込みを実行計画生成の考慮に入れるものです。
資料
MySQL :: [MySQL 5.7入門セミナー講演資料] チューニング基礎編、SQLチューニング編 (※要Oracleアカウント登録(無料))
どれくらい絞りこめると判断したかどうかはExplainのfilterd列に表示されています(数字が少ないほど絞り込めている(と判断した))。
下記のテーブル構成(FK以外のINDEXは無し)を元に説明します。
-- 約8,000行(一番少ない) CREATE TABLE `company` ( `company_id` bigint(20) NOT NULL AUTO_INCREMENT, `company_name` varchar(128) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`company_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8056 DEFAULT CHARSET=utf8mb4 -- 約30,000行 CREATE TABLE `customer` ( `customer_id` bigint(20) NOT NULL AUTO_INCREMENT , `company_id` bigint(20) NOT NULL , `customer_name` varchar(256) CHARACTER SET utf8 NOT NULL, `flg` char(1) CHARACTER SET utf8 NOT NULL , `start_date` date NOT NULL , `end_date` date NOT NULL , PRIMARY KEY (`customer_id`), KEY `fk_company_id` (`company_id`), CONSTRAINT `fk_company_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ) ENGINE=InnoDB AUTO_INCREMENT=31219 DEFAULT CHARSET=utf8mb4 --約60,000行 CREATE TABLE `customer_setting` ( `customer_setting_id` bigint(20) NOT NULL AUTO_INCREMENT , `customer_id` bigint(20) NOT NULL COMMENT , `setting_code` bigint(20) DEFAULT NULL , `value` bigint(20) NOT NULL , `flg` char(1) CHARACTER SET utf8 NOT NULL , PRIMARY KEY (`customer_setting_id`), KEY `fk_customer_id` (`customer_id`), CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=62512 DEFAULT CHARSET=utf8mb4
下記のSQLを実行します。
select * from company inner join customer on customer.company_id = company.company_id inner join customer_setting on customer_setting.customer_id = customer.customer_id where customer_setting.flg = 'n' and customer.flg = 'n' and customer.start_date <= '2017-01-01' and customer.end_date >= '2017-04-01'
5.6では下記のような実行計画で動いていました。一番レコード数が少ないcompany表を駆動表として各表をinner joinしています。
+----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+ | 1 | SIMPLE | company | ALL | PRIMARY | NULL | NULL | NULL | 7948 | NULL | | 1 | SIMPLE | customer | ref | PRIMARY,fk_company_id | fk_company_id | 8 | sampl.company.company_id | 1 | Using where | | 1 | SIMPLE | customer_setting | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | Using where | +----+-------------+------------------+------+-----------------------+----------------+---------+----------------------------+------+-------------+
さて、同じSQLを5.7+condition_fanout_filter=ON(defaultはONです)の状態で実行すると、
+----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+ | 1 | SIMPLE | customer | NULL | ALL | PRIMARY,fk_company_id | NULL | NULL | NULL | 29913 | 1.11 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | | 1 | SIMPLE | company | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sampl.customer.company_id | 1 | 100.00 | NULL | +----+-------------+------------------+------------+--------+-----------------------+----------------+---------+----------------------------+-------+----------+-------------+
こうなりました。WHERE句で絞り込まれている( and customer.flg = 'n' and customer.start_date <= 'YYYY-MM-DD' and customer.end_date >= 'YYYY-MM-DD') customer表が駆動表に入れ替わりましたが、INDEXが無いのでFULL SCAN。5.6の実行計画も駆動表はFULL SCANなので問題ないかと思いきや、元の表よりレコード数が多いので遅くなってしまい、爆死しました。
対策としては
1) INDEXを貼る。駆動表の条件句+結合句に貼ってあげるとおそらく一番妥当な実行計画になります。
sql > create index ix_customer_flg_enddate_customerid on customer(flg,end_date,customer_id);
+----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | customer | NULL | range | PRIMARY,fk_company_id,ix_customer_flg_enddate_customerid | ix_customer_flg_enddate_customerid | 6 | NULL | 5374 | 33.33 | Using index condition; Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | | 1 | SIMPLE | company | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sampl.customer.company_id | 1 | 100.00 | NULL | +----+-------------+------------------+------------+--------+----------------------------------------------------------+------------------------------------+---------+----------------------------+------+----------+------------------------------------+
2) 駆動表を固定する。とりあえず元の実行計画に戻したい場合はstraight join で結合順序を固定するのも手です。
sql > explain select * from company straight_join customer on customer.company_id = company.company_id inner join customer_setting on customer_setting.customer_id = customer.customer_id where customer_setting.flg = 'n' and customer.flg = 'n' and customer.start_date <= '2017-06-26' and customer.end_date >= '2017-06-26';
+----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+ | 1 | SIMPLE | company | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7948 | 100.00 | NULL | | 1 | SIMPLE | customer | NULL | ref | PRIMARY,fk_company_id,ix_customer_flg_enddate_customerid | fk_company_id | 8 | sampl.company.company_id | 3 | 5.99 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 10.00 | Using where | +----+-------------+------------------+------------+------+----------------------------------------------------------+----------------+---------+----------------------------+------+----------+-------------+
3) condition_fanout_filterをOFFにする。そもそもfilterをoffにしてしまえば元に戻るのはという事でoffにしたら元に戻りました(filteredはすべて100になる)。
sql > set optimizer_swith='condition_fanout_filter=off'; sql > select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: 〜,condition_fanout_filter=off,〜
+----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+ | 1 | SIMPLE | company | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7948 | 100.00 | NULL | | 1 | SIMPLE | customer | NULL | ref | PRIMARY,fk_company_id | fk_company_id | 8 | sampl.company.company_id | 1 | 100.00 | Using where | | 1 | SIMPLE | customer_setting | NULL | ref | fk_customer_id | fk_customer_id | 8 | sampl.customer.customer_id | 2 | 100.00 | Using where | +----+-------------+------------------+------------+------+-----------------------+----------------+---------+----------------------------+------+----------+-------------+
トラブル発生時はfilterオプションの存在を知らずINDEX追加で対処しようとしましたが、同じようにパフォーマンスが劣化したSQLが雨後の筍のように発生した為、同僚の人が見つけてくれた(3)condition_fanout_filter=OFFで対処しました。資料にも、パフォーマンス悪かったらoffにしろと書いてありますね。ただINDEXが適切に貼ってあればこのオプションが有効に働くケースの方が多いと思われるので、メジャーバージョンアップ時はSQLのパフォーマンステストは必須という事と、INDEXはちゃんと貼りましょうという教訓を得ました。
Amazon Performance Insightsをとりあえず触ってみる
これは PostgreSQL Advent Calendar 2017 の Day4 の記事です。 昨日はsnagaさんの「Dockerを使ってデータ分析用にPostgreSQLを使ってみる」でした。
さて私からは、先般正式リリースされたAmazon Aurora with PostgreSQL Compatibilityで使用出来るモニタリングツール、Aurora Performance insightを使ってみましたという(だけの)記事になります。 Aurora Performance Insight自体はまだPreview版(2017/12/04現在)という扱いなので、データは最長一日間しか保存されず対象エンジンはPostgreSQLのみで、使用出来るリージョンも限られています。
利用方法は簡単でAurora Postgresqlインスタンス作成時にパフォーマンスインサイトを有効化するチェックボックスがあるので有効を選択するだけです。追加料金もかかりませんが、GAになったらどうなるのかな?
インスタンスが立ち上がったらおもむろにEC2インスタンスからPgbenchを投げてみます。
-- psql pgbench インストール $ sudo yum install postgresql96 $ sudo yum install postgresql96-contrib -- pgbench初期化 $ pgbench -i -s 1000 -U awsuser -h xxxxxx.xxxxxx.us-east-1.rds.amazonaws.com -d sampledb -- pgbench実行 $ pgbench -r -c 100 -j 10 -t 1000 -U awsuser -h xxxxxx.xxxxxx.us-east-1.rds.amazonaws.com -d sampledb
で、RDSコンソールからまず待機(イベント)・SQL・Hostname・Userの中から表示したいものを選択し、右側のスライス基準という所でさらに切り口を指定すると欲しいグラフが表示されます。
SQL毎の待機イベント種別を表示 待機イベント毎の実行User ロック解除待ちのクエリも確認できます。
AWSコンソールからしかアクセス出来ず機能も簡素なものですが、正式リリース版では収集データ保持期間の長期化やAPIの提供も予定されているようなので今後に期待です。ここからは私の妄想ですが、さらにSQLチューニングアドバイザ的なものがもし加わってきたら、この手の機能を追加オプションと位置づけているEnterprise系ソリューションにもAuroraが食い込んでくるのでは〜と考える私なのでした。
明日はtjtakahashiさんです。よろしくお願い致します。
参考資料
pgbench で RDS PostgreSQL のマスターに負荷をかけてリードレプリカのレプリケーションラグを計測する - ablog