カテゴリー「mariadb」

10.1から10.2で変更になるパラメータを見てみた。

個人的に気になるものは以下の通り。

・max_allowed_packet
4MB -> 16MB

・sql_mode
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION -> STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

・innodb_additional_mem_pool_size
removed

・innodb_autoinc_lock_mode
1 -> 2

・innodb_buffer_pool_dump_at_shutdown
off -> on

・innodb_buffer_pool_dump_pct
100 -> 25

・innodb_buffer_pool_load_at_startup
off -> on

・innodb_buffer_pool_size
Dynamic

・innodb_checksum_algorithm
innodb -> crc32

・innodb_compression_algorithm
none -> zlib

・innodb_file_format
Deprecated

・innodb_file_format_max
Deprecated

・innodb_flush_sync
new

・innodb_large_prefix
Deprecated

 

俯瞰してみると、これまで10.1では必ず設定していたパラメータが、

defaultで採用されているケースが多い。

そして、767byteを超えるインデックスについて扱っていた

large_prefixに関係するパラメータがdeprecatedになり、

defaultで3072byteまでインデックスサイズが拡張されるということか。。

 

もうちょい深読みしてみないと、上記の変更が生む弊害が読めないが、

往々にして、より扱いやすい方向に進んでいる気がする。

なお、上記のパラメータの多くはMySQL5.7でも採用されている。

 

以上

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

mysqlではインデックスは1つしか使えないと言われていたが、

実際はインデックスマージという機能があるため、

2つ同時に使うことは可能である。

 

ただ、インデックスマージはwhere句でのみ作用可能に思われるので、

whereとorder byで違うインデックスを使うのは無理なのではないかと思っている。

MySQL5.7でもMariaDB10.2でも、このあたりの記述が見当たらなかった。。

 

上記の理解であっているのか、非常に気になるところである。

進展があったら、書こうと思う。

 

以上

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

GaleraClusterでは、書き込みノードを1つに絞って、

デッドロックを回避したほうがいいとよく言われる。

maxscaleを使えば、書き込みノードを絞ることは簡単であるが、

単に3つのクエリーを実行するだけで、書き込みノードを絞れるので、記載しておく。

なお、Perconaも似たような方法をやっていた。

 


1.SHOW GLOBAL STATUS LIKE 'wsrep_local_state'; の結果が4である

2.SHOW VARIABLES LIKE 'read_only'; の結果がoffである

3.SHOW GLOBAL STATUS LIKE 'wsrep_local_index'; の結果が0である

 

以上、3つを満たした場合、書き込みノードとして判定してOK。

haproxyと組み合わせることも簡単なので、

どこかでhaproxyとの組み合わせについても記載しようと思う。

 

以上

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

以前の記事で、galeraの書き込み能力を向上させるために、

spiderと併用したらいいのではないかと記載した。

しかし、この二つの組み合わせるには、

xa transactions(二相コミット)をあきらめる必要がある。

 

そもそも、galera自体、xaには非対応であり、

また、xa transactionsはバイナリログに対して、クラッシュセーフではないようだ。

(MySQLのドキュメントには記述があったが、MariaDBには記載を見つけられなかった。。)

 

上記前提を踏まえたうえで、

galeraとspiderの共演を実現するためには、xaをあきらめることは必須だが、

そもそもの話として、実装上複数ノードのwriteを極小化しておく必要が十分にあると考えられる。

 

これまで、プログラム側では、begin,begin,commit,commitみたいな実装を平気でしていたが、

そもそもこれ自体が出来るだけ避ける必要のある実装であることを、

今回のgaleraとspiderの共演を実現するために調査していた過程で気づいた。。。

今更の話だが、commitは失敗しないだろうという気持ちがどこかにあり、commitの失敗をリカバリーすることが頭の中から抜けていたように思えて反省している。

 

とはいえ、とはいえ、spiderを挟むことで、

プログラムのシャーディング負担を軽減し、

さらにgaleraで高可用性を確保する組み合わせは、とても魅力的ではある。

 

どこかで、galeraとspiderを共演するために、必要な設定・注意事項等をまとめようと思う。

検証した結果の結論から先に書くと、spiderは受信したSQLを変化させて、バックに投げるため、

場合によっては、SQLの発行回数の増加や、参照取得行数の増加が発生する。

この変化パターンを正確につかんでおかないと、気づかないうちに高負荷をバックに与えてしまう可能性があるため、十分に注意したい。

どっかでこのあたりもまとめてみようと思う。

 

以上

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

MariaDBで準同期レプリケーションで、

猛烈に書き込み中に、MariaDBを停止させ、

単純にslaveを昇格させただけでは、ロストする。

binlogをmasterから救い出さないといけない。

mhaではbinlogを救い出すという処理があるようだが、

当方が使っていたMariaDB Replication Managerにはそのような機能がない。

 

これに対し、Galleraで同じことをやっても、

ロストしなかった。

 

もうちょっといろいろなパターンでやってみないと分からないが、

Galleraのデータ整合性は、準同期よりも1ランク高い気がしている。

ただ、Galleraは書き込み性能がイマイチなので、

フロントにSpiderを置いて、shardingすれば、

高可用性・高負荷耐性をもったシステムが作れそうな気がしている。

 

また進展あれば、記載したいと思う。

 

以上

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

MySQL/MariaDBで、binlog_formatというのが、

  • statement
  • mixed
  • row

とありますが、

このうち、mixedを選択していた場合、

statementかrowがクエリーのタイプによって、

決定されると書いてあったのだが、

よく見ると、tx_isolationが

InnoDB テーブルを使用中で、トランザクション分離レベルが READ COMMITTED または READ UNCOMMITTED の場合、行ベースのロギングのみを使用することができます。ロギング形式を STATEMENT に変更することは可能ですが、InnoDB は挿入を実行できないため、実行時にこれを行うと、非常に速くエラーが発生します。』

という記述が公式にあった。

 

rowだと、バイナリログの出力サイズが大きいから、

mixedにして削減しようと思ったが、

そもそもREAD COMMITEDで運用していたから、意味なかったという話。。

 

投稿日時:2017年04月30日 21:50   カテゴリー:mariadb, 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ばっかりです。

実施したバージョンは、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つである。

  • slave-domain-parallel-threads(スキーマ)
  • slave-parallel-threads(グループ)

詳しくは本家の説明ページを参照されたい。

https://mariadb.com/kb/en/mariadb/parallel-replication/

 

諸々書いてきたが、

MySQL5.7とMariaDB10.1ではほぼ同等(世間的にはMySQL5.7の上かな?)といったところと思います。

ただ、MariaDBのレプリケーションは、結構素晴らしいと思っています。

並列レプリケーション以外にも、GTIDがデフォルトで使えることによる扱いやすさもあるので、

個人的には国内でもMariaDBがもっと流行るといいなと願っています。

 

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

ずいぶん時間が空いてしまったが、

MariaDBのHAで続きが出来たので、

記載します。

 

前回、負荷試験前でありましたが、

諸々実施後、以下の問題点が出てきました。

  • 秒間300コミットを超えると、slaveが遅延する
  • slave遅延により、Semiレプリケーションが外れる

 

秒間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の更新性能をアップするため、

  • slave_parallel_threads

という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コミット近く耐えらる。

 

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