← Back to team overview

maria-developers team mailing list archive

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';