Appearance
MySQL 如何使用 sql-bench 做基准测试？
🏷️ MySQL
cd 到 mysql 安装目录下的 sql-bench 目录，执行如下命令：
bash
./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)查询的基准测试：
bash
./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 来执行所有的测试。
bash
./run-all-tests --server=mysql --user=root --password=root --log --fast