アーカイブ「2018年09月」

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は嬉しい結果を出してくれました。

 

以上

投稿日時:2018年09月11日 20:08   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

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が使えるようです。(※公式より引用)

  • Adding a column. This feature is referred to as “Instant ADD COLUMN”. Limitations apply. See Section 15.12.1, “Online DDL Operations”.
  • Adding or dropping a virtual column.
  • Adding or dropping a column default value.
  • Modifying the definition of an ENUM or SET column. The same restrictions apply as described above for ALGORITHM=INSTANT.
  • Changing the index type.
  • Renaming a table. The same restrictions apply as described above for ALGORITHM=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負荷を持っていかれるか、試してみたいな。。

 

以上

 

投稿日時:2018年09月11日 18:48   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

久しぶりにMariaDB以外のことを書いてみる。

 

Redis3.2のrdbファイルは、Redis4に持っていっても、なんの障害もなく移行できる。

リリースノートにも書いてあるし、実際に試してみたところ、問題なく移行でき、キー操作も問題ない。

移行先でAOFを無効化しているので、rdbファイルを移行しただけだが、

AOFを有効化している場合は、aofファイルから復元されるので、注意が必要。

https://raw.githubusercontent.com/antirez/redis/4.0/00-RELEASENOTES

※リリースノートの一番下に、移行についての説明が記載されています。

 

で、Redis4についてですが、

AOFが単なる追記型ファイルとしてでなく、一定時点のスナップショット+追記、という形式(MIXED)に生まれ変わりました。

これにより、Redis3ではすごい速さで肥大化し続けるaofファイルが、

Redis4では肥大化速度が抑制され、さらにaofによる復元速度も早くなり、さらにさらにレプリケーションにも好影響があるということで、

Redis4からはAOFを積極的に使っていくべきだな、と感じております。

 

とはいえ、実際に移行を行う際、まるっとrdbファイルを移行しても、

移行先がAOFを有効化していると、aofファイルから復元が試みられる結果、

rdbファイルが読み込まれず、saveした際には、データがなくなってしまうという悲しい結果に終わる。

 

そこで、Redis3.2からRedis4への移行については、レプリケーションが好ましい。

以下のような形でサーバを組む。

--------------------
redis01 (version:3.2.x, aof:disable)
IP:192.168.35.11
--------------------
 |
 | (replication)
 |
--------------------
redis02 (version:4.0.x, aof:enable)
IP:192.168.35.12
--------------------

このように、移行元(redis01)ではAOFが無効化されており、

移行先(redis02)ではAOFを有効化したい場合、

移行先のAOFを有効化した状態で、レプリケーションを始める。

そうすると、移行先のデータディレクトリ(/var/lib/redisなど)に、rdbファイルとaofファイルの両方が作成される。

 

もし、MIX形式を使いたいなら、

aof-use-rdb-preamble yes

というように、「aof-use-rdb-preamble」を「yes」に設定する必要がある。

実際に、aofファイルにスナップショットが書かれるタイミングは、

auto-aof-rewrite-percentage

auto-aof-rewrite-min-size

という2つのパラメータに依存するため、なかなかaofファイルにスナップショットが書き込まれない場合、これらを小さくするとよい。

あくまで、aofにスナップショットが書かれたことを確認するためで、無理に小さくする必要はない。

ちなみに、aofファイルにスナップショットが含まれているかどうかは、lessコマンドで確認できる)

 

最後に、移行先(redis02)のaof関連パラメータを載せておく。

appendonly yes
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
aof-use-rdb-preamble yes

 

(参考)

Redis Mixed RDB+AOF

https://qiita.com/devneko/items/67662e016e8cd0b7791a

 

以上

投稿日時:2018年09月06日 16:45   カテゴリー:redis   [コメントがあればどうぞ]