MySQLとPostgreSQLによるログレポートの作成

by

MySQLとPostgreSQLにおけるログの確認方法

TPCCなどをダウンロードして参照のみ、更新と参照、参照と更新の比率変更等して様々な負荷をかけてベンチマークする方が良いと思うが、カジュアルにベンチマークしたいケースもあると思います。毎回、同じ方法でベンチマークする事で、ある程度サーバーの構成変更時のパフォーマンスの変化を把握できるのでMySQLやPostgreSQLにバンドルされているベンチマークツールも便利なベンチマークオプションとして使える。取得したログを簡単に確認したり、実際に運用中のシステムのログも定期的に確認出来れば尚便利。そんなニーズに対して、カジュアルにメモしておきました。

PostgreSQL


postgres@ubuntu:~$ pgbench -i scaffold
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.28 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

postgres@ubuntu:~$ psql scaffold -c "\d"
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)

postgres@ubuntu:~$

postgres@ubuntu:~$ pgbench -c 30 -t 1000 scaffold
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 30000/30000
latency average = 41.333 ms
tps = 725.808779 (including connections establishing)
tps = 726.545616 (excluding connections establishing)
postgres@ubuntu:~$

root@ubuntu:/var/log/postgresql# pgbadger postgresql-10-main.log
[========================>] Parsed 96663 bytes of 96663 (100.00%), queries: 0, events: 301
LOG: Ok, generating html report...
root@ubuntu:/var/log/postgresql# ls -l
total 1392
-rw-r--r-- 1 root root 1324353 May 24 10:34 out.html
-rw-r----- 1 postgres adm 96663 May 24 10:23 postgresql-10-main.log

pgbadger
*メモ：それぞれのパッケージは、aptもしくはyumでインストールしています。

pgbadgerのアウトプット
pgbadger

MySQL

負荷をかけてログを記録


[root@GA02 admin]# /usr/local/mysql/bin/mysqlslap --no-defaults --create-schema=SQLSLAP --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=InnoDB --number-int-cols=3 --number-char-cols=5 --concurrency=30 --auto-generate-sql-write-number=1000 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -p
Enter password:
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 19.724 seconds
Minimum number of seconds to run all queries: 19.724 seconds
Maximum number of seconds to run all queries: 19.724 seconds
Number of clients running queries: 30
Average number of queries per client: 1000

[root@GA02 admin]#

mysqldumpslow (MySQL　Default)でログ解析


[root@GA02 data]# mysqldumpslow -s  c /usr/local/mysql/data/mysql-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/mysql-slow.log
Count: 17349  Time=0.03s (463s)  Lock=0.00s (5s)  Rows=0.0 (0), root[root]@localhost
  INSERT INTO t1 VALUES (NULL,N,N,N,'S','S','S','S','S')

Count: 13375  Time=0.00s (2s)  Lock=0.00s (1s)  Rows=1.0 (13375), root[root]@localhost
  SELECT intcol1,intcol2,intcol3,charcol1,charcol2,charcol3,charcol4,charcol5 FROM t1 WHERE id =  'S'

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (30), root[root]@localhost
  #

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

Percona Tool Kitでログ解析


[root@GA02 admin]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
--2020-05-24 13:51:20--  https://repo.percona.com/yum/percona-release-latest.noarch.rpm
repo.percona.com (repo.percona.com) をDNSに問いあわせています... 167.71.118.3, 157.245.119.64, 167.99.233.229
repo.percona.com (repo.percona.com)|167.71.118.3|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 17684 (17K) [application/x-redhat-package-manager]
`percona-release-latest.noarch.rpm' に保存中

100%[==============================================================================================================================================================================================>] 17,684      --.-K/s 時間 0s      

2020-05-24 13:51:21 (51.5 GB/s) - `percona-release-latest.noarch.rpm' へ保存完了 [17684/17684]

[root@GA02 admin]# yum install percona-release-latest.noarch.rpm 
読み込んだプラグイン:fastestmirror, priorities
percona-release-latest.noarch.rpm を調べています: percona-release-1.0-18.noarch
次のリポジトリーへの更新として percona-release-latest.noarch.rpm を設定します: percona-release-0.1-4.noarch
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:0.1-4 を 更新
---> パッケージ percona-release.noarch 0:1.0-18 を アップデート
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                      リポジトリー                                                             容量
========================================================================================================================================================================================================================================
更新します:
 percona-release                                          noarch                                          1.0-18                                          /percona-release-latest.noarch                                           22 k

トランザクションの要約
========================================================================================================================================================================================================================================
更新  1 パッケージ

合計容量: 22 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  更新します              : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
  percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html
  整理中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
  検証中                  : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
  検証中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
更新:
  percona-release.noarch 0:1.0-18                                                                                                                                                                                                       
完了しました!
[root@GA02 admin]# 

[root@GA02 admin]# yum install percona-toolkit
読み込んだプラグイン:fastestmirror, priorities
percona-release-noarch                                                                                                                                                                                           | 2.9 kB  00:00:00     
percona-release-x86_64                                                                                                                                                                                           | 2.9 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirrors.cat.net
 * epel: ftp.jaist.ac.jp
 * extras: mirrors.cat.net
 * updates: mirrors.cat.net
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-toolkit.x86_64 0:3.2.0-1.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                              リポジトリー                                                     容量
========================================================================================================================================================================================================================================
インストール中:
 percona-toolkit                                          x86_64                                          3.2.0-1.el7                                             percona-release-x86_64                                           12 M

トランザクションの要約
========================================================================================================================================================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 12 M
インストール容量: 12 M
Is this ok [y/d/N]: y
Downloading packages:
警告: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-toolkit-3.2.0-1.el7.x86_64.rpm: ヘッダー V4 RSA/SHA256 Signature、鍵 ID 8507efa5: NOKEY========================================-] 184 kB/s |  12 MB  00:00:00 ETA 
percona-toolkit-3.2.0-1.el7.x86_64.rpm の公開鍵がインストールされていません
percona-toolkit-3.2.0-1.el7.x86_64.rpm                                                                                                                                                                           |  12 MB  00:00:50     
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-18.noarch (@/percona-release-latest.noarch)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか？ [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
  検証中                  : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
インストール:
  percona-toolkit.x86_64 0:3.2.0-1.el7                                                                                                                                                                                                  
完了しました!
[root@GA02 admin]#

pt-query-digestのアウトプット


[root@GA02 data]# pt-query-digest /usr/local/mysql/data/mysql-slow.log --no-version-check

# 20.9s user time, 50ms system time, 31.58M rss, 232.37M vsz
# Current date: Sun May 24 14:19:42 2020
# Hostname: GA02
# Files: /usr/local/mysql/data/mysql-slow.log
# Overall: 30.76k total, 9 unique, 242.22 QPS, 3.73x concurrency _________
# Time range: 2020-05-24T05:07:09 to 2020-05-24T05:09:16
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           474s     1us   221ms    15ms    34ms    15ms    20ms
# Lock time             7s       0   179ms   230us   332us     4ms   103us
# Rows sent         14.04k       0     999    0.47    0.99    5.52       0
# Rows examine      14.04k       0     999    0.47    0.99    5.52       0
# Query size        12.96M      17     707  441.72  685.39  291.70  685.39

# Profile
# Rank Query ID                        Response time  Calls R/Call V/M   I
# ==== =============================== ============== ===== ====== ===== =
#    1 0x76F7DD6D2A72EB7BCAEC22E79B... 468.9853 99.0% 17349 0.0270  0.00 INSERT t?
# MISC 0xMISC                            4.5256  1.0% 13413 0.0003   0.0 <8 ITEMS>

# Query 1: 667.27 QPS, 18.04x concurrency, ID 0x76F7DD6D2A72EB7BCAEC22E79B0EFD3B at byte 988485
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-05-24T05:08:50 to 2020-05-24T05:09:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         56   17349
# Exec time     99    469s     1ms   221ms    27ms    38ms    11ms    26ms
# Lock time     75      5s       0   179ms   309us   384us     5ms   108us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    90  11.67M     704     707  705.36  685.39       0  685.39
# String:
# Databases    SQLSLAP
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `SQLSLAP` LIKE 't1'\G
#    SHOW CREATE TABLE `SQLSLAP`.`t1`\G
INSERT INTO t1 VALUES (NULL,633998215,1598162325,1839677468,'l4dgQpQ2FFmHegccMlq0FmNIrmjthL1IPWzrCHuRYgk2KDWaqKb57qNyczrAKlSl7JcJcMe8uN26SYhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQ','W9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAjuZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQ','LKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8sgGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run','13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0XEufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E87','3FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52ZbYGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQz')\G
[root@GA02 data]#

MySQLのエラーログ含めてGUIで確認するのであればこんなツールもあります。
Percona Monitoring and Management
MySQL Enterprise Monitor

MySQLのMecabプラグインによる単語の解析（INNODB_FT_INDEX_TABLE）

by

MySQLのMECABによる最近つぶやかれている単語の解析

MySQLのMecabプラグインを利用して集計したデータを解析する過程で、INNODB_FT_INDEX_TABLEにて含まれている単語を確認し集計しようとしたが、
サーバーのスペックが低く集計中にハングしてしまったので、メモリーのINNODB_FT_INDEX_TABLEの内容をInnoDBテーブルに落として集計してみた。
もし、同様に形態素解析で解析されたワードを集計したいけれども、メモリー量が不足していて単語を集計出来ない場合の回避策として。参考までに。


root@localhost [(none)]> desc INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| WORD         | varchar(337)        | NO   |     |         |       |
| FIRST_DOC_ID | bigint(21) unsigned | NO   |     |         |       |
| LAST_DOC_ID  | bigint(21) unsigned | NO   |     |         |       |
| DOC_COUNT    | bigint(21) unsigned | NO   |     |         |       |
| DOC_ID       | bigint(21) unsigned | NO   |     |         |       |
| POSITION     | bigint(21) unsigned | NO   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

root@localhost [confirm]> show create table INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE\G
*************************** 1. row ***************************
       Table: INNODB_FT_INDEX_TABLE
Create Table: CREATE TEMPORARY TABLE `INNODB_FT_INDEX_TABLE` (
  `WORD` varchar(337) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.06 sec)

メモリーのINNODB_FT_INDEX_TABLEからInnoDBのテーブルにデータをコピーする


root@localhost [(confirm)]> SET GLOBAL innodb_ft_aux_table='APP/Tweets';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(confirm)]> SET GLOBAL innodb_optimize_fulltext_only=ON;optimize table Tweets;
Query OK, 0 rows affected (0.00 sec)

root@localhost [confirm]> CREATE TEMPORARY TABLE `FTS_INDEX_TABLE` (
    ->   `WORD` varchar(337) NOT NULL DEFAULT '',
    ->   `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `POSITION` bigint unsigned NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)


root@localhost [confirm]> insert into FTS_INDEX_TABLE select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
Query OK, 11642045 rows affected (12 min 51.51 sec)
Records: 11642045  Duplicates: 0  Warnings: 0

オーバーヘッド
MEMORYからデータのコピー中はサーバースペックも低いのでメモリー＋SWAPが最大限に利用されている。
時間も上記のように12分もかかってしまいました。

メモ：再集計する時に、TRUNCATEするのでTEMPORARYのままでもいいけど、念のためInnoDB間のみでデータコピー速度を確認してみた。
こちらは、1分半程度で終わっています。


root@localhost [confirm]> CREATE TABLE `FTS_WORD_TABLE` (
    ->   `WORD` varchar(337) NOT NULL DEFAULT '',
    ->   `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0',
    ->   `DOC_ID` bigint unsigned NOT NULL DEFAULT '0',
    ->   `POSITION` bigint unsigned NOT NULL DEFAULT '0'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [confirm]> insert into FTS_WORD_TABLE select * from FTS_INDEX_TABLE;
Query OK, 11642045 rows affected (1 min 29.70 sec)
Records: 11642045  Duplicates: 0  Warnings: 0

TWEETワードランキング
以下、直近のTweet集計結果です。コロナの影響もあり、殆どがコロナ関連の単語が上位にきている事が分かります。


root@localhost [confirm]> select rank() OVER(ORDER BY count(*) desc) ranking,WORD,count(*) 
    -> from FTS_WORD_TABLE where length(WORD) > 4 group by WORD having count(*) >= 10000 order by ranking;
+---------+-----------------+----------+
| ranking | WORD            | count(*) |
+---------+-----------------+----------+
|       1 | ウイルス        |   282891 |
|       2 | コロナ          |   232527 |
|       3 | 新型            |   173684 |
|       4 | 感染            |   157965 |
|       5 | https           |   137958 |
|       6 | ます            |   101664 |
|       7 | ない            |    97355 |
|       8 | から            |    81599 |
|       9 | する            |    70057 |
|      10 | こと            |    66332 |
|      11 | まし            |    65643 |
|      12 | いる            |    64802 |
|      13 | です            |    62606 |
|      14 | 拡大            |    43248 |
|      15 | バス            |    40119 |
|      16 | 日本            |    31022 |
|      17 | ある            |    29622 |
|      18 | 対策            |    29344 |
|      19 | ため            |    28480 |
|      20 | この            |    25914 |
|      21 | 中国            |    24729 |
|      22 | てる            |    24267 |
|      23 | など            |    23112 |
|      24 | まで            |    22573 |
|      25 | という          |    22429 |
|      26 | よう            |    22123 |
|      27 | 検査            |    21856 |
|      28 | なっ            |    20436 |
|      29 | 医療            |    20188 |
|      30 | 東京            |    19908 |
|      31 | これ            |    18426 |
|      32 | 影響            |    18350 |
|      33 | 確認            |    18141 |
|      34 | ので            |    17266 |
|      35 | あり            |    17175 |
|      36 | さん            |    16897 |
|      37 | について        |    16705 |
|      38 | ませ            |    16701 |
|      39 | たら            |    16575 |
|      40 | マスク          |    16337 |
|      41 | なり            |    16270 |
|      42 | 緊急            |    16173 |
|      43 | より            |    15770 |
|      44 | なく            |    15662 |
|      45 | 防止            |    15512 |
|      46 | 受け            |    15471 |
|      47 | 世界            |    15462 |
|      48 | って            |    15407 |
|      49 | けど            |    15108 |
|      50 | 発表            |    14998 |
|      51 | として          |    14969 |
|      52 | 政府            |    14914 |
|      53 | れる            |    14863 |
|      54 | yahoonewstopics |    14258 |
|      55 | なる            |    14207 |
|      56 | 自粛            |    12808 |
|      57 | その            |    12730 |
|      58 | 情報            |    12621 |
|      59 | 事態            |    12430 |
|      60 | だけ            |    12010 |
|      61 | 武漢            |    11872 |
|      62 | お知らせ        |    11751 |
|      63 | そう            |    11453 |
|      64 | できる          |    11284 |
|      65 | による          |    11218 |
|      66 | でき            |    11120 |
|      67 | 開催            |    11085 |
|      68 | たい            |    10899 |
|      69 | 宣言            |    10855 |
|      70 | でも            |    10801 |
|      71 | ニュース        |    10720 |
|      72 | 状況            |    10663 |
|      73 | 中止            |    10641 |
|      74 | 新た            |    10626 |
|      75 | おり            |    10507 |
|      76 | 患者            |    10391 |
|      77 | だっ            |    10352 |
|      78 | なら            |    10304 |
|      79 | 時間            |    10096 |
|      80 | 対応            |    10093 |
|      81 | 病院            |    10092 |
+---------+-----------------+----------+
81 rows in set (26.52 sec)

root@localhost [confirm]>

参考： Windows関数 on MySQL

MySQLとPostgreSQLにおけるステートメントの確認

by

MySQLとPostgreSQLにおける重い処理をシステムテーブル（view)から確認する方法

MySQL, PostgreSQLそれぞれ運用するなかで、遅い処理を確認する為にlog_min_duration_statementやslow_query_logを利用して該当のSQLをログに出力して適宜SQLをチューニングする等の対応されていると思います。ここでは、その他にMySQLとPostgreSQLでそれぞれ提供されている、管理用のVIEWを利用して実行されたSQLとその処理の詳細を確認しています。

log_min_duration_statement@PostgreSQL

このパラメータに設定した時間（ミリ秒）以上の処理時間を要したSQLは、そのSQL文とその所要時間をログに出力します。
例えば 10秒以上かかるクエリを特定するには、設定ファイルpostgresql.conf にて以下のように設定を行います。

log_min_duration_statement=10000

long_query_time@MySQL

slow_query_logとslow_query_log_fileシステム変数を使用します。slow_query_logログを無効にするには0に設定し有効にするには1に設定します。設定ファイル my.cnf にて以下のようにlong_query_timeにて所要時間(秒）の設定を行います。また、slow_query_log_fileログファイルの名前を指定するように設定します。これらは、動的パラメータなので稼働中もSETコマンドで設定する事が可能です。

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/host-ip-slow.log
long_query_time = 10

sys.statement_analysis@MySQL

これらのビューは統計を集計し正規化したステートメントをリストします。デフォルトで行は合計レイテンシを降順にソートされています。

27.4.3.35 The statement_analysis and x$statement_analysis Views


[sys]> select db,query,full_scan,exec_count,err_count,warn_count,total_latency,max_latency,avg_latency,lock_latency,rows_sent,tmp_disk_tables,sort_merge_passes from statement_analysis limit 10;
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| db    | query                                                             | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | tmp_disk_tabl
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| APP   | SELECT `WORD` FROM `INFORMATIO ... INNODB_FT_INDEX_TABLE` LIMIT ? | *         |          1 |         0 |          0 | 11.84 min     | 11.84 min   | 11.84 min   | 7.95 ms      |        10 |              
| APP   | SELECT `WORD` , COUNT ( `WORD` ...  ORDER BY `total` DESC LIMIT ? | *         |          1 |         1 |          0 | 9.24 min      | 9.24 min    | 9.24 min    | 153.00 us    |         0 |              
| APP   | COMMIT                                                            |           |      14300 |         0 |          0 | 3.02 min      | 850.07 ms   | 12.66 ms    |   0 ps       |         0 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdata`                           | *         |          3 |         0 |          3 | 29.54 s       | 11.67 s     | 9.85 s      | 6.14 ms      |    833154 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodes`                                | *         |          3 |         0 |          3 | 23.93 s       | 15.15 s     | 7.98 s      | 137.00 us    |   1486554 |              
| APP   | INSERT IGNORE INTO `FTS_dbdata ... TEM_USER , TEXT ) VALUES (...) |           |      14300 |         0 |          0 | 20.06 s       | 895.94 ms   | 1.40 ms     | 5.07 s       |         0 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodetags`                             | *         |          3 |         0 |          3 | 19.41 s       | 6.57 s      | 6.47 s      | 127.00 us    |   8332701 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbda`                             | *         |          3 |         0 |          3 | 17.62 s       | 6.21 s      | 5.87 s      | 130.00 us    |    852759 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdaa`                            | *         |          3 |         0 |          3 | 9.96 s        | 4.23 s      | 3.32 s      | 131.00 us    |    455574 |              
| APP   | SET NAMES ? COLLATE ?                                             |           |      14300 |         0 |          0 | 7.77 s        | 198.99 ms   | 543.57 us   |   0 ps       |         0 |              
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
10 rows in set (0.00 sec)

statement_analysisはevents_statements_summary_by_digestを参照しているので、テーブルに格納されるステートメントは以下のサイズに依存しています。


[sys]> show global variables like 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 10000 |
+---------------------------------+-------+

補足： MySQL SYS Schema


pg_stat_statements@PostgreSQL

pg_stat_statementsモジュールはサーバで実行されたすべてのSQL文の実行時の統計情報を記録する手段を提供します。このモジュールは追加の共有メモリを必要とするため、postgresql.confのshared_preload_librariesにpg_stat_statementsを追加してモジュールをロードしなければなりません。 このことは、このモジュールを追加もしくは削除するにはサーバを再起動する必要があるということを意味しています。

pg_stat_statementsはロードされると、サーバのデータベース全体に渡って統計情報を記録します。 この統計情報にアクセスしたり操作したりするために、このモジュールはビューpg_stat_statementsとユーティリティ関数pg_stat_statements_reset、pg_stat_statementsを提供します。 これらはサーバー全体で利用可能ではなく、CREATE EXTENSION pg_stat_statementsで特定のデータベースで利用可能になります。

参照： F.29. pg_stat_statements

postgresql.confの設定変更(サービスの再起動が必要)


shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.save = on

設定と確認

app=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

app=# \dx
                                        インストール済みの拡張一覧
        名前        | バージョン |  スキーマ  |                           説明                            
--------------------+------------+------------+-----------------------------------------------------------
 pg_freespacemap    | 1.2        | public     | examine the free space map (FSM)
 pg_stat_statements | 1.7        | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0        | pg_catalog | PL/pgSQL procedural language
(3 行)

app=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.7             | 1.7               | track execution statistics of all SQL statements executed
(1 行)


app=# select name, setting, unit from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit 
----------------------------------+---------+------
 pg_stat_statements.max           | 5000    | 
 pg_stat_statements.save          | on      | 
 pg_stat_statements.track         | top     | 
 pg_stat_statements.track_utility | on      | 
(4 行)

実際に記録されたステートメントの確認


app=# \pset pager
「ページャーを使う」は off です。
app=# select userid,dbid,queryid,left(query,30),calls,total_time,min_time,max_time,mean_time,stddev_time,shared_blks_hit,shared_blks_read from pg_stat_statements order by total_time desc;
 userid | dbid  |       queryid        |              left              | calls |      total_time      |       min_time        |       max_time       |      mean_time       |      stddev_time       | shared_blks_hit | shared_blks_read 
--------+-------+----------------------+--------------------------------+-------+----------------------+-----------------------+----------------------+----------------------+------------------------+-----------------+------------------
     10 | 49596 |  5910544648824246769 | select * from memo where id =  |    16 |   352.84530299999994 |              0.016142 |   351.54439399999995 |   22.052831437500004 |      85.07473611928185 |              44 |                4
     10 | 49596 |  6711476591887504002 | explain select * from memo     |     2 |           105.592792 |              0.074625 |           105.518167 |            52.796396 |     52.721771000000004 |              40 |               12
     10 | 49596 |  1258245615633632122 | explain select * from memo whe |     6 |    61.98016300000001 |              0.084408 |            61.497362 |   10.330027166666667 |      22.88272889325042 |               4 |                9
     10 | 49596 |  5761671877250351757 | select name,setting,category f |     3 |   5.1763959999999996 |              1.102193 |             2.606261 |   1.7254653333333332 |     0.6404653712024995 |               0 |                0
     10 | 49596 |    -3158048787087772 | select userid,dbid,queryid,lef |     3 |              0.41186 |   0.13373200000000002 |  0.14129799999999998 |  0.13728666666666667 |  0.0031056397444362608 |               0 |                0
     10 | 49596 |   956172393795458702 | select * from pg_stat_statemen |     3 |  0.26237699999999997 |  0.009907000000000001 |             0.129361 |             0.087459 |   0.054896911898575866 |               0 |                0
     10 | 49596 | -9208585654704159892 | explain select * from memo ord |     3 |             0.250689 |              0.073775 |             0.097503 |             0.083563 |   0.010122049331368954 |               4 |                0
     10 | 49596 |  3231151743520748149 | select userid,dbid,queryid,que |     2 |  0.21030100000000002 |              0.102633 |             0.107668 |  0.10515050000000001 |  0.0025174999999999954 |               0 |                0
     10 | 49596 |  8685157009571086729 | select userid,dbid,queryid,lef |     1 |  0.15841100000000002 |   0.15841100000000002 |  0.15841100000000002 |  0.15841100000000002 |                      0 |               3 |                0
     10 | 49596 |  4048041203833672862 | LOAD 'auto_explain'            |     1 |             0.125106 |              0.125106 |             0.125106 |             0.125106 |                      0 |               0 |                0
     10 | 49596 | -7547750407391136812 | explain select * from memo ord |     1 |             0.098115 |              0.098115 |             0.098115 |             0.098115 |                      0 |               0 |                0
     10 | 49596 |  6755220269130009187 | explain select * from memo ord |     1 |             0.087065 |              0.087065 |             0.087065 |             0.087065 |                      0 |               0 |                0
     10 | 49596 | -1920304080562092233 | SET auto_explain.log_min_durat |     3 |             0.064131 |              0.006951 |             0.049859 |             0.021377 |   0.020140381790489142 |               0 |                0
     10 | 49596 |  9022527725580902277 | select relname,relpages,reltup |     3 |             0.062013 |  0.017379000000000002 |             0.026977 | 0.020671000000000002 |   0.004460459468111628 |               9 |                0
     10 | 49596 | -7365002070969254732 | SELECT tablename,attname,corre |     1 | 0.051623999999999996 |  0.051623999999999996 | 0.051623999999999996 | 0.051623999999999996 |                      0 |              15 |                0
     10 | 49596 | -2189995260333419544 | select ($1 * $2) + ($3 * $4) " |     3 |             0.028727 | 0.0046240000000000005 |             0.019399 | 0.009575666666666666 |    0.00694622239462253 |               0 |                0
     10 | 49596 | -6203021216765832473 | SET auto_explain.log_min_durat |     3 | 0.022913999999999997 |              0.007163 |             0.007885 |             0.007638 | 0.00033596527598349574 |               0 |                0
     10 | 49596 |  2065125607359684573 | SET auto_explain.log_min_durat |     2 |             0.015098 |              0.007356 |             0.007742 |             0.007549 |  0.0001929999999999998 |               0 |                0
     10 | 49596 |  7413539732739831561 | SET auto_explain.log_min_durat |     1 |             0.007563 |              0.007563 |             0.007563 |             0.007563 |                      0 |               0 |                0
     10 | 49596 |  6161394367587035080 | SET auto_explain.log_analyze = |     1 |             0.007052 |              0.007052 |             0.007052 |             0.007052 |                      0 |               0 |                0
     10 | 49596 |  8733460176246631674 | select ($1 * $2) + ($3 * $4) + |     1 |             0.005196 |              0.005196 |             0.005196 |             0.005196 |                      0 |               0 |                0
(21 行)

app=#

pg_stat_statements.maxは、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statementsビューにおける行の最大数)です。
これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。 デフォルトは5000です。 このパラメータはサーバの起動時にのみ指定できます。

app=# select name,setting,unit,context,category,short_desc from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit |  context   |    category    |                              short_desc                              
----------------------------------+---------+------+------------+----------------+----------------------------------------------------------------------
 pg_stat_statements.max           | 5000    |      | postmaster | 独自オプション | Sets the maximum number of statements tracked by pg_stat_statements.
 pg_stat_statements.save          | on      |      | sighup     | 独自オプション | Save pg_stat_statements statistics across server shutdowns.
 pg_stat_statements.track         | top     |      | superuser  | 独自オプション | Selects which statements are tracked by pg_stat_statements.
 pg_stat_statements.track_utility | on      |      | superuser  | 独自オプション | Selects whether utility commands are tracked by pg_stat_statements.
(4 行)

MySQLとPostgreSQLにおける相関係数

by

MySQLとPostgreSQLにおける相関係数

数値の間に相関性があるかどうか説明する場合に、数値化して求められる事があるケースがあると思う。参考値として相関係数を算出して説明する為のMySQLとPostgreSQLの関数概要。
PostgreSQLにはCORRという関数がある。MySQLには相関係数を算出する関数自体は無いが算出する事が可能。比較軸にもよるけれど、説明する上で数値的な参考値としては使える。

−1以上1以下の実数に値をとる。相関係数が正のとき確率変数には正の相関が、負のとき確率変数には負の相関があるという。また相関係数が0のとき確率変数は無相関であるという
参照：WIKI
参考：日経

例）広告宣伝費と売り上げの相関性のシュミレーション

EXCELの場合

CORREL関数を利用
CORREL

下の表は、相関性が高いので相関係数が1になっている。
CORREL関数

PostgreSQLの場合


rails_app=# select corr(ad,sales) from t1;
       corr        
-------------------
 0.848628572784027
(1 row)

rails_app=# select * from t1;
 ad | sales 
----+-------
 10 |    11
 20 |    30
 30 |    20
 40 |    30
 50 |    50
(5 rows)

MySQLの場合


[confirm]> select (( SUM( ad * sales ) - SUM( ad ) * SUM( sales ) / COUNT( ad ) ) / COUNT( sales )) / (STDDEV_POP(ad) * STDDEV_POP(sales)) as corr from t1;
+--------------------+
| corr               |
+--------------------+
| 0.8486285727840269 |
+--------------------+
1 row in set (0.00 sec)

 [confirm]> select * from t1;
+------+-------+
| ad   | sales |
+------+-------+
|   10 |    11 |
|   20 |    30 |
|   30 |    20 |
|   40 |    30 |
|   50 |    50 |
+------+-------+
5 rows in set (0.01 sec)

補足：負の相関関係


root@localhost [confirm]> select (( SUM( ad * price ) - SUM( ad ) * SUM( price ) / COUNT( ad ) ) / COUNT( price )) / (STDDEV_POP(ad) * STDDEV_POP(price)) as corr from t1;
+---------------------+
| corr                |
+---------------------+
| -0.9999999999999999 |
+---------------------+
1 row in set (0.00 sec)

root@localhost [confirm]> select * from t1;
+------+-------+
| ad   | price |
+------+-------+
|   10 |  1000 |
|   20 |   800 |
|   30 |   600 |
|   40 |   400 |
|   50 |   200 |
+------+-------+
5 rows in set (0.00 sec)

root@localhost [confirm]>

MySQLとPostgreSQLの実行プランについて

by

MySQLとPostgreSQLの基本的な実行プランについてまとめてみた

実際のチューニングに関しては、色々と参考になるドキュメントがあったので、そちらを参考にExplainの結果をもとにチューニングして頂ければ思います。

MySQL, PostgreSQLそれぞれ,コストベースに実行プランを作成し殆どのケースで最適な実行プランを作成し、最も少ないコストで適切にデータを抽出してくれる。
MySQLもPostgreSQLも細かく設定が出来るようになっているが、細かくチューニングする検証コストを考えると、SQLやインデックスを追加・変更した方が高速化出来るケースが多々あるので、あまり細かくパラメータを変更して実行プランをコントロールするケースは少ないかと思います。ただ、バージョンアップ等を実施した時に、Optimizerの設定が変わって早くなったり、遅くなったりするクエリーもあるので、処理の重要度など必要に応じてオプティマイザーの設定を下位互換にすることなともあるかと思います。何はともあれ、まずはExplainを見てデータベースの判断および処理方法を確認し、意図したものか？意図しない場合はどのように対応するか検討してみましょう。

root@localhost [mysql]> show global variables like '%innodb_stat%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_include_delete_marked   | OFF         |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_transient_sample_pages  | 8           |
| innodb_status_output                 | OFF         |
| innodb_status_output_locks           | OFF         |
+--------------------------------------+-------------+
9 rows in set (0.00 sec)

root@localhost [mysql]> 


postgres=# select name,setting,unit,context,category,short_desc from pg_settings where name like '%cost%';
             name             | setting | unit | context |                   category                    |                                                     short_desc                                                    
------------------------------+---------+------+---------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------
 autovacuum_vacuum_cost_delay | 2       | ms   | sighup  | 自動VACUUM                                    | 自動VACUUM用のミリ秒単位のコストベースのVACUUM処理の遅延時間です。
 autovacuum_vacuum_cost_limit | -1      |      | sighup  | 自動VACUUM                                    | 自動VACUUM用のVACUUM処理を一時休止させるまでに使用できるコスト。
 cpu_index_tuple_cost         | 0.005   |      | user    | 問い合わせのチューニング / プランナコスト定数 | インデックススキャンにおける一つのインデックスエントリの処理についてプランナで使用する見積もりコストを設定。 
 cpu_operator_cost            | 0.0025  |      | user    | 問い合わせのチューニング / プランナコスト定数 | 一つの演算子または関数の処理についてプランナで使用する見積もりコストを設定。
 cpu_tuple_cost               | 0.01    |      | user    | 問い合わせのチューニング / プランナコスト定数 | 一つのタプル(行)の処理についてプランナで使用する見積もりコストを設定。
 jit_above_cost               | 100000  |      | user    | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルを実行します。
 jit_inline_above_cost        | 500000  |      | user    | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルされた関数をインライン化します。
 jit_optimize_above_cost      | 500000  |      | user    | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルされた関数を最適化します。
 parallel_setup_cost          | 1000    |      | user    | 問い合わせのチューニング / プランナコスト定数 | 並列問い合わせ実行のためのワーカプロセスの起動についてプランナで使用する見積もりコストを設定。
 parallel_tuple_cost          | 0.1     |      | user    | 問い合わせのチューニング / プランナコスト定数 | 並列処理ワーカからマスタバックエンドへの一つのタプル(行)の受け渡しについてプランナが使用する見積もりコストを設定。
 random_page_cost             | 4       |      | user    | 問い合わせのチューニング / プランナコスト定数 | ひと続きでは読み込めないディスクページについてプランナで使用する見積もりコストを設定。
 seq_page_cost                | 1       |      | user    | 問い合わせのチューニング / プランナコスト定数 | ひと続きに読み込むディスクページについてプランナで使用する見積もりコストを設定。
 vacuum_cost_delay            | 0       | ms   | user    | 使用リソース / コストベースvacuum遅延         | ミリ秒単位のコストベースのVACUUM処理の遅延時間です。
 vacuum_cost_limit            | 200     |      | user    | 使用リソース / コストベースvacuum遅延         | VACUUM処理を一時休止させるまでに使用できるコスト。
 vacuum_cost_page_dirty       | 20      |      | user    | 使用リソース / コストベースvacuum遅延         | VACUUM処理が1つのページをダーティにした際に課すコスト。
 vacuum_cost_page_hit         | 1       |      | user    | 使用リソース / コストベースvacuum遅延         | バッファキャッシュにある1つのページをVACUUM処理する際のコスト。
 vacuum_cost_page_miss        | 10      |      | user    | 使用リソース / コストベースvacuum遅延         | バッファキャッシュにない1つのページをVACUUM処理する際のコスト。
(17 行)

Explain Sample

PostgreSQLの場合 (例：Hash Join)
PostgreSQLには、Marge JOIN, Hash JOIN, Nested LoopのJOINの方法があり、オプティマイザーが最適なJOIN方式を選択してJOINが実行されます。

app=# explain select * from mem2 join memo using(id);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Hash Join  (cost=598.00..623.67 rows=660 width=116)
   Hash Cond: (mem2.id = memo.id)
   ->  Seq Scan on mem2  (cost=0.00..16.60 rows=660 width=94)
   ->  Hash  (cost=348.00..348.00 rows=20000 width=26)
         ->  Seq Scan on memo  (cost=0.00..348.00 rows=20000 width=26)
(5 行)

app=# explain select * from memo join mem2 on memo.id = mem2.id;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Hash Join  (cost=598.00..623.67 rows=660 width=120)
   Hash Cond: (mem2.id = memo.id)
   ->  Seq Scan on mem2  (cost=0.00..16.60 rows=660 width=94)
   ->  Hash  (cost=348.00..348.00 rows=20000 width=26)
         ->  Seq Scan on memo  (cost=0.00..348.00 rows=20000 width=26)
(5 行)

app=#

MySQLの場合 (例:Nested loop)
MySQLはNested Loopを利用してJOINを実行 (MySQL8.0.18以降:Hash Joinをサポート)
Nested Loopの場合は、駆動表（外部表）のデータを出来るだけ少なくして、内部表のテーブルにインデックスが張ってあれば他のJOINの方式よりも高速化可能。


root@localhost [confirm]> explain select * from mem2 join memo using(id);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
|  1 | SIMPLE      | memo  | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            |   22 |   100.00 | NULL  |
|  1 | SIMPLE      | mem2  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | confirm.memo.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

root@localhost [confirm]> explain select * from memo join mem2 on memo.id = mem2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
|  1 | SIMPLE      | memo  | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            |   22 |   100.00 | NULL  |
|  1 | SIMPLE      | mem2  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | confirm.memo.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

root@localhost [confirm]> explain select * from mem2 straight_join memo using(id);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
|  1 | SIMPLE      | mem2  | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            |   10 |   100.00 | NULL  |
|  1 | SIMPLE      | memo  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | confirm.mem2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

root@localhost [confirm]> explain format=tree select * from mem2 straight_join memo using(id);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=12.25 rows=10)
    -> Table scan on mem2  (cost=1.25 rows=10)
    -> Single-row index lookup on memo using PRIMARY (id=mem2.id)  (cost=1.01 rows=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL8.0.18からはHash Joinもサポートされています。


Hash joins have been implemented as a way of executing inner equi-joins in MySQL. For example, a query such as this one can be executed as a hash join beginning with this release.
A hash join requires no index for execution. In most cases, a hash join is more efficient than the block-nested loop algorithm previously used for equi-joins without indexes.
By default, beginning with this release, a hash join is used whenever a join includes at least one equi-join condition. This preference can be overridden by setting the hash_join optimizer switch to off, or by using the NO_HASH_JOIN optimizer hint. In addition, you can control the amount of memory used by a hash join by setting join_buffer_size. A join whose memory requirement exceeds this amount is executed on disk; an on-disk hash join uses a number of disk files and may not be executable if this number exceeds open_files_limit.
A hash join cannot be employed if the join conditions for any pair of joined tables do not include at least one equi-join condition among all join conditions used. A hash join is used for a Cartesian product—that is, a join that specifies no join conditions at all.
You can see whether hash joins have been used to optimize a query in the output of EXPLAIN FORMAT=TREE or EXPLAIN ANALYZE.
In addition, inner joins using hash joins can now also take advantage of Batched Key Access (BKA). Outer joins still allocate the entire join buffer.
For more information, see Hash Join Optimization.

参照：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

MySQL8.0.20で更にHASH JOINコンディションが拡張されています。

Hash joins are now used any time a nested block loop would be employed. This means that hash joins can be used for the following types of queries:

Inner non-equi-joins
Semijoins
Antijoins
Left outer joins
Right outer joins

This builds on work done for MySQL 8.0.18, and removes a limitation in the implementation such that a hash join could be used only with a query having at least one equi-join condition. In addition, both inner and outer joins (including semijoins and antijoins) can now employ batched key access (BKA), which allocates join buffer memory incrementally so that individual queries need not use up large amounts of resources that they do not actually require for resolution. For more information, see Batched Key Access Joins.

This fix completes the task of replacing the executor used in previous versions of MySQL with the iterator executor, including replacement of the old index subquery engines that governed queries of the form WHERE value IN (SELECT column FROM table WHERE condition) for those IN queries which have not been converted into semijoins, as well as queries materialized into the same form, which depended on internals from the old executor.

For more information and examples, see Hash Join Optimization. (Bug #30528604, Bug #30473261, Bug #30912972)

参照：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html

基本的な統計情報とExplainのまとめ

MySQLとPostgreSQLにおけるインデックスメンテナンス

by

MySQLにしてもPostgreSQLにしてもある程度のデータを扱うのであれば、インデックスが利用出来るようなwhere句を書いて処理の最適化を行うと思いますので、MySQLとPostgreSQLのインデックスのメンテナンスをメモとして記録。

インデックスメンテナンス@MySQL

フラグメンテーションの確認

SELECT 
table_schema as `Database`, 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size_in_MB`,
round((DATA_FREE/1024/1024),2) 'Free_in_MB',
(round((DATA_FREE/1024/1024),2)/round(((data_length + index_length) / 1024 / 1024), 2)) * 100 as 'Fragment(%)'
FROM information_schema.TABLES where table_schema = 'test' ORDER BY (data_length + index_length) limit 10;

ONLINEで処理するのであればALTERコマンドで良いでしょう。

root@localhost [APP]> ALTER TABLE T_scripts engine = InnoDB, ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [APP]>

FTSを設定している場合はALGORITHM=INPLACE,LOCK=NONEオプションは利用できません。

OPTIMIZEでも可能ですが、5.6.17までは参照ロックがかかるので更新処理が停止してしまいます。

For InnoDB tables prior to 5.6.17 and other table types, MySQL locks the table during the time. OPTIMIZE TABLE is running. As of MySQL 5.6.17, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. 


root@localhost [APP]> optimize table T_scripts;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| APP.T_scripts | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.00 sec)

root@localhost [APP]>

データベース全体


[ec2-user]$ mysqlcheck --optimize APP -u root -p
Enter password: 
APP.FTS_Tweets                                     OK
APP.FTS_Tweets2                                    OK
APP.FTS_Tweets3                                    OK
APP.T_Article                                      OK
APP.T_Articles                                     OK
APP.users                                          OK
[ec2-user]$

インデックスメンテナンス@PostgreSQL

### 特定のINDEXのメンテンナンス

tasks_db=# reindex index index_tasks_on_user_id;
REINDEX

特定のテーブルのインデックスメンテンナンス

tasks_db=# reindex table tasks;
REINDEX
tasks_db=#

特定データベースのINDEXメンテンナンス

tasks_db=# reindex database tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
NOTICE:  table "public.schema_migrations" was reindexed
<SNIP>
NOTICE:  table "public.active_storage_attachments" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
NOTICE:  table "information_schema.sql_features" was reindexed
REINDEX
tasks_db=#

特定データベースのシステムカタログのINDEXメンテンナンス

tasks_db=# reindex system tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
<SNIP>
NOTICE:  table "pg_catalog.pg_range" was reindexed
NOTICE:  table "pg_catalog.pg_largeobject" was reindexed
NOTICE:  table "pg_catalog.pg_attrdef" was reindexed
NOTICE:  table "pg_catalog.pg_index" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
REINDEX
tasks_db=#

verboseオプションによるステータス表示
メモ：Clusterコマンドでは物理配列の復活

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=#

REINDEX中はテーブルがロックされるので、更新処理はできません。ロックの影響を避けたい場合は、CONCURRENTLYオプションを付けると対象テーブルのロックを取得しないため、実行中も対象テーブルの更新が可能です。
メンテナンスしたいINDEXと同じインデックスをCONCURRENTLYで作成して作成後に古いINDEXを削除する。(オンラインメンテナンス）

app=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | t_post | table | app_user
(1 row)

app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 

app=# create index concurrently idx_note on t_post(note);
CREATE INDEX
app=# 

<運用してインデックスのフラグメンテーション発生>

app=# create index concurrently idx_note_new on t_post(note);
CREATE INDEX
app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 
Indexes:
    "idx_note" btree (note)
    "idx_note_new" btree (note)

app=# drop index idx_note;
DROP INDEX
app=#

Clustered Indexについて

MySQLではClusterインデックスとしてPrimary KeyやUnique Index（NOT NULL)がクラスター化されクラスター性を保ちますが、
PostgreSQLではClusterを実施した直後のテーブルはCluster化されテーブルデータの並び順はインデックスの順番になりますが、
そのあと運用していく中でクラスター性が無くなっていきます。

CLUSTERインデックス@MySQL
すべての InnoDB テーブルは、行のデータが格納されているクラスタ化されたインデックスと呼ばれる特別なインデックスを持っています。一般に、クラスタ化されたインデックスは主キーのシノニムです。クエリー、挿入、およびその他のデータベース操作で最適なパフォーマンスを実現するには、InnoDB がクラスタ化されたインデックスを使用して、テーブルごとにもっとも一般的な検索と DML 操作を最適化する方法について理解する必要があります。テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。作成するテーブルごとに主キーを定義します。論理的に一意で、Null 以外のカラムまたはカラムのセットが存在しない場合は、自動的に値が入力される新しい自動インクリメントカラムを追加します。

テーブルに PRIMARY KEY が定義されていない場合、MySQL はすべてのキーカラムが NOT NULL の UNIQUE インデックスを最初に検索し、InnoDB はそれをクラスタ化されたインデックスとして使用します。テーブルに PRIMARY KEY も適切な UNIQUE インデックスも存在しない場合には、InnoDB の内部で、行 ID 値を含む合成カラム上に非表示のクラスタ化されたインデックスが生成されます。そのようなテーブルでは、InnoDB が行に割り当てる ID に基づいて行の順序付けが行われます。行 ID は、新しい行が挿入されると単調に増加する 6 バイトのフィールドです。したがって、行 ID で順序付けられた行が物理的な挿入順になります。

CLUSTERインデックス@PostgreSQL

テーブルがクラスタ化されると、それぞれのテーブルはインデックス情報に基づいて物理的に並べ直されます。クラスタ化は、1回限りの操作です。 クラスタ化後にテーブルが更新されても、その変更はクラスタ化されません。 つまり、新規に追加された行や更新された行は、インデックス順には保管されません。 （インデックス順に保管したい場合は、コマンドを再度入力し、定期的に再クラスタ化を行います。 また、更新される行は十分な領域が利用可能ならば同一ページ内に保持されますので、テーブルのFILLFACTOR格納パラメータを100%より小さく設定することで、更新処理中のクラスタ順序付けを保護するという点で役に立ちます。）

テーブルがクラスタ化されると、PostgreSQLはクラスタ化に使用されたインデックスを記録します。 CLUSTER table_nameという構文によって、以前と同じインデックスを使用してテーブルを再クラスタ化します。 またALTER TABLEのCLUSTERもしくはSET WITHOUT CLUSTER構文を使用して、将来のクラスタ化操作で使用するインデックスを設定したり、任意の過去の設定を取り消すことができます。

Clusterコマンドを実行すると、Reindexも実行されるので断片化とクラスター性の改善を同時に実施可能です。（Vacuum Fullと同等）
注意：一時的に対象テーブル・インデックスと同等のディスク容量が必要　（MySQLのAlter table algorithm=CPOPYと同等）
　　　Clusterコマンド実行時には排他ロックがかかります。（大きなテーブルの場合は事前に検証が必要）

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=# cluster memo;
CLUSTER
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# select relname from pg_class where oid in (select indexrelid from pg_class c,pg_index i where c.oid = i.indrelid and i.indisclustered = 't');
   relname   
-------------
 idx_memo_id
(1 行)

app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# alter table memo set without cluster;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id)

app=# alter table memo cluster on idx_memo_id;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=#

その他：
WL#6747: InnoDB: make fill factor settable
innodb_fill_factor
第三回 HOTの上手な使い方

DB参照ツールmetabase

by

エンジニア以外のメンバーが気軽にデータベースを参照出来るツールは無いかと見てみたら、metabaseは使い勝手が良さそうだなと感じました。

1) アカウントを複数作成出来る
2) グループに分けられる
3) クエリーを使うこともGUIで参照する事も可能
4) Slackやメールとの連携が可能
5) OpenSourceである事。（翻訳に協力しようと考えてます)
6) 管理工数、セットアップが容易
7) ビジネス側のメンバーが容易にグラフで状況を理解する事が可能
8) 非エンジニアでも、SQLを少し学べばカスタマイズ出来る事（日付範囲等)

インストール (CentOS7を利用しています)
Javaのバージョンが要件を満たしていれば、metabase.jarをダウンロードしきて実行するだけ。


[root@GA01 admin]# java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)
[root@GA01 admin]# 


[root@GA01 metabase]# wget http://downloads.metabase.com/v0.30.4/metabase.jar
--2018-10-27 21:13:55--  http://downloads.metabase.com/v0.30.4/metabase.jar
downloads.metabase.com (downloads.metabase.com) をDNSに問いあわせています... 52.216.100.114
downloads.metabase.com (downloads.metabase.com)|52.216.100.114|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 117673285 (112M) [application/java-archive]
`metabase.jar' に保存中

[root@GA01 metabase]# java -jar /usr/local/metabase/metabase.jar 
10-27 21:25:49 INFO util.encryption :: 保存された資格情報は、このMetabase Instanceでは無効化されています 🔓 
For more information, see https://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest
10-27 21:26:44 INFO metabase.core :: MetabseをSTANDALONEモードで起動中
10-27 21:26:44 INFO metabase.core :: configで埋め込みJetty Webserverを起動中： 
 {:port 3000}

10-27 21:26:45 INFO metabase.core :: Metabseバージョンv0.30.4 (8bf182f release-0.30.4)を起動中...
10-27 21:26:45 INFO metabase.core :: システムタイムゾーンは"Asia/Tokyo"です
WARNING: any? already refers to: #'clojure.core/any? in namespace: monger.collection, being replaced by: #'monger.collection/any?
10-27 21:27:05 INFO metabase.core :: Metabse DBを移動設定中。これには時間がかかることがあります。
10-27 21:27:05 INFO metabase.db :: h2データベース接続を確認中...
10-27 21:27:06 INFO metabase.db :: データベース接続を確認... ✅

インストールが終わったら以下のURLにアクセスして初期設定
Default Portは3000ですが、初期設定後に設定画面からURL含めて変更する事が可能です。


http://localhost:3000/setup/

レポーティングとクエリーコンソール

ユーザー管理
アカウントやグループ毎に関連するデータベース接続があっても良いかもしれません。（監査等）
共通アプリケーションアカウントでも問題ありません。
機密情報や重要な情報にアクセス出来ないようにデータベースアカウントの権限を絞ると良いかと思います。

その他、Slack等との連携

なかなか便利なツールです。非エンジニア以外の方にはテンプレートを作成してあげたり、読み込み専用DBにアクセスを分けてあげると安心です。
Let’s enjoy metabase.

DB Tech Showcase Tokyo 2018

by

DB Tech Showcase 2018 Tokyo

DB Tech Showcase 2018 TokyoにてInnoDB Clusterを用いた、MySQLの高可用性構成への取り組みに関して紹介させて頂きました。プロダクションのデータベースなので、これまでの機能紹介やシンプルなデモとは異なり、様々な確認や調整が必要で、DBのアップグレード以上に時間を割いている感じです。移行後は、シンプルにマスターを切り替える事で、MySQL Routerが自動的に接続を切り替えてくれるので非常に楽になります。
また、作業工数も大幅に削減出来るので、会社にとってもダウンタイムによる機会損失削減といったメリットだけでなく、エンジニアの工数削減によりサービス開発に専念が出来るようになり非常に良いソリューションだと考えています。

これからもMySQLだけでは無く、様々な高可用性構成をコストと運用の観点から考察して、順次導入していきたいと思っています。
システム移行と同時に様々な、改善も並行して出来るので1年を目途に最適化が出来ると良いかなと思ってます。

“MySQL5.6から5.7へ、そして更なるサービスの可用性を目指して ～急成長する靴の通販 LOCONDO.jp を支えるサービス安定化への取り組み”

補足：
資料には明記してませんが、シングルプライマリーモードで構成しているので、グループレプリケーション構成直後に以下の値を全て1に変更しています。


group_replication_auto_increment_increment 
auto_increment_increment
auto_increment_offset

Group Replicationのマスター、スレーブ間のレプリケーションラグの確認方法

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-1),':',1),'-',-1) last_executed;select @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-1),':',1),'-',-1) last_received FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';select (@last_rec - @last_exec) as real_lag;

補足：RECOVERY中は値が正確に取得出来ない様子
https://bugs.mysql.com/bug.php?id=92219

Connector/Jの設定確認

by

MySQL用のコネクター、Connector/Jの動作を改めて確認してみました。
検証した内容としては、jdbc:mysql、jdbc:mysql:replication、jdbc:mysql＋ReplicationDriver, jdbc:mysql:loadbalanceの接続方法による挙動の違い。

検証環境：
Connector/J:　mysql-connector-java-community-5.1.36-bin.jar
MySQL環境：　 mysql 5.7.21 でグループレプリケーション（シングルマスターモード）


-bash-4.2$ ./gr_mysql_status.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 1d69db5d-a273-11e8-b673-080027d65c57 | replications | 3310 | ONLINE |
| group_replication_applier | 271d12be-a273-11e8-bc0a-080027d65c57 | replications | 3320 | ONLINE |
| group_replication_applier | 2e395847-a273-11e8-866f-080027d65c57 | replications | 3330 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$

オフィシャルマニュアルには以下のように書いてあるけど、十分に検証しないといけないので少し落ち着いたタイミングにならないとアップグレードは少々躊躇する。
変更点を一つ一つ確認していくのは難しいので、にバージョン毎の差を明確に、分かりやすく書いてあるマニュアルがあると有難い。
現状でも、5.1.17～5.1.47までリリースがある。

MySQL Connector / J 8.0は、MySQL Server 8.0,5.7,5.6、および5.5での使用を強く推奨します。 MySQL Connector / J 8.0にアップグレードしてください。

Connector / J 5.1は、JDBC 3.0,4.0,4.1、および4.2仕様に準拠したタイプ4の純粋なJava JDBCドライバーです。 5.5,5.6、および5.7を含むMySQLのすべての機能との互換性を提供します。 Connector / J 5.1は、Driver Managerによる自動登録、標準化された妥当性チェック、分類されたSQLExceptions、大規模な更新回数のサポート、java.timeパッケージからのローカルおよびオフセットの日時のサポートのサポート、 JDBC-4.x XML処理、接続ごとのクライアント情報のサポート、NCHAR、NVARCHARおよびNCLOBデータ型のサポートします。

Connector / J 8.0は、Java 8プラットフォーム用のタイプ4の純粋なJava JDBC 4.2ドライバーです。 これは、MySQL 5.5,5.6,5.7,8.0のすべての機能との互換性を提供します。

Connector / Jのバージョンの概要

*いくつかの暗号スイートを使用する場合、Connector / J 5.1がMySQL 5.6,5.7、および8.0にSSL / TLSで接続するためには、JRE 1.8.xが必要です。
*Connector / Jの現在の推奨バージョンは5.1です。 このガイドでは、以前のバージョンのConnector / Jについても説明します。具体的な注釈は、設定が特定のバージョンに適用される場合に指定します。

Connector / Jに必要なJavaバージョンの要約

Pattern(1): Standard Connector/J (jdbc:mysql)

8.1 Configuring Server Failover


[root@GA01 java]# cat conn_j_standard.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard{
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql://" + strSQLNode1 + "," +strSQLNode2 +',' + strSQLNode3 + "/" +
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&connectTimeout=30000"+
"&socketTimeout=30000"+
"&maxReconnects=1"+
"&initialTimeout=1"+
"&loadBalanceBlacklistTimeout=3600000"+
"&loadBalancePingTimeout=100"
);
//conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。 基本的には、常時左から順に利用可能なサーバーにアクセスして処理します。 但し、アクセスしているサーバーに障害が発生した場合は、例2のようにフェールオーバーする事が可能です。


[root@GA01 java]# ls -l conn_j_standard.*
-rw-r--r--. 1 root root 2275 8月 18 08:26 conn_j_standard.class
-rw-r--r--. 1 root root 2443 8月 18 08:26 conn_j_standard.java
[root@GA01 java]#

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3310

例2）10回目の接続時に最初にアクセスしたサーバーをダウンさせたらフェールオーバーします。一番、左側に記載したサーバーが利用可能になると、一番左側のサーバーにアクセスします。（オプション設定）

[root@GA01 java]# java testmysql
0 replications 3310
1 replications 3310
2 replications 3310
3 replications 3310
4 replications 3310
5 replications 3310
6 replications 3310
7 replications 3310
8 replications 3310
9 replications 3310
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3320

Pattern(2): Connector/J Replication接続 (jdbc:mysql:replication)

8.3 Configuring Master/Slave Replication with Connector/J


[root@GA01 java]# cat conn_j_replication.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_replication {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";

conn = DriverManager.getConnection("jdbc:mysql:replication://" + strSQLNode1 + "," +strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&autoReconnect=true" +
"&autoReconnectForPools=true"+
"&failOverReadOnly=true"+
"&roundRobinLoadBalance=true"+
"&initialTimeout=1"
);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

コンパイルして実行してみます。基本的に,　以下の設定でマスターへの接続かスレーブへの接続か判断して処理します。

conn.setReadOnly(false)　＝ マスター接続（特に記載なければマスター接続)
conn.setReadOnly(true)　 ＝　スレーブ


[root@GA01 java]# javac conn_j_replication.java
[root@GA01 java]# java conn_j_replication
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3320
7 replications 3330
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3320
13 replications 3320
14 replications 3320
15 replications 3330
[root@GA01 java]#

Pattern(3): Connector/J Replication接続② (jdbc:mysql + 明示的にレプリケーションドライバーを指定)
マニュアルには具体的な内容は発見出来ていませんが以下のようにもコメントされています。現状では上記のようにjdbc:mysql:Replicationでコーディングする方が主流なのかと。

Deprecate宣言

com.mysql.jdbc.[NonRegistering]Driver now understands URLs of the format jdbc:mysql:replication:// and jdbc:mysql:loadbalance:// which will create a ReplicationConnection (exactly like when using [NonRegistering]ReplicationDriver) and an experimental load-balanced connection designed for use with SQL nodes in a MySQL Cluster/NDB environment, respectively.

In an effort to simplify things, we’re working on deprecating multiple drivers, and instead specifying different core behavior based upon JDBC URL prefixes, so watch for [NonRegistering]ReplicationDriver to eventually disappear, to be replaced with com.mysql.jdbc[NonRegistering]Driver with the new URL prefix.

https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-0-6.html

Remove宣言

4.3.1.6 Other Changes
Here are other changes with Connector/J 8.0:

Removed ReplicationDriver. Instead of using a separate driver, you can now obtain a connection for a replication setup just by using the jdbc:mysql:replication:// scheme.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html

If you have a write transaction, or if you have a read that is time-sensitive (remember, replication in MySQL is asynchronous), set the connection to be not read-only, by calling Connection.setReadOnly(false) and the driver will ensure that further calls are sent to the master MySQL server. The driver takes care of propagating the current state of autocommit, isolation level, and catalog between all of the connections that it uses to accomplish this load balancing functionality.

To enable this functionality, use the com.mysql.jdbc.ReplicationDriver class when configuring your application server’s connection pool or when creating an instance of a JDBC driver for your standalone application. Because it accepts the same URL format as the standard MySQL JDBC driver, ReplicationDriver does not currently work with java.sql.DriverManager-based connection creation unless it is the only MySQL JDBC driver registered with the DriverManager .

書き込みトランザクションがある場合や、読み込み時間が重要な場合（MySQLのレプリケーションが非同期であることを覚えておいてください）、Connection.setReadOnly（false）を呼び出して接続を読み取り専用に設定すると、ドライバは それ以降のコールがマスターMySQLサーバーに送信されるようにしてください。 ドライバは、このロードバランシング機能を達成するために使用するすべての接続間で、自動コミット、分離レベル、およびカタログの現在の状態を伝達します。

この機能を有効にするには、アプリケーション・サーバーの接続プールを構成するとき、またはスタンドアロン・アプリケーション用のJDBCドライバのインスタンスを作成するときにcom.mysql.jdbc.ReplicationDriverクラスを使用します。 標準のMySQL JDBCドライバと同じURLフォーマットを受け入れるため、DriverManagerに登録されている唯一のMySQL JDBCドライバでない限り、ReplicationDriverは現在java.sql.DriverManagerベースの接続作成では動作しません。 


[root@GA01 java]# cat conn_j_standard2.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_standard2 {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
ReplicationDriver driver = new ReplicationDriver();

try {
for(int i=0; i < 100; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("roundRobinLoadBalance","true");
props.put("failOverReadOnly","true");
props.put("user","router_user");
props.put("password","password");

Connection conn =
driver.connect(
"jdbc:mysql://192.168.56.111:3310,192.168.56.111:3320,192.168.56.111:3330/demodb",props);

conn.setReadOnly(true);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}
[root@GA01 java]#

jdbc:mysql:Replicationと同じく、conn.setReadOnly(false)はマスターにconn.setReadOnly(true)はスレーブに参照しに行きます。
上記の例だとスレーブ間をラウンドロビンします。

[root@GA01 java]javac conn_j_standard2.java
[root@GA01 java]# java conn_j_standard2
0 replications 3320
1 replications 3330
2 replications 3330
3 replications 3330
4 replications 3330
5 replications 3330
6 replications 3330
7 replications 3320
8 replications 3330
9 replications 3320
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3320
14 replications 3320
15 replications 3330

Pattern(4): Connector/J Load Balance処理用の接続 (jdbc:mysql:loadbalance)

8.2 Configuring Load Balancing with Connector/J


[root@GA01 java]# cat conn_j_loadbalance.java
import java.sql.*;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

class conn_j_loadbalance {
public static void main (String args[])
throws SQLException, ClassNotFoundException{
Connection conn = null;

try {
for(int i=0; i < 10000; i++){
try{
Thread.sleep(500);
} catch (Exception ex) {
// handle any errors
System.out.println(ex);
}

String strSQLNode1 = "192.168.56.111:3310";
String strSQLNode2 = "192.168.56.111:3320";
String strSQLNode3 = "192.168.56.111:3330";
String strDatabase = "";
conn = DriverManager.getConnection("jdbc:mysql:loadbalance://" + strSQLNode1 + "," + strSQLNode2 + "," + strSQLNode3 + "/"+
strDatabase+"demodb?" +
"user=router_user" +
"&password=password" +
"&loadBalanceConnectionGroup=first" +
"&loadBalanceEnableJMX=true"
);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select @@hostname,@@port from dual");
while ( rset.next() ) {
System.out.println(i + "\t" + rset.getString(1) + "\t" + rset.getString(2));
}
rset.close();
stmt.close();
conn.close();
}
} catch (SQLException ex) {
// handle any errors
System.out.println(ex);
}
}
}

[root@GA01 java]#

以下のように、全てのサーバーにロードバランスされるのでマルチマスター環境で利用可能です。MySQL NDB Cluster, MySQL InnoDB Cluster (Multi Master設定）の構成で便利です。
但し、InnoDB Clusterの場合は制限事項があるので、バッチ処理等と同じデータベースと利用する場合は十分に検証が必要。


[root@GA01 java]# javac conn_j_loadbalance.java
[root@GA01 java]# ls -l conn_j_loadbalance.*
-rw-r--r--. 1 root root 1992 8月 18 09:03 conn_j_loadbalance.class
-rw-r--r--. 1 root root 1890 8月 18 09:04 conn_j_loadbalance.java
[root@GA01 java]#

[root@GA01 java]# java conn_j_loadbalance
0 replications 3330
1 replications 3330
2 replications 3320
3 replications 3310
4 replications 3310
5 replications 3330
6 replications 3310
7 replications 3330
8 replications 3330
9 replications 3330
10 replications 3320
11 replications 3320
12 replications 3330
13 replications 3330
14 replications 3320
15 replications 3310

参考：
MySQL Connector/J 5.1 Developer Guide
Chapter 10 Using Connector/J with Tomcat
Chapter 12 Using Connector/J with Spring

MySQL8.0 SYS Schema

by

MySQL8.0 SYSスキーマの確認。　
基本的には、MySQL5.7と変わりは無いですが、ロックを取得するVIEW内部のテーブルがInformation_schemaからPerformance_Schemaに変更になったりしている。但し、VIEW内部のスクリプト変更なので基本的に問題無く、これまで通り利用する事が可能です。

オフィシャルマニュアル： Chapter 26 MySQL sys Schema

確認したバージョン


root@localhost [mysql]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [mysql]>

ドキュメント