MySQL 8.0 のパラメータ

Slide 1

Slide 1 text

MySQL 8.0 のパラメータ 2020/01/28 MySQL Casual Talks vol.13

Slide 2

Slide 2 text

Slide 3

Slide 3 text

⾃⼰紹介 3 • 三⾕智史（Twitter: @mita2） • MySQL DBA @ どっかのポータルサイト • どきどきブログを書いてます https://mita2db.hateblo.jp/

Slide 4

Slide 4 text

Slide 5

Slide 5 text

5 MySQL 8.0⽤の秘伝のタレできましたか︖

Slide 6

Slide 6 text

MySQL のパラメータ数 473 515 567 0 100 200 300 400 500 600 v5.6 v5.7 v8.0 パラメータ数パラメータ数 6

Slide 7

Slide 7 text

MySQL Parameters • https://mysql-params.tmtms.net/ • @tmtms さん作成 • パラメータだけでなく、CharsetやStatusもサポート 7

Slide 8

Slide 8 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 8

Slide 9

Slide 9 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 9

Slide 10

Slide 10 text

log_timestamps • デフォルトではUTCでログが出⼒されるように 10 2020-01-19T01:51:42.137957Z 0 [System] [MY-010229] [Server] Starting crash recovery... 2020-01-19T01:51:42.152569Z 0 [System] [MY-010232] [Server] Crash recovery finished. 2020-01-19T01:51:42.236273Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

Slide 11

Slide 11 text

オレの秘伝のタレ • デフォルト値 • log_timestamps = UTC • オレの秘伝のタレ • log_timestamps = SYSTEM • 理由 • ⽇本で、働いて、暮らしてますんで・・・ 11

Slide 12

Slide 12 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 12

Slide 13

Slide 13 text

• information_schema_stats_expiry 13 mysql> DELETE FROM sbtest.sbtest1 ; Query OK, 1000000 rows affected (9.53 sec) mysql> SELECT TABLE_ROWS FROM information_schema.tables WHERE table_name = 'sbtest1'; +------------+ | TABLE_ROWS | +------------+ | 986400 | +------------+ 1 row in set (0.01 sec)

Slide 14

Slide 14 text

• information_schema_stats_expiry • 統計情報をキャッシュを保持しておく期間 • デフォルトは１⽇（86400秒） • キャッシュがexpireしたら、InnoDBに「最新の統計情報」を取りに⾏く 14

Slide 15

Slide 15 text

オレの秘伝のタレ • デフォルト値 • 86400 • オレの秘伝のタレ • information_schema_stats_expiry = 0 • 理由 • ⽇々の運⽤で考慮しなければならない点を減らしたい • （たぶん）無効にして今までより悪くなることはないはず・・・ 15

Slide 16

Slide 16 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 16

Slide 17

Slide 17 text

sql_require_primary_key • 主キー⼤主きー❤ @ lhfukamachi https://www.slideshare.net/hidemifukamachi/sql-require-primarykey • 有効にすると主キーを必須とする 17 mysql> CREATE TABLE without_pk (col1 VARCHAR(10)); ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Slide 18

Slide 18 text

sql_require_primary_key • 主キーがないとレプリが遅い • 100万⾏をアップデート 18 binlog_format 最⼤ Seconds_Behind_Master 主キーあり ROW 28 秒主キーなし 1147 秒主キーあり STATEMENT 16 秒主キーなし 12 秒

Slide 19

Slide 19 text

sql_require_primary_key • デフォルト値 • OFF （無効） • 設定する値 • sql_require_primary_key = on • 理由 • レプリ遅延のリスクの低減 • Group Replication への布⽯ • SQLのパフォーマンス改善 19

Slide 20

Slide 20 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 20

Slide 21

Slide 21 text

local-infile • MySQL 8.0 で LOAD DATA LOCAL INFILE は無効化 • （LOCAL句なしのLD は影響なし 🙆） 21 mysql> LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tbl SET name=@1, created_at=@2; ERROR 1148 (42000): The used command is not allowed with this MySQL version ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

Slide 22

Slide 22 text

22 Because LOAD DATA INFILE LOCAL is チョット危険 x 2

Slide 23

Slide 23 text

1. DBに保存されている情報だけでなく・・・ 23 DB サーバクライアント任意の SQLが実⾏可能な環境 LOAD DATA INFILE LOCAL ‘/home/mita2/secret.csv’ INTO tmp SELECT * FROM tmp

Slide 24

Slide 24 text

2. LOAD DATA INFILE LOCALの仕組み 24 DB サーバクライアント LOAD DATA INFILE LOCAL ‘/tmp/file.csv’ /tmp/file.csv の内容を送って︕ ﬁle.csv TBL

Slide 25

Slide 25 text

2. LOAD DATA INFILE LOCALの仕組み 25 DB サーバクライアント LOAD DATA INFILE LOCAL ‘/tmp/file.csv’ /tmp/secret.txt の内容を送って︕ 指定されたものと異なるファイル mysqld 改ざん

Slide 26

Slide 26 text

チョット危険 • LOAD DATA INFILE LOCAL そのものに脆弱性はない • 万が⼀、他の脆弱性があった場合、攻撃可能範囲が広がる可能性がある 26

Slide 27

Slide 27 text

MySQL 8.0 の挙動 • mysql コマンドにも同様のオプション 27 $ mysql mysql> LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tbl; ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides $ mysql –-infile-local=1 mysql> LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tbl; Query OK, 0 rows affected, 2 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 2 Warnings: 2

Slide 28

Slide 28 text

オレの秘伝のタレ • デフォルト値 • local-infile = OFF • オレの秘伝のタレ • local-infile = OFF • アップグレードする場合は、必要に応じてON • 理由 • セキュリティ強化 • ⼤きなトランザクションは減らしたい。レプリ遅延回避。 28

Slide 29

Slide 29 text

オレの秘伝のタレ • デフォルト値 • local-inﬁle = OFF • オレの秘伝のタレ • local-inﬁle = OFF • アップグレードする場合は、必要に応じてON • 理由 • セキュリティ強化 • ⼤きなトランザクションは減らしたい。レプリ遅延回避。 29 SHOW GLOBAL STATUS LIKE 'Com_load';

Slide 30

Slide 30 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 30

Slide 31

Slide 31 text

explicit-defaults-for-timestamp • TIMESTAMP型のデフォルトの振る舞いをSQL標準に即した挙動にする • 8.0 でデフォルトで有効になりました 31 v.5.6 v.5.7 v.8.0 OFF OFF ON [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

Slide 32

Slide 32 text

DDLの挙動 • DDL • explicit_defaults_for_timestamp = OFF （〜5.7） • explicit_defaults_for_timestamp = ON （8.0〜） 32 Create Table: CREATE TABLE `t_on` ( `ts` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Create Table: CREATE TABLE `t_off` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 〜 mysql> CREATE TABLE t_on (ts TIMESTAMP);

Slide 33

Slide 33 text

explicit-defaults-for-timestamp • テーブル定義 • explicit_defaults_for_timestamp = OFF （〜5.7） • explicit_defaults_for_timestamp = ON （8.0〜） • ） 33 mysql> CREATE TABLE `t1` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); mysql> INSERT INTO t1 VALUES(NULL); SELECT * FROM t1 ¥G *************************** 1. row *************************** ts: 2020-01-25 11:20:07 mysql> INSERT INTO t1 VALUES(NULL); ERROR 1048 (23000): Column 'ts' cannot be null

Slide 34

Slide 34 text

explicit-defaults-for-timestamp • やるべきこと • DDL で NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP を明⽰的に指定 • DML で NULL を使わない • 💔 INSERT INTO t1 (pk, ts) VALUES (1, NULL); • 🆗 INSERT INTO t1 (pk, ts) VALUES (1, NOW()) • 🆗 INSERT INTO t1 (pk, ts) VALUES (1, DEFAULT) • 🆗 INSERT INTO t1 (pk) VALUES (1) 34

Slide 35

Slide 35 text

オレの秘伝のタレ • デフォルト値 • explicit-defaults-for-timestamp = ON • オレの秘伝のタレ • explicit-defaults-for-timestamp = ON（変更せず） • 理由 • 将来に備えて 35

Slide 36

Slide 36 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 36

Slide 37

Slide 37 text

innodb-buffer-pool-in-core-file • バッファプールの内容をcoreに含めるかどうか • デフォルトはON（従来と変わらず） • ※ デフォルトでは core 出⼒⾃体がOFF 37

Slide 38

Slide 38 text

core ファイルとは • 異常終了した際にメモリイメージをファイルにダンプ • 障害の解析に利⽤ 38 01:53:22 UTC - mysqld got signal 11 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x46000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x43) [0x4402af3]

Slide 39

Slide 39 text

課題 • バッファプール⼤盛り → メモリサイズ⼤ → core ファイル⼤ → 落ちては復活を繰り返し・・・ → 障害時にディスクあふれ 😅 • 外部（商⽤サポート）に渡せない • バッファプールにはデータがキャッシュされている • セキュリティ⾯ 39

Slide 40

Slide 40 text

innodb-buffer-pool-in-core-file • ON（デフォルト） • OFF 40 $ ls -alh *core* -rw------- 1 mysql mysql 5.0G Nov 4 15:19 mysqld.core.4947 $ ls -alh *core* -rw------- 1 mysql mysql 892M Nov 4 15:22 mysqld.core.5218

Slide 41

Slide 41 text

オレの秘伝のタレ • デフォルト値 • innodb-buffer-pool-in-core-file = ON • オレの秘伝のタレ • innodb-buffer-pool-in-core-file = OFF • 理由 • バッファプールがcoreに含まれてないと解析できないケースはきっとレア 41

Slide 42

Slide 42 text

お品書き 1. log-timestamps 2. information-schema-stats-expiry 3. sql-require-primary-key 4. local-infile 5. explicit-defaults-for-timestamp 6. innodb-buffer-pool-in-core-file 7. admin-address 42

Slide 43

Slide 43 text

接続あふれの挙動おさらい • max_connections を超えると、 too many connections エラー • SUPER 権限があれば、max_connections を超えて接続可 • ただし、１本だけ 43

Slide 44

Slide 44 text

admin_address / admin_port • max_conn の影響を受けないポート • 管理者⽤ 44 $ mysql -P3307 -uappuser -h db01 ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation $ mysql -P3306 -uroot -h db01 ERROR 1040 (HY000): Too many connections $ mysql -P3307 -uroot -h db01 mysql>

Slide 45

Slide 45 text

admin_address はTCP接続のみ • skip_name_resolve = OFF • skip_name_resolve = ON 45 $ mysql -P3307 -uroot -h 127.0.0.1 mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ $ mysql -P3307 -uroot -h 127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1’ (using password: YES)

Slide 46

Slide 46 text

接続できる・できない GRANTしているユーザソケット接続 TCP接続 (-h 127.0.0.1) skip_name_resolve = OFF root@ʼlocalhost` OK OK root@ʼ127.0.01` NG OK skip_name_resolve = ON root@ʼlocalhost` OK NG root@ʼ127.0.0.1` NG OK 46 • skip_name_resolve=ONの場合admin_addressで接続する⽤のユーザが必要 • admin_address にソケット接続を許すオプションほしかった・・・

Slide 47

Slide 47 text

オレの秘伝のタレ • デフォルト値 • admin_address = • オレの秘伝のタレ • admin_address = 127.0.0.1 • 理由 • Too many conn 時の障害解析をスムーズに • ローカルに絞って安全に 47

Slide 48

Slide 48 text

48 まとめ

Slide 49

Slide 49 text

49 パラメータ名デフォルト俺のタレ log-timestamps UTC SYSTEM information-schema-stats-expiry 86400 0 sql-require-primary-key OFF ON local-infile OFF OFF explicit_defaults_for_timestamp ON ON innodb-buffer-pool-in-core-file ON OFF admin_address - 127.0.0.1 MySQL 8.0 パラメータまとめ

Slide 50

Slide 50 text

MySQL 8.0 パラメータまとめ • ベストプラクティスを強制できる設定が増えて嬉しい • sql-require-primary-key, local-infile など • とみたさんは、すごい⼈ 50

Slide 51

Slide 51 text

51 Enjoy MySQL !

Slide 52

Slide 52 text

52 お知らせ

Slide 53

Slide 53 text

MyNA 20周年イベント • 2020年3⽉4⽇(⽔) 19:00〜 • 場所︓ SCSK様（豊洲） • テーマ︓ 「ユーザ会と MySQLの20年」 • 内容︓ 飲⾷付きの交流を含むセミナー形式 53

Slide 54

Slide 54 text

その他いろいろ 54 パラメータ名俺のタレ event_scheduler 8.0からデフォルトでONに。Event Scheduler を使わせたくないなら、OFFにする（もしくは、EVENT権限を付与しない）。 binlog-row-metadata Binlog にカラム名が記載される。他システムでのデータ連携するときは有効かすると便利かも binlog_expire_logs_seconds expire_logs_days は deprecated です log_slow_extra ONにする。Handler_xxx の回数がスローログに落ちる。多少チューニングの参考になるかもしれない。 collation_server utf8mb4_general_ci