かみぽわーる

kamipo's blog

MySQLにうるう秒は保存できるのか試した

結果、保存できなかった😢

SET sql_mode = STRICT_ALL_TABLES;

CREATE TABLE time_leap (
  dt datetime,
  ts timestamp
) ENGINE=InnoDB;

INSERT INTO time_leap VALUES ('2012-06-30 23:59:59', '2012-06-30 23:59:59');
-- Query OK, 1 row affected (0.02 sec)

INSERT INTO time_leap VALUES ('2012-06-30 23:59:60', '2012-06-30 23:59:60');
-- ERROR 1292 (22007): Incorrect datetime value: '2012-06-30 23:59:60' for column 'dt' at row 1

これを試そうと思ったのは、timestamp型は内部表現がepoch秒なのでうるう秒保存するの無理だろうけどdatetime型は秒の精度に6bit割いてるので[0, 64)の範囲の値を保存できるだけの精度を持ってると思ったんですけど、よく見たらドキュメントに6 bits second (0-59)って書いてたしどこかでバリデーションしてるんでしょうねきっと。

InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い

この投稿はMySQL Casual Advent Calendar 2014の5日目の記事です。

先週ツイッターInnoDBのことを質問されまして、せっかくなのでアドカレのネタにしようと思いますってことでInnoDBのファイルフォーマット毎の違いをカジュアルに説明しようと思います。

InnoDBのファイルフォーマットBarracudaと新機能

InnoDBにはファイルフォーマットとして昔からあるAntelopeと新しいフォーマット(5年も前からあるので新しくはないが)のBarracudaがあって、新しいフォーマットのBarracudaにはいろいろ改良や新機能が追加されています。

例えば、ROW_FORMAT=COMPRESSEDとかは分かりやすい新機能で、上のCOMPRESSEDではなくDYNAMICを選んでいる理由を質問されたときには、とりいそぎ圧縮展開するため最大スループットが落ちますという回答をしたんですけど、COMPRESSEDの性能については@さんのエントリが参考になります。

他には、innodb_large_prefixによるインデックスサイズ制限の拡張もBarracudaの新機能です。

もうひとつ、見た目地味だけどドキュメントには書いてある大きな違いがあって、それは可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いについてです。

可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いの違い

ざっくりいうと、従来のAntelopeでは可変長カラムの値は先頭768バイトはローカルページに保存して残りの部分を外部のオーバーフローページに保存するけど、Barracudaは可変長カラムの値をすべて外部のページに保存してローカルページにはそのページへの20バイトのポインタだけ保存します。

これは実際どういう効果が期待できるかという話なんですけど、サイズの大きい可変長カラムをクラスタインデックスから追い出して行のサイズを小さく保てるということは、ページあたりより多くの行を詰め込めるようになるということで、バッファプールにより多くの行を乗せられるようになることを期待できると思います。

また、InnoDBには1つの行の最大サイズはページサイズの半分(innodb_page_sizeのデフォルトは16KBなので約8000バイト)までという制限があるので、Antelopeだと例えばutf8でvarchar(255)のカラムが11個あるテーブルに最大サイズまでデータを入れようとするとエラーになるんですけど、Barracudaだとローカルページにはポインタしか保持してないので余裕で保存できたりします。

CREATE TABLE tbl_compact (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=COMPACT;

CREATE TABLE tbl_dynamic (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=DYNAMIC;
# error
$ perl -le 'print "INSERT INTO tbl_compact VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
ERROR 1118 (42000) at line 1: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1118): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1030): Got error 139 from storage engine

# ok
$ perl -le 'print "INSERT INTO tbl_dynamic VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
参考

Rails複数DB Casual TalksでMySQLとActiveRecordの話をしてきた

Rails複数DB Casual Talks - connpass お疲れさまでした。

当日は準備不足で資料がただのリンク集だったのを公開用に当社比800%ぐらい加筆したので、当日参加できなかった人が見ても内容がわかるようになってると思います。

MySQLとActiveRecord

@さんのツイートふぁぼってたらみんなが複数DBの話するなかでひとりだけMySQLの話するとか公開処刑なのでは!?と思ったけど、ちょうどみんなestablish_connectionの話でおなかいっぱいでしたからね、楽しんでもらえたようでよかったです。

Rails 複数DB Casual Talks #mdb_casual - Togetterまとめ

MySQLユーザーのためのPostgreSQL対応表

毎回わからなくなってググってるから今度からここに追記していく。

MySQL PostgreSQL
SHOW DATABASES; \l
USE dbname \c dbname
SHOW TABLES; \dt
SELECT * FROM tblname\G \x on
SELECT * FROM tblname;
SELECT * FROM information_schema.processlist; SELECT * FROM pg_stat_activity;
KILL <pid>; SELECT pg_terminate_backend(pid);
KILL QUERY <pid>; SELECT pg_cancel_backend(pid);

table / column の情報

MySQL PostgreSQL
SHOW TABLE STATUS FROM dbname; わからん
SHOW CREATE TABLE [dbname.]tblname; わからん
SHOW FULL COLUMNS FROM [dbname.]tblname; \d+ tblname もしくは↓で代替
SELECT * FROM information_schema.columns WHERE table_schema = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position; SELECT * FROM information_schema.columns WHERE table_catalog = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position;
SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor(data_length/1024/1024) as dataMB, floor(index_length/1024/1024) as indexMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC; SELECT relname, cast(reltuples as bigint) as num_rows, (relpages/128) as size_mb, cast(relpages*8192.0/(reltuples+1e-10) as bigint) as avg_row_size FROM pg_class ORDER BY size_mb DESC;

dump / restore

MySQL PostgreSQL
$ mysqldump --no-data dbname [tblname1 tblname2 ...] > schema.sql $ pg_dump --schema-only [-t tblname1] [-t tblname2] dbname > schema.sql
$ mysqldump --single-transaction dbname > backup.sql $ pg_dump dbname > backup.sql
$ mysql dbname < backup.sql $ psql dbname < backup.sql

ISUCON4予選に参加してきた

ISUCON4予選お疲れさまでした。

すこし時間が経ってしまったけど、当日うまくいかなかったことの復習をしたので備忘としてここに記します。

今回のチームメンバーは@さんと@ちゃんでした。ギリギリのオファーにも関わらず一緒に参加してくれてありがとう!

チームメンバーの参加エントリはコチラ

当日うまくいかなかったこと

役割分担で僕が目指していたのは、セットアップや開発基盤をすばやく整えて、負荷やアクセスログを分析して根拠をもってなにをすべきかを明らかにすることで、メンバーそれぞれが力を発揮して問題に取り組めるようにできればいいなと思ってた。いわゆるファシリテータというやつなんですかね。

結果からいって自己評価は、そのほとんどがうまくいかなかったと思っていて、その部分がとても心苦しかったです。

うまくいかなかったと思うことが大きく2つあって

ひとつが、isuconユーザのパーミッション問題にハマって(結局終わってみるまでパーミッションの問題だとすら気づけなかった)、それ絡みかどうか解決できてないのでわからないけど、僕がサーバ側で git push できない状態を解決できなかったので Initial commit の git push に30分以上ぐらい掛かってしまったこと(ローカルに rsync してきて git push した)。

もうひとつが、詳細はやっぽさんのエントリ参照ですが、//mypageはアプリ通さずnginx側で返せるようにできるよねってことで、じゃあちょっとnginxの様子見てくる!って張り切って出ていったきり、それを実現できずにずっとハマってたこと。

それで、アクセス数多いからせめて/だけでも静的に返そうってことで全員で nginx.conf いじりながら/で静的ファイル返す方法を模索することになってしまった。

最終的にやっぽさんが気合いで解決してくれて、その状態で提出となりました。

/を静的に返せるようになったアクセスログを集計したのが以下。

*** HTTP requests total: 202764 ***
 
*** HTTP requests stats order by time ***
 
count: 18433, total: 188.261999999996, mean: 0.0102133130798023
path: /login
 
count: 3483, total: 8.70599999999991, mean: 0.00249956933677861
path: /mypage
 
count: 1, total: 0.397, mean: 0.397
path: /report
 
count: 36866, total: 0, mean: 0
path: /images/isucon-bank.png
 
count: 36866, total: 0, mean: 0
path: /stylesheets/bootstrap.min.css
 
count: 36866, total: 0, mean: 0
path: /stylesheets/bootflat.min.css
 
count: 36866, total: 0, mean: 0
path: /stylesheets/isucon-bank.css
 
count: 18433, total: 0, mean: 0
path: /
 
count: 11654, total: 0, mean: 0
path: /?error=not_found
 
count: 2091, total: 0, mean: 0
path: /?error=banned
 
count: 1205, total: 0, mean: 0
path: /?error=locked
 
*** HTTP methods stats ***
GET:  184331
POST: 18433
 
*** HTTP statuses stats ***
200: 184331
302: 18433

/を1ms未満で返せるようになったけどスコアはそんなに伸びなかったので、/loginが10msぐらいなのを根本的に解決しないと上位は難しそう、という集計結果を出すことができなかった。

/loginがもっと速くないとダメだってことなら、じゃあこれ以上速くするならusersは不変なマスターデータだから起動時にプロセスのメモリに持とうとか、tsvから読み込んだら生パスワード分かってるからpassword_hash計算しなくていいよねとか、そういうことに根拠をもって取り掛かれたはず。

ハマって時間に余裕ない状態じゃなければ、やっぽさんはbenchmarkerのバイナリ見て、このDOMチェックしてるから人間が見たときの見た目変えずに最小のDOM返せばよくね?って言ってて、いや、それってありなん…みたいなのも余裕があったら試してみることもできたと思う。

ただ今回は、予選特有の環境における攻略法が知られていたからか、それだけではボーダーを超えるのが難しい予選だったので結果はどうなったか分からなかったわけですが、それでもやりたいことはやりきったよねと思える状態まで行きたかったなというのが正直なところ。

やっぽさんが気合いで解決してくれたことでなにでハマってたか分かったので、復習してみて//mypageをnginx側で返すことができた。/のときと同様/mypageも1ms未満で返せるようになったが、スコアの伸びは1割程度だった。どうすればよかったかのミニマムな設定を以下に示す。

http {
    upstream app {
        server 127.0.0.1:8080;
    }

    map $arg_error $index {
        default   index.html;
        locked    err_locked.html;
        banned    err_banned.html;
        not_found err_not_found.html;
        not_login err_not_login.html;
    }

    server {
        root /home/isucon/webapp/public;
        index $index;

        location /mypage {
            rewrite /mypage /mypage.html;
        }

        location /mypage.html {
            ssi on;
            set $login   $cookie_login;
            set $last_ip $cookie_last_ip;
            set $last_at $cookie_last_at;
            # 2014-10-02 03:51:08 <- 2014-10-02%2003%3A51%3A08
            if ( $last_at ~ (.*)%20(.*) ) {
                set $last_at "$1 $2";
            }
            if ( $last_at ~ (.*)\s(.*)%3A(.*)%3A(.*) ) {
                set $last_at "$1 $2:$3:$4";
            }
        }

        location ~ ^/(login|report) {
            proxy_pass http://app;
        }
    }
}

ログイン情報を、クエリストリングに入れて渡すとスペースが%20に、cookieに入れて渡すと加えて:%3Aエンコードされてくるのをデコードする方法が分からなかったのでそこだけがんばったけど、それ以外は仕組みが分かればなんということはなかった。

ハマってたのは location のマッチに関する挙動をちゃんと理解していなくて、ちょっとした書き方の差でうまく動いてないのが、全部location /に吸われていってるせいだということに全然気づけてなかった。

まずassets系をnginxで返すのに

        location ^~ /(stylesheets|images) {
            root /home/isucon/webapp/public;
        }

        location / {
            proxy_pass http://app;
        }

と書いてたけど、これが全部location /にマッチして静的ファイルがちゃんと返せてないのに気づいて試行錯誤して

        location /images {
            alias /home/isucon/webapp/public/images;
        }

        location /stylesheets {
            alias /home/isucon/webapp/public/stylesheets;
        }

こう書いてなんとか返せるようになったけど、^~を使ってたのがよくなくて

        location ~ ^/(stylesheets|images) {
            root /home/isucon/webapp/public;
        }

こうすればマッチした。

あと/をエラーメッセージで分岐して静的ファイルを返すのに

        map $arg_error $index {
            default   index.html;
            locked    err_locked.html;
            banned    err_banned.html;
            not_found err_not_found.html;
            not_login err_not_login.html;
        }

        location = / {
            root /home/isucon/webapp/public;
            index $index;
        }

        location / {
            proxy_pass http://app;
        }

という感じに書いていたけど、これはindexディレクティブによってrewriteされた結果location /にマッチするらしく、これも期待した動作をしなかった。

SSIを試そうとしたときも、最終的にlocation /にマッチしてしまってバックエンドが静的ファイルを返してしまっていたからSSIのタグが置換されずにそのまま出てしまっていた。

もし一度でもlocation /を消したミニマムなケースで試していたらすぐに気づけたかもしれないけど、そもそも基本的なことを理解していなかったことが要因なので実に不甲斐なかった。

おわりに

ダメだったとこ復習して反省したらだいぶスッキリしたので、今度チームでおいしい日本酒飲みに行きましょう!

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して採番されちゃうリスクが軽減されて安心感が増しますね!