InnoDBで行ロックを取得してるSQLを調べる

InnoDBでSELECT FOR UPDATEでセッションが溢れて、原因のpidはわかるけど、ロック取得しているSQLがなんとかとれないかなと調べてみた。

MySQL5.7以降であれば下のSQLでとれるかも。たぶんいけるはず。

SELECT 
    lo.*,
    t.thread_id AS blocking_thread_id,
    GROUP_CONCAT(CONCAT('\n',LPAD('', 25, ' '),event_id,':',sql_text) ORDER BY event_id) blocking_thread_sqlinfo
FROM
    (SELECT 
        GROUP_CONCAT(CONCAT('\n',LPAD('', 25, ' '), waiting_pid, ':', waiting_query)) wait_pidlist,
            blocking_pid,
            MAX(sql_kill_blocking_query) killsql,
            blocking_query
    FROM
        sys.innodb_lock_waits a
    LEFT JOIN (SELECT DISTINCT
        waiting_pid AS wpid
    FROM
        sys.innodb_lock_waits) b ON a.blocking_pid = b.wpid
    WHERE
        wpid IS NULL
    GROUP BY blocking_pid) lo
        LEFT JOIN
    performance_schema.threads t ON lo.blocking_pid = t.processlist_id
        LEFT JOIN
    performance_schema.events_statements_current USING (thread_id)
GROUP BY 2\G

試す

下を同時に動かして２つのレコードロックと待機を発生させる

• session 1 :begin;SELECT * FROM t0 WHERE id=1 FOR UPDATE; ← ロック取得
• session 2 : begin;SELECT * FROM t0 WHERE id=2-1 FOR UPDATE; ← session 1で待機

• session 3 : begin;SELECT * FROM t0 WHERE id=3-2 FOR UPDATE; ← session 1で待機

• session 4 : begin;SELECT * FROM t0 WHERE id=2 FOR UPDATE; ← ロック取得

• session 5 : begin;SELECT * FROM t0 WHERE id=4-2 FOR UPDATE; ← session 4で待機

でさっきのSQLを実行

*************************** 1. row ***************************
           wait_pidlist:
                         8:SELECT * FROM t0 WHERE id=2-1 FOR UPDATE,
                         17:SELECT * FROM t0 WHERE id=3-2 FOR UPDATE
           blocking_pid: 5
                killsql: KILL QUERY 5
         blocking_query: NULL
     blocking_thread_id: 30
blocking_thread_sqlinfo:
                         35:SELECT * FROM t0 WHERE id=1 FOR UPDATE
*************************** 2. row ***************************
           wait_pidlist:
                         6:SELECT * FROM t0 WHERE id=4-2 FOR UPDATE
           blocking_pid: 7
                killsql: KILL QUERY 7
         blocking_query: NULL
     blocking_thread_id: 28
blocking_thread_sqlinfo:
                         27:SELECT * FROM t0 WHERE id=2 FOR UPDATE

blocking_thread_sqlinfoがロックを取得しているSQL。
ロックを取得してるセッションをkillするにはkill sqlを実行すればOK。
現在進行系のものであればとれるけど、過去のものはわからない。

最近知ったSET型について

MySQL Casual Advent Calendar 2017の15日目の記事です。

MySQLには古くからSET型というカラムの型がありまして、知らない人もいるかもしれません。
私は聞いたことあるぐらいで、使ったこと、使っている現場に遭遇したことなかったです。

今回は初めてめぐり逢ったSET型について、今更ながら調べた話です。

ググってみるとあまり使用することをおすすめしないことが書いてあることが多いのですが、その理由は少しわかりました。

ざっくりSET型とは

• 文字列オブジェクト
• 0個以上のメンバーを格納
• 最大 64 個 の メンバー
• ENUM型と似ているが、ENUM型は1つのメンバーのみ格納できるが、SET型は複数メンバー可。
• 複数のメンバーをセットする場合はカンマで区切る

ex) set('a','b')の場合、以下が格納可能

null
'a'
'b'
'a,b'

• ストレージの消費量

要素数	バイト数
1 - 8	1
9 - 16	2
17 - 24	3
25 - 32	4
33 - 64	8

• 2 進値 と 10 進値 で管理
  ex) set('a','b','c','d')の場合

要素	2進値	10進値
'a'	00000001	1
'b'	00000010	2
'c'	00000100	4
'd'	00001000	8

'a,d' は 10進値 1 + 8 = 9で表すこともできる。

SET型の詳細はこちら。MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型

検索でインデックスが効かない

要素個別ではなくカラム全体にインデックスが作成される。

特定の要素を含むレコードを検索するときは FIND_IN_SET() 関数 か LIKE 演算子を使用するため、インデックスが効かない。

ex) 'b'を含むレコードを検索

SELECT * FROM t0 WHERE col like '%b%';

or

SELECT * FROM t0 WHERE FIND_IN_SET(col,'b');

以下のように完全一致で 'a,d' を検索する場合は インデックスは効く。

ex) 'a,d' または 10進値 1 + 8 = 9

SELECT * FROM t0 WHERE col = 'a,d';

or

SELECT * FROM t0 WHERE col = 9;

また カラム名 + 0 することで、そのレコードがもつ10進値の値を取得できるので、仮想列使ってうまくやれば特定の要素でもインデックス使えるかも。(全然思いついてないけど・・)

SELECT col , col+0 FROM t0 WHERE col = 'a,d';
+------+--------+
| col  | col +0 |
+------+--------+
| a,d  |      9 |
+------+--------+

要素リスト追加の罠

要素の追加は基本は即時で完了しますが、前述のストレージの消費量のバイトが増えるタイミングはALGORITHM=COPY の ALTER TABLE 発生。 9 , 17 , 25 , 33個目の要素追加時にテーブル全体のwrite lockが発生するので注意が必要。
もちろん、ALGORITHM=COPYなのでオンラインDDLが不可。

• ex ) 要素8 , 9個目追加

mysql > SHOW CREATE TABLE t0;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                       |
+-------+----------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (
  `col` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------+

mysql > ALTER TABLE t0 modify col set('a','b','c','d','e','f','g','h');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > ALTER TABLE t0 modify col set('a','b','c','d','e','f','g','h','i');
Query OK, 2 rows affected (0.10 sec) ← ALGORITHM=COPYで実行された
Records: 2  Duplicates: 0  Warnings: 0

要素リストのリネームができない

これはENUM型も同様ですが、
すでに変更する要素のデータが存在する場合は、リネームできない。

mysql > SHOW CREATE TABLE t0;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                       |
+-------+----------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (
  `col` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------+

mysql > SELECT * FROM t0;
+------+
| type |
+------+
| a,b  |
| a    |
+------+

mysql > SET sql_mode='STRICT_TRANS_TABLES';
mysql > ALTER TABLE t0 modify col set('zz','b','c');

ERROR 1265 (01000): Data truncated for column 'col' at row 1
Error (Code 1265): Data truncated for column 'col' at row 1
Error (Code 1317): Query execution was interrupted

変更する要素のデータが存在しない場合は、ALGORITHM=COPY の ALTER TABLE 発生。

mysql > SELECT * FROM t0;
+------+
| type |
+------+
| a,b  |
| a    |
+------+

mysql > SET sql_mode='STRICT_TRANS_TABLES';
mysql > ALTER TABLE t0 modify col set('a','b','zz');

Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

以上、最近知ったSET型についてでした。

xtrabackupが終わらない

xtrabackupでバックアップとっていたがいつまでたっても終わる気配ないという状況に。 バージョンはxtrabackup2.4.7。 xtrabackupにはIOを制御するための--throttleオプションがあって、これにやられた。

マニュアルの情報。

--throttle=#
This option limits xtrabackup –backup to the specified number of read+write pairs of operations per second.
@The xtrabackup Option Reference

詳細はここThrottling Backups。

簡単にまとめると、バックアップの時にread + writeを1 MB単位での1秒あたりの入出力操作の数を制御するとのこと。
単位はIOでデフォルトは0で制御しない。

• xtrabackupのざっくり動作

Innodbの場合はデータファイル(ibd file)とトランザクションログ(Innodb log file)をコピーする。
で、--parallelオプションがあって、ここに値を設定するとibd fileはパラレルでコピーされるが、Innodb log fileには効かなくてシリアルでコピーする。

1. 最初にInnodb log fileのコピーをシリアル実行で開始する。
2. Innodb log fileのコピーが最新の更新に追いついたら、ibd fileのコピーをパラレル実行で開始する。（Innodb log fileのコピーと並列に）
3. InnoDBのテーブルをバックアップした後に、FLUSH TABLE WITH READ LOCKして InnoDB以外のテーブルをコピーする。

ここで先程のthrottleオプションはInnodb log fileとibd fileのコピーをスレッドごとにIO制御するようになっていて、 終わらなかった原因としては1のInnodb log fileのコピーをずっとしていたようだった。

--throttleオプションで制御してコピーする量よりも更新量のが多いと、Innodb log fileのコピーがいつまでたっても最新の更新まで追いつかずにこのようなことが起こるので注意が必要。

マニュアルにも書いてたね。

the backup might be so slow that it will never catch up with the transaction logs that InnoDB is writing, so the backup might never complete.

ちなみに商用版のEnterprise BackupはInnodb log fileとibd fileのコピーを同時に開始している模様。
そして、--throttleオプションのようなIOを制御する--sleepオプションがあり、これはibd fileのコピーには有効であって、Innodb log fileには効かなくて全力で書き込んでくれる感じ。Enterprise Backupのが理想的な動きをしてくれるようです。

redis-fainaでよく使用されるコマンドを確認する

redis-faina使おうとするときすぐ使えるようにするメモ。

github.com

RedisのMonitorコマンドを使って出力した情報をsummaryしてくれるすごいツール。 Monitorしてると負荷上がるからなにかある時に使う。

ダウンロード

git clone https://github.com/facebookarchive/redis-faina.git

準備

redis-benchmarkを使用してそのワークロード実行しておく

# redis-benchmark
====== PING_INLINE ======
  100000 requests completed in 0.83 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
120192.30 requests per second

====== PING_BULK ======
  100000 requests completed in 1.01 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
98522.17 requests per second

====== SET ======
  100000 requests completed in 0.91 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

99.95% <= 1 milliseconds
99.95% <= 15 milliseconds
99.98% <= 16 milliseconds
100.00% <= 16 milliseconds
110375.27 requests per second

====== GET ======
  100000 requests completed in 0.89 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
112612.61 requests per second
・
・
====== MSET (10 keys) ======
  100000 requests completed in 3.39 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

0.01% <= 1 milliseconds
98.14% <= 2 milliseconds
100.00% <= 3 milliseconds
100.00% <= 3 milliseconds
29455.08 requests per second

redis-benchmark実行中にmonitorコマンド使用して、ファイルにリダイレクトさせておく。 CTRL+Cとかで終了もできる。

# redis-cli monitor > monitor.txt

redis-faina実行して確認する

# cat monitor.txt | ./redis-faina/redis-faina.py
Overall Stats
========================================
Lines Processed         1500001
Commands/Sec            43812.98

Top Prefixes
========================================
key             300000  (20.00%)
counter         100000  (6.67%)

Top Keys
========================================
mylist                  700000  (46.67%)
key:__rand_int__        300000  (20.00%)
myset                   200000  (13.33%)
counter:__rand_int__    100000  (6.67%)

Top Commands
========================================
LRANGE          400000  (26.67%)
PING            200000  (13.33%)
LPUSH           200000  (13.33%)
INCR            100000  (6.67%)
SET             100000  (6.67%)
SADD            100000  (6.67%)
GET             100000  (6.67%)
SPOP            100000  (6.67%)

Command Time (microsecs)
========================================
Median          6.0
75%             33.5
90%             63.0
99%             132.0

Heaviest Commands (microsecs)
========================================
LRANGE          20988495.0
MSET            3393906.75
LPUSH           2054070.75
PING            1696820.0
SET             1180424.75
SADD            1095502.5
INCR            1053511.25
LPOP            944271.25

Slowest Calls
========================================
13712.0         "LRANGE" "mylist" "0" "599"
13146.0         "PING"
13027.0         "LRANGE" "mylist" "0" "299"
12669.0         "LRANGE" "mylist" "0" "449"
12163.0         "PING"
11780.0         "LRANGE" "mylist" "0" "99"
11622.0         "LPUSH" "mylist" "xxx"
11323.0         "SET" "key:__rand_int__" "xxx"

sysbench1.0でMySQLベンチマークするメモ

sysbench1.0でMySQLのベンチマークとったのでその方法のメモ。 OSはCentOS7.3

MySQLをyumでインストールする

# wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# rpm -i mysql57-community-release-el7-11.noarch.rpm
# yum install -y mysql-server mysql
# systemctl start mysqld

パスワード確認

# cat /var/log/mysqld.log | grep "temporary pass"
2017-08-01T09:23:25.727487Z 1 [Note] A temporary password is generated for root@localhost:xxxxxxxx

sysbench1.0 インストール

参考

github.com

# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench

ベンチマークテスト

準備

• データベース作成

mysql> CREATE DATABASE sysbenchdb;

• prepare
  • –mysql-dry-run=ONで実際に実行せずに確認できる

# sysbench oltp_common \
    --mysql-dry-run=off \
    --db-driver=mysql \
    --mysql-user=root \
    --mysql-password=test \
    --mysql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    prepare

実行

–threadsでスレッド数。 –timeで時間指定（秒）。

# sysbench oltp_read_write \
    --mysql-dry-run=off \
    --db-driver=mysql \
    --mysql-user=root \
    --mysql-password=test \
    --mysql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    --threads=2 \
    --time=30 \
    run

sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 2
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            430766
        write:                           122987
        other:                           61509
        total:                           615262
    transactions:                        30740  (1024.40 per sec.)
    queries:                             615262 (20503.45 per sec.)
    ignored errors:                      29     (0.97 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0055s
    total number of events:              30740

Latency (ms):
         min:                                  1.72
         avg:                                  1.95
         max:                                 10.54
         95th percentile:                      2.14
         sum:                              59936.56

Threads fairness:
    events (avg/stddev):           15370.0000/160.00
    execution time (avg/stddev):   29.9683/0.00

oltp_read_writeのような[testname]は下のlua scripts

# ls /usr/share/sysbench/
bulk_insert.lua  oltp_insert.lua        oltp_read_write.lua        oltp_write_only.lua       tests
oltp_common.lua  oltp_point_select.lua  oltp_update_index.lua      select_random_points.lua
oltp_delete.lua  oltp_read_only.lua     oltp_update_non_index.lua  select_random_ranges.lua