sysbenchでテストデータ作成

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)
```
ぐぬぬ