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 SlapをNDB対して実行して、メモリー割り当て変化の簡単な確認

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

[root@misc bin]# 

ndb

Node, alloc_bytes,free_bytesを確認する限りだと、ノード間のデータは同期されているので均等に分かれている。
こちらに、追加でノードグループを増やしたときのテーブル分割を次回確認してみる。

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       1 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       2 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.02 sec)

mysql> select * from resources;
+---------+---------------------+----------+------+-------+
| node_id | resource_name       | reserved | used | max   |
+---------+---------------------+----------+------+-------+
|       1 | RESERVED            |     4113 | 5495 | 13703 |
|       1 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       1 | DISK_RECORDS        |        0 |    0 |     0 |
|       1 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       1 | JOBBUFFER           |        0 |    0 |     0 |
|       1 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       1 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       1 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       1 | QUERY_MEMORY        |        0 |    0 |     0 |
|       1 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
|       2 | RESERVED            |     4113 | 5495 | 13703 |
|       2 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       2 | DISK_RECORDS        |        0 |    0 |     0 |
|       2 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       2 | JOBBUFFER           |        0 |    0 |     0 |
|       2 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       2 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       2 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       2 | QUERY_MEMORY        |        0 |    0 |     0 |
|       2 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
+---------+---------------------+----------+------+-------+
20 rows in set (0.01 sec)

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       1 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       2 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       98304 |       6624 |
|    1 |    1 |       98304 |       4080 |
|    2 |    0 |       98304 |       6624 |
|    2 |    1 |       98304 |       4032 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      524288 |      26928 |
|    1 |    1 |      524288 |      27984 |
|    2 |    0 |      524288 |      26880 |
|    2 |    1 |      524288 |      27984 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      819200 |      27888 |
|    1 |    1 |      819200 |      30720 |
|    2 |    0 |      819200 |      27888 |
|    2 |    1 |      819200 |      30672 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |     1212416 |      22272 |
|    1 |    1 |     1212416 |      16080 |
|    2 |    0 |     1212416 |      22272 |
|    2 |    1 |     1212416 |      16032 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
Empty set (0.02 sec)

mysql> 

mysqlslap — クライアント負荷エミュレーション

mysqlslapはMySQLサーバのクライアント負荷をエミュレートし、各ステージのタイミングを報告する
診断プログラムです。サーバにたいして複数のクライアントがアクセスしているかのように作動します。
mysqlslapはMySQL 5.1.4.から提供されています。

[root@colinux data]# mysqlslap --no-defaults -V
mysqlslap  Ver 1.0 Distrib 5.5.0-m2, for pc-linux-gnu (i686)
[root@colinux data]#
[root@colinux data]# mysqlslap --no-defaults -?
mysqlslap  Ver 1.0 Distrib 5.5.0-m2, for pc-linux-gnu (i686)
Copyright (C) 2005 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Run a query multiple times against the server

Usage: mysqlslap [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlslap client
The following options may be given as the first argument:
<code>--print-defaults </code>       Print the program argument list and exit
<code>--no-defaults</code>           Don't read default options from any options file
<code>--defaults-file=</code>#       Only read default options from the given file #
<code>--defaults-extra-file=</code># Read this file after the global files are read
<code>  -?, --help </code>         Display this help and exit.
<code>  -a, --auto-generate-sql</code>
                      Generate SQL where not supplied by file or command line.
<code>  --auto-generate-sql-add-autoincrement</code>
                      Add an AUTO_INCREMENT column to auto-generated tables.
<code>  --auto-generate-sql-execute-number=#</code>
                      Set this number to generate a set number of queries to
                      run.
<code>  --auto-generate-sql-guid-primary</code>
                      Add GUID based primary keys to auto-generated tables.
<code>  --auto-generate-sql-load-type=name</code>
                      Specify test load type: mixed, update, write, key, or
                      read; default is mixed.
<code>  --auto-generate-sql-secondary-indexes=</code>#
                      Number of secondary indexes to add to auto-generated
                      tables.
<code>  --auto-generate-sql-unique-query-number=</code>#
                      Number of unique queries to generate for automatic tests.
<code>  --auto-generate-sql-unique-write-number=</code>#
                      Number of unique queries to generate for
                      auto-generate-sql-write-number.
<code>  --auto-generate-sql-write-number=</code>#
                      Number of row inserts to perform for each thread (default
                      is 100).
<code>  --commit=</code>#          Commit records every X number of statements.
<code>  -C, --compress </code>     Use compression in server/client protocol.
<code>  -c, --concurrency=name</code>
                      Number of clients to simulate for query to run.
<code>  --create=name  </code>     File or string to use create tables.
<code>  --create-schema=name</code>
                      Schema to run tests in.
<code>  --csv[=name]  </code>      Generate CSV output to named file or to stdout if no file
                      is named.
<code>  -#, --debug[=#] </code>    This is a non-debug version. Catch this and exit.
<code>  --debug-check </code>      Check memory and open file usage at exit.
<code>  -T, --debug-info</code>    Print some debug info at exit.
<code>  -F, --delimiter=name</code>
                      Delimiter to use in SQL statements supplied in file or
                      command line.
 <code> --detach=</code>#          Detach (close and reopen) connections after X number of
                      requests.
<code>  -e, --engine=name </code>  Storage engine to use for creating the table.
<code> -h, --host=name </code>    Connect to host.
<code>  -i, --iterations=</code>#  Number of times to run the tests.
<code>  -x, --number-char-cols=name</code>
                      Number of VARCHAR columns to create in table if
                      specifying --auto-generate-sql.
<code>  -y, --number-int-cols=name</code>
                      Number of INT columns to create in table if specifying
                      --auto-generate-sql.
<code>  --number-of-queries=</code>#
                      Limit each client to this number of queries (this is not
                      exact).
<code>  --only-print</code>        This causes mysqlslap to not connect to the databases,
                      but instead print out what it would have done instead.
<code>  -p, --password[=name]</code>
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
<code>  -P, --port=</code>#        Port number to use for connection.
<code>  --post-query=name</code>   Query to run or file containing query to execute after
                      tests have completed.
<code>  --post-system=name</code>  system() string to execute after tests have completed.
<code>  --pre-query=name </code>   Query to run or file containing query to execute before
                      running tests.
<code>  --pre-system=name</code>   system() string to execute before running tests.
<code>  --protocol=name </code>    The protocol of connection (tcp,socket,pipe,memory).
<code>  -q, --query=name </code>   Query to run or file containing query to run.
<code>  -s, --silent  </code>      Run program in silent mode - no output.
<code>  -S, --socket=name </code>  Socket file to use for connection.
<code>  -u, --user=name </code>    User for login if not current user.
<code>  -v, --verbose </code>      More verbose output; you can use this multiple times to
                      get even more verbose output.
<code>  -V, --version</code>       Output version information and exit.
[root@colinux data]#

————————————————————————————————————————–
ここではDBをSLAPという名前で指定してますが、DBはmysqlslapを実行すると
自動作成(CREATE)されて最後に自動削除(DROP)されます。
既存のDB名を指定して実行する場合はバックアップしておかないとDBが無くなって
しまいますので注意が必要です。
————————————————————————————————————————–
MyIsamを指定してmysqlslapを実行してます

mysqlslap --no-defaults --create-schema=SLAP --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=10 --auto-generate-sql-write-number=1000 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -p

InnoDBを指定してmysqlslapを実行してます

mysqlslap --no-defaults --create-schema=SLAP --auto-generate-sql --auto-generate-sql-guid-primary --engine=InnoDB --number-int-cols=3 --number-char-cols=5 --concurrency=10 --auto-generate-sql-write-number=1000 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -p

--no-defaults

を最初に付けないと、
mysqlslap: unknown variable ‘default-character-set=utf8’というエラーが出てきたので
オプションファイルのDefaultをSkipしてテストしています。

mysqlslap

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 5.391 seconds
Minimum number of seconds to run all queries: 5.391 seconds
Maximum number of seconds to run all queries: 5.391 seconds
Number of clients running queries: 10
Average number of queries per client: 1000

Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 9.250 seconds
Minimum number of seconds to run all queries: 9.250 seconds
Maximum number of seconds to run all queries: 9.250 seconds
Number of clients running queries: 10
Average number of queries per client: 1000

参考サイト

7.16. mysqlslap — クライアント負荷エミュレーション


mysqlslapはMySQLサーバのクライアント負荷をエミュレートし、各ステージのタイミングを
報告する診断プログラムです。サーバにたいして複数のクライアントがアクセスしているかのように
作動します。mysqlslapはMySQL 5.1.4.から提供されています。

[root@study01 bin]# pwd
/usr/local/mysql/bin
[root@study01 bin]#./mysqlslap 
--no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam 
--number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 
--auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed  -u root -p

Enter password:
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.813 seconds
        Minimum number of seconds to run all queries: 0.813 seconds
        Maximum number of seconds to run all queries: 0.813 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

[root@study01 bin]#./mysqlslap 
--no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam 
--number-int-cols=3 --number-char-cols=5 --concurrency=5 --auto-generate-sql-write-number=100 
--auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed  -u root -p

Enter password:
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 1.448 seconds
        Minimum number of seconds to run all queries: 1.448 seconds
        Maximum number of seconds to run all queries: 1.448 seconds
        Number of clients running queries: 5
        Average number of queries per client: 1000
[root@study01 bin]#

3つの同時接続結果

con4

5つの同時接続結果

con5

mysqlslap実行中に他のターミナルからProcessを確認してみると以下のような感じで
SQL文が実行されていた。

query

[参考URL]

7.16. mysqlslap — クライアント負荷エミュレーション


[MySQLウォッチ]第42回 性能検査ツールmysqlslapとMySQLコンファレンス報告