Post on 24-Jan-2017
光のMySQL 5.7たまにはMySQL 5.7のいいところを話します
2015/09/11
yoku0825YAPC::Asia リジェクトコン
Do̲you̲love̲MySQL57?
1/85
呪いのLTから半⽉
2/85
解呪の進捗どうですか
3/85
光のMySQL 5.7
4/85
闇の話が聞きたい⼈︖
5/85
リジェクトされたトーク
6/85
もういいよね︖
7/85
\こんにちは/
yoku0825@とある企業のDBAオラクれない-ポスグれない-マイエスキューエる-
家に帰ると妻の夫-せがれの⽗-ムスメの⽗-
Twitter: @yoku0825Blog: ⽇々の覚書MyNA ML: ⽇本MySQLユーザ会
8/85
※個⼈の⾒解であり、所属する組織あるいは所属しない組織の意⾒を代表するわけがありません
9/85
ちょっと待て︕ そのカラムNOT
NULL制約ついてる︕︖
10/85
( ゚д゚) ハッ11/85
※個⼈の⾒解であり、所属する組織あるいは所属しない組織あるいは
NULLの意⾒を代表するわけがありません
12/85
MySQL 5.7について
2013年4⽉に5.7.1-m11(Development Milestone Release, ベータ版)2015年8⽉に5.7.8-rc2(Realease Candidate, リリース候補版)2015/09/08現在、GA(General Available, ⼀般公開版)はまだ5.6のGA時点で「Oracleとしては18〜24か⽉で次のメジャーバージョンをリリースする予定」と⾔っていたので、結構ズレこんでいるのは確か
13/85
MySQL 5.7の新機能
MySQL :: MySQL 5.7 Reference Manual :: 1.4 What Is New in MySQL 5.7MySQL :: MySQL 5.7 Release NotesComplete list of new features in MySQL 5.7MySQL 5.7の新機能完全リスト Yakst⽇々の覚書: 5.7
14/85
頑張って訳したので、yakst読んでくれればいいと思
います15/85
show_compatibility_56= OFF問題 解決
p̲s.global̲status, p̲s.session̲status, p̲s.global̲variables, p̲s.session̲variablesはSELECT権限なしでSELECT可能に(i̲sと同じ扱い)まあまあ悪くない解決策だと思うMySQL Bugs: #77732: REGRESSION: replication fails for insufficient privileges
16/85
5.6までのGTID
今までは2択&オンライン変更不可能enforce̲gtid̲consistencyもOFF/ONの2択でオンライン変更不可能
master\slave gtid-mode= OFF gtid-mode= ON
gtid-mode= OFF ○ ×
gtid-mode= ON × ○
17/85
5.7のGTID
master\slave OFF OFF̲PERMISSIVE ON̲PERMISSIVE ON
OFF ○ ○ ○ ×
OFF̲PERMISSIVE
○ ○ ○ ×
ON̲PERMISSIVE
× ○ ○ ○
ON × ○ ○ ○
18/85
5.7のenforce̲gtid̲consistency
5.6はこれも起動時パラメーターだったmysql57> SELECT @@enforce_gtid_consistency;+----------------------------+| @@enforce_gtid_consistency |+----------------------------+| WARN |+----------------------------+1 row in set (0.00 sec)
mysql57> CREATE TABLE t2 AS SELECT * FROM t1;Query OK, 1 row affected, 1 warning (0.04 sec)Records: 1 Duplicates: 0 Warnings: 1
mysql57> SHOW WARNINGS;+---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1786 | Statement violates GTID consistency: CREATE TABLE ... SELECT. |+---------+------+---------------------------------------------------------------+1 row in set (0.00 sec)
ON̲PERMISSIVE以上のgtid̲modeではエラーになるステートメントがワーニングで出⼒される。
19/85
使い道
MHA for MySQLがrelay_log_info_repository= TABLE(クラッシュセーフスレーブの条件)で上⼿く動かなかったのでこれでMySQLファブリッ {⽂字はここで途切れている}
20/85
マルチソースレプリケーション
Multi-Threaded Slaveとは別物1つのI/Oスレッドは相変わらず1つのマスターにしか接続しない今までのレプリケーション関連のコマンドの最後にFOR
CHANNEL 'channel_name'
mysql57> SHOW SLAVE STATUS FOR CHANNEL 'node2'\G..
21/85
シングルスレッドなレプリケーション
22/85
マルチスレッドレプリケーション(MTS)
23/85
マルチソースレプリケーション
24/85
使い道
これでETLかっ⾶ばして集計DB作れるreplicate_rewrite_dbと合わせてインスタンス集約無双マルチソースとMTSの併⽤は今のところ不可能
25/85
ダイナミックレプリケーションフィルター
mysql57> CHANGE REPLICATION FILTER replicate_ignore_db= (mysql, performance_schema);ERROR 3017 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first
mysql57> STOP SLAVE sql_thread;mysql57> CHANGE REPLICATION FILTER replicate_ignore_db= (mysql, performance_schema);Query OK, 0 rows affected (0.00 sec)
mysql57> START SLAVE sql_thread;
26/85
結局SQLスレッドは⽌めるんかい︕︕
27/85
あと、引数が⾏リテラル(あんまり馴染みなさそう)
28/85
ダイナミックレプリケーションフィルターその2
mysql57> STOP SLAVE sql_thread;mysql57> CHANGE REPLICATION FILTER replicate_rewrite_db= ((d1, d2), (d3, d4));Query OK, 0 rows affected (0.00 sec)
mysql57> START SLAVE sql_thread;
--replicate-rewrite-dbのもともとの記法は”d1->d2”全然違うじゃねーかオプション記法だと2⾏書かないといけなかったのは⾏リテラルの⾏リテラルで渡す
29/85
その他タレ的な
rpl̲semi̲sync̲master̲wait̲point= {AFTER̲SYNC|AFTER̲COMMIT}rpl̲semi̲sync̲master̲wait̲for̲slave̲count= {1|N}slave̲parallel̲type= {DATABASE|LOGICAL̲CLOCK}slave̲parallel̲workers= {0|N}
30/85
InnoDBのオンラインバッファプールリサイズ
“オンラインって何だったんだろう”状態は変わらないSET GLOBAL innodb_buffer_pool_load_now= 1より遥かに⾼速そういうもんだと割り切って使えば良い機能
31/85
トランザクションの並列性能向上
5.5までトランザクションが開始されるたび、トランザクションリストに登録
-
5.6START TRANSACTION READ ONLYが指定された時のみトランザクションリストに登録しない
-
それ以外の時はトランザクションリストに登録-5.7全てのトランザクションの開始時にトランザクションリストに登録 しない
-
更新ステートメントが発⾏された時にトランザクションリストに登録
-32/85
InnoDBテンポラリーテーブル
バッファプールがすさまじく余っている場合は速い…のかな個⼈的にはMyISAMでええやんって感じInnoDBのまま使うのであればinnodb_tmp_data_file_path=ibtmp1:1Gとかautoextendじゃないようにするの推奨
33/85
ALTER TABLE .. RENAME INDEX .. TO ..
俺得メタデータの書き換えだけで対応インデックスの名前はidx_col1_col2_col3派
mysql57> ALTER TABLE sbtest1 RENAME INDEX k_1 TO idx_k;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
34/85
innodb_numa_interleave
さよならSwap Insanity
35/85
Transparent Page Compression
今までの圧縮はソフトウェア(InnoDB内)でページまるごとzlib圧縮ページの中のデータ部分だけ圧縮ページ内の空き領域の圧縮はファイルシステムとデバイスに任せるzlibとlz4対応対応ディストリビューションに限定はあるもののThird day with InnoDB transparent page compression
36/85
innodb_log_write_ahead_size
InnoDBログのread-on-write問題に対応するInnoDBログを先読み
37/85
InnoDB FTS
N-gramMeCabまさか⽇本語しか対応してないMeCabを⼊れてくるとは思わなかった
mysql57> CREATE TABLE t1 (num serial, val varchar(32), FULLTEXT KEY(val) WITH PARSER mecab);Query OK, 0 rows affected (0.16 sec)
38/85
InnoDB ページサイズ
4k (As of 5.6)8k (As of 5.6)16k32k (new!!)64k (new!!)
39/85
UNDO log truncation
前提条件としてinnodb-undo-tablespaces= 2が必要innodb-undo-tablespacesはibdata1を作成時に指定されていないといけないSET GLOBAL innodb_undo_log_truncate= 1で有効化ちゃんとオンラインでも常時有効化は無理(負荷かなりある)
mysql> SHOW GLOBAL VARIABLES LIKE '%undo%';+--------------------------+----------+| Variable_name | Value |+--------------------------+----------+| innodb_max_undo_log_size | 10485760 || innodb_undo_directory | . || innodb_undo_log_truncate | ON || innodb_undo_logs | 128 || innodb_undo_tablespaces | 2 |+--------------------------+----------+5 rows in set (0.00 sec)
40/85
変更されたデフォルト
innodb_file_format= Barracudainnodb_large_prefix= 1
innodb_default_row_format= Dynamic (5.7.9なので未確認)kamipo時代の到来
41/85
オプティマイザー関連
“賢くなった”の⼤半は5.6なんだけれど、それでももうちょっと賢くなってる他のRDBMSにようやく追いついてきたと⾔った⽅が正しいかもUNION ALLがテンポラリーテーブルを作成しないvarcharでソートしていた時に実は無駄に使っていたソートバッファを削減○racleっぽいヒント句の追加
42/85
サーバーサイドステートメントタイムアウト
5.7.7までと5.7.8以降で構⽂が変わっている単位はミリ秒セッション変数(暗黙のデフォルトとしてのグローバル変数)でも設定可能
mysql57> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1;ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql57> SET SESSION max_execution_time= 1000;Query OK, 0 rows affected (0.00 sec)
mysql57> SELECT * FROM t1;ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
43/85
generated column
あるカラムの値を演算した結果を格納する列直接値は格納できないVIRTUAL, STOREDの⼆種類どちらのタイプもセカンダリーインデックスを作成可能
44/85
サンプルテーブル
mysql57> SELECT tbl.name, idx.name, idx.index_id FROM information_schema.INNODB_SYS_INDEXES AS idx INNER JOIN information_schema.INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';+-------+---------+----------+| name | name | index_id |+-------+---------+----------+| d1/t1 | PRIMARY | 75 || d1/t1 | md5 | 76 |+-------+---------+----------+2 rows in set (0.00 sec)
45/85
STORED generated column
mysql57> ALTER TABLE t1 ADD stored_sha256 varchar(64) AS (sha2(num, 256)) STORED;Query OK, 100000 rows affected (1.33 sec)Records: 100000 Duplicates: 0 Warnings: 0
46/85
index with STORED generated column
mysql57> ALTER TABLE t1 ADD KEY (stored_sha256);Query OK, 0 rows affected (0.44 sec)Records: 0 Duplicates: 0 Warnings: 0
47/85
VIRTUAL generated column
mysql57> ALTER TABLE t1 ADD virtual_sha256 varchar(64) AS (sha2(num, 256)) VIRTUAL;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
48/85
index with VIRTUAL generated column
mysql57> ALTER TABLE t1 ADD KEY (virtual_sha256);Query OK, 0 rows affected (0.44 sec)Records: 0 Duplicates: 0 Warnings: 0
49/85
使い道
関数インデックスSTOREDはALTERする時に値が計算され、VIRTUALはSELECTされた時に計算するWHERE age >= 20なんて茨の道も、is_age_over_20 tinyint
AS (age >= 20)なんて列を⽣成してインデックスを貼っておけばイケるNOT NULL制約とIF(cond, TRUE, NULL)と組み合わせると、似非CHECK制約が出来上がる
50/85
generated columnに対してmysqldump
結果のダンプファイルに⽣成列の値も書きこんじゃう⽣成列には直接値を詰め込めないのでINSERTステートメントが転ける残念、今後に期待
51/85
mysql_no_login認証プラグイン
いわゆる/bin/nologin状態SQL SECURITY= DEFINERと組み合わせて使う
mysql57> INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';Query OK, 0 rows affected (0.00 sec)
mysql57> CREATE USER nologin IDENTIFIED WITH mysql_no_login;Query OK, 0 rows affected (0.01 sec)
$ mysql57 -unologinERROR 1045 (28000): Access denied for user 'nologin'@'localhost' (using password: NO)
52/85
1テーブル複数トリガー
闇が⾒える
mysql57> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;Query OK, 0 rows affected (0.01 sec)
mysql57> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;Query OK, 0 rows affected (0.00 sec)
mysql57> INSERT INTO t1 VALUES (1, 'one');Query OK, 1 row affected (0.00 sec)
mysql57> SELECT * FROM t2;+-----+--------------------+| num | val |+-----+--------------------+| 1 | 残念だったな |+-----+--------------------+1 row in set (0.00 sec)
53/85
Partitioned InnoDB
インデックスコンディションプッシュダウンできるようになったFLUSH TABLE .. FOR EXPORTできるようになったFOREIGN KEYは5.8以降に持ち越し
54/85
SYSスキーマ
旧ps̲helperperformance̲schemaのVIEW、ファンクションとして動作
mysql57> SHOW TABLES FROM sys;mysql57> SELECT routine_name, routine_type, routine_comment FROM information_schema.routines WHERE routine_schema= 'sys'\G
55/85
mysql p ump
--defer-table-indexesがデフォルト--compress-outputオプション(lz4, zlib)今のところ、--default-parallelismが2以上だと(=パラレルダンプ, 暗黙のデフォルト2)--single-transactionが効かないレプリケーション関連の情報は--master-info-repository=
TABLE, --relay-log-info-repository= TABLEが期待されていて、GTIDもmysql.gtid̲executedに依存している。--userでmysql.*の中⾝をGRANTステートメントで出してくれる--watch-progress便利
56/85
--log-syslog
エラーログをloggerで出⼒する基本的には両⽅書くみたいだけどmysqladmin debugの出⼒は記録されなかった
Sep 11 16:51:48 host-133-130-53-106 mysqld[903]: Event Scheduler: Loaded 0 eventsSep 11 16:51:48 host-133-130-53-106 mysqld[903]: /usr/mysql/5.7.8/bin/mysqld: ready for connections.#012Version: '5.7.8-rc-log' socket: '/usr/mysql/5.7.8/data/mysql.sock' port: 64057 Source distributionSep 11 16:52:41 host-133-130-53-106 mysqld[903]: Access denied for user 'aaa'@'localhost' (using password: NO)
57/85
offline_mode
クエリーが終わったものから順番に切断(server has gone awayが返る)SUPER権限以外はそれ以降接続できなくなる
mysql57> SET GLOBAL offline_mode= 1;Query OK, 0 rows affected (0.00 sec)
mysql57> show databases;ERROR 2006 (HY000): MySQL server has gone away
$ mysql57 -uyoku0825ERROR 3032 (HY000): The server is currently in offline mode
58/85
GET̲LOCKの複数ロック獲得
複数取れるってことは、デッドロックの可能性があるってことだデッドロックしてもInnoDBのように他のロックをリリースはしてくれないから、コネクションを切断するか⾃分でrelease_lockかrelease_all_locksしてやらないとダメ
ERROR 3058 (HY000): Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
59/85
--require-secure-transport
SSL接続を強制ソケット接続には関係なしオンライン変更可能
mysql57> SET GLOBAL require_secure_transport= 1;Query OK, 0 rows affected (0.00 sec)
$ mysql57 --protocol=tcpERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
60/85
mysql_ssl_rsa_setup
コマンド1つでオレオレ証明書を⼀括で作ってくれる出⼒先は--datadirなので、他のところに作るなら--datadir
オプションを渡してやる
$ /usr/mysql/5.7.8/bin/mysql_ssl_rsa_setup --datadir=./$ lltotal 32-rw------- 1 yoku0825 yoku0825 1675 Sep 11 15:37 ca-key.pem-rw-r--r-- 1 yoku0825 yoku0825 1078 Sep 11 15:37 ca.pem-rw-r--r-- 1 yoku0825 yoku0825 1086 Sep 11 15:37 client-cert.pem-rw------- 1 yoku0825 yoku0825 1679 Sep 11 15:37 client-key.pem-rw------- 1 yoku0825 yoku0825 1679 Sep 11 15:37 private_key.pem-rw-r--r-- 1 yoku0825 yoku0825 451 Sep 11 15:37 public_key.pem-rw-r--r-- 1 yoku0825 yoku0825 1086 Sep 11 15:37 server-cert.pem-rw------- 1 yoku0825 yoku0825 1679 Sep 11 15:37 server-key.pem
61/85
Query Rewrite Plugin
MySQL Enterprise Firewallはたぶんこれ使ってるパーサーを通す前とパーサーを通してオプティマイザーに渡る前にフックがある
62/85
MySQL Enterprise Firewall
MySQL :: MySQL Enterprise Firewall
63/85
JSON data type
型制約違反の時のエラーコードがちょっと想定外
mysql57> CREATE TABLE t1 (num serial);Query OK, 0 rows affected (0.02 sec)
mysql57> INSERT INTO t1 VALUES ('one');ERROR 1366 (HY000): Incorrect integer value: 'one' for column 'num' at row 1
mysql57> CREATE TABLE t2 (num serial, j json);Query OK, 0 rows affected (0.02 sec)
mysql57> INSERT INTO t2 VALUES (1, 'aaa');ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value (or column) 'aaa'.
64/85
InnoDB Online DDL
varcharの拡⼤ができるようになったのは夢OPTIMIZE TABLEができるようになったのが嬉しい
65/85
以上、MySQL 5.7の光でした
66/85
Questions and/or
Suggestions?67/85
( ゚д゚)68/85
( ゚д゚ )69/85
(゚д゚ )70/85
( ゚д゚) クワッ
https://vicky2183.wordpress.com/2010/05/14/postgresql-vs-mysql/ 71/85
では72/85
前回のLTから
73/85
新たに発⾒された
74/85
といっても前回から1つもバージョン 上がってません
ので75/85
5.7.9 のチェンジログをちょっと紹
介(そんなに闇じゃないです)
76/85
/var/lib/mysqlのパーミッション変更(rpm)
755から751chefな⼈は気を付けて(︖)
77/85
libmysqlclient̲r.soの削除
かつての”thread-safe-client”ライブラリーlibmysqlclient.soがスレッドセーフになる前の遺物5.5で削除、libmysqlclient.soへのシンボリックリンクになっているリンクしてる何かを使ってる⼈は気を付けて古いmysql2が使ってたのは⾒つけたけど今は使ってないぽい
-
78/85
mysql_install_dbの削除
5.5までBorn-Shell
5.6Perl
5.7.8までC++
5.7.9からmysqld --initialize
79/85
innodb_default_row_format
The innodb_file_format configuration option is ignored if a table is created or altered to use ROW_FORMAT=DYNAMIC. For example, innodb_file_format=Antelope is ignored if you create a table with a DYNAMIC row format. The Barracuda file format is used instead.
80/85
created or altered
81/85
ALTER TABLE .. ADD COLUMN ..でオンラインだけどテーブル再編成…︖
情報が錯綜してるMySQL Bugs: #78347: innodb̲default̲row̲format: Undesireable new behaviour「再編成は起こらない、リリースノートの間違いだ」って論調︖中の⼈でも⾔ってることがちらほら違う。まだ様⼦⾒。
82/85
SHUTDOWNステートメント
あっ、これMariaDBで⾒たやつだ︕SQLインジェクションでサーバーが合法的に落とせるそういえばSQLインジェクションノーガード戦法のHTTPぷらぎn
83/85
RCって何だったんだ
©RKajiyama84/85
Questions and/or
Suggestions?85/85