かみぽわーる

kamipo's blog

YAPC::Asia 2014に行ってきた

#yapcramen

OSSにも貢献しました!

あと最近声優ソムリエ業で忙しい@さんに30日ぐらい放置されてたpullreqもYAPC::Asiaのおかげでマージされました!

───、以下感想。

今年はYAPC::Asiaの週の頭から体調くずしてて昼から参加だったので、あんまりトーク聞かずにイベントホールとかHUBでまったりしてた。

どの部屋も満席立ち見だったようでそれはそれでよかったんですが、聞きたいトークが聞けなかったりずっと立ち見で不満を言ってる人とかもいて、なんというかワインみたいなもんで良作の年もあり不作の年もありそれ含めて楽しんで参加できたらいいなと僕は思ってるんですが、たぶん今回はじめて参加してそれが不満だった人はもう次からは参加してくれなくなるのかなって思うとちょっと残念だけど仕方ないのやもしれぬ。

トークを聞かずにHUBでは@さん、@さん、@さんが飲んでるテーブルにお邪魔してActiveRecordの運用まわり

聞いてた。

あとはMySQLの話で

あとなんかいろいろ話せて有意義だった。

あと今回はリクルートホールディングス様の粋な計らいにより夕方からHUB貸し切りのフリードリンクだったおかげで、終電までずっと飲んでだべってられたのがすごいよかった。

去年は協生館と学食以外あんま出歩かなかったからあまり日吉を堪能してなかったけど、今年はYAPC::Asia Ramen Challengeのおかげで日吉のラーメンも堪能できて@++でした。

今年のYAPC::Asiaも楽しかった。

また次回のYAPC::Asiaも楽しもうと思う。

٩(๑❛ᴗ❛๑)۶

プライマリキーを使った1:1関連のテーブル分割で自動採番をしないようにする

プライマリキーを使った1:1関連でカラム数の多いテーブルを分割する - Hidden in Plain Sight

プチ・デザインパターン的なやつ、僕もよくやってます。

で、運用エンジニア的にはデータの不整合を起こしうる要因はできる限りDB側の制約でも防ぎたいので、このusersとprofilesの場合だと、usersで自動採番されたidをprofilesでも使うのでprofilesの自動採番する機能は残しておくと事故るリスクがあるので落としたいわけです。

なので、僕はいつもこんな感じでmigrationを書いてます。

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t| 
      t.string :email, charset: 'ascii', collation: 'ascii_bin', null: false
      t.string :password_digest, charset: 'ascii', collation: 'ascii_bin'
    end

    add_index :users, :email, name: "idx_email", unique: true
  end
end

class CreateProfiles < ActiveRecord::Migration                                                                                              
  def change                                                                                                                                
    create_table :profiles, id: false do |t|                                                                                                
      t.integer  :id, null: false
      t.string   :name
      t.integer  :gender, limit: 1                                                                                                          
      t.datetime :birthday                                                                                                                  
    end

    execute("ALTER TABLE `profiles` ADD PRIMARY KEY (`id`)")                                                                                
  end
end

usersテーブルの文字セットや照合順序の指定はRails - ActiveRecordでカラム毎にcharsetとcollationを指定する - Qiitaでやってます。

ActiveRecordが作る自動採番されるプライマリキーとは異なる定義を使うとrake db:schema:loadスキーマが正確に復元できなくなるので config/application.rbスキーマのフォーマットをSQLに変更しています。

config.active_record.schema_format = :sql

db/structure.sqlにこんな感じでダンプされます。

-- (snip)

--
-- Table structure for table `profiles`
--

DROP TABLE IF EXISTS `profiles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `profiles` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `schema_migrations`
--

DROP TABLE IF EXISTS `schema_migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_migrations` (
  `version` varchar(191) NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `password_digest` varchar(255) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

-- (snip)

これでうっかりミスってprofilesにINSERTして採番されちゃうリスクが軽減されて安心感が増しますね!

InnoDBのロックの範囲とネクストキーロックの話

この記事はMySQL Casual Advent Calendar 2013 3日目の記事です。

はじめに

以前にSELECT ... FOR UPDATEとロックの挙動 - I sort my thought...の記事にTwitterで少し言及したんですが、それの補足というか、InnoDBのロックの範囲について僕はこう理解していますよという話です。

MySQLといえば、InnoDBをネットワークサーバとして使うためのフレームワークであり、SQLInnoDBのインデックスにアクセスするためのDSLといっても過言ではないでしょう。

InnoDBのロックとはつまるところインデックス行のロックなので、InnoDBのロックの範囲を理解するためにInnoDBのインデックスについて少し前置きしておきます(だいぶ端折ったけど長くなった…)。

クラスタインデックスとセカンダリインデックス

すでにInnoDBのインデックスについてよく知ってるという人はここは飛ばして大丈夫です!
ちょっと予習しておきたい人は以下の記事を参考にしてください。

今回の説明に必要そうなところを要約すると

  • 主キーがあるテーブルは主キーがクラスタインデックスになる
  • セカンダリインデックスのリーフにはクラスタインデックスのキー値が保存される
  • セカンダリインデックスからデータを参照するには保存されてるキー値からクラスタインデックスを参照する

こんなところ。

ロックの範囲の種類

MySQL :: MySQL 5.6 Reference Manual :: 14.2.2.6 InnoDB Record, Gap, and Next-Key Locksから引用すると、InnoDBには3種類のロックがあります。

  • Record lock: This is a lock on an index record.

  • Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

  • Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

ざっくりいうと

  • レコードロック: 単一インデックスレコードのロック
  • ギャップロック: インデックスレコード間のギャップのロック、先頭のインデックスレコードの前や末尾のインデックスレコードの後のギャップのロック
  • ネクストキーロック: レコードロックとそのレコードの直前のギャップロックの組み合わせ

という感じになります。

InnoDBでインデックスを走査するとき、遭遇したインデックスレコードをロックしていく。なのでロックする範囲を知るにはどのインデックスをどの値からどっちに(ASCかDESCか)どこまで走査するかが重要になる。

あとInnoDBのデフォルトの分離レベル(REPEATABLE-READ)ではロックの単位はネクストキーロックが基本で、あるインデックスに値 10、11、13、20 が含まれているときのネクストキーロックの区間は以下のような感じで、基本的にこの区間単位でロックすることになる。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

ネクストキーロック

ところで、ネクストキーロックというとsh2さんのMySQL InnoDBのネクストキーロック おさらい - SH2の日記の記事が有名ですよね。この、ひとつ先のインデックスレコードまでロックするのもネクストキーロックと呼ぶし、レコードロックとその直前のギャップロックの組み合わせもネクストキーロックと書いてるし、議論するときにはどちらの意味で使ってるのか文脈読み取れる社会性が必要そうです(今回はレコードロックとその直前のギャップロックの組み合わせの意味で使います)。

ロックの範囲を確認する

長くなったけどこのぐらい押さえておけば大丈夫だと思います!

トランザクションがブロックする、ブロックしないというのは、つまるところロックの範囲が被ったか被ってないかなので、walf443さんの記事にあるのと同じ感じのテーブルでロックの範囲を確認してみます。

CREATE TABLE `mysqlcasual` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) NOT NULL,
  `col2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mysqlcasual`(`col1`) VALUES (2),(4),(6),(8),(10),(12),(14),(16);

SELECT * FROM mysqlcasual;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    2 |    0 |
|  2 |    4 |    0 |
|  3 |    6 |    0 |
|  4 |    8 |    0 |
|  5 |   10 |    0 |
|  6 |   12 |    0 |
|  7 |   14 |    0 |
|  8 |   16 |    0 |
+----+------+------+

ロックの範囲を確認するにはinnodb_lock_monitorを有効にするとSHOW ENGINE INNODB STATUSTRANSACTIONSセクションに詳細なロック情報が出力されるようになります。

CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;

これでロックの範囲が確認できる、予定だったんですがちょっと思ってた通りの出力にならないんで簡単な例だけやりたいと思います!*1

BEGIN;
SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;

これでTRANSACTIONSセクションを見ると

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000003; asc     ;;

わかるところだけ読むとlock mode Sってのが共有ロック(でネクストキーロック)、locks gap before recがギャップロックなのでロックの範囲はidx_col1上の

(2, 4]
(4, 6)

の範囲でidx_col1しか読んでないのでカバリングインデックスになってるってやつです。 クエリを以下に変えてインデックスに含まれないカラムを加えると

BEGIN;
SELECT id, col1, col2 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;

TRANSACTIONSセクションに以下のインデックスレコードも加わります。

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028790 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

locks rec but not gapってのがレコードロックです。
col2の値を取得するためにクラスタインデックスにアクセスしてしまってカバリングインデックスが効いてないのがわかります。

また、定義してない謎のフィールドがあるように見えますがInnoDBの行の構造についてはMySQL :: MySQL 5.6 Reference Manual :: 14.2.2.13 InnoDB Table and Index Structuresに書いてあるので気になる人はそっち見てください。

次は排他ロックの場合。

BEGIN;
SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 FOR UPDATE;

更新するつもり(lock_mode X)のときはカバリングインデックスになっていてもクラスタインデックスもレコードロックします。

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000003; asc     ;;

これらは分離レベルREPEATABLE-READのときの挙動で、READ-COMMITTEDにするとギャップロックが無効になります。

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

この状態だとギャップに対してINSERTできるのでファントムリードが起きる状態になります。

さいごに

innodb_lock_monitorは大量にログを吐くので使い終わったら消しておきましょう。

DROP TABLE innodb_lock_monitor;

明日(12/4)はdo_aki@殿堂入りさんです!

*1:レコードが少なすぎたせいでした https://twitter.com/kamipo/status/408032716186071040 https://twitter.com/kamipo/status/408033740690968576

優勝したらあの子に告白することばかり考えていた #isucon

ISUCON3本選お疲れさまでした!

うちのチームのことはだいたいgfxが書いてる通りなんですけど、おもに僕がやったこととか本選後に振り返ってみたことを書いておきます。

予選後の教訓で、最初にちゃんとコードを読んで方針を決めようって話してたので、最初に全員でざっと構成とかコードとか初期状態でのベンチとか回してみて全体を把握してから昼に作戦会議。

そのときに僕が話した見解は

  • このアプリケーションから何らかの方法で参照時の画像変換のボトルネックを取り除いたとき、次にボトルネックになるのは帯域になる
  • なので理想的な状態から逆算すると5台でWANにトラフィックを吐く構成になってる必要がある
  • 最悪、参照時にまったく変換しなくて済む理想的な高速化に失敗してすべての変更をrevertすることになっても、5台並べて参照時の画像変換して返せるようにできてれば単純に初期状態の5倍のCPUでスケールできるから5台で動かすのは必須
  • 画像変換がボトルネックになっている限りクエリのチューニングに手を付けるのは時間の無駄

我がチームの戦略上のミスは、画像変換を先に済ませておけば最速になるという推定をしていたのに多分無理だろうと変換が時間内に終わりきるか(容量は足りるか)ちゃんと見積もらなかったことだと思う。

昼以降、僕は全台nginxで受けて更新系を1台目に流す部分をやって、Yappoさんが変換した画像のローカルキャッシュと1台目以外で動くように改修する部分、gfxが画像変換のImager化をやってたけど、最終的にみんなの力がひとつになることなくずっとFailしたまま僕たちのISUCONは終了した…。

まぁあとから思うと木曜から体調死にかけで当日心が弱りまくってたとか、予選ではエラーみた瞬間1秒で気づいたことが本選では30分ぐらいドハマりするぐらいぜんぜん気づけなかったとか(413 Request Entity Too LargeとかMySQL server has gone awayとか)、nginxの設定でハマってるときにもっとはやくYappoさんに見てもらえばよかったとか、なんか22番と80番以外のポートで通信できないなと思ってiptables叩いてもルール空で(1台目以外は空だった)ずっとおかしいなって思ってたのももっとはやくなんかおかしいっていえばすぐ解決したのに(Yappoさんが1台目でiptables打ったら一瞬で解決した)、それで自分の作業に掛かりっきりで他の人の変更追えてなかったからアプリがずっとFailしてるのを弱り切った心でコードレビューする気力なくなっててラスト30分諦めてぼーっとしてたりまぁとにかく反省することはたくさんあったけど、終わってから一番後悔してるのは全ての変更をrevertしてでもベンチが通る状態で終わらなかったこと。去年は要件を誤って解釈していたせいで最後断腸の思いで全ての変更をrevertしてベンチが通る状態に戻して苦い思いをしたけど、Failして終わるというのはそれ以上に苦い思いであった。

僕のバックグラウンドは運用エンジニアなので、運用エンジニアの矜持として動いてるものを壊したらあかんという気持ちをとても強く持ってる。これを外科医に例えると、腹開いてみてすごい末期だけどダメ元で手術してみて死んでしまいましたじゃなくて、手術で死なせるわけにはいかんからダメ元ならなにもせずに腹を閉じようの精神かもしれない。それを心が折れてたとはいえ最後諦めてしまった。つらい。しかし愚者も経験から学ぶのである。もう絶対に諦めたりしない。

そして来年こそこの思いをあの子に伝えられたらと思う。

#mysqlcasual vol.5 で話してきたこと

先日オラクルで開催された MySQL Casual Talks Vol.5 に参加してきました。

mysql-build よもやま話

ありがたいことにいつも発表の機会を頂いてるので、なにか新しいネタをと思ってるんですが今回ちょっとネタも余裕もなくて、それでも聴いてる人がちょっとでもへぇって思えるようなことをざっくばらんに話せたらいいなと思っていて

ちょうどFacebookMySQLのmysqldumpを爆速にする話がタイムリーだったのでそれに絡めてInnoDBのFast Index Creationを狙ってリストアも爆速になる話をしようって流れで今回の発表を考えました。前者をmatsunobuさんのブログ、後者をsh2さんのブログと発表資料を使って説明させてもらいました。僕の発表資料にはURL貼ってあるだけなんで詳しくはおふたりの素晴らしいエントリーを参照してください。

Yoshinori Matsunobu's blog: Making full table scan 10x faster in InnoDB

MySQL 5.6における大量データロード時の考慮点 - SH2の日記

あと、発表で話した--innodb-optimize-keysオプションはバグがあるみたいなので使ってみようってひとは気をつけてくださいね!

mysql-buildもがんばってますのでお気軽に@kamipoどうぞ:)

参考

好きな子のことかslow query logのことばかり考えてほかのことがおろそかになる性格なおしたい #isucon

ISUCON3 予選おつかれさまでした!

うちのチームのことはだいたいやっぽさんが書いてる通りなんですけど、おもに僕がやったこととかAMI提出後に振り返ってみたことを書いておきます。

振り返りはコチラ。

【ISUCON3】予選の振り返り【ぶっちゅぶす】 - Togetter

まず今回ISUCON3でチームを組む段階で考えていたのは、チームメンバーの共通言語は揃えておいたほうがいい(当たり前と思うかもしれないけど前回バラバラすぎて死んだ)ということと、得意分野がかぶってないチームにしたいと思ってた。その点でいうと、まだ予選がおわったところだけど今まで参加してきた教訓を活かせたと思う。

あとは前回力を出し切れない結果で悔しい思いをしたというのがあって、予選通過すれば次につながるので、横着せずに普段やってることで着実にスコアを伸ばしたい、fujiwaraさんのような役割を目指したいと思ってた。 それも得意分野がかぶってないおかげかまずまずうまくいったのではないかと思う。

だいたい僕がログ見る係でやっぽさんがクソクエリなおしまくってくれてたけど、'/recent/:page'のページャのOFFSETをWHEREに変換するのは僕の得意分野だったんで、こういうことしたいっていってやっぽさんに全部実装してもらったりしてました。あとは基本的にORDER BY 狙いのキーにしていったらMySQLの負荷はなくなりました。

あとこれは別件ですが、世間にはORDER BY created_at DESCする人がとても多いと思ってるんですけど、挿入順に単調増加する値でソートしたいんだったらAUTO_INCREMENTしてるPRIMARY KEYがあるでしょう、なんでそっちでソートしてくれないんすか、created_atにINDEX張るのも空間効率の無駄なのでやめてほしいです。これは5年以上前からいってるけどまったく世間に伝わってないと思うんでORDER BY 狙いのキーといっしょにこれも言い続けていきたいです。

最後に、本選へ向けての教訓としては

  • 好きな子のことかslow query logのことばかり考えてほかのことがおろそかになる性格なおしたい
  • 最初にちゃんとコードを読んで高速化の指針を検討したい

というところですかね。

こちらからは以上です。

YAPC::Asia 2013でmysql-buildのLTしてきた

2日目のLTで、MySQLSQLというDSLをつかって様々なストレージエンジンを共通のインターフェースで抽象化して扱うことのできるフレームワークなわけですがということで、mysql-buildでMySQLプラグインも一緒にビルドする話をしてきました。

mysql-buildでQ4Mやmroongaもビルドしたい

これでQ4Mとか簡単に使い始められるようになるので、みんなMySQLプラグインもつかってくれるかな?

(いいとも!)

動機とか

mysql-buildはMySQL Casual Advent Calendar 2011のときに書いたやつで、そのときからすでにQ4MやHandlerSocketを入れる的なネタが3つも4つもかぶって投稿されて、みんなどんだけQ4M入れたいんだよな感じだったわけですが、僕もそもそもはローカルでLTネタのプラグインとか書くときにソースからビルドしたMySQL環境が頻繁に必要になってたので書いたというのがあります。

なので当時からやる気が出たらプラグインも入れれるようにしようと思ってたけど、MySQL 5.6 で Q4M を動かしたいをやってたときにMySQLのビルドがCMake化したあたりの対応でいろいろハマったりして(これとかこれ)、まぁMySQL 5.5以降のCMakeでのビルドに対応したプラグインは置くべき場所に展開してcmake叩くだけでビルドできるようになってて簡単なんだけど、それも、知らなきゃ簡単かどうか調べるまでが面倒なんでコマンドパツイチで入るほうが敷居下がっていいですよね。僕も忘れたころにまたハマったりするの嫌だし。

現時点だとmysql-buildで指定できるQ4Mとmroongaは、MySQL 5.5以降のCMakeを使ったビルドだけ対応してるんでMySQL 5.1には対応してません(Q4Mとmroonga自体はMySQL 5.1にも対応してる)。僕がMySQL 5.1を使わないんでめっちゃモチベーション低いですが10人ぐらいのおっさんか1人のかわいこちゃんがどうしてもっていうなら対応するかもしれないです。

こちらからは以上です。