MySQL 8.0 の パラメータ

Transcript

1. MySQL 8.0 の パラメータ 2020/01/28 MySQL Casual Talks vol.13
3. ⾃⼰紹介 3 • 三⾕ 智史（Twitter: @mita2） • MySQL DBA @ どっかのポータルサイト • どきどきブログを書いてます https://mita2db.hateblo.jp/
5. 5 MySQL 8.0⽤の 秘伝のタレ できましたか︖
6. MySQL のパラメータ数 473 515 567 0 100 200 300 400 500 600 v5.6 v5.7 v8.0 パラメータ数 パラメータ数 6
7. MySQL Parameters • https://mysql-params.tmtms.net/ • @tmtms さん作成 • パラメータだけでなく、CharsetやStatusもサポート 7
8. お品書き 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
9. お品書き 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
10. 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.
11. オレの秘伝のタレ • デフォルト値 • log_timestamps = UTC • オレの秘伝のタレ • log_timestamps = SYSTEM • 理由 • ⽇本で、働いて、暮らしてますんで・・・ 11
12. お品書き 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
13. • 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)
14. • information_schema_stats_expiry • 統計情報をキャッシュを保持しておく期間 • デフォルトは１⽇（86400秒） • キャッシュがexpireしたら、InnoDBに 「最新の統計情報」を取りに⾏く 14
15. オレの秘伝のタレ • デフォルト値 • 86400 • オレの秘伝のタレ • information_schema_stats_expiry = 0 • 理由 • ⽇々の運⽤で考慮しなければならない点を減らしたい • （たぶん）無効にして今までより悪くなることはないはず・・・ 15
16. お品書き 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
17. 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.
18. sql_require_primary_key • 主キーがないとレプリが遅い • 100万⾏をアップデート 18 binlog_format 最⼤ Seconds_Behind_Master 主キーあり ROW 28 秒 主キーなし 1147 秒 主キーあり STATEMENT 16 秒 主キーなし 12 秒
19. sql_require_primary_key • デフォルト値 • OFF （無効） • 設定する値 • sql_require_primary_key = on • 理由 • レプリ遅延のリスクの低減 • Group Replication への布⽯ • SQLのパフォーマンス改善 19
20. お品書き 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
21. 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
22. 22 Because LOAD DATA INFILE LOCAL is チョット危険 x 2
23. 1. DBに保存されている情報だけでなく・・・ 23 DB サーバ クライ アント 任意の SQLが実⾏可能 な環境 LOAD DATA INFILE LOCAL ‘/home/mita2/secret.csv’ INTO tmp SELECT * FROM tmp
24. 2. LOAD DATA INFILE LOCALの仕組み 24 DB サーバ クライ アント LOAD DATA INFILE LOCAL ‘/tmp/file.csv’ /tmp/file.csv の内容を送って︕ file.csv TBL
25. 2. LOAD DATA INFILE LOCALの仕組み 25 DB サーバ クライ アント LOAD DATA INFILE LOCAL ‘/tmp/file.csv’ /tmp/secret.txt の内容を送って︕ 指定されたも のと異なる ファイル mysqld 改ざん
26. チョット危険 • LOAD DATA INFILE LOCAL そのものに脆弱性 は ない • 万が⼀、他の脆弱性があった場合、 攻撃可能範囲が広がる可能性がある 26
27. 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
28. オレの秘伝のタレ • デフォルト値 • local-infile = OFF • オレの秘伝のタレ • local-infile = OFF • アップグレードする場合は、必要に応じてON • 理由 • セキュリティ強化 • ⼤きなトランザクションは減らしたい。レプリ遅延回避。 28
29. オレの秘伝のタレ • デフォルト値 • local-infile = OFF • オレの秘伝のタレ • local-infile = OFF • アップグレードする場合は、必要に応じてON • 理由 • セキュリティ強化 • ⼤きなトランザクションは減らしたい。レプリ遅延回避。 29 SHOW GLOBAL STATUS LIKE 'Com_load';
30. お品書き 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
31. 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).
32. 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);
33. 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
34. 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
35. オレの秘伝のタレ • デフォルト値 • explicit-defaults-for-timestamp = ON • オレの秘伝のタレ • explicit-defaults-for-timestamp = ON（変更せず） • 理由 • 将来に備えて 35
36. お品書き 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
37. innodb-buffer-pool-in-core-file • バッファプールの内容をcoreに含めるかどうか • デフォルトはON（従来と変わらず） • ※ デフォルトでは core 出⼒⾃体がOFF 37
38. 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] <snip>
39. 課題 • バッファプール⼤盛り → メモリサイズ⼤ → core ファイル⼤ → 落ちては復活を繰り返し・・・ → 障害時にディスクあふれ 😅 • 外部（商⽤サポート）に渡せない • バッファプールにはデータがキャッシュされている • セキュリティ⾯ 39
40. 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
41. オレの秘伝のタレ • デフォルト値 • innodb-buffer-pool-in-core-file = ON • オレの秘伝のタレ • innodb-buffer-pool-in-core-file = OFF • 理由 • バッファプールがcoreに含まれてないと 解析できないケースはきっとレア 41
42. お品書き 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
43. 接続あふれの挙動おさらい • max_connections を超えると、 too many connections エラー • SUPER 権限があれば、max_connections を超えて接続可 • ただし、１本だけ 43
44. 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>
45. 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)
46. 接続できる・できない 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 に ソケット接続を許すオプションほしかった・・・
47. オレの秘伝のタレ • デフォルト値 • admin_address = • オレの秘伝のタレ • admin_address = 127.0.0.1 • 理由 • Too many conn 時の障害解析をスムーズに • ローカルに絞って安全に 47
48. 48 まとめ
49. 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 パラメータまとめ
50. MySQL 8.0 パラメータまとめ • ベストプラクティスを強制できる設定が増えて嬉しい • sql-require-primary-key, local-infile など • とみたさんは、すごい⼈ 50
51. 51 Enjoy MySQL !
52. 52 お知らせ
53. MyNA 20周年イベント • 2020年3⽉4⽇(⽔) 19:00〜 • 場所︓ SCSK様（豊洲） • テーマ︓ 「ユーザ会と MySQLの20年」 • 内容︓ 飲⾷付きの交流を含むセミナー形式 53
54. その他 いろいろ 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