GA

2018/12/26

InnoDBのテーブル断片化を解消させたいけどもマスターにそんなに容量が残っていない！ 時のテクニック

TL;DR

  • トランスポータブルテーブル表領域を使う
  • メンテが必要になるのが大前提で、それでもストレージ容量がカツカツの場合の対策
    • 容量に余裕があって、 ALTER TABLE .. ENGINE = InnoDB がオンラインで実行できるならそうする
元ネタはこちら。
( ´-｀).oO（見られない人は Join mysql-casual on Slack! から参加していただければどなたでも
前提条件として、
  • OPTIMIZE TABLE, ALTER TABLE .. Engine = InnoDB で空き領域が回収できることがわかっている
    • SELECT table_schema, table_name, data_free FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ORDER BY data_free DESC; とかで調べましょう
  • 短時間（容量による）のメンテに入れられる
  • オンラインの ALTER TABLE .. Engine = InnoDB では容量があふれそうである（または負荷的に耐えられないのでメンテに入れた方がマシ）
  • 一時的に、元のデータベース全体と同じだけの容量を持ったサーバーが用意できる
    • レプリケーションフィルターを使いこなせば全体と同じだけじゃなくてもいいけどオススメしない
  • トランスポータブル表領域は5.6とそれ以降の機能
これを満たさない場合はオンラインDDLで OPTIMIZE TABLE, ALTER TABLE したり、 pt-online-schema-change で Engine = InnoDB を指定したりする方が良い。
やり方。
まずはマスターのデータを複製して一時利用のサーバーにスレーブを作る。
この時、断片化を解消してやらないといけないので、 mysqldump, mysqlpump, myloader などの論理バックアップを取ってリストアするか、物理バックアップや xtrabackup の場合はリストアした後にスレーブ側で OPTIMIZE TABLE をかけておく。
d2.t3が断片化しているテーブルだと思いねぇ。
そして断片化を解消させた状態のサーバーをレプリケーションにぶら下げる。
この間、レプリケーションで同期は取られるので、少しくらいメンテまで間が空いても大丈夫。
メンテに入れたら、マスターで read_only = 1 とか offline_mode= 1 とか、とにかくちゃんと書き込めない状態にして、スレーブとのレプリケーションを切り離す。
その後、 d2.t3 （肥大化しているテーブル）を DROP TABLE 。ただし、ファイル操作が混じるのでバイナリーログをOFFにしてレプリケーションで流れないようにすること。
この時、バイナリーログのOFFも忘れてレプリケーションを切り離すのも忘れて DROP TABLE すると大事故になるので注意。
master> SET GLOBAL offline_mode= 1;
slave> STOP SLAVE;
slave> RESET SLAVE ALL;
master> SHOW CREATE TABLE d2.t3\G -- あとで使うのでメモっておく、切り離したスレーブで調べても良い
master> SET SESSION sql_log_bin = 0;
master> DROP TABLE d2.t3;
ここでスレーブのサーバーからトランスポータブル表領域の機能を使って d2.t3 テーブルをマスターにコピーする。ここでコピーする.ibdファイルは既にOPTIMIZE済のため、さっきDROPした.ibdファイルよりも断片化が解消されている分小さくなっている。
/* masterのバイナリーログはOFFのまま！ */
master> CREATE TABLE d2.t3 (..) ; -- さっきメモしたテーブル定義でテーブルを作る
master> ALTER TABLE d2.t3 DISCARD TABLESPACE; -- .ibdファイルを消し飛ばして.frmだけ残す操作
slave> FLUSH TABLE d2.t3 FOR EXPORT; -- スレーブの.ibdファイルをサーバー間移動できるようにするためのクエリー
/* FLUSH TABLE .. FOR EXPORTの効果はmysqlコマンドラインクライアントを抜けると解除されてしまうので、ターミナルは別のものを起動する */
slave $ scp /var/lib/mysql/d2/t3.{ibd,cfg} my-server1:/var/lib/mysql/d2/ ### FLUSH TABLE .. FOR EXPORTのクエリーがOKを返してから
master> ALTER TABLE d2.t3 IMPORT TABLESPACE /* まだmasterのバイナリーログはOFFのまま！ */;
インポートできたらメンテを解除して、一時サーバーを破棄しておしまい。
ね、簡単でしょ？ :thinking_face: ([要出典] 簡単 #とは)
​
投稿者 時刻: 0 件のコメント :
ラベル:

ペパボがProxySQLならこちらはMySQL Routerだ！

TL;DR

  • パッチはだいぶ下にあります。しかも大したやつじゃないです。
  • 9割余談です。
このエントリーは GMOペパボ Advent Calendar 2018 の26日目の記事です。
25日目は kurotaky さんの「結婚式の後に書きます！」の予定だったんですが、どう考えても結婚式の後にブログ書いてる暇があったら奥さんと結婚式の余韻に浸ってください。 末永くお幸せに！
ところでなぜ26日目なのかというと、
中の人へ
https://qiita.com/organizations/pepabo に所属すると登録できるようになります。お近くの CTL に Qiita のユーザ名を伝えて追加してもらってください
さすがにこの組織に追加してもらう訳にはいきません（注: わたしはペパボの中の人ではありません） が、26日目なら名乗ってもOKとのことだったので、これは26日目の記事（論理）です！
メリークリスマス！ （プラス1日）
さて、2018年のペパボと言えば、 ProxySQL だったように思います（独断と偏見と観測範囲による、主に おっくんさん と仲良くしてたからそう思うだけな気がする）
おもむろに もりたこさん がProxySQLの中身を読みだしたりしているところからもその気配は感じられますね。
あとは k1low さんが tcpdp を公開していたり
監査大事！ クエリーログ！ っていうふいんき（略）が伝わってきます。
ところでおっくんさんのスライドで紹介されている「いろいろなクエリログの取得方法」ですが
＿人人人人人人人人人人人人＿
＞　MySQL Routerがない！　＜
￣Y^Y^Y^Y^Y^Y^Y^Y^Y^Y^Y^Y^￣
ProxySQLはあるのに！！！
と思ったら、どうも
俺もProxySQL勧めたんだった！！！！！１
orz
ウチの環境では mikasafabric for MySQL というMySQL Fabricの なれの果て フォークプロダクトをバリバリ本番で使っていますので、MySQL Routerのハックはまれにですがよくやります。
今年の9月いっぱいくらいはMySQL Router 8.0.12にfabric+cache pluginをポーティングする 苦行 作業をしていました。やったぜ、これでMySQL 8.0でもmikasafabric for MySQLが使える！ つらくない！
ちなみにテスト用に入れていたパッチはこんな簡単なやつだったようです。
これアカウント名とか取るには認証パケットの段階で記録しておかないといけなさそうで、面倒でやめた気がしますが、ジェネラルログみたいに、CONNECTの時にスレッドIDと接続元とかアカウントを記録してやって、ログを見る時にスクリプトでほげればいいんだなきっと。
176 #ifdef QUERY_LOG
177     if (buffer[4] == 0x3)
178     {
179       // Com_QUERY
180       auto pkt = mysql_protocol::Packet(buffer);
181       auto query_length= pkt.get_lenenc_uint(0);
182       if (query_length > 2)
183         log_info(pkt.get_string(4, query_length).c_str());
184     }
185 #endif
もうちょっとカジュアルにMySQL Routerをゴニョれるようになりたいですね、来年は。
完璧に余談ですが、 おっくんさん は実は 席替えスクリプト
の時に何となく名前を認識して、MySQL Casualの時に「すごい人だなーってどこかで聞いたことのある名前だなー」って思って、今年ようやくリアルでも改めて交流ができた年でもありました。
さあ、また来年もMySQLやっていき！
​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2018/12/15

とある豆腐のエラー考察（未完）

このエントリーは
に対する考察（？）エントリーです。
というわけでまずは まみー さんの↑のエントリーを読まれてからこのエントリーを読んでいただけると幸いです！
免責事項（？）ですが、俺はこのエラーの再現環境があるわけではないので、かなりの部分を推測に基づいて考察します。

時系列まとめ

  1. MySQL 5.7.19を停止する
  2. yum upgrade でMySQL 5.7.19 から MySQL 5.7.24にバイナリー入れ替え
  3. (書かれていないけどMySQL 5.7.24の起動後) mysql_upgrade を実行（そしてこれが一部失敗している）
  4. アクセスできないテーブルがあり、.ibdファイルが失われていることが判明
らしいです

mysql_upgrade のエラー

1, 2, 3と来る手順は間違っておらず、大概の場合ここで正常終了して「マイナーバージョンアップ完了、おつかれさまでしたー」となるはずが、 mysql_upgrade を実行したところエラーが出ている。
この結果で気になるところは2つ。

InnoDBのテーブルスペース（.ibdファイルまたはibdata1、設定依存）がない

mamy1326.c_log
Warning  : InnoDB: Tablespace is missing for table mamy1326/c_log.
Error    : Tablespace is missing for table `mamy1326`.`c_log`.
error    : Corrupt

mamy1326.t_session_log
Warning  : InnoDB: Tablespace is missing for table mamy1326/t_session_log.
Error    : Tablespace is missing for table `mamy1326`.`t_session_log`.
error    : Corrupt
読んで字のごとく、テーブルスペースがないらしい。
InnoDBはInnoDBのディクショナリーに「テーブル名とテーブルスペース（.ibdファイルまたはibdata1ファイル、場合によってはジェネラルテーブルスペースで名前が可変）」のマッピングを持っていて、それに従ってテーブルスペースを探しに行ったが見つからなかった、というエラー。

mysql_upgrade_infoが書き込めないエラー

Could not create the upgrade info file '/var/lib/mysql/mysql_upgrade_info' in the MySQL Servers datadir, errno: 13
mysql_upgrade は「最後に mysql_upgrade が実行されたバージョン番号」を datadir/mysql_upgrade_info というテキストファイルに書き出そうとする。それに失敗したよ、というエラー。
$ perror 13
OS error code  13:  Permission denied
/var/lib/mysql のパーミッションがおかしい、とまみーさんは踏んだようだが、
  • mysql_upgrade_info は mysql_upgrade のプロセスが吐く、つまり mysql_upgradeコマンドを実行したOSユーザーの権限でファイルを作ろうとする
  • Oracle提供のrpmファイルは /var/lib/mysql を mysqlユーザー、mysqlグループの751として作成するので、 mysql_upgrade コマンドを実行したOSユーザーが mysqlユーザーまたはrootユーザーでない限りは確かにファイルは作れずに転けるはず
  • mysql_upgrade は特にどのアカウントで実行したのか明記されていないけど、mysqlユーザーでやっていたのかどうかがカギになってくる
    • mamyユーザーみたいなOSユーザーでやっていた場合
      • 確かに権限がないので転けるのは自然
        • ちなみに、 mysql_upgrade の内部処理そのものをやるのは mysqld なので、 mysql_upgrade を実行したユーザーによらずアップグレード処理は正常に実施される（テーブルスペースが見つからないエラーは置いておく）
    • OSのmysqlユーザーでやっていた場合
      • 本来あるはずの書き込み権限がなかった…ということならば、どこかのタイミングでパーミッションが変更されていたはずで、 mysqld の実効ユーザーがdatadirに書けなくなったままプロセスが動いていたのならばちょっとくらいおかしくなってても別に不思議ではない…。
まずはここの切り分け。
前者ならばこれは単に「無視すればいい納得のいくエラー」であり、後者なら「InnoDBテーブルスペースを失う原因になりえる環境の異常」だ。
[ERROR] InnoDB: Cannot open datafile for read-only: './mamy1326/c_log.ibd' OS error: 71
[ERROR] InnoDB: Operating system error number 2 in a file operation.
[ERROR] InnoDB: The error means the system cannot find the path specified.

$ perror 71
OS error code  71:  Protocol error

$ perror 2
OS error code   2:  No such file or directory
VirtualBoxの共有フォルダのファイル開こうとしてEPROTO食らってる こともあるみたいで、 /var/lib/mysql の実態はVirtualBoxの.vdiとかの中にあったのか、ホストからマウントしていたのかがちょっと気になる。
後者の場合はパーミッションの事情がややこしくなりそう…。
どうでしょう？
​
投稿者 時刻: 0 件のコメント :
ラベル:

2018/12/13

utf8mb4_0900_ai_ci の速度をどう見るか

TL;DR

  • MySQL 8.0からデフォルトの照合順序が latin1_swedish_ci から utf8mb4_0900_ai_ci になった
  • さすがに latin1 をそのまま使っているとは思えないけれど、 utf8mb4 だけで見てもデフォルトは utf8mb4_general_ci から utf8mb4_0900_ai_ci に変更になっている
  • 「思ったよりは遅くならない」と見るか、「そんなに遅くなるのか」と見るかは人による気がする
まずは等価比較。
それぞれ10億回繰り返しているので、1回当たりの時間はナノ秒単位になる。
あと、データは保管せずただ比較しているだけなので、単純にCPU勝負のワークロードになる。
mysql80 8> SELECT BENCHMARK(1000000000, '1' = '1' COLLATE utf8mb4_0900_ai_ci) AS utf8mb4_0900_ai_ci; -- 8.0のデフォルト
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+
|                  0 |
+--------------------+
1 row in set (41.11 sec)

mysql80 8> SELECT BENCHMARK(1000000000, '1' = '1' COLLATE utf8mb4_general_ci) AS utf8mb4_general_ci; -- 5.7までで指定せずにutf8mb4にした時はこれ
+--------------------+
| utf8mb4_general_ci |
+--------------------+
|                  0 |
+--------------------+
1 row in set (23.73 sec)

mysql80 8> SELECT BENCHMARK(1000000000, '1' = '1' COLLATE utf8mb4_bin) AS utf8mb4_bin; -- みんなだいすきbin
+-------------+
| utf8mb4_bin |
+-------------+
|           0 |
+-------------+
1 row in set (17.75 sec)

mysql80 8> SELECT BENCHMARK(1000000000, '1' = '1' COLLATE utf8mb4_ja_0900_as_cs) AS utf8mb4_ja_0900_as_cs; -- ハハ != パパ
+-----------------------+
| utf8mb4_ja_0900_as_cs |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (1 min 22.47 sec)

mysql80 8> SET NAMES latin1;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT BENCHMARK(1000000000, '1' = '1' COLLATE latin1_swedish_ci) AS latin1_swedish_ci; -- 一応latin1も
+-------------------+
| latin1_swedish_ci |
+-------------------+
|                 0 |
+-------------------+
1 row in set (17.56 sec)
17nsが82nsになったのを見てどう思うかは人次第。。
しかしこれ、1文字の比較でこれである。文字数が増えれば当然増えていく。
↓単位はすべてns
collate ‘1’ = ‘1’ ‘1a’ = ‘1a’ ‘yoku0825’ = ‘you0825’ ‘🍣’ = ‘🍺’
utf8mb4_0900_ai_ci 41.11 50.34 125.51 42.21
utf8mb4_general_ci 23.73 26.84 54.08 26.46
utf8mb4_bin 17.75 19.50 24.09 19.83
utf8mb4_0900_ja_as_cs 22.47 129.95 432.00 50.89
latin1_swedish_ci 17.56 21.64 30.02 24.20
たかだか8文字の比較で0.4usも持っていかれるのはなかなか…と見るか、それでもus単位だから十分じゃん？ と見るか。
更に文字列の比較といえば ORDER BY によるソートももちろん文字列の比較になるので、
mysql80 15> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `aici` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `generalci` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bin` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `ja` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 15> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.66 sec)

mysql80 15> SELECT * FROM t1 ORDER BY aici ASC LIMIT 1;
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| num    | aici                             | generalci                        | bin                              | ja
     |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 848775 | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.87 sec)

mysql80 15> SELECT * FROM t1 ORDER BY generalci ASC LIMIT 1;
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| num    | aici                             | generalci                        | bin                              | ja
     |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 848775 | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.96 sec)

mysql80 15> SELECT * FROM t1 ORDER BY bin ASC LIMIT 1;
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| num    | aici                             | generalci                        | bin                              | ja
     |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 848775 | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.92 sec)

mysql80 15> SELECT * FROM t1 ORDER BY ja ASC LIMIT 1;
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| num    | aici                             | generalci                        | bin                              | ja
     |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 848775 | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d | 0000104cd168386a335ba6bf6e32219d |
+--------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (1.74 sec)
100万行のクイックソートでこれくらい速度にも違いは出ますわな… utf8mb4_0900_ai_ci はデフォルトだから意識されている気がするけど、 utf8mb4_ja_0900_as_cs はやっぱりね…。


インデックスを作る時もやっぱりソートするので



mysql80 16> ALTER TABLE t1 ADD KEY (aici);
Query OK, 0 rows affected (6.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 16> ALTER TABLE t1 ADD KEY (generalci);
Query OK, 0 rows affected (5.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 16> ALTER TABLE t1 ADD KEY (bin);
Query OK, 0 rows affected (5.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 16> ALTER TABLE t1 ADD KEY (ja);
Query OK, 0 rows affected (6.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

こっちはまあ誤差くらいかな…。

ご利用は計画的に。
​
投稿者 時刻: 0 件のコメント :
ラベル: ,

2018/12/06

マイエスキューエルにはPerl Mongerが必要かもしれないはなし

この記事は

みなさんは innotop をご存知でしょうか？
そう、 SHOW PROCESSLIST や SHOW ENGINE INNODB STATUS の結果を top ライクに表示してくれる、みんなだいすき Perlで書かれた スクリプトです。
わたしはこのツールが随分気に入っていて、今までも何度もブログを書いていました。
息してないなーエントリの時もTwitterでちょくちょく「使ってたのに」的な反応があったので、案外（？）使っている人も多いのかも知れません。
最初は2012年だから本当にMySQLを真面目にやり始めた頃、MySQL 5.7対応でプルリクを出したりしたのは2016年だったんですね。今年はMySQL 8.0対応の Pull-Request を書きました。これでinnotopはMySQL 8.0でも使えます。安心ですね。
さて、ここ数年のinnotopへのコントリビュートを経て思ったことが、「マイエスキューエルにはPerl Mongerが必要かもしれない」ということです。
まずこの スクリプト は「如何にも昔から動き今も動くPerlスクリプト」という感じで、1ファイルで1万2千行以上、ファイルサイズも447kBもあります。
たとえばセマフォの情報を示すセクションをパースしているコードはこんな感じになっていて、
Perl Mongerでない限り心が折れてしまうんではないでしょうか。
ちなみにPerl Mongerでも心は折れると思います。俺は何度か折れた。
加えて、このバッファプールのセクションをパースしているあたりなんかは、Perlが問題なく読めたとしても「そもそも SHOW ENGINE INNODB STATUS が読めないとこれで合ってるのかどうか判断できない」というなかなかアレな状態です（新しいバージョンが出た時に自分自身でパースできないとテストケースの作りようがなくなるのでな…）
という訳で現在、innotopにパッチを当てられるのはおおむね (“Perl Mongerである” || “Perl Mongerではないが心が強い”) && “InnoDBと友達” な人に限られるのではないでしょうか…と言いつつ、OSSにパッチを投稿するには限りなく

自分が使っている = 不具合は自分に不都合

というモチベーションが必要です。
Perl MongerではないけどPerlが好きで、InnoDBと友達ではないけれど言いたいことはわかって、innotopをガシガシ本番で使っている俺がこのソフトウェアにパッチを当てるようになったのは自然なことなのかも知れません。

自分で使っているのを差っ引いても、innotopはおおむね便利なのでみなさまも使っていただけると（俺ではなくて中の人の）励みになると思います。


ちなみにここまでの文書、 s/innotop/pt-query-digest/g とか s/innotop/MHA for MySQL/g とかにしても似たようなことが言えます。

Perl製のMySQLのツールはそれなりにあるので、PerlでMySQLな人は そのツールが消えてなくなる前に パッチなどご協力いただければ幸いです。





( ´-｀).oO（レプリケーションと友達なPerl Mongerが捕まらなかったのか、使っているユーザーがいなかったのか、 MMM for MySQL …いやううんなんでもない

明日の

です。
明日もおたのしみに。
​
投稿者 時刻: 0 件のコメント :
ラベル: , ,
登録: 投稿 ( Atom )