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 インストール
参考
# 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
xtrabackupの--ftwrl-wait-timeoutと--ftwrl-wait-thresholdのメモ
xtrabackupの–ftwrl-wait-timeoutと–ftwrl-wait-thresholdをいつも忘れてしまうのでメモ。 xtrabackup2.4で試す。
–ftwrl-wait-threshold
Flush table with read lockを実行するタイミングでのクエリの実行時間。デフォルト60秒。–ftwrl-wait-timeout
–ftwrl-wait-thresholdの時間を過ぎたクエリがあった場合、Flush table with read lockを実行するために待機する時間。デフォルト0秒。よって待機しない。
xtrabackupの基本流れ
xtrabackup start | ibd file copy and innodb log copy | | ← Flush table with read lock | non-innodb file copy | ← UNLOCK TABLES xtrabackup finish
例:–ftwrl-wait-timeout=1800 and –ftwrl-wait-threshold=10
xtrabackup start | ibd file copy and innodb log copy | | ← 1.Flush table with read lockの実行前にこの時点で10秒以上経過したクエリがあるかチェック | 1-1.ない場合はFlush table with read lockを実行して、2はスキップ。 | 1-2.ある場合は Flush table with read lockを実行せずに1800秒待機。2へ。 | | 2.1800秒の間に毎秒、その10秒以上経過したクエリが終了したかチェック | 2-1. クエリが終了していた場合はFlush table with read lockを実行 | 2-2. クエリが終了せずに1800秒経過した場合はbackup失敗で終了 | non-innodb file copy | | ← UNLOCK TABLES xtrabackup finish
1のタイミングでクエリが8秒経過(10秒未満)していたとすると、1-1でFlush table with read lockがSET SESSION lock_wait_timeout=31536000で実行される。 しかし、このクエリがいつまでも終わらない場合はFlush table with read lockがwaiting flush tablesでずっと待たされてしまって、その他テーブルへの更新がブロックされてしまうので注意。
@keny_lala "2-1. クエリが終了していた場合はFlush table with read lockを実行" 単にSHOW FULL PROCESSLISTを見ているだけなので、その間に突っ込んできた新しいクエリーがthresholdに引っかかる可能性あるのですね
— yoku0825 (@yoku0825) 2017年4月11日
なるほど、こんな場合もあるのかー。
MySQLのsql_modeにあるORACLEとは
これはJPOUG Advent Calendar 2016の16日目の記事です。
最初に言っておきますが、この記事はなんも役に立ちません！
では始めます。
MySQLのsql_modeにはいろいろとsqlの動作の設定ができるんですが、
ここにORACLEというのが選択できます。
たぶんOracle databaseっぽく動作するんでしょうけど、設定したらどうなるか確認しました。
MySQLのバージョンは5.6.34です。
設定
mysql > select @@sql_mode; +------------------------+ | @@sql_mode | +------------------------+ | NO_ENGINE_SUBSTITUTION | +------------------------+ 1 row in set (0.00 sec) mysql> set global sql_mode=ORACLE; Query OK, 0 rows affected (0.00 sec)
sql_modeのデフォルトはNO_ENGINE_SUBSTITUTIONで
set globalでORACLEを設定します。で、sql_modeを確認すると、
mysql > select @@sql_mode; +----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------------------------------------------------------------+ | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
いっぱい表示されました。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード
マニュアルを見ると、いろんなsql_modeの組み合わせてOracle databaseの動作っぽくしてるみたいです。
他にもMSSQLやPOSTGRESQLなど選択できるようです。
動作の確認
sql_modeに追加された各モードの動作を確認してみます。
PIPES_AS_CONCAT
これは||を文字列結合に変更されます。従来ではORのシノニムです。
- sql_mode:デフォルト
mysql> select 0 || 1 ; +--------+ | 0 || 1 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
mysql> select 0 || 1 ; +--------+ | 0 || 1 | +--------+ | 01 | +--------+ 1 row in set (0.00 sec)
これはOracleっぽくなりますね。ちなみにMySQLはSELECT文にFROM DUALつけなくでも実行できます。
ANSI_QUOTES
二重引用符を使用してリテラル文字列を引用できなくなります。
- sql_mode:デフォルト
mysql> select "aaa" ; +-----+ | aaa | +-----+ | aaa | +-----+ 1 row in set (0.00 sec)
mysql> select "aaa" ; ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'
IGNORE_SPACE
関数名と 「(」 文字の間にスペースを許可します。
- sql_mode:デフォルト
mysql> select count (*) from ENGINES; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from ENGINES' at line 1
mysql> select count (*) from ENGINES; +-----------+ | count (*) | +-----------+ | 9 | +-----------+ 1 row in set (0.00 sec)
ORACLE
NO_KEY_OPTIONS
SHOW CREATE TABLE の出力で MySQL 固有のインデックスオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。
NO_TABLE_OPTIONS
SHOW CREATE TABLE の出力で MySQL 固有のテーブルオプション (ENGINE など) を出力しません。このモードはポータビリティモードで mysqldump によって使用されます。
NO_FIELD_OPTIONS
SHOW CREATE TABLE の出力に MySQL 固有のカラムオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。
この3つはまとめて紹介します。 以下のようなテーブルを作成します。
CREATE TABLE t1( id int primary key auto_increment, id2 varchar(10) character set 'utf8' collate 'utf8_bin' , id3 int, key id2_hash(id2) using hash, key id3_withcomment(id3) comment 'test' );
SHOW CREATE TABLEで出力の違いを確認します。
- sql_mode:デフォルト
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id2` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id2_hash` (`id2`) USING HASH,
KEY `id3_withcomment` (`id3`) COMMENT 'test'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`id2` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id2_hash` (`id2`),
KEY `id3_withcomment` (`id3`)
)
1 row in set (0.00 sec)
NO_KEY_OPTIONSでキー（セカンダリインデックス）に付属する情報の隠す。(USING HASHやCOMMENT 'test'のところ)
NO_TABLE_OPTIONSでMySQL特有のテーブル構文を隠す。(ENGINE=InnoDB DEFAULT CHARSET=latin1のところ)
NO_FIELD_OPTIONSでMySQL特有のカラム構文を隠す。(AUTO_INCREMENTのところ)
CHARACTER SET utf8 COLLATE utf8_binこれも特有じゃないのかなと思いつつも。
NO_AUTO_CREATE_USER
認証情報が指定される場合を除き、ほかの方法で実行される場合は、GRANT ステートメントで新規ユーザーを自動的に作成しません。ステートメントは IDENTIFIED BY を使用した空以外のパスワードを指定するか、IDENTIFIED WITH を使用した認証プラグインを指定する必要があります。
これはMySQL5.7からsql_modeのデフォルト値に含まれていて、
OFFであればGrant文でユーザが作成されますが、ONであるとユーザを先に作成していないエラーになります。
- sql_mode:デフォルト
mysql> GRANT SELECT ON *.* TO 'test'@'192.168.1.1'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON *.* TO 'test'@'192.168.1.1'; ERROR 1133 (42000): Can't find any matching row in the user table
結論はMySQLを使用するならsql_modeをORACLEにする理由はどこにもないです。
特にSHOW CREATE TABLEで大事な情報が隠れちゃうのはつらい。
明日は@discus_hamburgさんです！
確か去年も@discus_hamburgさんの前だった気がする。
Redis Cluster構築メモ
同一ホスト内にRedis Cluster構築してみたのでメモ。
version:Redis 3.2.6
インストール
動作確認のためなのでインストールの細かい設定は気にしない。適当に動作するように設定。
- ダウンロード
mkdir /redis wget http://download.redis.io/releases/redis-3.2.6.tar.gz tar zxvf redis-3.2.6.tar.gz make make install
- 起動スクリプト
Redisを複数起動するため起動スクリプト編集
# vim utils/redis_init_script REDISPORT=6379 → REDISPORT=$2
- 設定ファイル変更
6台構成で作成するため、6つ設定ファイル作成
vim /redis/redis-3.2.6/redis.conf 編集 daemonize no → yes port 6379 → PORTBANGOU protected-mode yes → no pidfile /var/run/redis_6379.pid → /var/run/redis_PORTBANGOU.pid dir ./ → /redis dbfilename dump.rdb dump_PORTBANGOU.rdb bind 127.0.0.1 → #bind 127.0.0.1 追加 cluster-enabled yes cluster-config-file nodes_PORTBANGOU.conf cluster-node-timeout 15000 cluster-require-full-coverage yes
- ポート番号7001 - 7006までの設定ファイル配置
# mkdir /etc/redis
# for ii in `seq 7001 7006`; do cat /redis/redis-3.2.6/redis.conf | sed -e "s/PORTBANGOU/${ii}/g" > /etc/redis/${ii}.conf ; done
# ls -ltr /etc/redis/
total 288
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7001.conf
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7002.conf
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7003.conf
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7004.conf
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7005.conf
-rw-r--r-- 1 root root 46823 Dec 8 02:03 7006.conf
- 起動
# for ii in `seq 7001 7006`; do /redis/redis-3.2.6/utils/redis_init_script start $ii; done [root@80efe0c08de2 redis-3.2.6]# ps aux | grep redis root 3727 0.0 0.0 40788 2620 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7001 [cluster] root 3732 0.0 0.0 40788 2624 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7002 [cluster] root 3737 0.0 0.0 40788 2620 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7003 [cluster] root 3742 0.0 0.0 40788 2624 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7004 [cluster] root 3747 0.0 0.0 40788 2620 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7005 [cluster] root 3752 0.0 0.0 40788 2620 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7006 [cluster]
6台構成でクラスター作成する
↓を作成する。
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- ポート番号7001,7003,7005をマスターとするクラスターを作成
# yum install rubygems -y # gem install redis # redis-trib.rb create 172.17.0.4:7001 172.17.0.4:7003 172.17.0.4:7005 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481164053943 2 connected 5461-10922 abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481164054945 3 connected 10923-16383
- 各マスターに対してスレーブ作成
# redis-trib.rb add-node --slave 172.17.0.4:7002 172.17.0.4:7001 # redis-trib.rb add-node --slave 172.17.0.4:7004 172.17.0.4:7003 # redis-trib.rb add-node --slave 172.17.0.4:7006 172.17.0.4:7005 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481164102029 1 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481164103031 2 connected 5461-10922 e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481164105034 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481164104033 3 connected 10923-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481164100229 3 connected
これでクラスター作成完了。
ノードを追加してみる
- 今こういう状態
| slot | master | slave | master_nodeid | slave_nodeid |
|---|---|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 | 1b6d03569f09e6009a25b941887ac1275759d172 | c17a5b65fb4ab1b802995ef8769edbe3290d7a03 |
| 5461-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 | 7d2e412e417f4d077bf50372f472f9103c480501 | e924857295ad43ccf9f2d792872e4d41293cab07 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 | abe6531b02045b6e16cf51849ba6f7ff22300aa5 | 89bc1da3bc8417e4513d33d7a175fc48764ec70a |
ここに172.17.0.4:7007 と 172:17.0.1:7008を追加してリシャードさせる
- 7007と7008のノードの設定ファイル作成して起動
# for ii in 7007 7008; do cat /redis/redis-3.2.6/redis.conf | sed -e "s/PORTBANGOU/${ii}/g" > /etc/redis/${ii}.conf ; done
# ls /etc/redis
7001.conf 7002.conf 7003.conf 7004.conf 7005.conf 7006.conf 7007.conf 7008.conf
# for ii in 7007 7008; do /redis/redis-3.2.6/utils/redis_init_script start $ii; done
# ps aux | grep edis
root 3727 9.4 0.0 40788 3612 ? Ssl 02:26 1:14 /usr/local/bin/redis-server *:7001 [cluster]
root 3732 0.0 0.0 40788 3252 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7002 [cluster]
root 3737 0.1 0.0 40788 3604 ? Ssl 02:26 0:01 /usr/local/bin/redis-server *:7003 [cluster]
root 3742 0.0 0.0 40788 3228 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7004 [cluster]
root 3747 0.1 0.0 40788 3608 ? Ssl 02:26 0:01 /usr/local/bin/redis-server *:7005 [cluster]
root 3752 0.0 0.0 40788 3212 ? Ssl 02:26 0:00 /usr/local/bin/redis-server *:7006 [cluster]
root 22474 0.0 0.0 36692 2560 ? Ssl 02:39 0:00 /usr/local/bin/redis-server *:7007 [cluster]
root 22479 0.0 0.0 36692 2568 ? Ssl 02:39 0:00 /usr/local/bin/redis-server *:7008 [cluster]
- クラスターに追加する
ここではまだハッシュスロットの割当はない
# redis-trib.rb add-node 172.17.0.4:7007 172.17.0.4:7001 # redis-trib.rb add-node --slave 172.17.0.4:7008 172.17.0.4:7007 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481164803460 1 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481164798952 2 connected 5461-10922 e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481164805463 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481164801455 3 connected 10923-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481164804460 3 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481164802958 0 connected 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481164802457 0 connected
- リシャード
- 16384のハッシュスロットに分かれてるので４シャードで割る。16384/4=4096
- 追加したマスター(port:7007)のノードIDを指定する
# redis-trib.rb reshard 172.17.0.4:7007 How many slots do you want to move (from 1 to 16384)? 4096 What is the receiving node ID? 0c55b1cba923d063cb7566b80f098c7dddea8f96 Please enter all the source node IDs. Type 'all' to use all the nodes as source nodes for the hash slots. Type 'done' once you entered all the source nodes IDs. Source node #1:all # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 1 connected 1365-5460 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481165063925 1 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481165061923 2 connected 6827-10922 e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481165061923 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481165060921 3 connected 12288-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481165062924 3 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481165058918 4 connected 0-1364 5461-6826 10923-12287 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481165058918 4 connected
| slot | master | slave |
|---|---|---|
| 1365-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 6827-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 |
| 12288-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
| 0-1364 5461-6826 10923-12287 | 172.17.0.4:7007 | 172.17.0.4:7008 |
完了
ノードを撤去してみる
- 今の状態
| slot | master | slave | master_nodeid | slave_nodeid |
|---|---|---|---|---|
| 1365-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 | 1b6d03569f09e6009a25b941887ac1275759d172 | c17a5b65fb4ab1b802995ef8769edbe3290d7a03 |
| 6827-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 | 7d2e412e417f4d077bf50372f472f9103c480501 | e924857295ad43ccf9f2d792872e4d41293cab07 |
| 12288-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 | abe6531b02045b6e16cf51849ba6f7ff22300aa5 | 89bc1da3bc8417e4513d33d7a175fc48764ec70a |
| 0-1364 5461-6826 10923-12287 | 172.17.0.4:7007 | 172.17.0.4:7008 | 0c55b1cba923d063cb7566b80f098c7dddea8f96 | 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 |
ここから172.17.0.4:7003と172.17.0.4:7004を取り除く
今のハッシュスロットの割合はノード単位で4096なので4096/3=1365,1365,1366でそれぞれのノードに割り振る
- 7001のノードに割当
# redis-trib.rb reshard --from 7d2e412e417f4d077bf50372f472f9103c480501 --to 1b6d03569f09e6009a25b941887ac1275759d172 --slots 1365 --yes 172.17.0.4:7001 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 5 connected 1365-5460 6827-8191 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481165235210 5 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481165233208 2 connected 8192-10922 e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481165236211 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481165236713 3 connected 12288-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481165238213 3 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481165237212 4 connected 0-1364 5461-6826 10923-12287 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481165234209 4 connected
- 7005のノードに割当
# redis-trib.rb reshard --from 7d2e412e417f4d077bf50372f472f9103c480501 --to abe6531b02045b6e16cf51849ba6f7ff22300aa5 --slots 1365 --yes 172.17.0.4:7005 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 5 connected 1365-5460 6827-8191 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481165279296 5 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481165282804 2 connected 9557-10922 e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481165283305 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481165278294 6 connected 8192-9556 12288-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481165282303 6 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481165284306 4 connected 0-1364 5461-6826 10923-12287 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481165281300 4 connected
- 7007のノードに割当
# redis-trib.rb reshard --from 7d2e412e417f4d077bf50372f472f9103c480501 --to 0c55b1cba923d063cb7566b80f098c7dddea8f96 --slots 1366 --yes 172.17.0.4:7007 # redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 5 connected 1365-5460 6827-8191 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481165327396 5 connected 7d2e412e417f4d077bf50372f472f9103c480501 172.17.0.4:7003 master - 0 1481165323386 2 connected e924857295ad43ccf9f2d792872e4d41293cab07 172.17.0.4:7004 slave 7d2e412e417f4d077bf50372f472f9103c480501 0 1481165324390 2 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481165322386 6 connected 8192-9556 12288-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481165325392 6 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481165321383 7 connected 0-1364 5461-6826 9557-12287 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481165326394 7 connected
| 元slot | reshard1回目slot | reshard2回目slot | reshard3回目slot | master | slave |
|---|---|---|---|---|---|
| 1365-5460 | 1365-5460 6827-8191 | 1365-5460 6827-8191 | 1365-5460 6827-8191 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 6827-10922 | 8192-10922 | 9557-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 | |
| 12288-16383 | 12288-16383 | 8192-9556 12288-16383 | 8192-9556 12288-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
| 0-1364 5461-6826 10923-12287 | 0-1364 5461-6826 10923-12287 | 0-1364 5461-6826 10923-12287 | 0-1364 5461-6826 9557-12287 | 172.17.0.4:7007 | 172.17.0.4:7008 |
7003と7004のノードへのハッシュスロットの割当がなくなった。
- ノードの削除
# redis-trib.rb del-node 172.17.0.4:7004 e924857295ad43ccf9f2d792872e4d41293cab07 # redis-trib.rb del-node 172.17.0.4:7003 7d2e412e417f4d077bf50372f472f9103c480501
# redis-cli -p 7001 cluster nodes | sort -k2 1b6d03569f09e6009a25b941887ac1275759d172 172.17.0.4:7001 myself,master - 0 0 5 connected 1365-5460 6827-8191 c17a5b65fb4ab1b802995ef8769edbe3290d7a03 172.17.0.4:7002 slave 1b6d03569f09e6009a25b941887ac1275759d172 0 1481165531737 5 connected abe6531b02045b6e16cf51849ba6f7ff22300aa5 172.17.0.4:7005 master - 0 1481165530735 6 connected 8192-9556 12288-16383 89bc1da3bc8417e4513d33d7a175fc48764ec70a 172.17.0.4:7006 slave abe6531b02045b6e16cf51849ba6f7ff22300aa5 0 1481165529732 6 connected 0c55b1cba923d063cb7566b80f098c7dddea8f96 172.17.0.4:7007 master - 0 1481165532739 7 connected 0-1364 5461-6826 9557-12287 3ed3899c6ba3ca1371235f91cd8502297c3a46b9 172.17.0.4:7008 slave 0c55b1cba923d063cb7566b80f098c7dddea8f96 0 1481165533739 7 connected
| slot | master | slave |
|---|---|---|
| 1365-5460 6827-8191 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 8192-9556 12288-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
| 0-1364 5461-6826 9557-12287 | 172.17.0.4:7007 | 172.17.0.4:7008 |
ノードを移動してみる
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
↓こうしたい
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7007 | 172.17.0.4:7008 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- 事前準備
先程作成したクラスター構成を作り直す
ノード停止 # for ii in `seq 7001 7008`; do /redis/redis-3.2.6/utils/redis_init_script stop $ii; done rdbファイルとクラスター構成ファイルを削除する # ls /redis/ dump_7001.rdb dump_7002.rdb dump_7003.rdb dump_7004.rdb dump_7005.rdb dump_7006.rdb dump_7007.rdb dump_7008.rdb nodes_7001.conf nodes_7002.conf nodes_7003.conf nodes_7004.conf nodes_7005.conf nodes_7006.conf nodes_7007.conf nodes_7008.conf redis-3.2.6 redis-3.2.6.tar.gz # rm -f /redis/*.rdb /redis/nodes*.conf
6台構成でクラスター作成するに戻って再作成
こういう状態にする。ポート7007と7008のノードはクラスターに参加していない状態。
# redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166155471 1 connected 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 172.17.0.4:7003 master - 0 1481166157474 2 connected 5461-10922 77d3ce18b67647392b753bbbf1148870b3581d42 172.17.0.4:7004 slave 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 0 1481166156472 2 connected c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166152466 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166156973 3 connected # ps aux | grep redis root 22562 0.0 0.0 38740 3092 ? Rsl 03:01 0:00 /usr/local/bin/redis-server *:7001 [cluster] root 22565 0.0 0.0 36692 2840 ? Ssl 03:01 0:00 /usr/local/bin/redis-server *:7002 [cluster] root 22572 0.0 0.0 38740 3036 ? Ssl 03:01 0:00 /usr/local/bin/redis-server *:7003 [cluster] root 22576 0.0 0.0 36692 2828 ? Ssl 03:01 0:00 /usr/local/bin/redis-server *:7004 [cluster] root 22582 0.0 0.0 38740 3056 ? Ssl 03:01 0:00 /usr/local/bin/redis-server *:7005 [cluster] root 22585 0.0 0.0 36692 2816 ? Ssl 03:01 0:00 /usr/local/bin/redis-server *:7006 [cluster] root 22604 0.0 0.0 36692 2564 ? Ssl 03:02 0:00 /usr/local/bin/redis-server *:7007 [cluster] root 22607 0.0 0.0 36692 2564 ? Ssl 03:02 0:00 /usr/local/bin/redis-server *:7008 [cluster] root 22615 0.0 0.0 6440 696 ? S+ 03:03 0:00 grep redis
- 7007のノードを7003のノードのスレーブにする
# redis-trib.rb add-node --slave 172.17.0.4:7007 172.17.0.4:7003 # redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166293708 1 connected 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 172.17.0.4:7003 master - 0 1481166294710 2 connected 5461-10922 77d3ce18b67647392b753bbbf1148870b3581d42 172.17.0.4:7004 slave 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 0 1481166290702 2 connected c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166291704 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166290201 3 connected eaa0636eaa96d62b20a6440d132ec57addf07109 172.17.0.4:7007 slave 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 0 1481166289700 2 connected 追加された
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7003 | 172.17.0.4:7004 |
| 172.17.0.4:7007 | ||
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- 7004のノードを抜く
# redis-trib.rb del-node 127.0.0.1:7004 77d3ce18b67647392b753bbbf1148870b3581d42 # redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166368835 1 connected 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 172.17.0.4:7003 master - 0 1481166366834 2 connected 5461-10922 c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166365831 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166369838 3 connected eaa0636eaa96d62b20a6440d132ec57addf07109 172.17.0.4:7007 slave 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 0 1481166370839 2 connected
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7003 | 172.17.0.4:7007 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- 7003のマスターノード - 7007のスレーブノードをフェールオーバする スレーブ側で実施する
# redis-cli -p 7007 cluster failover # redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166471012 1 connected 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 172.17.0.4:7003 slave eaa0636eaa96d62b20a6440d132ec57addf07109 0 1481166467004 4 connected c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166470010 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166469008 3 connected eaa0636eaa96d62b20a6440d132ec57addf07109 172.17.0.4:7007 master - 0 1481166468006 4 connected 5461-10922
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7007 | 172.17.0.4:7003 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- 7008のノードを7007のノードのスレーブにする
# redis-trib.rb add-node --slave 172.17.0.4:7008 172.17.0.4:7007 # redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166527103 1 connected 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 172.17.0.4:7003 slave eaa0636eaa96d62b20a6440d132ec57addf07109 0 1481166528104 4 connected c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166526602 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166524598 3 connected eaa0636eaa96d62b20a6440d132ec57addf07109 172.17.0.4:7007 master - 0 1481166522094 4 connected 5461-10922 13a4814123df21a1ead7616f2e801e2d47749396 172.17.0.4:7008 slave eaa0636eaa96d62b20a6440d132ec57addf07109 0 1481166529106 4 connected
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7007 | 172.17.0.4:7003 |
| 172.17.0.4:7008 | ||
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
- 7003のノードを抜く。
# redis-trib.rb del-node 127.0.0.1:7003 269d5a679b3bc24c47ecd0fc5cea911fbcc329b2 # redis-cli -p 7001 cluster nodes | sort -k2 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 172.17.0.4:7001 myself,master - 0 0 1 connected 0-5460 f7b36ed09baeae6d65004e066f11c1b0552cffe7 172.17.0.4:7002 slave 550a91f2f1a88e5630ee8e6c3d1d61970715de2b 0 1481166593220 1 connected c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 172.17.0.4:7005 master - 0 1481166587711 3 connected 10923-16383 40d5b8e1241560b53d114b4e363935de66fe5646 172.17.0.4:7006 slave c2fa1ae4060ad07ca30ea0c762e1e32d6e480360 0 1481166592219 3 connected eaa0636eaa96d62b20a6440d132ec57addf07109 172.17.0.4:7007 master - 0 1481166593721 4 connected 5461-10922 13a4814123df21a1ead7616f2e801e2d47749396 172.17.0.4:7008 slave eaa0636eaa96d62b20a6440d132ec57addf07109 0 1481166594223 4 connected
| slot | master | slave |
|---|---|---|
| 0-5460 | 172.17.0.4:7001 | 172.17.0.4:7002 |
| 5461-10922 | 172.17.0.4:7007 | 172.17.0.4:7008 |
| 10923-16383 | 172.17.0.4:7005 | 172.17.0.4:7006 |
MySQLのInnoDBセカンダリインデックスのロック
MySQLのセカンダリインデックスのロックで嵌ったのでメモ。
isolationがREPEATABLE-READのときギャップロックやらネクストキーロックはセカンダリーインデックスの場合どうなるかよくわかってなかった。
例えば、こんなテーブル作ってcol2にインデックスを作成する。で、インデックスのロックを見る。
CREATE TABLE `t1` ( `col_pk` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(10) DEFAULT NULL, PRIMARY KEY (`col_pk`), KEY `col2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
データはこんな。PKが3は空いている状態。
insert into t1(col_pk,col2,col3) values (1,10,'AAA'); insert into t1(col_pk,col2,col3) values (2,20,'AAA'); insert into t1(col_pk,col2,col3) values (4,40,'AAA'); insert into t1(col_pk,col2,col3) values (5,50,'AAA'); insert into t1(col_pk,col2,col3) values (6,60,'AAA'); +--------+------+------+ | col_pk | col2 | col3 | +--------+------+------+ | 1 | 10 | AAA | | 2 | 20 | AAA | | 4 | 40 | AAA | | 5 | 50 | AAA | | 6 | 60 | AAA | +--------+------+------+
これで、
UPDATE t1 SET col3='ZZZ' WHERE col2=50;
した場合どの範囲でロックが取得されるか。
InnoDB Lock Monitorで確認する。
---TRANSACTION 14903, ACTIVE 3 sec 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1 MySQL thread id 71, OS thread handle 0x7fcab3430700, query id 957 localhost root cleaning up TABLE LOCK table `oo`.`t1` trx id 14903 lock mode IX ★１ RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000032; asc 2;; 1: len 4; hex 80000005; asc ;; ★２ RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000003a37; asc :7;; 2: len 7; hex 55000001cc0312; asc U ;; 3: len 4; hex 80000032; asc 2;; 4: len 3; hex 5a5a5a; asc ZZZ;; ★３ RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000003c; asc <;; 1: len 4; hex 80000006; asc ;;
３つの種類のRECORD LOCKSが確認できる。まずその内容の理解。
まず★１
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000032; asc 2;; 1: len 4; hex 80000005; asc ;;
index `col2`がcol2のインデックス
lock_mode Xがネクストキーロック
0: len 4; hex 80000032; asc 2;;がcol2の値の16進数(16進数で32は10進数で50)
よって、col2インデックスの値50に対してネクストキーロック取得。
続いて★２
RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `oo`.`t1` trx id 14903 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000003a37; asc :7;; 2: len 7; hex 55000001cc0312; asc U ;; 3: len 4; hex 80000032; asc 2;; 4: len 3; hex 5a5a5a; asc ZZZ;;
index `PRIMARY` がプライマリーキー
lock_mode X locks rec but not gap がレコードロック
0: len 4; hex 80000005; asc ;;がプライマリーキーの値の16進数(16進数で5は10進数で5)
よって、プライマリーキーの値5に対してレコードロック取得。
最後★３
RECORD LOCKS space id 68 page no 4 n bits 72 index `col2` of table `oo`.`t1` trx id 14903 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000003c; asc <;; 1: len 4; hex 80000006; asc ;;
index `col2`がcol2のインデックス
lock_mode X locks gap before recがギャップロック
0: len 4; hex 8000003c; asc <;;がcol2の値の16進数(16進数で3cは10進数で60)
よって、col2インデックスの値60までギャップロックを取得。
これをまとめると、
col2 40 ------------------------- 50 --------------------------- 60
| | |
値50のネクストキーロック 値60ギャップロック
primary key 5にレコードロック
col2への挿入は問答無用で41～59まではロックされるのはわかっていたが、40と60でも挿入できないパターンがあった。
別トランザクションを起動して歯抜けだったPKの3と最大値の7でそれぞれ実行できるか待機するか確認。
| SQL | col2の値 | 結果 |
|---|---|---|
| insert into t1(col_pk,col2,col3) values (7,39,'AAA'); | 39 | OK |
| insert into t1(col_pk,col2,col3) values (7,40,'AAA'); | 40 | 待機 |
| insert into t1(col_pk,col2,col3) values (7,41,'AAA'); | 41 | 待機 |
| insert into t1(col_pk,col2,col3) values (7,59,'AAA'); | 59 | 待機 |
| insert into t1(col_pk,col2,col3) values (7,60,'AAA'); | 60 | OK |
| insert into t1(col_pk,col2,col3) values (3,40,'AAA'); | 40 | OK |
| insert into t1(col_pk,col2,col3) values (3,41,'AAA'); | 41 | 待機 |
| insert into t1(col_pk,col2,col3) values (3,59,'AAA'); | 59 | 待機 |
| insert into t1(col_pk,col2,col3) values (3,60,'AAA'); | 60 | 待機 |
| insert into t1(col_pk,col2,col3) values (3,61,'AAA'); | 61 | OK |
PKの3に対してはcol2の41～60が待機
PKの7に対してはcol2の40～59が待機
という結果に。41～59だけだと思ってたのでここで嵌った。
これは、セカンダリーインデックスはリーフノードにプライマリーキーを持つのでロックの範囲がそのプライマリーキーも意識しないとだめなようだった。
値50のネクストキーロック 値60ギャップロック
col2 40 ------------------------- 50 --------------------------- 60
| | |
col_pk 4 ------------------------- 5 ----------------------------- 6
↑ ↑
(7,40,'AAA')待機 (3,60,'AAA')待機
↑ ↑
(3,40,'AAA')OK (7,60,'AAA')OK
こうなっているわけだから、
(7,40,'AAA')はcol_pk:4より大きいため待機するが(3,40,'AAA')はcol_pk:4より小さいので挿入可能。
(3,60,'AAA')はcol_pk:6より小さいため待機するが(7,60,'AAA')はcol_pk:6より大きいので挿入可能。
参考
ギャップロックやらネクストキーロックはこちらの記事で大変理解できました。
InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる
