mysql基准测试 -benchmarks

迈不过友情╰ 2022-08-03 00:28 245阅读 0赞

测试环境 基准测试结果 ：

\[root@192-168-103-43 sql-bench\]\# ./test-insert --server=mysql --user=root --password=hive --log  
Testing server 'MySQL 5.6.25 73.1 log' at 2015-07-28 18:08:52

Testing the speed of inserting data into 1 table and do some selects on it.  
The tests are done with a table that has 100000 rows.

Generating random keys  
Creating tables  
Inserting 100000 rows in order  
Inserting 100000 rows in reverse order  
Inserting 100000 rows in random order  
Time for insert (300000): 67 wallclock secs ( 6.11 usr  3.34 sys +  0.00 cusr  0.00 csys =  9.45 CPU)

Testing insert of duplicates  
Time for insert\_duplicates (100000): 12 wallclock secs ( 1.86 usr  1.16 sys +  0.00 cusr  0.00 csys =  3.02 CPU)

Test of prepared+execute/once prepared many execute selects  
Time for prepared\_select (100000): 28 wallclock secs (12.91 usr  2.86 sys +  0.00 cusr  0.00 csys = 15.77 CPU)  
Time for once\_prepared\_select (100000): 22 wallclock secs ( 5.16 usr  1.15 sys +  0.00 cusr  0.00 csys =  6.31 CPU)  
Retrieving data from the table  
Time for select\_big (10:3000000): 10 wallclock secs ( 8.66 usr  0.10 sys +  0.00 cusr  0.00 csys =  8.76 CPU)  
Time for order\_by\_big\_key (10:3000000): 11 wallclock secs ( 8.85 usr  0.16 sys +  0.00 cusr  0.00 csys =  9.01 CPU)  
Time for order\_by\_big\_key\_desc (10:3000000): 11 wallclock secs ( 8.85 usr  0.12 sys +  0.00 cusr  0.00 csys =  8.97 CPU)  
Time for order\_by\_big\_key\_prefix (10:3000000): 10 wallclock secs ( 8.33 usr  0.10 sys +  0.00 cusr  0.00 csys =  8.43 CPU)  
Time for order\_by\_big\_key2 (10:3000000): 10 wallclock secs ( 7.75 usr  0.14 sys +  0.00 cusr  0.00 csys =  7.89 CPU)  
Time for order\_by\_big\_key\_diff (10:3000000):  9 wallclock secs ( 7.75 usr  0.13 sys +  0.00 cusr  0.00 csys =  7.88 CPU)  
Time for order\_by\_big (10:3000000): 10 wallclock secs ( 7.57 usr  0.09 sys +  0.00 cusr  0.00 csys =  7.66 CPU)  
Time for order\_by\_range (500:125750):  1 wallclock secs ( 0.48 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.49 CPU)  
Time for order\_by\_key\_prefix (500:125750):  1 wallclock secs ( 0.51 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.52 CPU)  
Time for order\_by\_key2\_diff (500:250500):  1 wallclock secs ( 0.87 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.88 CPU)  
Time for select\_diff\_key (500:1000):  0 wallclock secs ( 0.05 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.05 CPU)  
Time for select\_range\_prefix (5010:42084):  3 wallclock secs ( 1.15 usr  0.05 sys +  0.00 cusr  0.00 csys =  1.20 CPU)  
Time for select\_range\_key2 (5010:42084):  4 wallclock secs ( 1.01 usr  0.03 sys +  0.00 cusr  0.00 csys =  1.04 CPU)  
Time for select\_key\_prefix (200000): 52 wallclock secs ( 7.80 usr  1.32 sys +  0.00 cusr  0.00 csys =  9.12 CPU)  
Time for select\_key (200000): 48 wallclock secs (20.18 usr  6.23 sys +  0.00 cusr  0.00 csys = 26.41 CPU)  
Time for select\_key\_return\_key (200000): 48 wallclock secs (15.35 usr  3.99 sys +  0.00 cusr  0.00 csys = 19.34 CPU)  
Time for select\_key2 (200000): 52 wallclock secs ( 5.29 usr  0.61 sys +  0.00 cusr  0.00 csys =  5.90 CPU)  
Time for select\_key2\_return\_key (200000): 52 wallclock secs (28.14 usr  1.37 sys +  0.00 cusr  0.00 csys = 29.51 CPU)  
Time for select\_key2\_return\_prim (200000): 54 wallclock secs (26.45 usr  2.86 sys +  0.00 cusr  0.00 csys = 29.31 CPU)

Test of compares with simple ranges  
Time for select\_range\_prefix (20000:43500):  4 wallclock secs ( 1.41 usr  0.17 sys +  0.00 cusr  0.00 csys =  1.58 CPU)  
Time for select\_range\_key2 (20000:43500):  4 wallclock secs ( 1.16 usr  0.21 sys +  0.00 cusr  0.00 csys =  1.37 CPU)  
Time for select\_group (111): 10 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)  
Time for min\_max\_on\_key (15000):  3 wallclock secs ( 1.52 usr  0.41 sys +  0.00 cusr  0.00 csys =  1.93 CPU)  
Time for min\_max (60):  7 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)  
Time for count\_on\_key (100):  6 wallclock secs ( 0.01 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.02 CPU)  
Time for count (100): 12 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)  
Time for count\_distinct\_big (20):  8 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Testing update of keys with functions  
Time for update\_of\_key (50000):  18 wallclock secs ( 0.98 usr  0.58 sys +  0.00 cusr  0.00 csys =  1.56 CPU)  
Time for update\_of\_key\_big (501): 11 wallclock secs ( 0.01 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.03 CPU)

Testing update with key  
Time for update\_with\_key (300000):  94 wallclock secs ( 6.87 usr 10.20 sys +  0.00 cusr  0.00 csys = 17.07 CPU)  
Time for update\_with\_key\_prefix (100000):  35 wallclock secs ( 3.59 usr  0.77 sys +  0.00 cusr  0.00 csys =  4.36 CPU)

Testing update of all rows  
Time for update\_big (10):  26 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Testing left outer join  
Time for outer\_join\_on\_key (10:10):  10 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)  
Time for outer\_join (10:10):  13 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)  
Time for outer\_join\_found (10:10):  13 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)  
Time for outer\_join\_not\_found (500:10):  11 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Testing SELECT ... WHERE id in (10 values)  
Time for select\_in (500:5000)  0 wallclock secs ( 0.11 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.11 CPU)

Time for select\_join\_in (500:5000)  0 wallclock secs ( 0.12 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.12 CPU)

Testing SELECT ... WHERE id in (100 values)  
Time for select\_in (500:50000)  1 wallclock secs ( 0.26 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.28 CPU)

Time for select\_join\_in (500:50000)  0 wallclock secs ( 0.25 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.26 CPU)

Testing SELECT ... WHERE id in (1000 values)  
Time for select\_in (500:500000)  5 wallclock secs ( 1.76 usr  0.03 sys +  0.00 cusr  0.00 csys =  1.79 CPU)

Time for select\_join\_in (500:500000)  4 wallclock secs ( 1.88 usr  0.01 sys +  0.00 cusr  0.00 csys =  1.89 CPU)

Testing INSERT INTO ... SELECT  
Time for insert\_select\_1\_key (1):   4 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)  
Time for insert\_select\_2\_keys (1):   6 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)  
Time for drop table(2):  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Testing delete  
Time for delete\_key (10000):  2 wallclock secs ( 0.42 usr  0.04 sys +  0.00 cusr  0.00 csys =  0.46 CPU)  
Time for delete\_range (12):  4 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Insert into table with 16 keys and with a primary key with 16 parts  
Time for insert\_key (100000): 74 wallclock secs ( 2.57 usr  1.03 sys +  0.00 cusr  0.00 csys =  3.60 CPU)

Testing update of keys

Time for update\_of\_primary\_key\_many\_keys (256): 98 wallclock secs ( 0.01 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.02 CPU)

Deleting rows from the table  
Time for delete\_big\_many\_keys (128): 27 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)

Deleting everything from table  
Time for delete\_all\_many\_keys (1): 27 wallclock secs ( 0.02 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.02 CPU)

Inserting 100000 rows with multiple values  
Time for multiple\_value\_insert (100000):  4 wallclock secs ( 0.14 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.14 CPU)

Time for drop table(1):  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)

Total time: 1032 wallclock secs (214.23 usr 39.36 sys +  0.00 cusr  0.00 csys = 253.59 CPU)