カテゴリー「mysql」

前回も書いたが、

maxsclaleとphpのmysqlndでまた問題があったので、

記載しておく。

 

たぶんまた、


PDO::setAttribute( ATTR_EMULATE_PREPARES, false )

のせいなのだろうが。。

 

その1 maxscaleのコネクションプールが使えなくなる

nativeのエミュレートを有効にした状態で、

maxscaleのコネクションプールを使うと以下のようなエラーが頻発する。


Wrong COM_STMT_PREPARE response size. Received 7 with query:

これは、mysqlnd側が出力しているエラーなのだが、

どうもレスポンス(応答)のサイズが期待したものと異なるからのようある。

 

その2 高負荷でreadwriteが使えなくなる

maxscaleのreadwrite splitを使っている状態で、

高負荷になると以下のエラーが頻発する。


SQLSTATE[HY000]: General error: 2003 Lost connection to backend server. with query:

どうも、slaveを見失ってしまうことが多く、結果として、

master側への接続もエラーになるというもの。

 

使っていたバージョンは、

maxscale2.0.4なのであるが、

意外に高負荷に現状耐えることが出来てないのかな、、と思う。

とはいえ、すぐにbugfixされるので、継続的に見守っていきたい。

 

投稿日時:2017年03月21日 22:28   カテゴリー:mariadb, mysql, php   [コメントがあればどうぞ]

phpのmysqlndというライブラリは素晴らしいものである。

しかし、その機能に悩まされたので、記載しておく。

 

そもそもmysqlndは、

  1. ネイティブのprepared statementのサポート
  2. DBの型に合わせたphpの自動型変換サポート
  3. フェイルオーバ検知の仕組みの提供(mysqlnd_ms)

などの機能がある。

 

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に振ってくれればいいのだが。。

投稿日時:2017年03月14日 23:15   カテゴリー:mariadb, mysql, php   [コメントがあればどうぞ]

MariaDBを3台構成で、

準同期レプリケーション&スレーブ自動昇格

のクラスターを組んだので、備忘録として記載しておく。

 

[ソフトウェア]

  • MariaDB 10.1.18
  • Maxscale 2.0.1
  • MariaDB Replication Manager 0.6.4(以下MRM)

 

[インフラ]

  • web × 3 → MariaDB client, Maxscale, MRMを配備
  • db × 3 → MariaDB serverを配備

 

[処理概要]

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

 

今後、高負荷状況において、テストを行うので、

修正があれば別途記載しようと思う。

 

以上

投稿日時:2016年12月26日 23:36   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

以前の記事で、

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問わず同じ設定を入れている。

 

ここで準同期レプリケーションの課題を考えてみると、以下のようなものがありそうだ。

  1. ネットワーク帯域が狭いとクラスターのパフォーマンスに影響を及ぼす
  2. slave台数が増えるとクラスターのパフォーマンスに影響を及ぼす
  3. バイナリログサイズがでかいとクラスターのパフォーマンスに影響を及ぼす

 

非同期と混在させることが出来なかったような気がするので、

遠隔地にバックアップをおくこともできないと思われる。

 

これらについて、検証したら詳細を記述したい。

 

投稿日時:2016年05月27日 23:21   カテゴリー:mariadb, mysql, rdb   [コメントがあればどうぞ]

前回

Slave auto promotionについて記載するといったが、

詳細はさておき、先に結論を書いておく。

 

【前提】

  • maxscaleによるWriterReaderSplit機能を使う
  • masterダウン時には、生きているslaveが自動昇格する

 

【結論】

  • ノード数は3以上が推奨と書かれているが、ノード数が1になると、maxscaleのWriterReaderSplitが機能しなくなる
  • slaveの同期が失敗していている状態では、masterに昇格しないし、maxscaleのクラスターからも外れる

 

ということが挙げられる。

つまり、3ノードあっても、2ノード死んだら、実質アウトなのである。

 

 

そのため、打っておく手段としては、

  • masterがダウン→手動復旧→自動で新masterのslaveとなるようにする
  • 1ノードになった場合、maxscale経由でアクセスしないようにする

ということをやっておけば完璧だと思われる。

 

ちなみに、良い点として、masterがダウン→復旧しても、新masterとバイナリログポジションがずれているため、

maxsacle上のクラスターからは外され、マルチマスター状態にはならない。(単独のマスターとして存在する)

 

幸い、maxscaleは結構イベントが拾えるので、

上記対策もなんとか実現できそうな気がしている。

そのあたりの検証記事を、今後書こうと思います。

 

以上

 

投稿日時:2016年05月17日 00:20   カテゴリー:mariadb, mysql, rdb   [コメントがあればどうぞ]

MariaDBでクラスターを組む際、

  • maxscale
  • mariadb-replication-manager
  • MariaDB

を使うとよい。

 

そもそも、MariaDBのクラスターには、

  • Spider(マルチマスター、データ分散型)
  • Galera(マルチマスター、データ同期型)
  • Slave auto promotion(マスタースレーブ、データ同期型)

のような3つの手段があるが(もっとあるかも。。調べてません。)、

GaleraとSlave auto promotionであれば、maxslace & repliacation-managerの監視および発砲でいける。

 

すいません、Spliderは記載しておきながら、大してわからないので、

GaleraとSlave auto promotionの特徴を書いてみる。

 

【Galera】

(メリット)

  • 全てmasterであるため、障害時のダウンタイムがほぼ0
  • 各ノードのデータ状況に差異が出にくい

(デメリット)

  • データの同期化のため、ノード数が増えると更新時間がかかる
  • テーブルor行ロックは同一ノードのみ有効

 

【Slave auto promotion】

(メリット)

  • データ更新時間はシングル構成と変わらない(うそ、シングルより遅いし、レプリケーションタイプにも影響をうける)

(デメリット)

  • Slaveの昇格が完了するまではダウンタイムとなる
  • Slave遅延が発生するとデータのロストが発生する

 

上記のような特徴があるので、

使いどころはサービスに依存するのかもしれない。

 

私の見解では、

・同一データを更新することがない場合 → Galera

・同一データを更新することが多い場合 → Slave auto promotion

がよいと思う。

決め手は、更新速度とロックの有無かと。

※Spiderは分散データなので、Slave auto promotionと組み合わせないとデータロストしちゃうかも。。

 

Galeraは結構やっている人がいるから、

Slave auto promotionについて、

maxscaleとreplication-managerを用いた構成の説明(やってみた)を近々で書こうと思う。

 

以上

 

投稿日時:2016年05月17日 00:02   カテゴリー:mariadb, mysql, rdb   [コメントがあればどうぞ]

MySQLでは、

・truncate

・load in file

・create

などの一部はトランザクションが効かないのね。。

 

また、load in fileは、

replaceキーワードを使えば、主キーが存在していれば、

updateを行ってくれるのね。。

基本的なことだろうけど、知らなかったわ。。

 

load in fileの例(RDSにて)


LOAD DATA LOCAL INFILE '${file_path}' REPLACE INTO TABLE ${table_name} FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

 

投稿日時:2015年10月08日 19:17   カテゴリー:mysql   [コメントがあればどうぞ]

プレビュー版のようですが、

MySQLと互換性をもつRDSが発表されましたね。

詳しくは、こちら

 

ちょっと気になるのは、

AmazonのEC2で選択できるAmazonLinuxって、centos6系をベースにカスタマイズされたものだったような。

 

centosは7から、標準レポジトリにMySQLが外れて、mariadbになってたから、

当面はAmazonLinuxはcentos7には上げないのかね?

 

EC2とRDSで違うから、問題ないのかもしれないけど、

mariadbもaws(特にRDS)で使えるようになると、今後もっと流行りそうな気がします。

 

投稿日時:2014年11月26日 13:16   カテゴリー:aws, mariadb, mysql   [コメントがあればどうぞ]