IT系のめもを蓄積していこうかと
MySQL/MariaDBで、binlog_formatというのが、
とありますが、
このうち、mixedを選択していた場合、
statementかrowがクエリーのタイプによって、
決定されると書いてあったのだが、
よく見ると、tx_isolationが
『InnoDB テーブルを使用中で、トランザクション分離レベルが READ COMMITTED または READ UNCOMMITTED の場合、行ベースのロギングのみを使用することができます。ロギング形式を STATEMENT に変更することは可能ですが、InnoDB は挿入を実行できないため、実行時にこれを行うと、非常に速くエラーが発生します。』
という記述が公式にあった。
rowだと、バイナリログの出力サイズが大きいから、
mixedにして削減しようと思ったが、
そもそもREAD COMMITEDで運用していたから、意味なかったという話。。
前回も書いたが、
maxsclaleとphpのmysqlndでまた問題があったので、
記載しておく。
たぶんまた、
PDO::setAttribute( ATTR_EMULATE_PREPARES, false )
のせいなのだろうが。。
その１ maxscaleのコネクションプールが使えなくなる
nativeのエミュレートを有効にした状態で、
maxscaleのコネクションプールを使うと以下のようなエラーが頻発する。
Wrong COM_STMT_PREPARE response size. Received 7 with query:
これは、mysqlnd側が出力しているエラーなのだが、
どうもレスポンス（応答）のサイズが期待したものと異なるからのようある。
その２ 高負荷でreadwriteが使えなくなる
maxscaleのreadwrite splitを使っている状態で、
高負荷になると以下のエラーが頻発する。
SQLSTATE[HY000]: General error: 2003 Lost connection to backend server. with query:
どうも、slaveを見失ってしまうことが多く、結果として、
master側への接続もエラーになるというもの。
使っていたバージョンは、
maxscale2.0.4なのであるが、
意外に高負荷に現状耐えることが出来てないのかな、、と思う。
とはいえ、すぐにbugfixされるので、継続的に見守っていきたい。
phpのmysqlndというライブラリは素晴らしいものである。
しかし、その機能に悩まされたので、記載しておく。
そもそもmysqlndは、
などの機能がある。
1と2については、
PDO::setAttribute( ATTR_EMULATE_PREPARES, false )
により実現できる。
しかし、3については、これを設定すると機能しなくなる（らしい、未検証）。
さらに上記を設定した場合、
raw_queryでさえも、prepared statementとして扱われてしまう。
一見問題なさそうなのですが、
maxsacleのreadwrite splitをかましていると、
prepared statementがmasterに振られてしまうため、
slave参照が行われなくなってしまう。
じゃあ、mysqlnd_msでやればいいじゃんって話だが、
先ほど書いたように機能しなくなってしまう（らしい、未検証）。
というちょっと困る事象。。
maxslaceがprepared statemtでもslaveに振ってくれればいいのだが。。
最近MariaDBばっかりです。
実施したバージョンは、10.1.20です。
●スレッドプール
MySQL（Standard）にはなくて、MariaDBに備わっている目玉機能は、
スレッドプールでしょう（thread_handling=pool-of-threads）。
スレッドプールというのは、クライアントからの接続に対して、
プール済みのスレッドから割り当てるものである。
実はよく似て、非なるパラメータがある。
それは、thread_cache_sizeである。
スレッドプールとthread_cache_sizeは説明などをよく読むと、
「生成済みのスレッドを使い回し、スレッド生成コストを抑える」
みたいなことが書いてある。
確かにその通りなのだが、内部的な動きに大きな違いがある。
筆者はMariaDBとMySQLの専門家ではないので、
以下間違った解釈かもしれません。なので、以下を呼んでも鵜呑みにしないでいただきたい。
個人的な解釈だが、スレッドプールとスレッドキャッシュの違いは、
スレッドのコンテキストスイッチまで踏み込んでいるかどうかだと考えている。
javaをやる人ならおなじみにコンテキストスイッチというのは、
大量スレッドが生まれた時の、スレッドの切り替えのことであり、
これが意外にコストが重い。
スレッドプールは、コンテキストスイッチを抑える機能でもあり、
スレッドキャッシュは、スレッドの生成抑制のみで、コンテキストスイッチを抑える働きはないと思う。
たとえば、スレッドプールを無効化した状態で、スレッドキャッシュサイズを100に設定したとする。
このとき、200の接続がきたら、100はキャッシュされ、100は新規作成＆終わったら破棄される。
また次の瞬間200の接続が来たら、100はキャッシュから取り出され、100は新規作成＆終わったら破棄される。
このように、スレッドキャッシュは、同時実行スレッドの抑制まではしない。
それに対し、スレッドプールの場合、スレッドプールサイズを100に設定したとする。
このとき、200の接続がきたら、100はスレッドプールが割り当てられ、残りの100は待機状態になる。
しかし、プールされた100スレッドは、トランザクションの重さによって、徐々に重いグループに移動され、軽いグループの空きを作って、そこに残りの100接続が徐々に割り当てる。
このように、同時実行をある程度制御することで、コンテキストスイッチを抑え、持続的なパフォーマンス維持に努めるのがスレッドプールの特徴である。
つまり、スレッドプールをやったからといって、必ずしもパフォーマンスが向上するわけではない。
スレッドプールの最大の特徴は、パフォーマンスの維持であると考えている。
そのため、大量のOLTPには向いているが、バッチ処理などには向いていないなど、得手不得手もある。
●並列レプリケーション
MariaDBにはMySQLとは異なる形で、並列レプリケーション機能を提供している。
MySQLの並列レプリケーションは、「異なるスキーマ」に対してのみ機能する。
それに対し、MariaDBの並列レプリケーションは、「異なるスキーマ」「コミットグループ」に対して、機能する。
具体的には以下2つである。
詳しくは本家の説明ページを参照されたい。
https://mariadb.com/kb/en/mariadb/parallel-replication/
諸々書いてきたが、
MySQL5.7とMariaDB10.1ではほぼ同等（世間的にはMySQL5.7の上かな？）といったところと思います。
ただ、MariaDBのレプリケーションは、結構素晴らしいと思っています。
並列レプリケーション以外にも、GTIDがデフォルトで使えることによる扱いやすさもあるので、
個人的には国内でもMariaDBがもっと流行るといいなと願っています。
ずいぶん時間が空いてしまったが、
MariaDBのHAで続きが出来たので、
記載します。
前回、負荷試験前でありましたが、
諸々実施後、以下の問題点が出てきました。
秒間300コミット以上の場合は、
slave側で、innodb_flush_log_at_trx_commitを2にしないと追いつけなくなる。
これで秒間600コミットくらいまで耐えれるが、それ以上である場合、0にしないとダメであった。
同時に、Semiレプリケーションが外れて、通常レプリケーションとなると、
レプリケーションによるfailoverでのロスト率が高まってしまうため、
rpl_semi_sync_master_timeoutを1500ミリ秒に設定した。
同時に、slaveのデータ保全性を高めるため、
rpl_semi_sync_master_wait_pointにAFTER_SYNCを設定した（いわゆるロスレスレプリケーション）。
さらに、slaveの更新性能をアップするため、
というMariaDB特有のパラメータを設定した。
slave_parallel_threadsの特徴は、MariaDBではグループコミットという機能があり、
MySQLの並列レプリケーションよりも細かい粒度で書き込みをまとめることが出来る。
そんなこんなで出来上がった設定は以下の通り。
ちなみに、16CORE/104GBの仮想マシンです。
########## # mysqld ########## [mysqld] # -------------------------------------------------- # base # -------------------------------------------------- user=mysql bind-address=0.0.0.0 port=3306 pid-file=/var/run/mysqld/mysqld.pid datadir=/var/lib/mysql socket =/var/lib/mysql/mysql.sock symbolic-links=0 sql_mode=TRADITIONAL default-storage-engine=InnoDB transaction-isolation=READ-COMMITTED character-set-server=utf8mb4 collation-server=utf8mb4_general_ci skip-character-set-client-handshake=0 innodb_buffer_pool_load_at_startup=1 innodb_buffer_pool_dump_at_shutdown=1 innodb-defragment=1 innodb_fast_shutdown=0 thread_pool_max_threads=1000 thread_handling=pool-of-threads extra_port=3307 extra_max_connections=10 # -------------------------------------------------- # replication # -------------------------------------------------- server-id=1 binlog_format=row log-bin=mysql-bin max_binlog_size=128M sync_binlog=1 expire_logs_days=2 innodb_flush_log_at_trx_commit=1 innodb_autoinc_lock_mode=2 plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1500 rpl_semi_sync_slave_enabled=1 rpl_semi_sync_master_wait_point=AFTER_SYNC log-slave-updates relay_log_recovery=1 slave_parallel_threads=8 # -------------------------------------------------- # network # -------------------------------------------------- max_connections=7200 max_connect_errors=999999999 connect_timeout=10 max_allowed_packet=10M back_log=1024 # -------------------------------------------------- # logging # -------------------------------------------------- log_output=FILE log_warnings=1 log_error=/var/log/mysql/error.log slow_query_log=1 long_query_time=0.5 slow_query_log_file=/var/log/mysql/slow.log innodb_file_per_table=1 innodb_log_buffer_size=16M # -------------------------------------------------- # cache, memory # -------------------------------------------------- query_cache_size=0 max_heap_table_size=32M tmp_table_size=32M thread_cache_size=2400 innodb_buffer_pool_size=72G innodb_flush_neighbors=0 innodb_read_ahead_threshold=0 innodb_log_file_size=4G innodb_buffer_pool_instances=16 innodb_lru_scan_depth=2048 # -------------------------------------------------- # IO # -------------------------------------------------- innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_io_capacity=5000 innodb_io_capacity_max=20000 innodb_open_files=2048 innodb_purge_threads=4 innodb_sync_array_size=32 # -------------------------------------------------- # query # -------------------------------------------------- sort_buffer_size=4M read_rnd_buffer_size=2M read_buffer_size=512K join_buffer_size=512K ########## # mysqldump ########## [mysqldump] default-character-set=utf8mb4 max_allowed_packet=1G ########## # mysql ########## [mysql] default-character-set=utf8mb4 ########## # mysqld-safe ########## [mysqld_safe] log-error=/var/log/mysql/error.log
あとは、maxscaleで各種イベントを拾った時、
slave側には、以下のクエリーを投げてやることにした。
set global read_only = 1; set global innodb_flush_log_at_trx_commit = 0;
これで、slave側も秒間1000コミット近く耐えらる。
MariaDBを3台構成で、
準同期レプリケーション＆スレーブ自動昇格
のクラスターを組んだので、備忘録として記載しておく。
[ソフトウェア]
[インフラ]
[処理概要]
webに配備されたMaxscaleはReadWriteSplitterで起動し、
127.0.0.1:4000のアクセスを、dbに振り分ける。
同時に、Maxscaleのmonitor機能により、
masterがフェイルオーバした際は、
MRMがコールされ、スレーブの自動昇格を実現する。
[注意したこと]
執筆時点でMRMの0.7rc3はリリースされていたが、どうも不具合があり、0.6.4で対応した。
ただし、issueでも上がっていたので、近日中には修正されると期待している。
MaxscaleからMRMを呼び出すのだが、
masterダウンイベントが走った際、3つのMRMが同時に検知してフェイルオーバ処理を実施した場合、
昇格の一貫性を保証するかわからなかったので、
masterダウンイベントを受け取った、webノード3台が相互に生き死にを確認し、もっとも優先順位が高いノードが、
フェイルオーバ処理を実行するようにした。
上記により、現状手動によるフェイルオーバ検知はすべて正常に行わるが、
MRMを呼び出す部分においてラップしているrubyスクリプトが、
ゾンビプロセスとなる不具合がある。
これについては、現状理由がよくわかっていないが、
Maxscaleを再起動すればなくなるので、一旦放置とした。
参考に、各設定を残しておく。
[maxscale.conf]
# Global parameters [maxscale] threads=2 # Server definitions [s-db01] type=server address=XXX.XXX.XXX.1 port=3306 protocol=MySQLBackend [s-db02] type=server address=XXX.XXX.XXX.2 port=3306 protocol=MySQLBackend [s-db03] type=server address=XXX.XXX.XXX.3 port=3306 protocol=MySQLBackend # Monitor for the servers [m-db] type=monitor module=mysqlmon servers=s-db01,s-db02,s-db03 user=XXX passwd=XXX monitor_interval=3000 script=/usr/local/bin/mrm.rb -u XXX:XXX -r XXX:XXX -h $INITIATOR,$NODELIST -e $EVENT -m m-db --live_nodes $NODELIST events=master_down,master_up,slave_down,slave_up,new_master,new_slave # Service definitions [rw-db] type=service router=readwritesplit servers=s-db01,s-db02,s-db03 user=XXX passwd=XXX max_slave_connections=100% localhost_match_wildcard_host=1 [maxadmin service] type=service router=cli # Listener definitions for the services [l-db] type=listener service=rw-db protocol=MySQLClient port=4000 [maxadmin listener] type=listener service=maxadmin service protocol=maxscaled port=6603
[mrm.rb]
#!/usr/bin/ruby
# -*- encodinfg: utf-8 -*-
#
# maxscaleからfailoverを実行させるためのスクリプト
#
# 引数解析ライブラリ読み込み
require 'optparse'
##############################
# 初期化
admin_user = nil
replication_user = nil
target_hosts = nil
event_name = nil
monitor_name = nil
live_nodes = nil
# 引数解析
OptionParser.new do |opt|
# 管理ユーザ
opt.on('-u VALUE', 'admin user and password') do |v|
admin_user = v
logger.info('admin_user => %s' % v)
end
# レプリケーションユーザ
opt.on('-r VALUE', 'replication user and password') do |v|
replication_user = v
logger.info('replication_user => %s' % v)
end
# 対象ホスト
opt.on('-h VALUE', 'target hosts') do |v|
target_hosts = v
logger.info('target_hosts => %s' % v)
end
# 監視名
opt.on('-m VALUE', 'monitor name') do |v|
monitor_name = v
logger.info('monitor_name => %s' % v)
end
# イベント名称
opt.on('-e VALUE', 'event name') do |v|
event_name = v
logger.info('event_name => %s' % v)
end
# 稼働中ノード
opt.on('--live_nodes [VALUE]', 'live nodes (optional)') do |v|
live_nodes = v
logger.info('live_nodes => %s' % v)
end
# 解析
opt.parse!(ARGV)
end
# チェック
if !admin_user || !replication_user || !target_hosts || !event_name || !monitor_name
# 終了
exit
end
# クラスターを取得
got_result_ip_list = []
cluster_ip_list = getClusterInternalIpList('web')
cluster_ip_list.each do |cluster_ip|
# maxadminの状況確認
command = '/usr/bin/timeout 3 /usr/bin/maxadmin -pmariadb -h %s list monitors | grep -F "%s" | grep -F "Running"' % [cluster_ip, monitor_name]
result = exec(command)
# 結果を取得できたIPを取得
if result.length > 0
got_result_ip_list.push(cluster_ip)
end
end
# 結果を取得できたIPの先頭が自分のIPなら実行する
self_internal_ip = getSelfInternalIp()
if self_internal_ip != got_result_ip_list.shift
# 終了
exit
end
# eventチェック
command = nil
result = nil
if event_name == 'master_down'
# replication-manager実行
raw_command = <<EOF
/usr/local/bin/replication-manager\
--user=%s\
--rpluser=%s\
--hosts=%s\
--failover=force\
--interactive=false\
--gtidcheck=true\
2>&1 | cat
EOF
command = raw_command % [admin_user, replication_user, target_hosts]
result = exec(command)
end
# メール通知など
[my.cnf]
※3台とも同じ設定で、マシンスペックは4CORE/26GBの仮想マシンで、更新系に重点を置いている
########## # mysqld ########## [mysqld] # -------------------------------------------------- # base # -------------------------------------------------- user=mysql bind-address=0.0.0.0 port=3306 pid-file=/var/run/mysqld/mysqld.pid datadir=/var/lib/mysql socket =/var/lib/mysql/mysql.sock symbolic-links=0 sql_mode=TRADITIONAL default-storage-engine=InnoDB transaction-isolation=READ-COMMITTED character-set-server=utf8mb4 collation-server=utf8mb4_general_ci skip-character-set-client-handshake=0 innodb_buffer_pool_load_at_startup=1 innodb_buffer_pool_dump_at_shutdown=1 thread_handling=pool-of-threads # -------------------------------------------------- # replication # -------------------------------------------------- server-id=1 binlog_format=row log-bin=mysql-bin max_binlog_size=128M sync_binlog=1 expire_logs_days=3 innodb_flush_log_at_trx_commit=1 innodb_autoinc_lock_mode=2 plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 rpl_semi_sync_slave_enabled=1 # -------------------------------------------------- # network # -------------------------------------------------- max_connections=1500 max_connect_errors=999999999 connect_timeout=10 max_allowed_packet=1G # -------------------------------------------------- # logging # -------------------------------------------------- log_output=FILE log_warnings=1 log_error=/var/log/mysql/error.log slow_query_log=1 long_query_time=0.5 slow_query_log_file=/var/log/mysql/slow.log innodb_file_per_table=1 innodb_log_buffer_size=16M # -------------------------------------------------- # cache, memory # -------------------------------------------------- query_cache_size=0 max_heap_table_size=32M tmp_table_size=32M thread_cache_size=500 innodb_buffer_pool_size=20G innodb_flush_neighbors=0 innodb_log_file_size=!G # -------------------------------------------------- # query # -------------------------------------------------- sort_buffer_size=512K read_rnd_buffer_size=512K read_buffer_size=512K join_buffer_size=512K ########## # mysqldump ########## [mysqldump] default-character-set=utf8mb4 max_allowed_packet=1G ########## # mysql ########## [mysql] default-character-set=utf8mb4 ########## # mysqld-safe ########## [mysqld_safe] log-error=/var/log/mysql/error.log
今後、高負荷状況において、テストを行うので、
修正があれば別途記載しようと思う。
以上
muninでMariaDB10.0.4を監視してたら、
情報がほとんどあつまっていなかった。
munin自体はepelからインストールしたのだが、
これに内包されている「mysql_」プラグインはMySQL5.1レベルのものまでしかサポートしていないため、
以下にあるファイルで上書きする必要がある。
このファイルで上書き、
1行目の
#!@@PERL@@
を
#!/usr/bin/perl
に直したら、MariaDB10.0.4でも問題なく動いた。
以上
以前の記事で、
master、slave構成において、
があることが抜けていた。
今回のように、maxscaleでsalve auto promotionを使う場合、
データロスト率が低い準同期方式がよいと思われる。
準同期レプリケーションについては、詳細説明をされているホームページがあるので、
それを参照されたい。
準同期レプリケーションの設定は以下。
[mysqld] plugin-load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 rpl_semi_sync_slave_enabled=1
これで、プラグインのロードと、
準同期レプリケーションの設定が入る。
なお、ここでは、masterがslaveに、slaveがmasterになることがあるので、
master・slave問わず同じ設定を入れている。
ここで準同期レプリケーションの課題を考えてみると、以下のようなものがありそうだ。
非同期と混在させることが出来なかったような気がするので、
遠隔地にバックアップをおくこともできないと思われる。
これらについて、検証したら詳細を記述したい。
前回、
Slave auto promotionについて記載するといったが、
詳細はさておき、先に結論を書いておく。
【前提】
【結論】
ということが挙げられる。
つまり、3ノードあっても、2ノード死んだら、実質アウトなのである。
そのため、打っておく手段としては、
ということをやっておけば完璧だと思われる。
ちなみに、良い点として、masterがダウン→復旧しても、新masterとバイナリログポジションがずれているため、
maxsacle上のクラスターからは外され、マルチマスター状態にはならない。（単独のマスターとして存在する）
幸い、maxscaleは結構イベントが拾えるので、
上記対策もなんとか実現できそうな気がしている。
そのあたりの検証記事を、今後書こうと思います。
以上
MariaDBでクラスターを組む際、
を使うとよい。
そもそも、MariaDBのクラスターには、
のような3つの手段があるが（もっとあるかも。。調べてません。）、
GaleraとSlave auto promotionであれば、maxslace & repliacation-managerの監視および発砲でいける。
すいません、Spliderは記載しておきながら、大してわからないので、
GaleraとSlave auto promotionの特徴を書いてみる。
【Galera】
（メリット）
（デメリット）
【Slave auto promotion】
（メリット）
（デメリット）
上記のような特徴があるので、
使いどころはサービスに依存するのかもしれない。
私の見解では、
・同一データを更新することがない場合 → Galera
・同一データを更新することが多い場合 → Slave auto promotion
がよいと思う。
決め手は、更新速度とロックの有無かと。
※Spiderは分散データなので、Slave auto promotionと組み合わせないとデータロストしちゃうかも。。
Galeraは結構やっている人がいるから、
Slave auto promotionについて、
maxscaleとreplication-managerを用いた構成の説明（やってみた）を近々で書こうと思う。
以上