mita2 database life

主にMySQLに関するメモです

MySQL の 巨大な core ファイル 対策（MySQL 8.0〜）

MySQL

昨日記事では core ファイルを圧縮する方法を紹介しました。 昨日の記事では、OSの機能（kernel.core_pattern）を使って対応する方法を紹介しました。

 

mita2db.hateblo.jp


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）

約１ヶ月ぶりのブログです。

--

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

圧縮率はデータによって左右されると思います。
テストでは６分の１程度に容量が減りました。速度は、17Gのcoreを出力するのに、22秒かかるようになりました。元々は5秒程度だったので、出力スピードは4倍以上遅くなっています。もう少し、速いと嬉しいところですが、トレードオフなので仕方ありません。

--

今回はkernelの機能で対応しましたが、MySQL 8.0 では、この巨大なcoreファイル問題を回避する機能がMySQLに追加されました

 

mita2db.hateblo.jp

MySQL table_encryption_privilege_check パラメータの挙動

MySQL

前回の記事では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
MySQL Casual Talks vol.12 で発表してきた。




MySQLに入れるとしたらどれぐらいの容量が目安かアンケートしたりしてみました。 結果は、結構ばらけました。。。もうちょっと傾向が出ることを期待してたのですが。。。

１つのDBがどれだけ食えるかは、容量だけでなくワークロードの内容やら他の要素も大いに関係します。 一概には言えないというのが結果にも現れたかなと思います。




会場を提供してくださったLINEさん、tomboさんありがとうございました！

MySQL default_table_encryption パラメータの挙動

MySQL
MySQL 8.0 で 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 に参加してきた

MySQL
前回の、MySQL Technology Cafe #4 に続き、#5 に参加してきました。


タイムテーブル

 

時間内容登壇者
18:00-18:25受付-
18:25-18:30はじめにMySQL GBU
18:30-19:15MySQL 8.0 の便利機能とSQL標準MySQL テクニカルアナリスト 木村明治 氏
19:30-19:40MySQL Binlog のパケットと自作クライアント高野周哉 氏
19:40-20:30ネットワーキング-
20:45撤収-

 

MySQL 8.0 の便利機能とSQL標準


MySQL サポート担当の木村さんのお話！
あまり取り上げられない、MySQL 8 の新機能の紹介。


  • C言語の非同期API
    • 8.0.16でサポート
    • C言語のAPIの使い方を知りたければ、mysqldump.ccのソースが参考になる
    • 従来の同期APIの名前に  _nonblocking をつけた非同期APIがサポートされた
    • ポーリングするタイプの素朴な非同期API
  • 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標準の関わり

  • SQL標準で定められた機能でMySQLが最近サポートしたもの
    • CTE
      • RECURSIVE句はMySQLでは省略できない
      • 再帰が cte_max_recursion_depth を超えるとエラーになる→デフォルトは1000
    • Window Function
    • CHECK句
    • Generated Column
  • MySQLの機能がベースとなってSQL標準に取り込まれたり業界標準になったもの 
    • GROUP_CONCAT 
    • LIMIT句
      • SQL標準では　FETCH FIRST n ROWS という形式でサポート
    • REPLACE, INSERT ... ON DUPLICATE ...
    • INSERT ... VALUES (), (), () バルクインサート

MySQL Binlog のパケットと自作クライアント

 

高野さん

バイナリログから更新内容をリアルタイムに読み出すために、独自にRubyでクライアントを書いたお話でした！すごい！

バイナリログを使った、Pub/Sub型のシステムはニーズはそれなりにありそうなものの、なかなかデファクトとなる手法が定まらない分野の１つだと感じてます。自分も過去に、MySQL Binlog Events ライブラリを試したりしました。



  • Server Greeting Packet
    • パスワード認証時にパスワードからハッシュ生成するために使うSaltが含まれている
  • Ruby と TCP SocketでMySQLのサーバからバイナリログを読み取るクライアントを書いた
    • Pub/Subみたいな非同期処理に活用
    • 接続したら、最初に SET master_binlog_checksum=OFF にしないとエラーになる

その他

プロシージャの中身はPerconaだとスロークエリログに落ちる

MySQL
プロシージャの中身のクエリはスロークエリログに落ちない の続き。

Vanilla MySQLだとプロシージャの中身がログに落ちませんが、Percona Server ではスロークエリログが拡張されていて、ログに落ちます。

log_slow_sp_statements で出力の有無を設定します。デフォルトではプロシージャの中身をスロークエリログに出力する設定になってます。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_slow_sp_statements';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| log_slow_sp_statements | ON    |
+------------------------+-------+
1 row in set (0.00 sec)


試してみます。


mysql> DELIMITER //
mysql> CREATE PROCEDURE sample()
    -> BEGIN
    ->   SELECT SLEEP(10);
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql>
mysql> SET GLOBAL slow_query_log =on;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL long_query_time = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sample;
+-----------+
| SLEEP(10) |
+-----------+

Vanilla MySQL だとこうだったのが

/usr/sbin/mysqld, Version: 8.0.15 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-04-03T06:54:09.547179Z
# User@Host: root[root] @ localhost   Id:    30
# Query_time: 10.001274  Lock_time: 0.000376 Rows_sent: 1  Rows_examined: 0
SET timestamp=1554274439;
call test.sample();


Percona Server ではこう。
Stored_routine の項目にプロシージャの名前が記載され、プロシージャの中のSQLがログに記載されてます。

/usr/sbin/mysqld, Version: 8.0.15-6 (Percona Server (GPL), Release 6, Revision 63abd08). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2019-08-03T12:54:22.490311Z
# User@Host: root[root] @ localhost   Id:     9
# Schema: t  Last_errno: 0  Killed: 0
# Query_time: 10.000860  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 57
# Stored_routine: t.sample
use t;
SET timestamp=1564836852;
SELECT SLEEP(10);