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