maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hi!

Following the discussion of benchmarks of mrr_sort_keys=on|off functionality,
I've done a re-run. Please find the results below.

## Dataset: DBT-3, scale=10, xtradb,
##   innodb_file_per_table=1 
##   innodb_file_format=barracuda 
##   innodb_log_file_size=256M 
##   innodb_buffer_pool_size=4000M
## 
## Platform: work.askmonty.org host
##  Cold-cache is achieved by: 
##   - stopping the server
##   - echo 1 > /proc/sys/vm/drop_caches
##   - starting the server
##   - running EXPLAIN {query}
##
## MariaDB:  5.3-current, release build (compile-pentium-max)
##

Result summary:
 * mrr_sort_keys=on provides speedup (2.25 min vs 13.5 min) on cold cache.
 * on hot cache, there is no big difference (~1.3 sec in both cases).
 * I was able to repeat Igor's August results with the current 5.3. It is 
   not clear why Igor can't repeat them anymore. (An evasive bug? or just
   mistake in experiments?)

set join_buffer_size=1024*1024*32;
set join_buffer_space_limit=1024*1024*32;
set join_cache_level=6;


## 
## Cold cache, mrr_sort_keys=on:
## 
[22:12] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+
| id | select_type | table    | type  | possible_keys                              | key           | key_len | ref                        | rows   | Extra                              |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+
|  1 | SIMPLE      | orders   | range | PRIMARY,i_o_orderdate                      | i_o_orderdate | 4       | NULL                       | 142680 | Using where; Using index           |
|  1 | SIMPLE      | lineitem | ref   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY       | 4       | dbt3sf10.orders.o_orderkey |      1 | Using join buffer (flat, BKA join) |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+
2 rows in set (3.04 sec)

[22:06] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----------------------+
| avg(l_extendedprice) |
+----------------------+
|     38229.0683265894 |
+----------------------+
1 row in set (2 min 13.40 sec)

[22:09] root@dbt3sf10> show status like "%handler%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 2      |
| Handler_read_key           | 193396 |
| Handler_read_next          | 966535 |
+----------------------------+--------+
15 rows in set (0.29 sec)

#
# mrr_sort_keys=off, cold cache:
#
[22:20] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+
| id | select_type | table    | type  | possible_keys                              | key           | key_len | ref                        | rows   | Extra                    |
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+
|  1 | SIMPLE      | orders   | range | PRIMARY,i_o_orderdate                      | i_o_orderdate | 4       | NULL                       | 142680 | Using where; Using index | 
|  1 | SIMPLE      | lineitem | ref   | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY       | 4       | dbt3sf10.orders.o_orderkey |      2 |                          | 
+----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+

[22:20] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31';
+----------------------+
| avg(l_extendedprice) |
+----------------------+
|     38229.0683265898 | 
+----------------------+
1 row in set (13 min 23.68 sec)

[22:09] root@dbt3sf10> show status like "%handler%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 2      | 
| Handler_read_key           | 193396 | 
| Handler_read_next          | 966535 | 
+----------------------------+--------+

##
## Hot caches (re-run the query until time stabilizes):
##

mrr_sort_keys=off:  1.34 sec  
mrr_sort_keys=on:   1.38 sec 



BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog