2014年3月18日火曜日

MySQL 5.6への移行でmysqldumpを使わなかったらどうなるか

MySQL 5.6へのアップグレードはmysqldump推奨 なんですが、それを無視してmysql_upgradeでアップデートできないものかと考える。というか、深遠な理由でmysql_upgradeでアップグレードしたMySQLが正に目の前にある。


【2014/03/19 11:13修正】

1) performance_schemaが使えない

5.5のperformance_schemaと5.6のperformance_schemaはテーブル構造が盛大に違うので、mysqldを起動させることはできるが盛大にエラーを吐く。performance_schemaにアクセスしようとするとクエリーがエラーになる。

mysqlスキーマの構造が違うのはmysql_upgradeがよしなにしてくれる(ので、InnoDB統計情報永続化とか、relay_log_info_repository= TABLEは使える)が、performance_schemaに関してはノータッチなのでこうなる。

【2014/03/19 11:27修正】

少なくとも5.6.16ではmysql_upgradeがよしなにやってくれているぽい。5.6.13で失敗したことがあったので 差を見てみると、scripts/mysql_fix_privilege_tables.sql というファイルが増えているので、ここかも知れない。ただ、5.6.13にも5.6.16にも scripts/mysql_system_tables.sql というのがあって、こっちでも同じようなことしてるっぽいんだよなぁ。。すいません。 そう思い込んでいたのかと。。ダメだ。。

ちなみに、5.6のmysql_install_dbをテキトーなディレクトリに向かって放ったあと、datadir/performance_schema をまるっとコピーして再起動したら直った(使えるようになった)が、これで本当に直ってるのか(他に影響が出ていないか)どうかは俺は知らない。。

2) TIMESTAMP, DATETIME型の方データ構造が微妙に変わっている

少なくともリファレンスマニュアル上でmysqldumpを推奨している理由についてリストアップされているのがこれ。マイクロ秒対応とかエンディアンが変わったとかパディング方式が変わったとか色々あるけれど、ただ使う分には問題なさげに動く。

例外は、「マスターはmysql_upgradeでアップグレードしたけど、スレーブはmysqldumpからリストアして構築した」もしくは「マスターはmysqldumpでアップグレードしたけど、スレーブはmysql_upgradeでアップグレードした」、かつ「バイナリーログがROWモードで記録された」場合。

これだと、マスターで記録された型情報とスレーブで再生されようとする型情報に不整合が発生するので、

mysql56> SHOW SLAVE STATUS\G
..
    Last_SQL_Errno: 1677
    Last_SQL_Error: Column 0 of table 'd1.t2' cannot be converted from type 'datetime' to type 'datetime'
..

こんな訳のわからない(datetime型からdatetime型への変換に失敗した)エラーでSQLスレッドが転ける。STATEMENTモードでは影響を受けないが、binlog_format= MIXEDでROWモードにフォールバックするようなクエリーが流れているとこれの直撃を食らう。

これは憶えておいた方がいいかも。

2014年3月4日火曜日

Mroongaのテーブルからノイズになるトークンを(手で)取り除いてみる

データは 日々の覚書: 全文検索のテスト用にtweets.csvを食わせるSQL で書いた、わたしのツイートをテーブルに突っ込んだもので試してみました。

トークナイザーとノーマライザーはデフォルトのTokenBigram, NormalizerMySQLGeneralCIにそれぞれなっています。

$ /usr/groonga/4.0.0/bin/groonga /usr/mysql/5.6.16/data/mroonga.mrn
> table_list --output_type tsv
0       1393927353.03097        0.000258684158325195
"id"    "UInt32"
"name"  "ShortText"
"path"  "ShortText"
"flags" "ShortText"
"domain"        "ShortText"
"range" "ShortText"
"default_tokenizer"     "ShortText"
"normalizer"    "ShortText"
259
"tweets"
"/usr/mysql/5.6.16/data/mroonga.mrn.0000103"
"TABLE_PAT_KEY|PERSISTENT"
"UInt64"



263
"tweets-text"
"/usr/mysql/5.6.16/data/mroonga.mrn.0000107"
"TABLE_PAT_KEY|PERSISTENT"
"ShortText"

"TokenBigram"
"NormalizerMySQLGeneralCI"
END

たぶん中の人には嫌われるでしょうが、わたしは--output_type tsv好きですよ :)
では早速トークンを覗き込んでみる。

> select tweets-text --output_type tsv
0       1393927720.5169 0.000890493392944336
51762
[       "_id"   "UInt32"        ]       [       "_key"  "ShortText"     ]       [       "index" "tweets"        ]
26653   "\t"    45
44250   "\tな"  1
15055   "\tを"  1
4       "\n"    944
3       "\n\n"  945
11621   "\nあ"  60
3101    "\nい"  23
14566   "\nう"  21
5776    "\nえ"  1
11625   "\nお"  77
END

いかにもノイズっぽいのがいっぱいありますね!
あ、"index" "tweets"のカラム(3カラム目の、数字が並んでるやつ)は、そのトークンを持ってるレコードの数とかじゃないのでご注意を。

もとの速度は

mysql56> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

mysql56> SELECT * FROM tweets WHERE MATCH(text) AGAINST ('+MySQL' IN BOOLEAN MODE);
2778 rows in set (0.02 sec)

mysql56> nopager
PAGER set to stdout

mysql56> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000055 |
| Waiting for query cache lock   | 0.000007 |
| init                           | 0.000006 |
| checking query cache for query | 0.000131 |
| checking permissions           | 0.000016 |
| Opening tables                 | 0.000115 |
| init                           | 0.000084 |
| System lock                    | 0.000030 |
| optimizing                     | 0.000019 |
| statistics                     | 0.000663 |
| preparing                      | 0.000036 |
| FULLTEXT initialization        | 0.003164 |
| executing                      | 0.000021 |
| Sending data                   | 0.019530 |
| end                            | 0.000027 |
| query end                      | 0.000012 |
| closing tables                 | 0.000177 |
| freeing items                  | 0.003743 |
| cleaning up                    | 0.000056 |
+--------------------------------+----------+
19 rows in set, 1 warning (0.00 sec)

まあこんなもんとしておきます。

$ /usr/groonga/4.0.0/bin/groonga /usr/mysql/5.6.16/data/mroonga.mrn "select tweets-text --output_type tsv --limit -1" | perl -nlae 'if (@F[1] =~ /\\/) {my $cmd= sprintf("/usr/groonga/4.0.0/bin/groonga /usr/mysql/5.6.16/data/mroonga.mrn \"delete tweets-text --output_type tsv --filter \\\"_id== %d\\\"\"", @F[0]); system($cmd);}'
..

とまあ、要らなさそうなトークンを選んで消していきます。他にも記号だけで構成されたトークンとかいろいろゴニョゴニョがんばって消してみました。どうか。

mysql56> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

mysql56> SELECT * FROM tweets WHERE MATCH(text) AGAINST ('+MySQL' IN BOOLEAN MODE);
2778 rows in set (0.03 sec)

mysql56> nopager
PAGER set to stdout

mysql56> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000045 |
| Waiting for query cache lock   | 0.000009 |
| init                           | 0.000006 |
| checking query cache for query | 0.000099 |
| checking permissions           | 0.000012 |
| Opening tables                 | 0.000119 |
| init                           | 0.000054 |
| System lock                    | 0.000019 |
| optimizing                     | 0.000022 |
| statistics                     | 0.000623 |
| preparing                      | 0.000033 |
| FULLTEXT initialization        | 0.003134 |
| executing                      | 0.000018 |
| Sending data                   | 0.019653 |
| end                            | 0.000038 |
| query end                      | 0.000012 |
| closing tables                 | 0.000199 |
| freeing items                  | 0.002571 |
| cleaning up                    | 0.000047 |
+--------------------------------+----------+
19 rows in set, 1 warning (0.00 sec)

うん、誤差!
プロファイルのDurationを足し合わせると2回目の方がほんの少し小さいのに、"2778 rows in set"のあとの数字は2回目の方が大きいふしぎ!

そこはそれとして、さらに、アルファベットのみで構成されたトークン以外を全部消してみた。

mysql56> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

mysql56> SELECT * FROM tweets WHERE MATCH(text) AGAINST ('+MySQL' IN BOOLEAN MODE);
2778 rows in set (0.02 sec)

mysql56> nopager
PAGER set to stdout

mysql56> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000050 |
| Waiting for query cache lock   | 0.000006 |
| init                           | 0.000007 |
| checking query cache for query | 0.000104 |
| checking permissions           | 0.000013 |
| Opening tables                 | 0.000055 |
| init                           | 0.000055 |
| System lock                    | 0.000021 |
| optimizing                     | 0.000018 |
| statistics                     | 0.000387 |
| preparing                      | 0.000032 |
| FULLTEXT initialization        | 0.002243 |
| executing                      | 0.000021 |
| Sending data                   | 0.018932 |
| end                            | 0.000027 |
| query end                      | 0.000012 |
| closing tables                 | 0.000175 |
| freeing items                  | 0.003542 |
| cleaning up                    | 0.000046 |
+--------------------------------+----------+
19 rows in set, 1 warning (0.00 sec)

プロファイルのDurationの合計、もともと 27.892ms, ノイズ削除 26.713ms, アルファベット以外全部削除 25.746ms。

結論、効果はあるんだろうけど(少なくとも16000レコードちょっと、51000トークン程度では)わざわざやる必要なし。

本番データで試したくなってきました。

information_schemaでちょこちょこ使えるTIPS

ちょこちょこ使うi_s関連のSELECTステートメント。
やる前にSET GLOBAL innodb_stats_on_metadata= 0; しておかないと重くなる。

  • xxxってカラム、どのテーブルにあるんだっけ?
mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, column_type FROM columns WHERE column_name= ? ORDER BY 1, 2;

  • データベース上のINDEXの一覧がほしい。
mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns FROM statistics WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY 1, 2;

  • フルテキストインデックスどこだっけ?
mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns FROM statistics WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND index_type= 'FULLTEXT' GROUP BY 1, 2;

  • パーティションの一覧と、入っている件数がほしい。
    • partition_name IS NOT NULLをはずすと、パーティショニングされてないテーブルも出力できる。
mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, partition_name, table_rows FROM partitions WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND partition_name IS NOT NULL ORDER BY 1, 2;

  • PARTITION .. LESS THAN MAXVALUEなパーティションに今入っている件数をチェック。
mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, partition_name, table_rows FROM partitions WHERE partition_description= 'MAXVALUE' ORDER BY 1, 2;

  • InnoDBバッファプールにどのテーブルのデータがどれくらい載ってるか知りたい。
    • table_name, index_nameがNULLのは空きページ。
mysql56> SELECT table_name, index_name, SUM(number_records) AS record, SUM(data_size) AS datasize FROM innodb_buffer_page GROUP BY 1, 2;

  • テーブルごと、スキーマごと、エンジンごとのデータサイズを一発で。
mysql56> SELECT engine, table_schema, table_name, SUM(data_length+ index_length) AS size FROM tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY 1, 2, 3 WITH ROLLUP;

mysql56> SELECT name, count, status FROM innodb_metrics;


InnoDBのロック競合を解析するアレはご本家をどうぞ。
 ⇒ MySQL InnoDBにおけるロック競合の解析手順 - SH2の日記


【2014/03/05 18:41】
1箇所ANDが抜けてた…(´・ω・`)

2014年3月2日日曜日

MySQLのパラメーターチューニング at OSC 2014 Tokyo/Spring

去る2014/03/01(土)の OSC 2014 Tokyo/Spring でMyNAとしてセミナーの枠をいただいたので、おはなしししてきました。

雨の中たくさんの方に足を運んでいただきました。本当にどうもありがとうございました。



"MySQLパラメーターチューニングの理屈と定石"と銘打って、普段アプリを書いているような人向けに、俺が普段やってるパラメーターチューニングと同じくらいのことが誰にでもできるように…とか思って資料を作っていたんですが(社内のDB勉強会に使うネタのうち、パラメーター調整の部分だけを切り出した、というのがもともとのコンセプトです)、作れば作るほど、いかに自分が「考えるな。感じるんだ」でチューニングしているのかをむしろ思い知らされました。

「これとこれ見るでしょ? そうするとなんか変な感じがするじゃない? で、こことここだからこれかなって。いじってみたら違うから、近いパラメーターいじってみったら上手くいった」 みたいな。更に言うなら、パラメーターだけいじることなんて運用フェーズに入ればそんなになくて、「このSQLのネックはこことここのはずで、こっちはSQL書き換えて回避できるけどもう片方はすぐにはできなさそうだから、パラメーターで誤魔化しておく」とかフツーにあるわけですよ。

そうやってあたりをつけながら切り分けて、最適…とまではいかなくともまあまあな状態にチューニングするんですが、これを体系立てて説明する…? とか、正直スキルが足りなさすぎました_| ̄|○

「これだけ設定しておけば間違いない!」みたいなのを紹介したかったし、期待されていたと思うんですが、さりとてテキトーなことを言うわけにもいかず、innodb_buffer_pool_sizeとinnodb_log_file_size* innodb_log_files_in_groupくらいで、そんなの当然みなさんご存知ですよねごめんなさい、という風情です。

スライド中に出てきたツールとか、リンク張るの忘れてた(というか、PDFだ。。)ので、こちらにリンクだけ掲載しておきます。




どうもありがとうございました。