IT系のめもを蓄積していこうかと
PHPのparse_url関数のような動作をするMySQLのUDFをcgoで作成しました。
https://github.com/shigenobu/mysql_ws_parse_url
（参考）PHPのparse_url関数
https://www.php.net/manual/ja/function.parse-url.php
この関数のポイントは、クエリー形式のパラメータをキー名でとれるところです。
最近はアクセスログをDBに保存して、そのまま分析に使うなどをすることもあり、MySQL側で一気に加工できれば楽だなと思いつくりました。
これ以外にも作成したものがいくつかあるので、そのうち公開しようと思います。
なお、動作確認したのはMariaDB10.4のみなので、もしお使いになる場合は自身の環境での動作確認をお願いします。
以上
pt-online-schema-changeをslaveに対して実行した場合に痛い目見たので、その備忘録。
そもそもの話、master側で不要なINDEXをslaveのみ張ってました。
OLTP側ではいらないINDEXだけど、集計とかであると嬉しいINDEXってやつですね。
そして、slaveに張っているINDEXをpt-oscでオンラインで張替えをしようとしたら、
リレーログからの取り込みが、旧テーブルに行ってしまい、
pt-oscで作成される新テーブルに入らなかったいうオチ。。。
その結果、レプリケーションの不整合が起きて、最初から構築するという目にあった。。
このような場合、素直にstop slaveを掛けてからDDLを実行すれば良かったです。。
とはいえ、リレーログからのデータ取り込みの際、pt-oscで実行しているとダメなことがわかったので、
もうちょっとこのあたりについて調べてみようかと思います。
あ、binlog_row_image=FULLだったし、MariaDB10.3だからflashback使えば、
もっと短時間で復旧できたかも、、って書いているときに思いました。。
以上
unionする前にwhereをするのと、
unionした後にwhereをする場合、
mysqlにおいて、差異はあるのかを確認してみた。
動機としては、
「unionする前にwhereをする」のと「unionした後にwhereをする」が同パフォーマンスであれば、
multisourceでviewを作っておいても十分高速に引ける可能性がある、と思ったところです。
いろいろなバージョンで試してみました。
予め以下のようなテーブルを作成しておき、100万件登録しておきます。
> desc users; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | email | varchar(128) | NO | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
（MySQL5.6）
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | NULL | | 2 | DERIVED | users | ALL | NULL | NULL | NULL | NULL | 996473 | NULL | | 3 | UNION | users | ALL | NULL | NULL | NULL | NULL | 996473 | NULL | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------+---------------+-------------+---------+-------+--------+-----------------+ 4 rows in set (0.12 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+ 4 rows in set (0.00 sec)
（MySQL5.7）
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 996473 | 100.00 | NULL | | 3 | UNION | users | NULL | ALL | NULL | NULL | NULL | NULL | 996473 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec)
（MySQL8）
mysql> explain select * from (select * from users union all select * from users) as t where id = 7542; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 996761 | 100.00 | NULL | | 3 | UNION | users | NULL | ALL | NULL | NULL | NULL | NULL | 996761 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 3 rows in set, 1 warning (0.05 sec) mysql> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 3 | UNION | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec)
（MariaDB10.1）
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1994176 | Using where | | 2 | DERIVED | users | ALL | NULL | NULL | NULL | NULL | 997088 | | | 3 | UNION | users | ALL | NULL | NULL | NULL | NULL | 997088 | | +------+-------------+------------+------+---------------+------+---------+------+---------+-------------+ 3 rows in set (0.01 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.00 sec)
（MariaDB10.2）
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.001 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.001 sec)
（MariaDB10.3）
MariaDB [test]> explain select * from (select * from users union all select * from users) as t where id = 7542; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.001 sec) MariaDB [test]> explain select * from (select * from users where id = 7542 union all select * from users where id = 7542) as t; +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 3 | UNION | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+------------+-------+---------------+---------+---------+-------+------+-------+ 3 rows in set (0.000 sec)
これ、正直言って、動機を満たす結果を得るのは無理だと思ってたが、、、
MariaDB10.2と10.3は嬉しい結果を出してくれました。
以上
MariaDB10.3、MySQL8から、ALTER構文にinstantの機能が導入されました。
利用できる範囲は限られていますが、その中であれば超高速でALTERが完了します。
たぶん、メタデータだけ書き換えて、実際のデータは初回アクセス時に補完する形をとっているような感じです。
詳細は、公式マニュアルを参考指定だければと思います。
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
実際にデータを突っ込んでみて、どのくらいALTERが高速になるか試してみました。
以下のようなテーブルを用意して、100万件のデータを入れておきます。
> desc users; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | email | varchar(128) | NO | | NULL | | +--------+---------------------+------+-----+---------+-------+
（MySQL5.7）
mysql> alter table users add column money1 bigint unsigned not null; Query OK, 0 rows affected (2.35 sec) Records: 0 Duplicates: 0 Warnings: 0
（MySQL8）
mysql> alter table users add column money1 bigint unsigned not null; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
一旦drop。
mysql> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
（MariaDB10.3）
MariaDB [test]> alter table users add column money1 bigint unsigned not null; Query OK, 0 rows affected (0.006 sec) Records: 0 Duplicates: 0 Warnings: 0
一旦drop。
MariaDB [test]> alter table users add column money1 bigint unsigned not null, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.007 sec) Records: 0 Duplicates: 0 Warnings: 0
MySQL5.7と比較して、超高速ですね。。
なお、MariaDB10.3もMySQL8も、デフォルトで最後尾へのadd columnはINSTANTとして扱われるようです。
ちなみに、MySQL8のほうが、INSTANTが扱える範囲が広いようです。
以下のような条件でINSTANTが使えるようです。（※公式より引用）
instantが発動したかどうかは、
MariaDB10.3の場合は以下のクエリーによって確認できます。
SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column';
MySQL8の場合は以下。
SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%{table_name}%';
条件はあるものの、MySQL8は仮想列に対するinstantにも対応しているので、結構使い勝手も広がる予感です。
とはいえ、これまでalterに非常に大きな時間・処理コストがかかっていたのが、一気に超高速になり、非常に素晴らしいと思います。
パッチを提供したTencentに感謝ですね。
あとは、instantで変更をかけて、その後のアクセスでどのくらいIO負荷を持っていかれるか、試してみたいな。。
以上
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関数を使う必要がある。
MariaDB10.3でも、binlog_row_value_optionsがないため、
JSONの部分更新はできない。
おまけに、JSONはlongtextのエイリアスのため、
「JSONの正しさ」は保証しない。
JSONを使うなら、virtual columnもあるMSQL8が現時点では良いと思う。
（２０１８年１１月１日追記）
＞ おまけに、JSONはlongtextのエイリアスのため、
＞ 「JSONの正しさ」は保証しない。
大変申し訳ありません。
上記は間違っていました。
https://mariadb.com/kb/en/library/json-data-type/
にcheck制約を使うことで、「JSONの正しさ」を保証することが可能でした。
以上
先月の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に部分更新があるのかどうかにさえ、今気付いた。。
というわけで、
の2つのGAについて、まずはJSONまわりから挙動確認していこうと考えてます。
以上
mysqlサーバが突然死して、
クライアント側から見ると生きている状態、つまりハーフCLOSE状態となるのだが、
このとき、他のmysqlサーバに接続している状態で、上記が発生すると、
生きている側のmysqlサーバへの接続がCLOSEされない待ち状態となることがある。
HTTPアクセスからだと、以下のような場合が該当する。
このとき、6の応答を待ったまま、処理が続行され、
5のロックが解除されない状態となる。
5のロックを解除するには、
という方法のいずれかになる。
JDBCではsocketTimeoutといういわゆる実行タイムアウトがあるのだが、
phpのPDOでは、デフォルトで実行タイムアウトの概念はない。
set session 〜 で対応するくらいになるだろう。
しかし、set session 〜 で出来ることは、mysqlサーバのクエリー実行時間の制御であり、
クライアント側のタイムアウト制御はできない。
そこで、あのmysqlndを使っているのであれば、
mysqlnd.net_read_timeout
というパラメータを設定することで、実行タイムアウト例外を検知できるようになる。
apacheであれば、virtualhostに、php-fpmであれば、www.confに設定できる。
このように実行タイムアウト、いわゆるexecution timeoutをちゃんと処理していないことで、
痛い目を見る可能性があるので、このあたりはチェックしておくといざというときに大きな障害を回避できる。
以上
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でも採用されている。
以上
mysqlではインデックスは1つしか使えないと言われていたが、
実際はインデックスマージという機能があるため、
2つ同時に使うことは可能である。
ただ、インデックスマージはwhere句でのみ作用可能に思われるので、
whereとorder byで違うインデックスを使うのは無理なのではないかと思っている。
MySQL5.7でもMariaDB10.2でも、このあたりの記述が見当たらなかった。。
上記の理解であっているのか、非常に気になるところである。
進展があったら、書こうと思う。
以上
GaleraClusterでは、書き込みノードを1つに絞って、
デッドロックを回避したほうがいいとよく言われる。
maxscaleを使えば、書き込みノードを絞ることは簡単であるが、
単に3つのクエリーを実行するだけで、書き込みノードを絞れるので、記載しておく。
なお、Perconaも似たような方法をやっていた。
１．SHOW GLOBAL STATUS LIKE 'wsrep_local_state'; の結果が4である ２．SHOW VARIABLES LIKE 'read_only'; の結果がoffである ３．SHOW GLOBAL STATUS LIKE 'wsrep_local_index'; の結果が0である
以上、3つを満たした場合、書き込みノードとして判定してOK。
haproxyと組み合わせることも簡単なので、
どこかでhaproxyとの組み合わせについても記載しようと思う。
以上