かみぽわーる

kamipo's blog

MySQL と Unicode Collation Algorithm (UCA)

utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる で、日本語が分かる人には utf8_unicode_ci のヤバさを感じてもらえたと思うんですけど、この挙動はドキュメントによると UCA というアルゴリズムによるものらしい。

MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. Currently, the xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese, Yoruba, and some smaller languages such as Navajo. http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

これもしかして、 Unicode って日本語のこと分かってない人たちによって作られてて日本語の検索とかなんも考慮されてないんとちゃうんかと思って調べてみたら、 そんなことはまったくなくて MySQL が UCA を部分的にしか実装していないということだった。

UTS #10: Unicode Collation Algorithm によると、 UCA では Multi-Level Comparison といって複数レベルに分けて文字列の比較を行い、各レベルの比較で文字列が一致した場合(tie-breaking level)、次のレベルで比較を行うを繰り返して順序を決定する。L1は大文字小文字アクセント無視(Base Charaters)、L2はアクセントを無視しない(Accents)、L3は大文字小文字を無視しない(Case/Variants)、のような感じである。

MySQL が UCA を部分的にしか実装していないとはどういうことかというと、 allkeys.txt でいうところの Primary weight range (L1比較のためのweight) しか実装していないので、アクセントを区別しないと常用に適さない言語(日本語とか)殺しな挙動になっているということだった。

mysql-5.7.6/strings/ctype-uca.c#L17-L31 からコメントを引用する。

/* 
   UCA (Unicode Collation Algorithm) support. 
   Written by Alexander Barkov <bar@mysql.com>
   
   Currently supports only subset of the full UCA:
   - Only Primary level key comparison
   - Basic Latin letters contraction is implemented
   - Variable weighting is done for Non-ignorable option
   
   Features that are not implemented yet:
   - No Normalization From D is done
     + No decomposition is done
     + No Thai/Lao orderding is done
   - No combining marks processing is done
*/

アクセントを区別しないと常用に適さない言語が日本語以外にどれぐらいあるのか言語に詳しくないので分からないけど、これを理由に以下のコミットをrevertできたりしないですかね。

参考

utf8_unicode_ci に対する日本の開発者の見解

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

おわりに

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