カテゴリー「mysql」

MySQL8のJSON部分更新を確認してみた。

 

最初こんなテーブルを作り、データを更新する。

> create database hoge;
> use hoge;
> create table fuga (id integer not null, body json not null, primary key(id));
> insert into fuga values (1, '{"x":10, "y":20}');
> update fuga set body = json_replace(body, '$.y', 50);

この状態でバイナリログを見ると、以下のようになっている。

### UPDATE `hoge`.`fuga`
### WHERE
### @1=1
### @2='{"x": 10, "y": 20}'
### SET
### @1=1
### @2='{"x": 10, "y": 50}'

部分更新じゃなくね?と思ったが、

my.cnfに以下を記載する。

binlog_row_image=minimal
binlog_row_value_options=partial_json

再起動完了後、以下のようなSQLを実行する。

> update fuga set body = json_replace(body, '$.y', 12);

この時、バイナリログは、

### UPDATE `hoge`.`fuga`
### WHERE
### @1=1
### SET
### @2=JSON_REPLACE(@2, '$.y', 12)

となる。

もともと、binlog_row_imageはバイナリログに変更分しか記載しないもので、
5.6から導入されていたが、8ではbinlog_row_value_optionsというパラメータが追加された。
これにより、JSONの部分更新がバイナリログ上でも明示される。

とはいえ、JSONの部分更新を成立させるには、
以下のJSON関数を使う必要がある。

  • JSON_SET()
  • JSON_REPLACE()
  • JSON_REMOVE()

MariaDB10.3でも、binlog_row_value_optionsがないため、

JSONの部分更新はできない。

おまけに、JSONはlongtextのエイリアスのため、

「JSONの正しさ」は保証しない。

JSONを使うなら、virtual columnもあるMSQL8が現時点では良いと思う。

 

以上

投稿日時:2018年06月14日 22:46   カテゴリー:mysql   [コメントがあればどうぞ]

先月のMySQL8のGAにつづき、

MariaDB10.3がGAされました。

https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-103/

 

MySQL5.7もそんなにいじってないうちに8が出たので、

これから8を検証しようとしているうちに、10.3が出たのですが、

10.3では気になっていたJSONの改善はなさそうです。

 

そもそもMySQLでは5.7で導入されたJSONがNativeJSONのため、

登録時のvalidationが効くという特性がありました。

MariaDB10.2で導入されたJSONはText型のaliasのようで、

validationは効かないような感じです。(未検証です。すいません。)

ただ、MariaDBのドキュメント読む限り、Textのパースは高速と記載されています。

 

MySQL8ではJSONの部分更新機能が導入されました。

これにより、巨大なJSONの一部を更新しても、更新負荷を下げることが可能となり、

もちろん、バイナリログにもそのように記載されるとのことです。

このようなJSONの部分更新がMariaDB10.3でも入るかと期待してましたが、

そのような記載が見あたらず。。

そもそもdynamic columnsに部分更新があるのかどうかにさえ、今気付いた。。

 

というわけで、

  • MySQL8
  • MariaDB10.3

の2つのGAについて、まずはJSONまわりから挙動確認していこうと考えてます。

 

以上

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

mysqlサーバが突然死して、

クライアント側から見ると生きている状態、つまりハーフCLOSE状態となるのだが、

このとき、他のmysqlサーバに接続している状態で、上記が発生すると、

生きている側のmysqlサーバへの接続がCLOSEされない待ち状態となることがある。

HTTPアクセスからだと、以下のような場合が該当する。

  1. mysqlサーバAにconnect
  2. mysqlサーバBにconnect
  3. mysqlサーバAにbegin
  4. mysqlサーバBにbegin
  5. mysqlサーバAにselect for update
  6. mysqlサーバBにselect
  7. mysqlサーバBが突然死
  8. HTTP要求がタイムアウト

このとき、6の応答を待ったまま、処理が続行され、

5のロックが解除されない状態となる。

5のロックを解除するには、

  • mysqlサーバAからプロセスをkillする
  • HTTPサーバを再起動する
  • 実行タイムアウトまで待つ

という方法のいずれかになる。

JDBCではsocketTimeoutといういわゆる実行タイムアウトがあるのだが、

phpのPDOでは、デフォルトで実行タイムアウトの概念はない。

set session 〜 で対応するくらいになるだろう。

しかし、set session 〜 で出来ることは、mysqlサーバのクエリー実行時間の制御であり、

クライアント側のタイムアウト制御はできない。

そこで、あのmysqlndを使っているのであれば、

mysqlnd.net_read_timeout

というパラメータを設定することで、実行タイムアウト例外を検知できるようになる。

apacheであれば、virtualhostに、php-fpmであれば、www.confに設定できる。

 

このように実行タイムアウト、いわゆるexecution timeoutをちゃんと処理していないことで、

痛い目を見る可能性があるので、このあたりはチェックしておくといざというときに大きな障害を回避できる。

 

以上

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

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   [コメントがあればどうぞ]