sql-bench mysql_【MySQL】如何使用sql-bench做基准测试？

cd到mysql安装目录下的sql-bench目录，执行如下命令：

```

./test-connect --server=mysql --user=root --password=root --log --fast

```

该命令测试数据库的connect，log自动输出到该目录的log目录。

结果如下:

> Testing server 'MySQL 5.6.20 log' at 2016-01-12  9:33:00

>

> Testing the speed of connecting to the server and sending of data

> Connect tests are done 10000 times and other tests 100000 times

>

> Testing connection/disconnect

> Time to connect (10000):  4 wallclock secs ( 0.29 usr  2.94 sys +  0.00 cusr  0.00 csys =  3.23 CPU)

>

> Test connect/simple select/disconnect

> Time for connect+select_simple (10000):  5 wallclock secs ( 0.22 usr  3.65 sys +  0.00 cusr  0.00 csys =  3.87 CPU)

>

> Test simple select

> Time for select_simple (100000):  8 wallclock secs ( 0.21 usr  5.18 sys +  0.00 cusr  0.00 csys =  5.39 CPU)

>

> Test simple select

> Time for select_simple_cache (100000):  7 wallclock secs ( 0.13 usr  5.08 sys +  0.00 cusr  0.00 csys =  5.21 CPU)

>

> Testing connect/select 1 row from table/disconnect

> Time to connect+select_1_row (10000):  5 wallclock secs ( 0.23 usr  3.70 sys +  0.00 cusr  0.00 csys =  3.93 CPU)

>

> Testing select 1 row from table

> Time to select_1_row (100000): 11 wallclock secs ( 0.12 usr  5.44 sys +  0.00 cusr  0.00 csys =  5.56 CPU)

>

> Time to select_1_row_cache (100000): 10 wallclock secs ( 0.14 usr  5.17 sys +  0.00 cusr  0.00 csys =  5.31 CPU)

>

> Testing select 2 rows from table

> Time to select_2_rows (100000): 10 wallclock secs ( 0.12 usr  5.41 sys +  0.00 cusr  0.00 csys =  5.53 CPU)

>

> Test select with aritmetic (+)

> Time for select_column+column (100000): 11 wallclock secs ( 0.12 usr  5.39 sys +  0.00 cusr  0.00 csys =  5.51 CPU)

>

> Testing retrieval of big records (65000 bytes)

> Time to select_big_str (10000):  2 wallclock secs ( 0.00 usr  1.19 sys +  0.00 cusr  0.00 csys =  1.19 CPU)

>

> Total time: 73 wallclock secs ( 1.58 usr 43.15 sys +  0.00 cusr  0.00 csys = 44.73 CPU)

查询的基准测试：

```

./test-select --server=mysql --user=root --password=root --log --fast

```

测试结果：

> Testing server 'MySQL 5.6.20 log' at 2016-01-12  9:38:24

>

> Testing the speed of selecting on keys that consist of many parts

> The test-table has 10000 rows and the test is done with 500 ranges.

>

> Creating table

> Inserting 10000 rows

> Time to insert (10000):  7 wallclock secs ( 0.19 usr  0.65 sys +  0.00 cusr  0.00 csys =  0.84 CPU)

>

> Time for book-keeping (1):  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

>

> Test if the database has a query cache

> Time for select_cache (10000): 29 wallclock secs ( 0.10 usr  1.06 sys +  0.00 cusr  0.00 csys =  1.16 CPU)

>

> Time for select_cache2 (10000): 29 wallclock secs ( 0.10 usr  1.05 sys +  0.00 cusr  0.00 csys =  1.15 CPU)

>

> Testing big selects on the table

> Time for select_big (70:17207):  0 wallclock secs ( 0.02 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.03 CPU)

> Time for select_range (410:1057904): 14 wallclock secs ( 1.55 usr  0.00 sys +  0.00 cusr  0.00 csys =  1.55 CPU)

> Time for min_max_on_key (70000):  9 wallclock secs ( 0.83 usr  5.55 sys +  0.00 cusr  0.00 csys =  6.38 CPU)

> Time for count_on_key (50000): 41 wallclock secs ( 0.37 usr  5.48 sys +  0.00 cusr  0.00 csys =  5.85 CPU)

>

> Time for count_group_on_key_parts (1000:100000):  4 wallclock secs ( 0.05 usr  0.20 sys +  0.00 cusr  0.00 csys =  0.25 CPU)

> Testing count(distinct) on the table

> Time for count_distinct_key_prefix (1000:1000):  0 wallclock secs ( 0.00 usr  0.10 sys +  0.00 cusr  0.00 csys =  0.10 CPU)

> Time for count_distinct (1000:1000):  3 wallclock secs ( 0.01 usr  0.11 sys +  0.00 cusr  0.00 csys =  0.12 CPU)

> Time for count_distinct_2 (1000:1000):  7 wallclock secs ( 0.01 usr  0.12 sys +  0.00 cusr  0.00 csys =  0.13 CPU)

> Time for count_distinct_group_on_key (1000:6000):  6 wallclock secs ( 0.00 usr  0.13 sys +  0.00 cusr  0.00 csys =  0.13 CPU)

> Time for count_distinct_group_on_key_parts (1000:100000):  5 wallclock secs ( 0.07 usr  0.19 sys +  0.00 cusr  0.00 csys =  0.26 CPU)

> Time for count_distinct_group (1000:100000):  6 wallclock secs ( 0.05 usr  0.20 sys +  0.00 cusr  0.00 csys =  0.25 CPU)

> Time for count_distinct_big (100:1000000):  2 wallclock secs ( 1.27 usr  0.00 sys +  0.00 cusr  0.00 csys =  1.27 CPU)

> Total time: 162 wallclock secs ( 4.62 usr 14.86 sys +  0.00 cusr  0.00 csys = 19.48 CPU)

同样的在该目录下还有Insert等相关的测试程序。

也可以执行`run-all-tests`来执行所有的测试。

```

./run-all-tests --server=mysql --user=root --password=root --log --fast

```