GA

2017/01/26

MyNA(日本MySQLユーザ会)会 2017年1月でMySQLer 7つ道具の話をしてきた

2017/1/25の MyNA会 行ってきました。

赤井さん、やまさきさん、俺、かじやまさんでインプレスさんからMySQLの本が出た順にしゃべるヤーツ。





ネタ的にはこの時のリライトです（ただし結構リライトしてる…）

日々の覚書: 2年越しの #ChugokuDB in 中国地方


基本的にはくだんの本を書いた時より変わった運用とか、書かなかったポエミーなこととか、そんなものを紹介しています。

こちらもあわせてどうぞ！
MyNA（日本MySQLユーザ会）会 2017年1月ハッシュタグまとめ - Togetterまとめ
投稿者 時刻: 0 件のコメント :
ラベル: ,

2017/01/19

最近のPercona Serverで/usr/local/mysqlにシンボリックリンクを張ったら上手く起動しないでござる

バイナリー.tar.gz版のはなし。


こんな風に/usr/local/mysqlじゃないところにPercona Serverをダウンロードして、/usr/local/mysqlにシンボリックリンクを張る。

$ cd /usr/local
$ wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.16-10/binary/tarball/Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
$ tar xf Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
$ ln -s Percona-Server-5.7.16-10-Linux.x86_64.ssl101 mysql
$ ll
total 234348
drwxr-xr-x  2 root root        18 Jun  8  2016 bin
drwxr-xr-x  2 root root         6 Sep 23  2011 etc
drwxr-xr-x  2 root root         6 Sep 23  2011 games
drwxr-xr-x  2 root root         6 Sep 23  2011 include
drwxr-xr-x  2 root root         6 Sep 23  2011 lib
drwxr-xr-x  3 root root        18 Jun  8  2016 lib64
drwxr-xr-x  2 root root         6 Sep 23  2011 libexec
lrwxrwxrwx  1 root root        44 Jan 19 11:47 mysql -> Percona-Server-5.7.16-10-Linux.x86_64.ssl101
drwxr-xr-x 11 root root      4096 Jan 19 11:48 Percona-Server-5.7.16-10-Linux.x86_64.ssl101
-rw-r--r--  1 root root 239966730 Nov 28 05:07 Percona-Server-5.7.16-10-Linux.x86_64.ssl101.tar.gz
drwxr-xr-x  2 root root         6 Sep 23  2011 sbin
drwxr-xr-x  6 root root        58 Jun  8  2016 share
drwxr-xr-x  2 root root         6 Sep 23  2011 src


で、イニシャライズして起動しようとすると転ける。

$ useradd mysql
$ cd mysql
$ bin/mysqld --no-defaults --initialize --datadir=./data --user=mysql
$ bin/mysqld_safe --no-defaults --datadir=./data --user=mysql
 mysqld_safe Adding '/usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1' to LD_PRELOAD for mysqld
 mysqld_safe ld_preload libraries can only be loaded from system directories (/usr/lib64, /usr/lib, /usr/local/mysql/lib)


Percona Serverはもともと（特にインストールしてなければ） $MY_BASEDIR_VERSION/lib/libjemalloc.so.1 をロードして勝手にjemallocを使ってくれるようになっている けれど、CVE-2016-6662 対策で 変なところからLD_PRELOADできないようにmysqld_safeに修正 が入っている。

ちなみに本家5.7.16は オプションをパースする時にバリデーション してるだけだけど、Percona Serverは「デフォルトで勝手にjemallocをロードする」ために、 LD_PRELOADをセットする手前でもう一回バリデーション している。


 288 add_mysqld_ld_preload() {
 289   lib_to_add="$1"
 290   lib_to_add=$(readlink -f $lib_to_add)
 291   log_notice "Adding '$lib_to_add' to LD_PRELOAD for mysqld"
 292
 293   # Check if the library is in the reduced number of standard system directories
 294   case "$lib_to_add" in
 295     /usr/lib64/* | /usr/lib/* | ${MY_BASEDIR_VERSION}/lib/*)
 296       ;;
 297     *)
 298       log_error "ld_preload libraries can only be loaded from system directories (/usr/lib64, /usr/lib, ${MY_BASEDIR_VERSION}/lib)"
 299       exit 1
 300       ;;
 301   esac

290行目 のreadlinkが肝で、自動でロードしようとした /usr/local/mysql/lib/libjemalloc.so.1 をシンボリックリンク展開して /usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/lib/mysql/libjemalloc.so.1 にしてしまい、結果 $MYSQL_BASEDIR_VERSION/lib ディレクトリーの外側だから不正、NG！ となってる。

取り敢えずこの行をコメントアウトすれば動かせるようにはなる。
ただしその場合リアルにlibjemalloc.so.1をシンボリックリンクで変な共有ライブラリーに置き換えられると死ぬ。

…けど、そんなところ置き換えられるような権限取られたらもっとひどいことされてるだろうから大丈夫だとは思う。野良ビルドでなければ。


5.7.16でしか確認していないけど、CVE-2016-6662対応以降 のマイナーバージョンであれば5.5, 5.6も同じことが起こるかも知れない。
投稿者 時刻: 0 件のコメント :
ラベル: ,

2017/01/06

AWS上でMySQL5.7動作環境を最速で作る方法(MySQL::Sandbox使用)

@sakaik さんの連載（？）を読んでいたらやたら楽しそうで俺もなんかビルドしたくなってたまらなくなったので。

- MySQLをビルドする on AWS Red Hat 7.3 - sakaikの日々雑感～(T)編
- ふたつのバージョンのMySQLを同時に動かす on AWS Red Hat 7.3 - sakaikの日々雑感～(T)編
- MySQL 5.7 のビルドにトライ ～ 5.6とは大違い - sakaikの日々雑感～(T)編
- MySQL 5.7 を t2.micro でもビルドできた！ - sakaikの日々雑感～(T)編
- AWS上でMySQL5.7動作環境を最速で作る方法(Generic binaries使用) - sakaikの日々雑感～(T)編

タイトルからわかるように、最後のエントリーに対抗してみました。

先に書いておくと、MySQL::Sandboxのインストールまで終わっていれば 15秒くらい でMySQL 5.7.17の起動までたどり着けます（あとで書きますがこれはMySQL::Sandboxの **中の人** が上手くやってる）


TL;DR

$ sudo su -i
# yum install -y perl-ExtUtils-MakeMaker perl-Data-Dumper
# curl -L cpanmin.us | perl - App::cpanminus
# /usr/local/bin/cpanm MySQL::Sandbox
# useradd mysql
# su - mysql
$ make_sandbox_from_url 5.7.17
$ msb 5717


みんなだいすき MySQL::Sandbox ですが、make_sandbox と make_replication_sandbox だけで満足していませんか？

MySQL::Sandbox 3.1.10から（2017年1月6日現在、最新版は3.2.05）は make_sandbox_from_url というコマンドを備えています。
引数なしで叩くとusage吐いてくれるので取り敢えず叩く。


$ make_sandbox_from_url
    The MySQL Sandbox,  version 3.2.05
    (C) 2006-2016 Giuseppe Maxia
### version needed

This script tries to create a sandbox using binaries downloaded from the web.
The success of this scripts depends on good connectivity
and availability of downloading tools.
##########################
# IT ONLY WORKS ON LINUX
##########################
The repository of available binaries is at https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata .
Supported:
# 5.0 -> [ 5.0.96 ]
# 5.1 -> [ 5.1.72 ]
# 5.5 -> [ 5.5.51 5.5.52 ]
# 5.6 -> [ 5.6.32 5.6.33 ]
# 5.7 -> [ 5.7.14 5.7.15 ]
# 8.0 -> [ 8.0.0 ]
The binaries are imported into /usr/mysql .

  make_sandbox_from_url X.X.XX [options]

    where X.X.XX is the version number.
    You can then pass any options accepted by make_sandbox.

make_sandbox_from_urlという名前ながら、引数はURLではなくてバージョン番号（make_sandboxと同じ）
これもともとは本当にURLだったけど、途中で変わったんじゃないかって気がする。確証はないし調べてもいない。


$ make_sandbox_from_url 5.7.17
2017-01-06 07:56:30 URL:https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.txt [98/98] -> "/tmp/available.txt" [1]
wget -nv -O 5.7.17.tar.gz 'https://github.com/datacharmer/mysql-docker-minimal/blob/master/dbdata/5.7.17.tar.gz?raw=true'
2017-01-06 07:56:33 URL:https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/5.7.17.tar.gz [40959490/40959490] -> "5.7.17.tar.gz" [1]
Executing low_level_make_sandbox --basedir=/home/mysql/opt/mysql/5.7.17 \
        --sandbox_directory=msb_5_7_17 \
        --install_version=5.7 \
        --sandbox_port=5717 \
        --no_ver_after_name \
        --my_clause=log-error=msandbox.err
..

do you agree? ([Y],n) Y
# Starting server
.. sandbox server started
# Loading grants
Your sandbox server was installed in $HOME/sandboxes/msb_5_7_17

$ msb 5717
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) >

…しかし…数百MBあるバイナリーを落としてるにしては…？ と思ったら、公式バイナリーではなく、MySQL::Sandbox作者のGitHubから取ってきてる。
軽量版MySQLバイナリー.tar.gzは このスクリプト で作っている様子。

というわけでこれを使えば、5.0でも5.1でも5.5でも5.6でも5.7でも8.0でも好きなリリースシリーズが入れられますよ！ ;)

ちなみにダウンロードされたバイナリー.tar.gzと展開後のファイルは$HOME/opt/mysql に置かれる。40MB………どんだけいろんなもん積んでるんだよ公式バイナリー。。


$ ll opt/mysql/
total 79712
drwxrwxr-x 7 mysql mysql     4096 Dec 14 18:46 5.7.17
-rw-rw-r-- 1 mysql mysql 40959490 Jan  6 07:56 5.7.17.tar.gz
drwxrwxr-x 7 mysql mysql     4096 Sep 12 15:22 8.0.0
-rw-rw-r-- 1 mysql mysql 40654528 Jan  6 07:57 8.0.0.tar.gz
投稿者 時刻: 0 件のコメント :
ラベル: ,

2016/12/23

MySQLのCOUNTを速くする（？）SQL1本ノック その2

この記事は MySQL Casual Advent Calendar 2016 の23日目の記事です。
そしてどうやら、 日々の覚書 の400本目の公開記事です。そんなに書いてたのか。。

前回 のあらすじ。


mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

COUNTが遅い！ のでサマリーテーブルを作ってトリガーを仕掛けた！ これで勝つる！
（前回とはPRIMARY KEYの順番を変えてあります。1個キーが追加されたのは、FKが勝手に作ったからです）


mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`),
  KEY `game_score_summary_ibfk_1` (`game_id`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

だが、この設計ではボトルネックを生むのであった_|￣|○
ちなみにこの段階でボトルネックにならない場合、ボトルネックになるまではここから先に書いてあるようなことは考えなくて良いと思う。

さて、このテーブルのPRIMARY KEYは(play_date, game_id) で、play_date は game_score.play_end_time から派生してくる。game_score.play_end_timeはおそらく NOW() か、それに類するものから派生する。

ということはつまり、このサマリーテーブルはほぼ game_id 単位でしかロックを持たないことになる。
1分に数回しかプレイされないような環境ならまだしも、秒間数回（これはテキトー。 実際にはもっと叩かれていたし フィクションです）となってくるとこれでは詰まる。

パフォーマンスは失われないように、ロックの粒度は大きくなりすぎないように、単位を分割するのがここでのたしなみ。
もちろん、ロックを放棄して走り去るなどといった、はしたないDBAなど存在していようはずもない。

という訳でいくつかの分割案。


1. mysqldでのスレッド単位

コネクションプール環境ならこれが一番確実だと思う。MySQLには mysqld上のprocesslist_idを参照できる CONNECTION_ID関数と pseudo_thread_idセッション変数があるので、これをトリガーに埋め込むことができる。


mysql57> SHOW CREATE TABLE game_score_summary_by_thread\G
*************************** 1. row ***************************
       Table: game_score_summary_by_thread
Create Table: CREATE TABLE `game_score_summary_by_thread` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `processlist_id` bigint(20) unsigned NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`play_date`,`game_id`,`processlist_id`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `game_score_summary_by_thread_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_thread_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_thread (game_id, play_date, processlist_id, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), @@pseudo_thread_id, 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 12:11:37.18
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql57> SELECT game_id, SUM(count_star) AS count_star FROM game_score_summary_by_thread WHERE play_date = '2016/12/22' GROUP BY game_id;
+---------+------------+
| game_id | count_star |
+---------+------------+
|       0 |       4275 |
|       1 |       4279 |
|       2 |       4240 |
|       3 |       4084 |
|       4 |       4304 |
|       5 |       4231 |
|       6 |       4406 |
|       7 |       4254 |
|       8 |       4166 |
|       9 |       4253 |
+---------+------------+
10 rows in set (0.00 sec)

これで、ロックの粒度をスレッド単位で分割できる。当然ながら1スレッド単位で見れば完全に直列するので、ロックの競合は発生しない。フェッチする行数は game_id * コネクションプールから張られる総数で済むので、GROUP BYでSUMしても大した負荷ではない。

コネクションプール以外の環境（つまり、トランザクションの都度接続して切断するスタイル）の場合、pseudo_thread_idはどんどんインクリメントされるので、つまりgame_scoreからカウントしてるのと変わらなくなってしまってダメ。COUNTじゃなくてSUMな分悪くなると思う。

じゃあ…


2. 接続元ホスト単位

個人的にはイマイチ好きじゃないけど一つの案。
pseudo_thread_idとは違って、同じホストから複数のクエリーが同時に㌧で来るのは十分あり得るので、「スケールアウトよりもスケールアップ」で戦っているケースだとあんまりロックが分割されない。

それでも分割しないよりはマシであろうとは思うけれど、MySQLの中からクライアントの接続元を一発で引ける変数や関数はないので、クライアント側で「トリガーの中で使っているセッション変数に接続元をセットしてやる」か、「トリガーを諦めて同じトランザクションの中で一緒にサマリーの方も更新するか」のどちらかになる。

1ホスト1アカウントになるようにアカウントをIP決め打ちで設定していれば CURRENT_USER関数が使えそう。文字列型になるのでハッシュ化したとしてもちょっと容量かさむけれども。

サマリーテーブルやトリガーやSUMは省略。


3. 時間を使って分割

MySQL 5.6とそれ以降ではNOW関数がマイクロ秒まで出してくれるので、マイクロ秒単位の下2桁で分解する。具体的には RIGHT(NOW(6), 2) で分解してみた。

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_by_time_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary_by_time (game_id, play_date, fragment, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), RIGHT(NOW(6), 2), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-22 19:08:12.68
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)


これが驚くくらい詰まらずにちゃんと分割された。こんなにうまくいくとは思ってなかった。びっくり。


というわけで、

- Connection Poolなら pseudo_thread_id
- それ以外で
  - MySQL 5.6とそれ以降なら RIGHT(NOW(6), 2)
  - MySQL 5.5とそれ以前なら CURRENT_USER() か アプリから文字列渡し

って感じで俺の中では落ち着きました。

明日はクリスマスイブ、ｶﾐﾎﾟｩ さんです！
投稿者 時刻: 0 件のコメント :
ラベル:

2016/12/19

MySQLのCOUNTを速くする（？）SQL1本ノック その1

この記事は MySQL Casual Advent Calendar 2016 の19日目の記事です。

「MySQLのCOUNTが遅いなー」って言うと、「トリガーでサマリーテーブル作ったら？」と言われること、多くありませんか？ 特に @soudai1025 に言われる率が高い気がしています（個人の感想です）

個人的にはトリガーよりは同じトランザクションの中でカウンターテーブル（サマリーテーブルと違うもののつもりで言ってるけど、世の中でいうサマリーテーブルの中にはこの形式が含まれるかも知れない）をUPDATEする方が好き（だって、トリガーはブラックボックスになるんだもの）なんですが、頭の体操ということでトリガーで組んでみましょう。


サンプルテーブルはこんな感じ。


mysql57> SHOW CREATE TABLE game_score\G
*************************** 1. row ***************************
       Table: game_score
Create Table: CREATE TABLE `game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8mb4


mysql57> SELECT * FROM game_score LIMIT 3;
+-----+---------+---------+---------------------+--------+
| seq | game_id | user_id | play_end_time       | score  |
+-----+---------+---------+---------------------+--------+
|   1 |       9 |    2445 | 2016-12-19 11:18:32 | 981760 |
|   2 |       9 |    1440 | 2016-12-19 11:18:33 | 108172 |
|   3 |       6 |    7879 | 2016-12-19 11:18:34 | 180756 |
+-----+---------+---------+---------------------+--------+
3 rows in set (0.00 sec)


こんなテーブルの中から、「今日、各ゲームが何回プレイされたか」を抽出するクエリーは

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.37 sec)

おそらくこんなん。
死ぬほど遅いのは、GROUP BY狙いのキー(game_id)を使っちゃってるから。WHERE狙いのキー(play_end_time)に変えさせれば100msくらいにはなる。それでも遅いけど。

これをトリガーに突っ込む。日付の条件は日々変わるだろうから、DATE型までサマライズしたカラムも必要かな。あと、game_scoreはログテーブルなのでINSERT以外は走らないものとする。おそらくバッチでDELETEはするだろうけど、それは取り敢えず置いておく（一緒に消せばいいだけなので）
とするとこんな感じだろうか。


mysql57> SHOW CREATE TABLE game_score_summary\G
*************************** 1. row ***************************
       Table: game_score_summary
Create Table: CREATE TABLE `game_score_summary` (
  `game_id` int(11) NOT NULL,
  `play_date` date NOT NULL,
  `count_star` int(10) unsigned NOT NULL,
  PRIMARY KEY (`game_id`,`play_date`),
  CONSTRAINT `game_score_summary_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game_score` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: game_score_summary_increment
               Event: INSERT
               Table: game_score
           Statement: INSERT INTO game_score_summary (game_id, play_date, count_star) VALUES (NEW.game_id, DATE(NEW.play_end_time), 1) ON DUPLICATE KEY UPDATE count_star = count_star + 1
              Timing: AFTER
             Created: 2016-12-19 11:53:52.69
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)


INSERT INTO .. ON DUPLICATE KEY UPDATEで「無ければ1、あればインクリメント」にしてる。
30万行くらい（別の日付で）追加で突っ込んでみる。

mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/25' AND '2016/12/26' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     8817 |
|       1 |     8513 |
|       2 |     8655 |
|       3 |     8782 |
|       4 |     8664 |
|       5 |     8643 |
|       6 |     8525 |
|       7 |     8741 |
|       8 |     8633 |
|       9 |     8428 |
+---------+----------+
10 rows in set (1.23 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/25';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-25 |       8817 |
|       1 | 2016-12-25 |       8513 |
|       2 | 2016-12-25 |       8655 |
|       3 | 2016-12-25 |       8781 |
|       4 | 2016-12-25 |       8664 |
|       5 | 2016-12-25 |       8643 |
|       6 | 2016-12-25 |       8525 |
|       7 | 2016-12-25 |       8741 |
|       8 | 2016-12-25 |       8633 |
|       9 | 2016-12-25 |       8428 |
+---------+------------+------------+
10 rows in set (0.00 sec)

おっと…play_dateにインデックス張るの忘れてたけど、追加してやればシンプルな10行フェッチで済む（PRIMARY (play_date, game_id) にする方が筋がいいかな）

このトリガーを張った瞬間より前の履歴については整合性が保証できないので、一度更新をブロックしてgame_score_summaryにマージしてやるか、諦めて「明日以降のやつはsummaryから取って、今日以前のやつはgame_scoreから取る」というロジックをアプリケーション側に入れ込むか。


mysql57> SELECT game_id, COUNT(*) FROM game_score WHERE play_end_time BETWEEN '2016/12/19' AND '2016/12/20' GROUP BY game_id;
+---------+----------+
| game_id | COUNT(*) |
+---------+----------+
|       0 |     4610 |
|       1 |     4751 |
|       2 |     4532 |
|       3 |     4470 |
|       4 |     4581 |
|       5 |     4534 |
|       6 |     4523 |
|       7 |     4472 |
|       8 |     4583 |
|       9 |     4633 |
+---------+----------+
10 rows in set (0.08 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
Empty set (0.00 sec)

mysql57> LOCK TABLE game_score READ, game_score_summary WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql57> INSERT INTO game_score_summary SELECT game_id, MAX(DATE(play_end_time)) AS play_date, COUNT(*) AS count_star FROM game_score WHERE play_end_time < '2016/12/20' GROUP BY game_id;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql57> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql57> SELECT * FROM game_score_summary WHERE play_date = '2016/12/19';
+---------+------------+------------+
| game_id | play_date  | count_star |
+---------+------------+------------+
|       0 | 2016-12-19 |       4610 |
|       1 | 2016-12-19 |       4751 |
|       2 | 2016-12-19 |       4532 |
|       3 | 2016-12-19 |       4470 |
|       4 | 2016-12-19 |       4581 |
|       5 | 2016-12-19 |       4534 |
|       6 | 2016-12-19 |       4522 |
|       7 | 2016-12-19 |       4472 |
|       8 | 2016-12-19 |       4583 |
|       9 | 2016-12-19 |       4633 |
+---------+------------+------------+
10 rows in set (0.00 sec)

LOCK TABLEするならこんな感じで。game_score_summaryだけロックしておけばgame_score側は要らないかも知れない（AFTER INSERTトリガーが止まるから）

実はこのテーブル構造、致命的に性能の欠陥があるので 次回 に続く。


明日は ogataka50 さんです！


【2016/12/19 14:39】

ちなみに、サンプルデータを作るのに使ったスクリプトはこちらです。
https://gist.github.com/yoku0825/0608014dcfd3e1cd244a51ef7e590ca0
投稿者 時刻: 0 件のコメント :
ラベル:

2016/12/12

試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた

いやあ試された試された。

- (金曜日) 昼の便で夕方には市内に着く予定だったのが出発遅延、チェックインしたのは日付が変わったくらい

- (土曜日) （ちゃんと間に合う時間に）目を覚ましたら、外がなんか人生初体験レベルの積雪だった。東京と同じ感覚で新雪を踏もうとしたらズボっといった

- (土曜日) @kunihirotanakaさん と @yoheiaさん が色々似ていることに懇親会で気付く。そしてやっぱり @yoheiaさん のアイコンは貯金箱だと認識されていた

- (日曜日) まさかの止まない雪。続々とやられていく首都圏のPerl Mongerたち。そんな中、かろうじて搭乗手続きが始まった便に乗り込むyoku0825を待っていたものとは…？

- (月曜日) 羽田についたけど、終電がないよ。ぽえーん。


その他、 #yapcjapan で検索すると色々阿鼻叫喚の声や大地の白さを記録したブログなど色々見つかると思います。


北の大地の洗礼をたっぷり受けながらも なんとか日付が変わる前に着陸できそうということは、 日付変わりましたが無事着陸できたお祝いを兼ねて（？） 11日目がんばれって🐬の神様が言ってるんですねそうですね。

というわけで、この記事は MySQL Casual Advent Calendar 2016 の11日目の記事です！



YAPC::Hokkaido 2016 Sapporo で朝イチのセッションでした。同じ時間が @kazuhoさん のキーノートだったので、迷ってくれた方もいらしたようでした。ありがたやありがたや。

@songmuさん に「再演しますか？」って聞いてもらったんですが、過去のもの含め再演は機会（= どこかの勉強会とか、社内勉強会とか、自分で運営せずにお呼ばれできるものなら何でも…） さえあればいくらでも再演しますので、興味のある方はお声がけください！







OSC広島で（というか、 遡れば去年のYAPC::Asia 2015の時から ） @sakaikさん に、「yokuさんを知ってる人は楽しんでくれると思うけど、yokuさんをよく知らない人はあの言葉の通りにMySQLの印象を持っちゃうよ」と言われていくらか悩んだ結果、

今回は「いかにもMySQLが好きで定点観測してる人が、自分の印象を語る」という切り口で行きました。

「5.6から5.7がこうだったから、8.0もこうなるんじゃないか」
「5.7でここがダメだったから、8.0はそこを補ってくれるんじゃないか」
「MySQL Xはどこに行くんだろう」
「罠～罠だよ～」

そんなドキドキとワクワク とピキピキ が伝われば幸いです。

Have fun!!


そして、やっぱり同窓会っぽくとんでもなく楽しいYAPCを届けてくれたJPAのみなさま、コアスタッフのみなさま、当日スタッフのみなさま、本当にありがとうございました！
投稿者 時刻: 0 件のコメント :
ラベル: , ,

2016/12/10

MySQLのビルド環境にConoHaを選んでいる理由

この記事は ConoHa Advent Calendar 2016 と MySQL Casual Advent Calendar 2016 の10個目の窓です。

俺は とある企業のDBA なので、基本的にコードは書きません。Bot書いたりとか運用ツール書いたりとかMySQLにパッチ当てたりだとかそのくらいですね。意外と書いてた。

Botはついでに動かしているだけで、もともとBotのためにConoHaを使っているわけでもなかったし（当時はCPU2コア, メモリー1GB, HDD 100GBが最小プランだったけど、Botに使うにはちょっとオーバースペック）、じゃあ何に使っているのかというと

MySQLのビルド なわけですが。これがConoHaだとかなり良い。とても良い。
というか最近、ソースコードも量が増えて、へたれな仮想サーバーとかPCのVirtualBoxだと まともな時間でビルドが終わらない ようになってきました。特に5.7から先。10時間かかっても終わらないとか挙句OOM Killerに殺されちゃったりとかなってきました。つらい。

その点ConoHaだと8.0.0でも1時間あればお釣りがくるくらいなので助かっています…というか本当に助かったこれは。、

まずMySQLおじさんたるもの、取り敢えず新しいマイナーバージョンが出たらそれをビルドします。pre 5.0は捨てるにしても、5.0.96, 5.1.73, 5.5.53, 5.6.34, 5.7.16, 8.0.0の5つのバージョンのバイナリーとソースコードを保管しないといけない訳です。

あとたまに触りたくなると（触ったあとに消すけど）lab版とかPercona ServerやMariaDB、MySQL Clusterもビルドすることになるので、常時6～8個のmysqldは常に置いておかないといけません。


$ du -sch /usr/mysql/*
104M    /usr/mysql/5.0.96
306M    /usr/mysql/5.1.73
385M    /usr/mysql/5.5.53
676M    /usr/mysql/5.6.34
1.2G    /usr/mysql/5.7.16
1.2G    /usr/mysql/8.0.0
1.6G    /usr/mysql/http
1.2G    /usr/mysql/labs
6.5G    total


あとはもちろんソースコードと、コンパイルしたバイナリーは残しておかないといけません。デバッグとか、動作を理解するためにgdb刺してステップ実行とかは結構します。

$ du -shc mysql-*[0-9]
208M    mysql-5.0.96
503M    mysql-5.1.73
806M    mysql-5.5.53
1.4G    mysql-5.6.34
4.4G    mysql-5.7.16
15M     mysql-connector-java-5.1.40
2.3M    mysql-connector-python-2.1.3
8.9M    mysql-router-2.0.3
7.3G    total


複数台まとめて検証したりするにはDockerが便利ですよね。

$ docker images
REPOSITORY                                TAG                    IMAGE ID            CREATED             SIZE
docker.io/groonga/mroonga                 latest                 a0b64d145f97        5 weeks ago         1.236 GB
docker.io/groonga/mroonga                 mysql5634_mroonga610   6b31bf4e0d48        5 weeks ago         1.236 GB
docker.io/groonga/mroonga                 mysql5716_mroonga610   5c849df969e4        5 weeks ago         1.778 GB
docker.io/groonga/mroonga                 mysql5634_mroonga609   1a7d7e2ba454        6 weeks ago         1.234 GB
docker.io/centos                          centos6.6              d03626170061        3 months ago        202.6 MB
docker.io/centos                          centos6.8              0cd976dc0a98        3 months ago        194.5 MB
docker.io/centos                          centos5                1ae98b2c895d        3 months ago        284.7 MB
docker.io/yoku0825/mysql_router           latest                 4e14f7e09d50        3 months ago        519.6 MB
docker.io/yoku0825/mysql_fabric_command   latest                 6a4304d6b8a2        6 months ago        556.8 MB
docker.io/yoku0825/mysql_fabric_aware     latest                 2fac9fb7cc7e        6 months ago        1.664 GB
docker.io/yoku0825/mysql_fabric_server    latest                 e45808b5e20b        6 months ago        1.673 GB
docker.io/groonga/mroonga                 mysql5623_mroonga410   37f49ca977bc        9 months ago        1.132 GB


とするとこうなりますよね。

$ df -h .
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   48G   44G  3.6G  93% /


CentOS 7.2でDocker最新版使おうと思って旧のHDD 100GBから乗り換えなければよかった。。追加SSD、流石に200GBは要らないんだよなあ。。50GB単位とかになったら嬉しい。


取り敢えず、MySQLのビルドしたりgtagsでタグ付けしてソースコード読んだり、空いたリソースでbotを起動したりする分には、お値段以上に楽しんでいる方だと思います。


明日の ConoHa Advent Calendar 2016 は Atnanasiさん 、 MySQL Casual Advent Calendar 2016 はｳｯ


【2016/12/13 15:58】

新しいのが出たので計り比べたらこんなんだった。


versionrealusersysinit前のbasedirサイズ
5.5.546:12.0885:35.8050:34.668301M
5.6.3611:14.03410:12.3440:58.376497M
5.7.1732:06.06826:00.0222:30.209873M


https://gist.github.com/yoku0825/f99e1de45d22f60cc932bbb60dd6e3a9
投稿者 時刻: 0 件のコメント :
ラベル: ,
登録: 投稿 ( Atom )