oct2019_b-2-1

Oracle ACE が語る

MySQL 8

Oracle ACE が語る

MySQL 8.

0.15

2019/05/17 Oracle Code 2019 Tokyo

Satoshi Mitani

⾃⼰紹介

•

三⾕ 智史（Twitter: @mita2）

•

MySQL DBA @ Yahoo! JAPAN

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

•

インデックス

•

対象のカラムでソートされたツリー構造

降順インデックス

PK from_date 1 2017-10-01 2 2018-10-02 3 2019-10-03 4 1995-07-21 1995 2017 2018 2019 昇順 インデックス 4 ₁ 2 3

•

降順インデックス

降順インデックス

mysql> CREATE INDEX idx_from_date ON salaries (from_date

DESC

);

PK from_date 1 2017-10-01 2 2018-10-02 3 2019-10-03 4 1995-07-21 2019 2018 2017 1995 降順 インデックス 3 ₂ 1 4

v8.0で降順インデックスがサポートされました

•

v5.7

•

v8.0

mysql> CREATE INDEX idx_from_date ON salaries (from_date

DESC

);

Query OK, 0 rows affected (5.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX idx_from_date ON salaries (from_date

DESC

);

Query OK, 0 rows affected (5.79 sec)

v8.0で降順インデックスがサポートされました

•

v5.7

•

v8.0

mysql> CREATE INDEX idx_from_date ON salaries (from_date DESC);

Query OK, 0 rows affected (5.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX idx_from_date ON salaries (from_date DESC);

Query OK, 0 rows affected (5.79 sec)

v5.7でも DESC で

v8.0で降順インデックスがサポートされました

•

v5.7

•

v8.0

mysql> CREATE INDEX idx_from_date ON salaries (from_date DESC);

Query OK, 0 rows affected (5.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX idx_from_date ON salaries (from_date DESC);

Query OK, 0 rows affected (5.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

これまでは DESC を

無視してたよ☆(・ω<)

使いどころ

•

降順ソート(ORDER BY hoge DESC) が⾼速化される

•

複合インデックスで

使いどころ

•

降順ソート(ORDER BY hoge DESC) が⾼速化される

•

複合インデックスで

（極端なケースで）くらべてみました

クエリ インデックス 実⾏時間

1 ORDER BY from_date ASC なし 1.91s

2 ORDER BY from_date ASC ASC 0.86s

3 ORDER BY from_date DESC 1.22s

4 ORDER BY from_date DESC DESC 0.86s

0 0.5 1 1.5 2 2.5 実⾏時間(s) クエリ実⾏時間 1. v8.0 インデックスなし 2. v8.0 昇順ソート＋昇順インデックス 3. v8.0 昇順ソート＋降順インデックス 4. v8.0 降順ソート＋降順インデックス

（極端なケースで）くらべてみました

クエリ インデックス 実⾏時間

1 ORDER BY from_date ASC なし 1.91s

2 ORDER BY from_date ASC ASC 0.86s

3 ORDER BY from_date DESC 1.22s

4 ORDER BY from_date DESC DESC 0.86s

0 0.5 1 1.5 2 2.5 実⾏時間(s) クエリ実⾏時間 1. v8.0 インデックスなし 2. v8.0 昇順ソート＋昇順インデックス 3. v8.0 昇順ソート＋降順インデックス 4. v8.0 降順ソート＋降順インデックス Backward Index Scanによる最適化。 ※ 新機能ではない

実⾏計画（EXPLAIN結果）

•

インデックスがソートに使われてない

•

インデックスがソートに使われている

•

インデックスがソートに使われている（Backward index scan)

mysql> EXPLAIN SELECT emp_no FROM salaries ORDER BY from_date ¥G

<snip>

Extra:

Using filesort

mysql> EXPLAIN SELECT emp_no FROM salaries ORDER BY from_date ¥G

<snip>

Extra:

mysql> EXPLAIN SELECT emp_no FROM salaries ORDER BY from_date ¥G

<snip>

forward / backwardが8.0

使いどころ

•

降順ソート(ORDER BY hoge DESC) が⾼速化される

•

複合インデックスで

複合インデックスでソートにインデックスが効かないケース

mysql> CREATE INDEX idx_k1_k2 ON sbtest1(k1 ASC, k2 ASC); Query OK, 0 rows affected (3.80 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM sbtest1 ORDER BY k1 ASC, k2 DESC LIMIT 5;

+----+---+~+---+---+~+---+---+---+ | id | select_type |~| type | possible_keys |~| rows | filtered | Extra | +----+---+~+---+---+~+---+---+---+ | 1 | SIMPLE |~| ALL | NULL |~| 1922110 | 100.00 | Using filesort | +----+---+~+---+---+~+---+---+---+ 1 row in set, 1 warning (0.00 sec)

↓クエリ /→ インデックス A) k1 ASC, k2 ASC B) k1 ASC, k2 DESC C) k1 DESC, k2 ASC

1) ORDER BY k1 ASC, k2 ASC ◎ forward scan ✖ ✖

2) ORDER BY k1 ASC, k2 DESC ✖ ◎ forward scan ◎ backward scan 3) ORDER BY k1 DESC, k2 ASC ✖ ◎ backward scan ◎ forward scan

•

⼀致させることができるように

複合インデックスによるソート

mysql> CREATE INDEX idx_k1_k2_desc ON sbtest1(k1 ASC, k2 DESC); Query OK, 0 rows affected (3.97 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM sbtest1 ORDER BY k1 ASC, k2 DESC LIMIT 5;

+----+---+~+---+---+~+---+---+---+ | id | select_type |~| type | possible_keys |~| rows | filtered | Extra | +----+---+~+---+---+~+---+---+---+ | 1 | SIMPLE |~| index | NULL |~| 5 | 100.00 | NULL | +----+---+~+---+---+~+---+---+---+

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

ファンクションインデックスもサポートされた

•

ファンクションインデックス

mysql> CREATE INDEX idx_func ON t2 ( (CONCAT(c1, c2)) );

ファンクションインデックスもサポートされた

•

JSON型のデータの⼀部に対してもインデックス追加

mysql> SELECT * FROM jsont LIMIT 1 ¥G

*************************** 1. row ***************************

pk: 1

json_data: {"id": "aa", "hoge": "piyo"}

mysq> CREATE INDEX idx_json_id ON jsont

( (CAST(JSON_EXTRACT(json_data, "$.id") AS CHAR(10))) );

mysql> SELECT * FROM jsont WHERE CAST(json_extract(json_data,

"$.id") AS CHAR(10)) = "a";

インデックスの張りすぎ注意

•

SQLアンチパターン︓インデックスショットガン

•

容量の増加

•

更新時のインデックス更新のオーバーヘッド

インデックス削除の悩み

•

もしかしたら、知らないところで使われているかも・・・

•

消してもパフォーマンスに問題なそうなんだけど、

確信が持てない・・・

Invisible Index

•

8.0でInvisible Index がサポート

•

インデックスを削除する前に様⼦⾒ができる

•

INVISIBLEにして、問題なければDROP

•

INVISIBLE 状態でもインデックスの更新は継続

•

問題があれば、VISIBLEに戻すだけ

mysql> ALTER TABLE t ALTER INDEX idx_c1 [INVISIBLE|VISIBLE];

Query OK, 0 rows affected (0.01 sec)

INVISIBLE INDEXの例

mysql> EXPLAIN SELECT emp_no FROM salaries WHERE salary = 59204 ;

+----+~+---+---+---+---+---+---+---+ | id |~| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+~+---+---+---+---+---+---+---+ | 1 |~| idx_salary | idx_salary | 4 | const | 52 | 100.00 | Using index | +----+~+---+---+---+---+---+---+---+ mysql> ALTER TABLE salaries ALTER INDEX idx_salary INVISIBLE;

Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT emp_no FROM salaries WHERE salary = 59204 ;

+----+~+---+---+---+---+---+---+---+ | id |~| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+~+---+---+---+---+---+---+---+

INVISIBLE INDEXの例

mysql> SELECT INDEX_NAME, IS_VISIBLE

FROM information_schema.STATISTICS WHERE INDEX_NAME = 'idx_salary'; +---+---+

| INDEX_NAME | IS_VISIBLE | +---+---+ | idx_salary | NO | +---+---+ 1 row in set (0.01 sec)

注意点

•

FORCE/USE INDEXで指定しているとエラーに

mysql> ALTER TABLE t ALTER INDEX idx_c1 INVISIBLE;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT c1 FROM t FORCE INDEX (idx_c1) LIMIT 1;

インデックス⼤掃除に使える TIPS 1

•

利⽤されてないインデックス

mysql> CALL sys.ps_truncate_all_tables(FALSE);

+---+

| summary |

+---+

| Truncated 49 tables |

+---+

mysql> SELECT * FROM sys.schema_unused_indexes;

+---+---+---+

| object_schema | object_name | index_name |

+---+---+---+

| xy

| t | pk

|

+---+---+---+

必要に応じて

インデックス⼤掃除に使える TIPS 2

•

カラムが重複しているインデックスの洗い出し

SELECT i1.TABLE_SCHEMA,

CONCAT(i2.INDEX_NAME, " INCLUDING ", i1.INDEX_NAME) "IDX2 INCLUDING IDX1", i2.columns "IDX2 COLUMNS", i1.columns as "IDX1 COLUMNS",

i1.TABLE_NAME as "TABLE NAME" FROM

(SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns

FROM `information_schema`.`STATISTICS`

WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')

AND NON_UNIQUE = 1 AND INDEX_TYPE='BTREE' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS i1 INNER JOIN

( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns

インデックス⼤掃除に使える TIPS 2

•

カラムが重複しているインデックスの洗い出し

mysql> CREATE INDEX idx_c1 OJ t2(c1);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX idx_c1_c2 ON t2(c1,c2);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

+~+---+---+---+---+

|~| IDX2 INCLUDING IDX1 | IDX2 COLUMNS | IDX1 COLUMNS | TABLE NAME |

+~+---+---+---+---+

|~| idx_c1_c2 INCLUDING idx_c1 | c1,c2 | c1 | t2 |

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

スロークエリログ

•

実⾏時間が閾値を超えたクエリをログに落とす機能

スロークエリログの8.0における改善

•

log_slow_extra

•

有効にするとログに落ちる情報が増える

# Time: 2019-04-08T10:50:16.493715Z

# User@Host: root[root] @ localhost [] Id: 41

# Query_time: 1.000240 Lock_time: 0.000000 Rows_sent: 1

Rows_examined: 0

Thread_id: 41 Errno: 0 Killed: 0 Bytes_received: 0

Bytes_sent: 56 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0

Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0

Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0

Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start:

2019-04-08T10:50:15.493475Z End: 2019-04-08T10:50:16.493715Z

便利なヤツ

•

Bytes_sent

•

結果セットのサイズ

•

ネットワーク帯域を圧迫しているクエリを⾒つけやすく

•

Rows_sent からエスパーする必要がなくなった

•

Start / End

•

クエリの開始/終了時間

•

ログはクエリの実⾏完了タイミングで落ちる

便利なヤツ

•

Sort_merge_passes

•

sort_buffer も増量で性能向上する可能性のあるクエリ

•

Created_tmp_disk_tables

•

DISKソートになっているクエリ

•

Temp ⽤メモリの増量で性能向上する可能性のあるクエリ

ありそうでなかったものたち・・・

•

Rows_affected

•

⼤きな更新クエリが探したい・・・

•

Temp Disk Table のサイズ

•

Created_tmp_disk_tablesで作られたかどうかはわかるが…

•

Disk への Read 量

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

Group Replication

•

⾼可⽤性ソリューション

•

Paxos ベース

•

マルチライター構成が組める

Master

Master

_Master

_Master

_Master

UPDATE t

SET col = ʻBʼ

WHERE pk = 2

UPDATE t

SET col = ʻAʼ

WHERE pk = 1

⾃動リカバリー

Master

Master

_Master

_Master

_Master

•

障害を⾃動検知し、データ同期対象から切り離す

Write性能のスケールは限定的

•

Write性能のスケールを主⽬的としたものではない

Master

Master

_Master

_Master

_Master

UPDATE t

SET col = ʻAʼ

WHERE pk = 1

構成パターン

Master

Read only

_Master

Read only

_Master

Single Primary Mode

•

任意の1台のみWriteが可能になる

•

従来のマスター/スレーブに似てる

Master

Master

Master

Multi Primary Mode

•

全部に読み書き

•

楽観的ロックによる競合回避

group_replication_single_primary_mode=FALSE group_replication_single_primary_mode=TRUE

最⼤の特徴

•

障害検知・切り替わりの

仕組みがビルドイン

•

クラスタリングソフト etc

不要

•

単純にアクセスの向き先を

変えるだけ

Group Replication とメンバー間の整合性

1 2 3 UPDATE Ce rtif ica tio n Ce rtif ica tio n Ap ply ACK 更新する内容 を伝える Ap ply Ce rtif ica tio n OK ACK Fi nila zie d Fi na liz ed Fi na liz ed リレーログから トランザクション を復元して適⽤ 更新が競合 してないか チェック CONFLICT ERROR

古いデータを読み取ってしまう 例1）

1 2 3 UPDATE c1=

new

Ap ply ACK Ap ply OK ACK Fi nila zie d Fi na liz ed

mysql> SELECT c1 FROM tbl

old

古いデータを読み取ってしまう 例2）

1 2 3 ⼤きなトラン ザクション Ap ply ACK Ap ply OK ACK Fi nila zie d

•

⼤きな

トランザクション

•

Applyに時間がかかる

mysql> SELECT c1 FROM tbl

old

group_replication_consistency パラメータ

•

読み書きするデータの⼀貫性をコントロール

•

group_replication_consistency パラメータで制御

•

以下の値をとる

•

EVENTUAL (default)

•

BEFORE

•

BEFORE_ON_PRIMARY_FAILOVER

•

AFTER

•

BEFORE_AND_AFTER

group_replication_consistency パラメータ

1. EVENTUAL (default)

2. BEFORE

3. BEFORE_ON_PRIMARY_FAILOVER

4. AFTER

5. BEFORE_AND_AFTER

group_replication_consistency=EVENTUAL

•

デフォルトの設定

•

（書いたメンバーと違うメンバーから）

group_replication_consistency パラメータ

1. EVENTUAL (default)

2. BEFORE

3. BEFORE_ON_PRIMARY_FAILOVER

4. AFTER

5. BEFORE_AND_AFTER

group_replication_consistency=BEFORE

•

トランザクション開始時点のコミット済みの内容が

適⽤されるのを待つ

•

最新のデータを読み取ることを保証

•

例）以下のSELECT結果は最新のデータとなる

mysql> SET group_replication_consistency=BEFORE;

mysql> SELECT c1 FROM tbl

group_replication_consistency=BEFORE

1 2 UPDATE c1=new Ap ply OK ACK Fi nila zie d Fi na liz ed

mysql> SET group_replication_consistency=BEFORE

mysql> SELECT c1 FROM tbl

適⽤待ち…

SELECT

RETURN

•

wait_timeout を超えるとエラー

group_replication_consistency=BEFORE

1 2 UPDATE c1=new Ap ply OK ACK Fi nila zie d Fi na liz ed

mysql> SET group_replication_consistency=BEFORE

mysql> SELECT c1 FROM tbl;

ERROR 3797 (HY000): Error while waiting for

group transactions commit on

group_replication_consistency パラメータ

1. EVENTUAL (default)

2. BEFORE

3. BEFORE_ON_PRIMARY_FAILOVER

4. AFTER

5. BEFORE_AND_AFTER

シンプルな回避⽅法

•

古いデータの読み取りを回避する⼀番シンプルな⽅法

•

１つのメンバーでRWすること

•

ただし、切り替わり時に

古いデータを読取るリスクはある

Master

Read only

_Master

Read only

_Master

group_replication_consistency=

BEFORE_ON_PRIMARY_FAILOVER

•

障害発⽣時の挙動を制御

•

Single Primary 運⽤時にのみ有効

•

新プライマリがキャッチアップするまで結果を返さ

ない（クエリを待機させる）

group_replication_consistency パラメータ

1. EVENTUAL (default)

2. BEFORE

3. BEFORE_ON_PRIMARY_FAILOVER

4. AFTER

5. BEFORE_AND_AFTER

group_replication_consistency=AFTER

•

そのトランザクションで更新した内容が、以降のトランザ

クションで、どのメンバーであっても読み取られることを

保証する。

動作の様⼦ (demo)

•

そのトランザクションで更新した内容が、以降のトランザ

クションで、どのメンバーであっても読み取られることを

保証する。

AFTERの難しいところ・・・

1. （更新は順番に適⽤されていくため）AFTERで実⾏したト

ランザクション以前の更新の適⽤も結果的に待つ

2. AFTERのトランザクションはタイムアウトしない

–

wait_timeout の値は適⽤されない

3. AFTERのトランザクションをCOMMITした以降に開始され

た他のトランザクションは

すべてのノードでAFTER指定の

更新の

適⽤が完了するまで、待たされる

動作の様⼦ (demo)

1. （更新は順番に適⽤されていくため）AFTERで実⾏したト

ランザクション以前の更新の適⽤も結果的に待つ

2. AFTERのトランザクションはタイムアウトしない

–

wait_timeout の値は適⽤されない

3. AFTERのトランザクションをCOMMITした以降に開始され

た他のトランザクションは

すべてのノードでAFTERの更新

の

適⽤が完了するまで、待たされる

–

BEFORE 同様 wait_timeout を超えたらエラー

group_replication_consistency パラメータ

1. EVENTUAL (default)

2. BEFORE

3. BEFORE_ON_PRIMARY_FAILOVER

4. AFTER

5. BEFORE_AND_AFTER

group_replication_consistency=BEFORE_AND_AFTER

•

BEFOREとAFTERを合わせた挙動

•

最新のデータの適⽤を待ち、かつ、書いたデータが

Consistency Level まとめ

設定値

利⽤ケース

EVENTUAL（デフォ） ある程度、古いデータを読み取っても問題ない⽤途

BEFORE

⼤半のトランザクションは古いデータを読み取っても構わ

ないが、最新のデータを読み取ることを保証したい少数の

トランザクションがある場合にその指定する。バッチ実⾏

時に最新のデータで処理したいなど。

BEFORE_ON_

PRIMARY_FAILOVER

Single-Primary モードで運⽤していて、F/O発⽣時にお

いて、古いデータの読み取りリスクを排除したいケース。

AFTER

多くの更新は遅れて読み取られて問題ないが、⼀部の更新

トランザクションは以降のトランザクションで確実に読み

取って欲しいケース。ただし、後続の読取りも待たされる。

BEFORE_AND_AFTER 最新のデータを読み取りかつ、更新内容を全てのメンバー

最も楽な⽅法

•

Single Primary で Read も Write も１台に寄せる

Master

Read only

_Master

Read only

_Master

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

なくなりました

クエリキャッシュは

クエリ キャッシュとは

•

結果セット(SELECT結果) をキャッシュ

•

同じSELECT⽂が実⾏されたらキャッシュから返す

•

パラメータをONにするだけ

なくなった背景

なくなった背景

•

マッチするユースケースが限られる

•

SQLが⼀字⼀句同⼀である必要がある

•

１⾏でも更新があるとキャッシュがクリアされる

良いことだと思います

•

システムの予測可能性が上がる

•

突然、負荷がスパイクすることがなくなる

•

アプリサイドでキャッシュするほうが効果が⾼い

•

AP-DB間の通信が不要

•

より細かな条件によるキャッシュの管理

ヒント句が⽂法エラーになるので注意

<= v5.7

mysql> SELECT SQL_CACHE NOW(); +---+

| NOW() | +---+ | 2019-03-25 17:34:15 | +---+

1 row in set, 1 warning (0.00 sec)

mysql> SELECT SQL_NO_CACHE NOW(); +---+

| NOW() | +---+ | 2019-03-25 17:36:05 |

= v8.0

mysql> SELECT SQL_CACHE NOW();

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()' at line 1

mysql> SELECT SQL_NO_CACHE NOW(); +---+

| NOW() | +---+ | 2019-03-25 17:36:28 |

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

MySQL 8 まとめ

•

MySQL 8 では⼤幅に機能が強化されました

•

降順・ファンクション・INVISIBLE インデックス

•

Group Replication Consistency

•

その他 開発エンジニアに役⽴ちそうな機能 etc..

•

CTE (Common Table Expressions)

•

Window 関数

•

JSON型の改良

•

GISの改良

Oracle ACE が語る MySQL 8

•

降順インデックス

•

Invisible Index

•

スロークエリログの拡張

•

Group Replication と Consistency levels

•

さよならクエリキャッシュ

•

まとめ

2. ユーザ コミュニティ

•

MySQL Casual Slack

•

https://t.co/QobukOxvUw

•

⽇本MySQL ユーザ会 ML

3. Yahoo! JAPANでは⼀緒に仲間を募集しています︕

•

DB運⽤の経験の少ない⽅でも歓迎

•

フレックスタイム・リモートワーク 完備

•

地⽅拠点（⼤阪・名古屋・福岡）歓迎

MySQL Router

•

Group Replication の構成変更に追随して

アクセスを振り分けてくれる

•

プロキシ的な存在

MySQL

Router