maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04278
Performance speed-up for DBT3 query set with MariaDB 5.3.0
Hi,
Here are the results for DBT3 query set I've got on my laptop with
MariaDB 5.3.0 installed.
[ Dell Latitude E6510: 4-cores with multithreading (8 threads in total)
Memory: 8GB, SSD,
SuSE 11.3 ]
DBT3 factor 10 (60M rows in lineitem) built for InnoDB.
Mysqld was launched with the following parameters:
--innodb-buffer-pool-size=1G
--innodb-additional-mem-pool-size=20M
--innodb-log-file-size=256M
--innodb-file-per-tab
--innodb_file_format=barracuda
Each query was executed
1. with default optimization options (as they are set in mysql-5.1/5.5)
2. with the best optimization options found for the query
I would like to attract your attention to the fact that hash join can
really compete with BKA join for some queries (see Q9, Q12, Q13).
Also: EXPLAIN for Q7, Q8, Q9 now is instantaneous.
Q18 practilly cannot be executed without optimizations.
My estimation tells me that it would take ~50 years to execute it unless
the in_to_exists transformation is optimized for group by queries (then
it will take only ~80 hours).
See how effectively optimize_join_buffer_size can be used for Q9.
(similar join buffer optimization could be applied for other queries,
I just did not do it).
Enjoy the numbers below.
Regards,
Igor.
====================================================
time in seconds time in seconds ratio
w/o optimizations with optimizations
Q1 172 - [172] 1.0
Q2 112 31 3.61
Q3 189 197 0.96
Q4 417 103 4.05
Q5 416 137 3.04
Q6 119 - [119] 1.0
Q7 558 221 2.52
Q8 1023 338 3.02
Q9 2399 218 (265) 11.00 (9.05)
Q10 542 139 3.90
Q11 33 25 1.32
Q12 308 247 1.25
Q13 446 133 3.35
Q14 302 92 3.28
Q15 1334 164 8.13
Q16 45 10 4.5
Q17 46 51 0.90
Q18 [*] 252 (218) -
Q19 76 63 1.21
Q20 463 337 1.37
Q21 297 304 0.98
Q22 11.02 10.72 1.03
====================================================
optimization settings used
Q1
[none]
Q2
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*64;
set join_cache_level=6;
Q3
set join_buffer_space_limit=1024*1024*256;
set join_buffer_size=1024*1024*32;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q4
set optimizer_switch='mrr=on';
set mrr_buffer_size=1024*1024*8;
Q5
set optimizer_switch='mrr=on';
set mrr_buffer_size=1024*1024*8;
set join_buffer_space_limit=1024*1024*1024;
set join_buffer_size=1024*1024*32;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q6
[none]
Q7
set optimizer_switch='derived_merge=on';
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*1024;
set join_buffer_size=1024*1024*64;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q8
set optimizer_switch='derived_merge=on';
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*1024;
set join_buffer_size=1024*1024*128;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q9
set optimizer_switch='derived_merge=on';
set optimizer_switch='optimize_join_buffer_size=on';
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*128;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
(
set optimizer_switch='derived_merge=on';
set optimizer_switch='optimize_join_buffer_size=on';
set join_buffer_space_limit=1024*1024*32;
set join_buffer_size=1024*1024*24;
set join_cache_level=4;
)
Q10
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*128;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q11
set optimizer_switch='mrr=on';
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*8;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q12
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*512;
set join_cache_level=4;
Q13
set tmp_table_size=1024*1024*32;
set max_heap_table_size=1924*1024*32;
set optimizer_switch='outer_join_with_cache=on';
set join_buffer_space_limit=1024*1024*16;
set join_buffer_size=1024*1024*8;
set join_cache_level=4;
Q14
set optimizer_switch='mrr=on';
set mrr_buffer_size=1024*1024*8;
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*32;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q15
set tmp_table_size=1024*1024*32;
set max_heap_table_size=1924*1024*32;
set optimizer_switch='derived_with_keys=on';
set optimizer_switch='mrr=on';
set mrr_buffer_size=1024*1024*32;
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*16;
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q16
set optimizer_switch='materialization=on';
Q17
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*16;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q18
set tmp_table_size=1024*1024*32;
set max_heap_table_size=1024*1024*32;
set optimizer_switch='materialization=on';
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*128;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
(
set tmp_table_size=1024*1024*32;
set max_heap_table_size=1024*1024*32;
set optimizer_switch='materialization=on';
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*128;
set optimizer_switch='mrr=on';
set join_cache_level=4;
)
Q19
set join_buffer_space_limit=1024*1024*128;
set join_buffer_size=1024*1024*16;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q20
set optimizer_switch='semijoin=on';
set optimizer_switch='materialization=on';
set optimizer_switch='in_to_exists=off';
Q21
set join_buffer_space_limit=1024*1024*512;
set join_buffer_size=1024*1024*64;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=6;
Q22
set optimizer_switch='derived_merge=on';
