鮎の魚拓

今日も鬱々と、ミステリ読んだりコード書いて生きています。

2017年 YAPC::Kansaiでの発表資料が出てきたので供養する

google driveへ久しぶりにアクセスしたところ、なくしたと思った昔の発表資料が出てきた。


実は2017年のYAPC::Kansaiにひっそり登壇していて、オンプレでのmysqlの運用話をしていた。当時は劣等感から発表しに行ったと大々的にはweb上で言わないようにしていたし、スライドも自分では上げていなかったので、そのうちファイルがどこに行ったかわからなくなっていた。時間が立って微妙に心境の変化もあり、せっかく出てきたのでここに供養しておく。


speakerdeck.com


当時はパブリッククラウドでの運用をやったことがなく、RDSだとPITRみたいな機能があることも知らなかったので、最後のスライドのような疑問を持っていた。とはいえRDSのPITRや最近でたAuroraでのbacktrackだと秒単位の指定での巻き戻しで、トランザクション単位で細かく戻せず、1秒未満のトランザクションはロストする可能性があり、遅延レプリほど完璧に直前までは戻せないはずなので、完璧な代用かと言われると微妙なところ*1。そこまで気にする？ という話もまああるけど、決済関係が絡むとやはりトランザクションの取りこぼしはなるべく避けたくなる*2

*1:わかってないだけで実はできるのだったら誰か教えてほしい...

*2:ロールフォワードさせる地点のあとに発生していた決済は結局別途対応いるじゃんというのはあるが...

isucon8ごっこした

isucon8、結局例年通り日和って出なかったんですけど、雰囲気だけ味わうか、ということでvagrantで環境立てて練習した。 githubに公開されてる以下のVagrantFileでmacbookに環境を作る。弄らず吊るしのまま立てていたので、CPU1スレッド、メモリ1GBの割り当て。 github.com

vagrantで起動した直後の状態だと、run_local.sh が見当たらなかったりして、どう云う状態なのかぱっとみで把握できなかったので、結局公式のリポジリをforkしたもものをVMの中にcloneし直して、そこで作業することに。言語はなれているperlで。

どこまでいけたか

吊るしの状態で600~800くらいのラインから4500くらいまで行ったところで力尽きた。多分低いんだろうと思うけど、実戦と違ってスペック低いVM1台でやってるのであんまり比較しようがないな、という感じ。どうにもやめ時がなくて、土曜の夕方から初めて、朝になって力尽きるまで回らない頭でやってしまった。

作業した結果のdiff

https://github.com/meru-akimbo/isucon8-qualify/pull/1

my.cnfとかもリポジトリになるべく入れてたけど、pull-reqでわからないインフラ作業とかもまあやってます。

主にやったこと振り返り

MariaDBからMySQLへの移行

MariaDB1ミリもわからん.... わからないのでとりあえずMySQL5.7系に移行。packageの依存関係とか、DB入れ替えるとDBD::mysqlの再ビルドが必要になったりとかで初手から軽くはまる... 入れ替えてもスコア的にはあまり変動なかったはず。

my.cnf調整

さっとできる調整してみるか、ということで簡単な調整をした。ただ用意したmy.cnfがちゃんと適用できる状態に持っていくのにまた少しグダった....vmのメモリが少なすぎるのでbuffer poolの値に困った。他の作業しつつ調整してみたが、ベンチ走らせるとがんがんswapするしあんまり多くしてもスコア改善してるように見えなかったので最終的に200MBくらいで落ち着いていた。後半の方で試しに innodb_io_capacity とか innodb_io_capacity_max あたりもいじっていたが、悪化した印象があったので結局デフォルトに戻った。何につけてもとにかくメモリが足りなくて頑張るポイント思いつかず、結局ほぼチューニングできてない。innodb_flush_log_at_trx_commit=2 にしてたのは多少効果あったのだろうか。innodb_log_file_sizeとかは調整してみても良かったかも。
最終的にはこんな感じ。


https://github.com/meru-akimbo/isucon8-qualify/pull/1/commits/c401ebfc41883d4d17f395f53666822c54945745#diff-1fdfa1d6df8863e2f27ea6c12c634258

h2oの設定調整

alpでログを解析したかったので形式だけ整えてる。特にパフォーマンス対策はできてない...LB周りチューニングして成果出す自信なかったのであんま頑張ってない。


https://github.com/meru-akimbo/isucon8-qualify/pull/1/files#diff-1714d91998cc37c8c0cd28d9777d0025

PSGIサーバをGazelleにした

お手軽で多少効果あるかな、と。気休め程度だったかも。

https://github.com/meru-akimbo/isucon8-qualify/pull/1/commits/63919740e3819d835e0ddf3ad149d468f384641a

/api/users/{id} のreservationの取得のクエリ

https://github.com/isucon/isucon8-qualify/blob/master/webapp/perl/lib/Torb/Web.pm#L137

SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id WHERE r.user_id = ? ORDER BY IFNULL(r.canceled_at, r.reserved_at) DESC LIMIT 5
mysql> explain SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id WHERE r.user_id = 1 ORDER BY IFNULL(r.canceled_at, r.reserved_at) DESC LIMIT 5\G                                                                                                                                         
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 191807
     filtered: 10.00
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: torb.r.sheet_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

フルテーブルスキャンしつつUsing where; Using filesort が辛そうに見えた。ORDER BY IFNULL()って始めてみたけどきな臭いしなんか潰せないかなーというのも考えた結果、reservationsのindexを調整してみながら直近のeventの更新状況を保存するresent_eventというテーブル(名前最悪感ある...)を作りそれとjoinさせてみた。

            SELECT r.*, s.rank
                AS sheet_rank, s.num
                AS sheet_num
            FROM reservations r
            INNER JOIN recent_event re
                ON r.id = re.reservation_id
            INNER JOIN sheets s
                ON s.id = r.sheet_id
            WHERE r.user_id = ?
            ORDER BY re.change_at DESC LIMIT 5',
    -> \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: PRIMARY,user_reserved_idx,user_canceled_idx
          key: user_reserved_idx
      key_len: 4
          ref: const
         rows: 33
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: re
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,change_idx
          key: PRIMARY
      key_len: 4
          ref: torb.r.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.10 sec)

Using temporary; Using filesortがアレだが、多少scoreが上がって1000点くらい出るようになった記憶。ただ今冷静にindexいじった状態で前のクエリ見返すとreservationsのindexいじるだけのほうがマシだった感じがしている。

mysql> explain SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id WHERE r.user_id = 1 ORDER BY IFNULL(r.cancel
ed_at, r.reserved_at) DESC LIMIT 5\G                                                                                                                                         
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: user_reserved_idx,user_canceled_idx
          key: user_reserved_idx
      key_len: 4
          ref: const
         rows: 33
     filtered: 100.00
        Extra: Using index condition; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: torb.r.sheet_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.93 sec)

余計な更新クエリも増やさずに済んだし... ORDER BY IFNULL()がどれくらい影響あるのか落ち着いて検証すべきだった・

get_event周り調整

/admin/ とかがやたらと遅いので追ってみたらget_event周りのN+1がかなり険しかったので気合で調整。なるべくクエリをまとめるように試行錯誤したところある程度スコアへの影響が見られた。

https://github.com/meru-akimbo/isucon8-qualify/compare/758b44e...ef0846e

/admin/api/reports/salesのレポートデータ取得

https://github.com/isucon/isucon8-qualify/blob/master/webapp/perl/lib/Torb/Web.pm#L544

SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.id AS event_id, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id ORDER BY reserved_at ASC FOR UPDATE
mysql> explain SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.id AS event_id, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id ORDER BY reserved_at ASC FOR UPDATE\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 22
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: event_id_and_sheet_id_idx
          key: event_id_and_sheet_id_idx
      key_len: 8
          ref: torb.e.id,torb.s.id
         rows: 12
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

きつい。

FOR UPDATEいらなくない? とかsheets の取得を分けて Using join buffer (Block Nested Loop) なくしてみるとかはやってみたがあまり大きな成果は出た印象が無い。

https://github.com/meru-akimbo/isucon8-qualify/pull/1/files#diff-789822921399450b283ea4401c2fc060L539

sanitize_eventのORDER BY RAND

https://github.com/isucon/isucon8-qualify/blob/master/webapp/perl/lib/Torb/Web.pm#L303

mysql> explain SELECT * FROM sheets WHERE id NOT IN (SELECT sheet_id FROM reservations WHERE event_id = 1 AND canceled_at IS NULL FOR UPDATE) AND `rank` = 'A' ORDER BY RAND() LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: sheets
   partitions: NULL
         type: ref
possible_keys: rank_num_uniq
          key: rank_num_uniq
      key_len: 514
          ref: const
         rows: 150
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: reservations
   partitions: NULL
         type: ref
possible_keys: event_id_and_sheet_id_idx
          key: event_id_and_sheet_id_idx
      key_len: 8
          ref: const,func
         rows: 12
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

ORDER BY RAND 潰せるなら潰したい...ということでいじっていた。 インデックスきかせてid拾ってperl側でrandしてから再度取得、という感じであがいてみているが、あんまりスコア変わらなかった。 https://github.com/meru-akimbo/isucon8-qualify/pull/1/files#diff-789822921399450b283ea4401c2fc060L303

mysql> explain SELECT id FROM sheets WHERE id NOT IN (SELECT sheet_id FROM reservations WHERE event_id = 1 AND canceled_at IS NULL FOR UPDATE) AND `rank` = 'A'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: sheets
   partitions: NULL
         type: ref
possible_keys: rank_num_uniq
          key: rank_num_uniq
      key_len: 514
          ref: const
         rows: 150
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: reservations
   partitions: NULL
         type: ref
possible_keys: event_id_and_sheet_id_idx
          key: event_id_and_sheet_id_idx
      key_len: 8
          ref: const,func
         rows: 12
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)


mysql> explain SELECT id, num  FROM sheets WHERE id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sheets
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

/admin/api/reports/events/{id}/salesのreservation取得

https://github.com/isucon/isucon8-qualify/blob/master/webapp/perl/lib/Torb/Web.pm#L520

SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id WHERE r.event_id = ? ORDER BY reserved_at ASC FOR UPDATE
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: event_id_and_sheet_id_idx
          key: event_id_and_sheet_id_idx
      key_len: 8
          ref: const,torb.s.id
         rows: 12
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

Using temporary; Using filesort を潰せるようにあがいてみたけどあまり変わらなかった記憶...

https://github.com/meru-akimbo/isucon8-qualify/pull/1/commits/c17db6db6811cf4f0c57d4d20ea93a4938774af5

mysql> explain SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id WHERE r.event_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ref
possible_keys: event_id_and_sheet_id_idx
          key: event_id_and_sheet_id_idx
      key_len: 8
          ref: const,torb.s.id
         rows: 12
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

感想

思った以上にだめだった気がする

もうちょいスコア伸ばしたかった。環境が違うのでスコアの比較対象がなく、ダメさ加減があまり正確にわからないが、低い気はする...

isucon用の開発環境ちゃんと作って練習したほうがいい

最初macからforkしたリポジトリにpushして、vmでpullしてベンチしていたが、だるすぎてそのうちvmで直接書き始め、なれない環境でコードを書いていた。やり始めはこういう本質的じゃないところに引っかかってストレスだったので、また練習するときは軽く事前準備したほうが良さそう。

時間区切ったほうが良さそう

無限に時間があると結構非効率な動きもなあなあでやってしまうので、区切ってやったほうが練習になりそう。

あんまり飛び道具つかわず愚直に直していた

メモリが全然足りてなかったのと、redisとかに乗せれば一発で爆速みたいな絵が深夜の脳だと浮かばなかったので、他にメモリ当てて愚直に直せる範囲で頑張ってみた感じだった。