諸事情あって、blogger から はてなブログに引越ししました。 MySQL Geek な人たち、はてなブログ民が多い!
MySQL の 巨大な core ファイル 対策(MySQL 8.0〜)
昨日記事では core ファイルを圧縮する方法を紹介しました。 昨日の記事では、OSの機能(kernel.core_pattern)を使って対応する方法を紹介しました。
MySQL 8.0からは、MySQLの機能(設定)を利用して、巨大なcoreファイルを抑制することができます。
https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-in-core-file.html
一般的な運用では、MySQLのメモリのうち大半、InnoDBバッファプールが占めます。 MySQL 8.0.14 から InnoDB バッファプールをcoreファイルに含めるかどうかを指定するパラメータ innodb_buffer_pool_in_core_file が追加されました。
デフォルトはONで、これまで同様バッファプールを含めた、(大きな) coreファイルが出力されます。
--
試してみます。
mysql> show global variables like '%innodb_buffer_pool_in_core_file%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| innodb_buffer_pool_in_core_file | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
$ sudo kill -SIGSEGV $(pidof mysqld)
$ ls -alh *core*
-rw------- 1 mysql mysql 5.0G Nov 4 15:19 mysqld.core.4947
5Gのcoreが生成されました。次に、OFFにして試してみます。
このパラメータはオンラインで変更することが出来ます(意外でした) 。
mysql> set global innodb_buffer_pool_in_core_file=off;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%innodb_buffer_pool_in_core_file%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| innodb_buffer_pool_in_core_file | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
5Gだったcoreファイルが892Mと大幅にコンパクトになっています。
$ sudo kill -SIGSEGV $(pidof mysqld)
$ ls -alh *core*
-rw------- 1 mysql mysql 892M Nov 4 15:22 mysqld.core.5218
良いですね。デフォルトはONですが、自分はOFFにしておいて、万が一、バッファプールをcoreにふくめないといけないようなバグにヒットしたら、ONにしていく運用しようと思ってます。
MySQL の 巨大な core ファイル 対策(~MySQL 5.7)
約1ヶ月ぶりのブログです。
--
core ファイルとはプロセスのメモリを丸ごとファイルにダンプしたものです。core ファイルをgdb等のデバッガで解析することで、プロセスが落ちた原因を探ることができます。
MySQL では以下のように設定することで、mysqldが落ちた際にcoreファイルを自動的に取得できるようになります。
- /etc/my.cnf
[mysqld]
core-file
- sysctl
fs.suid_dumpable=1
core ファイルサイズにlimitが効いている場合は ulimit -c unlimit でサイズの制限も解除する必要があります。
--
さて、MySQL に大きなメモリを割り当てていると、core ファイルのサイズも大きくなります。最近ではサーバに数百GBのメモリを搭載していることは珍しくありません。そうするとcoreファイルも巨大に・・・ディスクが足りずに、途切れてしまったcoreファイルでは解析できません。
対策としてcoreファイルを圧縮します。具体的には kernel.core_pattern にパイプで圧縮するコマンドを挟みました。圧縮方法は「圧縮率はそこそこだが、速いもの」を選びました。
圧縮率が高いアルゴリズムだと、圧縮に時間がかかり、coreを出力するのに時間がかかってしまいます。mysqldがダウンするとmysqld_safeによって、速やかに再起動されるわけですが、coreを書き出すのに時間がかかっていると、復旧するまでの時間が長くなってしまいます。
facebookのベンチマークによると、lz4が要件を満たしそうだったので、lz4にします。
$ sudo sysctl -w kernel.core_pattern='|/usr/bin/lz4 - /var/crash/%e.core.%u.lz4
圧縮率はデータによって左右されると思います。
テストでは6分の1程度に容量が減りました。速度は、17Gのcoreを出力するのに、22秒かかるようになりました。元々は5秒程度だったので、出力スピードは4倍以上遅くなっています。もう少し、速いと嬉しいところですが、トレードオフなので仕方ありません。
--
今回はkernelの機能で対応しましたが、MySQL 8.0 では、この巨大なcoreファイル問題を回避する機能がMySQLに追加されました
MySQL table_encryption_privilege_check パラメータの挙動
前回の記事ではdefault_table_encryption パラメータについて触れました。 default_table_encryptionでは、ENCRYPTION句を省略した場合の挙動を制御することができます。
今回は、table_encryption_privilege_check パラメータについて確認してみます。 このパラメータを有効化すると、データベース(スキーマ)のENCRYPTIONと異なるENCYRPTIONをテーブル作成時に指定できなくなります。
mysql> SHOW CREATE DATABASE sbtest;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t3 (pk SERIAL PRIMARY KEY) ENCRYPTION='N';
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.
同様に、データベースのENCRYPTIONを無効にした場合、デーブル作成時に「ENCRYPTION=Y」を指定できなくなります。
mysql> SHOW CREATE DATABASE sbtest;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t4 (pk SERIAL PRIMARY KEY) ENCRYPTION='N';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t5 (pk SERIAL PRIMARY KEY) ENCRYPTION='Y';
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.
データの暗号化を強制することが以下の運用を行うことで実現できます。
- table_encryption_privilege_check をONにしておく
- DBAがENCRYPTION=Yを指定したデータベースを作成する
- 開発者にはそのデータベースにのみ権限を付与する
暗号化強制機能がこのような仕組みで実現されるとは想像していなかったです。
データベースとユーザを分けることで、暗号化強制の有無を切り替えられますし、なかなか便利な実装だと思いました。
MySQL Casual Talks vol.12 で発表してきた
MySQL default_table_encryption パラメータの挙動
「テーブルの暗号化をデフォルトで有効にするオプションかな?」と思って、CREATE TABLEしてみるも変わらず・・・
マニュアル見ると、データベースとテーブルスペースに効くと書いてありました・・・。名前が紛らわしい・・・。
Defines the default encryption setting applied to schemas and general tablespaces when they are created without specifying an ENCRYPTION
clause.
このパラメータを有効化した状態で、ENCRYPTIONオプションを省略してCREATE DATABASEすると、ENCRYPTION='Y'が暗黙的に追加されます。
mysql> SHOW VARIABLES LIKE 'default_table_encryption';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| default_table_encryption | ON |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE tde;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE tde;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| tde | CREATE DATABASE `tde` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
テーブルのENCRYPTION指定を省略した場合は、データベースのENCRYPTIONに従う仕様です。結果として、テーブルの暗号化をデフォルトとすることができます。
mysql> CREATE TABLE tde.test (pk SERIAL PRIMARY KEY, c1 VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE tde.test \G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`c1` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.00 sec)
default_table_encryption パラメータはENCRYPTION指定を省略した場合の挙動を変更します。明示的にENCRYPTION='N'を指定すれば、暗号化しないテーブルを作成できます。暗号化を強制する機能ではありません。
とはいえ、暗号化をし忘れた(ENCRYPTIONを指定してなかった)というケースが防げるだけでも、十分有用だと思います。
暗号化を強制したい場合は、table_encryption_privilege_check という別のパラメータが関連してきます。
つづく。。。
MySQL Technology Cafe #5 に参加してきた
タイムテーブル
時間 | 内容 | 登壇者 |
---|---|---|
18:00-18:25 | 受付 | - |
18:25-18:30 | はじめに | MySQL GBU |
18:30-19:15 | MySQL 8.0 の便利機能とSQL標準 | MySQL テクニカルアナリスト 木村明治 氏 |
19:30-19:40 | MySQL Binlog のパケットと自作クライアント | 高野周哉 氏 |
19:40-20:30 | ネットワーキング | - |
20:45 | 撤収 | - |
MySQL 8.0 の便利機能とSQL標準
MySQL サポート担当の木村さんのお話!
あまり取り上げられない、MySQL 8 の新機能の紹介。
- EXPLAINのTREE表示
- 8.0.16でサポートされたが、Experimental扱いなので注意
- 将来フォーマットが変わるかもしれない
- EXPLAIN FORMAT=TREE を指定すると某DBみたいな実行計画が出力される
- パスワード再利用ポリシー
- X世代前のパスワードはNGとすることができるように
- 新しいCollation、utf8mb4_0900_bin
- 8.0.17からサポート
- 従来は 'a' = 'a ' (a+スペース) だったが、このCollationは 'a' != 'a ' になる
- @tmtms さんのスライドが詳しい
MySQL と SQL標準の関わり
- MySQLの機能がベースとなってSQL標準に取り込まれたり業界標準になったもの
- GROUP_CONCAT
- LIMIT句
- SQL標準では FETCH FIRST n ROWS という形式でサポート
- REPLACE, INSERT ... ON DUPLICATE ...
- INSERT ... VALUES (), (), () バルクインサート
MySQL Binlog のパケットと自作クライアント
高野さん
バイナリログから更新内容をリアルタイムに読み出すために、独自にRubyでクライアントを書いたお話でした!すごい!
バイナリログを使った、Pub/Sub型のシステムはニーズはそれなりにありそうなものの、なかなかデファクトとなる手法が定まらない分野の1つだと感じてます。自分も過去に、MySQL Binlog Events ライブラリを試したりしました。
- Server Greeting Packet
- パスワード認証時にパスワードからハッシュ生成するために使うSaltが含まれている
- Ruby と TCP SocketでMySQLのサーバからバイナリログを読み取るクライアントを書いた
- Pub/Subみたいな非同期処理に活用
- 接続したら、最初に SET master_binlog_checksum=OFF にしないとエラーになる