2018年2月17日土曜日

Percona XtraDB Cluster 5.7 のバグたち(修正済)

私はRDBで一番大事なのは、durability (永続性、整合性) だと思っている。

データの整合性は、気づくことが難しい。整合性を疑って、正しいデータと比較して初めて気づける。
気づいたときには影響が広くなっており、すでに取り戻せないデータが発生している・・・ということも有り得る。

Availability (可用性) や Performance (性能) は、問題が出たとしても、いろいろな手段でカバー可能であることは多い。


ということで、踏み抜いておいた(踏み抜いてしまった)PXCのdurabilityに関わるバグたちを紹介。

1. LOAD DATA INFILE 利用時にノード間不整合


Wrong count if LOAD DATA used with GTIDs
https://jira.percona.com/browse/PXC-879

GTID を有効にしている状態で、かつ、LDI中にバイナリログのローテが入ると発動。
PXCにはパフォーマンスをあげるため、LDI時にデータを1万件ごとに小分けにしてコミットする機能が入っている。この機能にバグが潜んでいたようだ。
このバグを踏むと、例えば、31000件ロードしたら、LDIを実行したノードには31000件、正しく書き込まれる、
しかし、その他のノードでは1000件が失われ30000件だけ書き込まれる。

このバグは 5.7.20-29.24 で修正されている。

2. INSERT INTO ~ ON DUPLICATE KEY UPDATE でノード間不整合


Node consistency compromized for simple INSERT INTO ... ON DUPLICATE KEY UPDATE workload
https://jira.percona.com/browse/PXC-2039

複数ノードに同時並列でINSERT INTO ~ ON DUPLICATE KEY UPDATEを行うとノード間データ不整合が発生。

このバグはパッチは出ているものの、まだパッケージとしてはリリースされていない。
次のリリースに取り込まれるであろう。

3. Crash recovery 時にリカバリされないデータが発生


Transaction lost after recovery
https://jira.percona.com/browse/PXC-895

前提として、いわゆる durable (以下)なパラメータでPXCを運用している人のみが気にすべきバグである。
innodb_flush_log_at_trx_commit = 1
innodb_double_write = 1
PXCは複数ノードでデータを複製している。ノードが死んだ場合は、生きてるノードから最新のデータを
コピーする(SST)ため、crash recovery が必要なことは基本的にない。
Crash recovery が必要になるのは、全ノードがダウンした際だけである。

durable なパラメータで運用していない場合、全ノード ダウン時、少々データがロストするのはそもそも「仕様」である。

自分は durable で運用している。
複数のサーバが同時に死ぬことは、ほとんどありえないが、「ドミノ倒し」になることは有り得るからだ。
負荷やバグでノードが次々と死んでいくケースである。

実際、上記の1,2 のバグを踏んだ結果、残り1ノードになってしまうケースがある。
PXCではデータ不整合を検知すると、そのノードは自発的にシャットダウンする。
これ自体はデータ不整合が新たな不整合を生まないための仕組みで、望ましい挙動である。
しかし、結果として、1ノードになってしまうのだ。

このような状態でさらに想定外の事態が起きても、データロストしないよう durableな設定 で保険をかけているのだが、その保険が有効に機能してなかったという・・・
このバグも 5.7.20-29.24 で修正されている。

いずれも報告からすぐ修正がなされており、PXCのサポート体制は評価できる。
PXC5.7を利用している人は、最新バージョンに今すぐバージョンアップしよう。
(2のバグFIXはまだリリースされてないけど・・・)

2017年12月9日土曜日

ファイル倉庫系DBをSQLiteでなんとかできるか

MySQLのDBAをしていると、MySQLを「SQLでアクセス可能なファイル倉庫」として使う人たちに出会うことがある。

彼らの要件はだいたいこんな感じ・・・

  • SQLを喋れる「何か」である必要があるが、MySQLである必要はない
  • DBサーバに入れておけば、可用性やバックアップ面が担保されているので楽チンだからDBに入れる
  • DBサーバを自分たちで運用したくない
  • 処理はアドホックに行われ、雑なSQLが流れる、多くは解析系
  • ログやバイナリを雑に保存する。容量が増えていく

ファイル倉庫系は「そこそこ」動いてれば良いので問題が出ても根本対策がされず、だましだまし運用を続ける、「癌化」しやすい注意案件である。

Excel 以上、MySQL 未満 がマッチしているような案件…SQLite が使えるかも?と思ったので検証してみる。

SQLite は今や Mac OSやiOSに同梱されており、実績は十分である。Percona LIVEのkeynote のスライドを見るとSQLiteはぜんぜん「Lite」ではない高機能なプロダクトであることがわかる。

SQLiteのデータベースは単一ファイル。レプリカを作りたければ、他のサーバにデータファイルをコピーするだけで良い。
冗長化やバックアップも(そこそこのレベルで良いのであれば)非常にお手軽である。

検証環境

SQLite はローカルで更新・参照して計測した、MySQL はDBサーバとクライアントは分離する。
設定は全部デフォ。メモリはデータ量以上に積んでるので基本すべてメモリにキャッシュされている状態で試した。 サーバはVMと、、、割りと雑な検証です。。。
$ rpm -qa |egrep 'sqlite|mysql-community-server'
sqlite-3.7.17-8.el7.x86_64
mysql-community-server-5.7.20-1.el7.x86_64

容量比較

sysbench を利用して500万行のテーブルを作る。

$ sudo ls -alh /var/lib/mysql/sbtest
total 1.2G
drwxr-x--- 2 mysql mysql   55 Dec  9 15:26 .
drwxr-x--x 7 mysql mysql 4.0K Dec  9 15:22 ..
-rw-r----- 1 mysql mysql   65 Dec  9 15:22 db.opt
-rw-r----- 1 mysql mysql 8.5K Dec  9 15:26 sbtest1.frm
-rw-r----- 1 mysql mysql 1.2G Dec  9 15:26 sbtest1.ibd
こいつを、SELECT OUT INTO でCSVとして出力→sqliteでロードした。
$ ls -alh tmp.db
-rw-r--r--  1 user user 1.1G Dec  9 15:54 tmp.db
どっちも1Gちょい。容量はあまり変わらない。ただし、MySQL (InnoDB) は圧縮が利用できるので、MySQLで圧縮するほうが物理容量面では有利である。
※ データにも依存するが、InnoDBで圧縮すればだいたい半分ぐらいになることが多い。

インポートの時間

ほとんど差がなかった・・・! ネットワーク経由でデータを送るMySQLのほうが多少遅くなると思ったが、そうでもないようだ。

mysql> LOAD DATA LOCAL INFILE 'tmp2.csv' INTO TABLE sbtest1 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 5000000 rows affected (2 min 28.06 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0
$ time sqlite3 tmp.db  < imp.sql

real    2m28.788s
user    0m53.754s
sys     0m19.732s

エクスポートの時間

SQLiteのほうが3割ぐらい速い。

mysql> SELECT * FROM sbtest1 INTO OUTFILE '/var/lib/mysql-files/out.csv' FIELDS TERMINATED BY ','   OPTIONALLY ENCLOSED BY '"';
Query OK, 5000000 rows affected (14.96 sec)
sqlite> .mode csv
sqlite> .output result.csv
sqlite> .timer on
sqlite> SELECT * FROM sbtest1;
CPU Time: user 7.159407 sys 1.355699
ちなみにMySQLでローカルでやった場合もあまり時間は変わらなかった。
mysql>  SELECT * FROM sbtest1 INTO OUTFILE '/var/lib/mysql-files/aout.csv'   FIELDS TERMINATED BY ','   OPTIONALLY ENCLOSED BY '"';
Query OK, 5000000 rows affected (16.35 sec)

まとめ

ファイル倉庫系のワークロードでSQLiteが劣っているようなことはなさそう。
Excel以上、MySQL未満を必要とするユーザに提案してみたい。

2017年12月7日木曜日

MySQLのデッドロックについて

この記事は MySQL Casual Advent Calendar 2017 の7日目です。 最近、デッドロックを真剣に対応する機会があったのでその時のメモです。

デッドロックとは?

デッドロックとは「複数のトランザクションが互いがロックの開放を待ち、結果として、どの処理も先に進めなくなってしまった状態」を指す。 単にロック待ちが長時間になっているケースはデッドロックとは呼ばない。

時間 TRANSACTION(1) TRANSACTION(2)
1 BEGIN
2 BEGIN
3 UPDATE t1 SET col1 = 'session1' WHERE pk = 1;
4 UPDATE t2 SET col1 = 'session2' WHERE pk = 1;
5 UPDATE t2 SET col1 = 'session1' WHERE pk = 1;
※ TRANSACTION 2 がt2のロックを持ってるので進めない
6 UPDATE t1 SET col1 = 'session2' WHERE pk = 1;
※ TRANSACTION 1 がt1のロックを持ってるので進めない

MySQL (InnoDB) はデッドロック状態になった場合、片方のトランザクションを強制ロールバックし、もう片方のトランザクションに必要な ロックを獲得させ、処理を進める。その際、強制ロールバックされたクエリは以下のエラーが発生する。

mysql> UPDATE t1 SET col1 = 'session2' WHERE pk = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

デッドロックの対処方法


1. リトライ

根本的な対応ではないが、良くとられる解決策。 タイミング悪く更新が重なったときに起こる程度であれば、リトライで十分。

2. クエリを修正し、ロックを獲得する順番を揃える

上記の例でいうと、片方のトランザクションがt1→t2 という順でロックしているのに対し、もう片方はt2→t1という順でロックしているため、互いにロックを取り合う構造になっている。 ロックを取る順番を揃えれば、デッドロックにはならない(後続のクエリは待つ)。

3. ロックを取る範囲を小さくする

MySQL は条件に一致した行ではなく、スキャンした行やインデックスをロックする。 例えば、以下のクエリは条件にマッチする行が1つもないが、全部の行をロックしてしまう。

mysql> SELECT * FROM t1;
+----+------+
| pk | col1 |
+----+------+
|  1 | t1-1 |
|  2 | t1-2 |
|  3 | t1-3 |
|  4 | t1-4 |
|  5 | t1-5 |
+----+------+
5 rows in set (0.00 sec)

mysql> BEGIN;

-- mysql> EXPLAIN SELECT * FROM t1 WHERE col1 = 'INVALID' FOR UPDATE;
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+


-- col1 にインデックスがないのでフルスキャン
mysql> SELECT * FROM t1 WHERE col1 = 'INVALID' FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)

適切なインデックスを追加し、スキャンする範囲を小さくする、もしくは、
SELECTしたあとに主キーで対象の行のみを狙って更新する方法が有効。yoku0825さんの MySQLから大量のレコードをちまちま削除するメモ はその例。

SHOW ENGINE INNODB STATUS から情報を得る

SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK にデッドロックの詳細が記録される。
どのような経緯でデッドロックに到ったか確認する手助けになる。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-11-30 17:21:52 0x7fd26c11d700
*** (1) TRANSACTION:
TRANSACTION 1383, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140541732701952, query id 84 localhost root updating
UPDATE t2 SET col1 = 'session1' WHERE pk = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 3 n bits 80 index PRIMARY of table `lockt`.`t2` trx id 1383 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000568; asc      h;;
 2: len 7; hex 46000001530110; asc F   S  ;;
 3: len 8; hex 73657373696f6e32; asc session2;;

*** (2) TRANSACTION:
TRANSACTION 1384, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140541732968192, query id 85 localhost root updating
UPDATE t1 SET col1 = 'session2' WHERE pk = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `lockt`.`t2` trx id 1384 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000568; asc      h;;
 2: len 7; hex 46000001530110; asc F   S  ;;
 3: len 8; hex 73657373696f6e32; asc session2;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `lockt`.`t1` trx id 1384 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000567; asc      g;;
 2: len 7; hex 45000001520110; asc E   R  ;;
 3: len 8; hex 73657373696f6e31; asc session1;;

*** WE ROLL BACK TRANSACTION (2)

1. 取得するタイミングに注意

LATEST DETECTED DEADLOCK とあるとおり、「最後に発生したデッドロック」の情報になる。 複数のデッドロックが発生している場合、自分が狙ってる情報が取れるよう、必要に応じて定期的にSHOW ENGINE INNODB STATUSを実行するなどする。

2. 最後のクエリしか記載されない

SHOW ENGINE INNODB STATUSの情報にはトランザクションの最後のクエリしか表示されない。 例でいうと
TRANSACTION(1) の UPDATE t1 SET col1 = 'session1' WHERE pk = 1;
TRANSACTION(2) の UPDATE t2 SET col1 = 'session2' WHERE pk = 1;
が表示されてない。

どういうクエリが原因でデッドロックが発生したか特定するには、LATEST DETECTED DEADLOCKに記載されているクエリを手がかりにソースコードなどから トランザクション全体のクエリを把握する必要がある(SHOW ENGINE INNODB STATUSだけでは全体の流れはわからない)。

3. HOLDS THE LOCKS はTRANSACTION(2)しか表示されない

HOLDS THE LOKCS がTRANSACTION(1)に表示されてないがロックは取ってる。 X row lock(s) に注目したほうが良い。

2017年9月22日金曜日

MySQL 8.0 で導入されたリソースグループを試す

MySQL 8.0.3 リリース候補版がリリースされました。
8.0.3はDMR(Developer Milestone Release) ではなく、 RC(リリース候補) でした。正式リリースまで一歩前進。正式リリースが楽しみですね。

8.0.3 ではリソースグループという機能が導入されました。

The purpose of Resource Groups is to decide on the mapping between user/system threads and CPUs. This can be used to split workloads across CPUs to obtain better efficiency and/or performance in some use cases.
リソースグループはMySQLのスレッドを特定のCPUに割り当てることができる機能。
「ワークロードごとに割り当てるCPUを分けることで性能が上がるケースがある」とあるのは、固定することで CPUのキャッシュヒット率が上がるようなケースを指していると思われます。

デフォルトで、以下の2つのリソースグループが設定されている。
RESOURCE_GROUP_TYPEがSYSTEMのものはInnoDBのpurge thread のようなバックグランドスレッドに割り当てられるリソースグループ。

デフォルトではVCPU_IDSが0-3となっていて、すべてのCPUを使う設定になっている。 つまり、特に制限はかかってない状態。

mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;
+------------------------+-----------------------+------+-----+---------+-------+
| Field                  | Type                  | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| RESOURCE_GROUP_NAME    | varchar(64)           | NO   |     | NULL    |       |
| RESOURCE_GROUP_TYPE    | enum('SYSTEM','USER') | NO   |     | NULL    |       |
| RESOURCE_GROUP_ENABLED | tinyint(1)            | NO   |     | NULL    |       |
| VCPU_IDS               | blob                  | YES  |     | NULL    |       |
| THREAD_PRIORITY        | int(11)               | NO   |     | NULL    |       |
+------------------------+-----------------------+------+-----+---------+-------+

mysql> SELECT * from INFORMATION_SCHEMA.RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |               0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |               0 |
+---------------------+---------------------+------------------------+----------+-----------------+
2 rows in set (0.00 sec)
$ cat /proc/cpuinfo | grep processor
processor       : 0
processor       : 1
processor       : 2
processor       : 3

デフォルトリソースグループは消したり、変更したりできない。

mysql> DROP RESOURCE GROUP USR_default;
ERROR 4557 (HY000): Drop operation  operation is disallowed on default resource groups.

mysql> ALTER RESOURCE GROUP USR_default VCPU=0-1;
ERROR 4557 (HY000): Alter operation is disallowed on default resource groups.

mysql> ALTER RESOURCE GROUP USR_default DISABLE;
ERROR 4557 (HY000): Alter operation is disallowed on default resource groups.

CPUを1個しか使わないリソースグループを試しに作ってみる

mysql> CREATE RESOURCE GROUP OneCPU TYPE=USER VCPU=0 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from INFORMATION_SCHEMA.RESOURCE_GROUPS;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default         | USER                |                      1 | 0-3      |               0 |
| SYS_default         | SYSTEM              |                      1 | 0-3      |               0 |
| OneCPU              | USER                |                      1 | 0        |               0 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)
まずは、何もしない状態でsysbench で負荷をかけてみます。 CPU0-3まですべて使われてることがわかります。
$ mpstat -P ALL 1
02:20:36 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:20:37 PM  all   44.50    0.00    5.50    0.00    0.00    0.00   50.00    0.00    0.00   0.00
02:20:37 PM    0   43.56    0.00    5.94    0.00    0.00    0.00   50.50    0.00    0.00   0.00
02:20:37 PM    1   44.00    0.00    6.00    0.00    0.00    0.00   50.00    0.00    0.00   0.00
02:20:37 PM    2   44.00    0.00    6.00    0.00    0.00    0.00   50.00    0.00    0.00   0.00
02:20:37 PM    3   45.00    0.00    5.00    0.00    0.00    0.00   50.00    0.00    0.00   0.00
次に、OneCPU グループを使うようにsysbench に細工してから実行します。
$ vim /usr/share/sysbench/oltp_read_only.lua
function prepare_statements()
   con:query("SET RESOURCE GROUP oneCPU")

   prepare_point_selects()
意図したとおり、1つのCPU(CPU0だけ)使うようになってますね!
$ mpstat -P ALL 1
02:24:08 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:24:09 PM  all   11.11    0.00    3.28    0.00    0.00    0.00   12.63    0.00    0.00   72.98
02:24:09 PM    0   41.00    0.00    9.00    0.00    0.00    0.00   50.00    0.00    0.00    0.00
02:24:09 PM    1    1.02    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00   98.98
02:24:09 PM    2    1.02    0.00    1.02    0.00    0.00    0.00    0.00    0.00    0.00   97.96
02:24:09 PM    3    1.01    0.00    1.01    0.00    0.00    0.00    0.00    0.00    0.00   97.98

ヒント句でも指定できる。 QueryRewrite プラグインでリソースグループを使うようにヒント句を差し込んで、特定の重い処理を隔離して他への影響を抑える・・・みたいな用途にも使えそう。
1. /*+ RESOURCE_GROUP(resource_group_name) */ -- for executing this SQL query switch the given thread to Resource Group 'name', then switch it back once the query execution is finished.

2017年6月17日土曜日

OSC 2017 Okinawa で MySQLの冗長構成について発表してきた

大阪で話した内容とほぼ一緒なのですが、、、MySQL Shell を試してみました。 OSC Hokkaido も参加したので、これで南は沖縄から北は北海道まで行ったと言える・・・!

2017年3月25日土曜日

MyNA 会 201703 で MySQL のDB利用者向けベストプラクティスの話してきた

MySQL のプロダクトマネジャーである Matt Lord さんをお招きして 日本MySQLユーザ会会 を開催しました。

当日はベストプラクティス・・・というかDBAからのお願いを発表してきました。
DB利用者に意識してほしいことを中心に取り上げてみました。



Matt さんからはGroup Replication を使った高可用構成と、さらにGroup Replicationのセット複数並べてシャーディングする InnoDB Cluster を紹介がありました。

当日の様子は yoku0825 さんが toggeter にまとめてくれてます。
MyNA(日本MySQLユーザ会)会 2017年3月まとめ
https://togetter.com/li/1092945


しかし、Matt さんのパンクな見た目のインパクトが凄かった(笑)

2017年2月2日木曜日

MySQL Causal Talk vol.10 で Group Replication について話した

MySQL Causal Talk vol.10 で Group Replication について話してきました。 最近ずっとGroup Replication しか触ってない・・・・

また、 myfinder さんから、MySQL Casual slack の general チャンネルを英語用とし、日本語用のチャンネルを別途作ることが提案されました。
早速、general チャンネルにMySQLの中の人(!)が投稿し、ちょっとした騒ぎになっている様です。