sysbenchを使ってベンチマークの準備をする際のメモ
今回使用したバージョン: sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
github
https://github.com/akopytov/sysbench
インストール
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
prepare
事前にデータベースは作成しておく
今回はbenchmarkという名前で作成している。
sysbench --db-driver=mysql \
--mysql-host= IPアドレス\
--mysql-user=ユーザ名 \
--mysql-password=パスワード \
--mysql-db=データベース名 \
oltp_read_write \
prepare;\
これで作成できる。
デフォルトのテーブル数は１。レコード数は10,000
mysql> SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS total_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(*) AS tables, CURDATE() AS today FROM infor
mation_schema.tables GROUP BY table_schema ORDER BY 2 DESC;
+--------------------+------------+------------+------------+--------+------------+
| table_schema | total_mb | data_mb | index_mb | tables | today |
+--------------------+------------+------------+------------+--------+------------+
| benchmark | 2.67187500 | 2.51562500 | 0.15625000 | 1 | 2018-05-16 |
| mysql | 2.44198513 | 2.22811794 | 0.21386719 | 31 | 2018-05-16 |
| information_schema | 0.15625000 | 0.15625000 | 0.00000000 | 61 | 2018-05-16 |
| sys | 0.01562500 | 0.01562500 | 0.00000000 | 101 | 2018-05-16 |
| performance_schema | 0.00000000 | 0.00000000 | 0.00000000 | 87 | 2018-05-16 |
+--------------------+------------+------------+------------+--------+------------+
5 rows in set (0.03 sec)
mysql> SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/
-> 1024) AS index_mb FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+------------+--------+----------+------+--------+---------+----------+
| table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb |
+------------+--------+----------+------+--------+---------+----------+
| sbtest1 | InnoDB | 9936 | 265 | 2 | 2 | 0 |
+------------+--------+----------+------+--------+---------+----------+
tbl_rowsが10,000じゃなかった。
mysql> analyze table sbtest1;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| benchmark.sbtest1 | analyze | status | OK |
+-------------------+---------+----------+----------+
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
analyze tableしても数が合わなかった。。ログは撮り忘れ
ただcountであっているから良しとする。
大体デフォルトで2MB程度のデータなので10GBにしたかったらテーブル数やレコード数で調整する。
sysbench --db-driver=mysql \
--mysql-host=IPアドレス \
--mysql-user=ユーザ名 \
--mysql-password=パスワード \
--mysql-db=benchmark \
--tables=5 \
--table_size=10000000 \
oltp_read_write prepare
思ったより大きかった。
mysql> SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS total_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(*) AS tables, CURDATE() AS today FROM infor
mation_schema.tables GROUP BY table_schema ORDER BY 2 DESC;
+--------------------+----------------+----------------+------------+--------+------------+
| table_schema | total_mb | data_mb | index_mb | tables | today |
+--------------------+----------------+----------------+------------+--------+------------+
| benchmark | 10344.00000000 | 10344.00000000 | 0.00000000 | 5 | 2018-05-16 |
| mysql | 2.44198513 | 2.22811794 | 0.21386719 | 31 | 2018-05-16 |
| information_schema | 0.15625000 | 0.15625000 | 0.00000000 | 61 | 2018-05-16 |
| sys | 0.01562500 | 0.01562500 | 0.00000000 | 101 | 2018-05-16 |
| performance_schema | 0.00000000 | 0.00000000 | 0.00000000 | 87 | 2018-05-16 |
+--------------------+----------------+----------------+------------+--------+------------+
5 rows in set (0.13 sec)
mysql> SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/ 1024
)
-> AS index_mb FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+------------+--------+----------+------+--------+---------+----------+
| table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb |
+------------+--------+----------+------+--------+---------+----------+
| sbtest2 | InnoDB | 9611529 | 232 | 2133 | 2133 | 0 |
| sbtest4 | InnoDB | 9864446 | 225 | 2122 | 2122 | 0 |
| sbtest3 | InnoDB | 9866743 | 221 | 2086 | 2086 | 0 |
| sbtest1 | InnoDB | 9869785 | 216 | 2038 | 2038 | 0 |
| sbtest5 | InnoDB | 9874498 | 208 | 1965 | 1965 | 0 |
+------------+--------+----------+------+--------+---------+----------+
5 rows in set (0.00 sec)
mysql> analyze table sbtest1,sbtest2,sbtest3,sbtest4,sbtest5;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| benchmark.sbtest1 | analyze | status | OK |
| benchmark.sbtest2 | analyze | status | OK |
| benchmark.sbtest3 | analyze | status | OK |
| benchmark.sbtest4 | analyze | status | OK |
| benchmark.sbtest5 | analyze | status | OK |
+-------------------+---------+----------+----------+
5 rows in set (3.85 sec)
mysql> SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/ 1024)
AS index_mb FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+------------+--------+----------+------+--------+---------+----------+
| table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb |
+------------+--------+----------+------+--------+---------+----------+
| sbtest5 | InnoDB | 9863064 | 227 | 2291 | 2143 | 148 |
| sbtest4 | InnoDB | 9863064 | 227 | 2291 | 2143 | 148 |
| sbtest3 | InnoDB | 9863064 | 227 | 2291 | 2143 | 148 |
| sbtest2 | InnoDB | 9863064 | 227 | 2291 | 2143 | 148 |
| sbtest1 | InnoDB | 9863064 | 227 | 2291 | 2143 | 148 |
+------------+--------+----------+------+--------+---------+----------+
5 rows in set (0.02 sec)
ぐぬぬ