kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

最近知った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 - 33 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

MySQLyumでインストールする

# 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

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でずっと待たされてしまって、その他テーブルへの更新がブロックされてしまうので注意。

なるほど、こんな場合もあるのかー。