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には効かなくてシリアルでコピーする。
- 最初にInnodb log fileのコピーをシリアル実行で開始する。
- Innodb log fileのコピーが最新の更新に追いついたら、ibd fileのコピーをパラレル実行で開始する。（Innodb log fileのコピーと並列に）
- 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使おうとするときすぐ使えるようにするメモ。
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 インストール
参考
# 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さんの前だった気がする。
