ablog

不器用で落着きのない技術者のメモ

sysbench で MySQL のベンチマークをとる

sysbench をインストールして MySQLベンチマークをとる手順をメモ。

インストール

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
$ sudo yum -y install sysbench mysql

ベンチマーク

  • 初期データロード
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-db=mydb \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --time=300 \
 --db-ps-mode=disable \
 --threads=16 \
 run

モニタリングする

> use mydb;
> set autocommit=0;
> select @@global.autocommit,@@session.autocommit;
+---------------------+----------------------+
| @@global.autocommit | @@session.autocommit |
+---------------------+----------------------+
|                   1 |                    0 |
+---------------------+----------------------+
1 row in set (0.02 sec)

>  update members set name = 'aze';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

> select * from information_schema.innodb_trx;
  • History list length を確認する。
> pager egrep -A 5 '^TRANSACTIONS'
> show engine innodb status\G
TRANSACTIONS
------------
Trx id counter 70715803
Purge done for trx's n:o < 70591422 undo n:o < 0 state: running but idle
History list length 666
LIST OF TRANSACTIONS FOR EACH SESSION:
  • バイナリログを見る。
> show binary logs;
(中略)
| mysql-bin-changelog.000149 | 134264241 |
| mysql-bin-changelog.000150 | 134218252 |
| mysql-bin-changelog.000151 | 134218203 |
| mysql-bin-changelog.000152 |  24293043 |
| mysql-bin-changelog.000153 |  51085842 |
| mysql-bin-changelog.000154 | 108366756 |
| mysql-bin-changelog.000155 | 134218418 |
| mysql-bin-changelog.000156 |  18849396 |
| mysql-bin-changelog.000157 |       120 |
+----------------------------+-----------+
153 rows in set (0.02 sec)