Q13 from DBT3 uses table scan instead of covering index scan

Bug #999251 reported by Axel Schwenke on 2012-05-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Committed
Critical
Igor Babaev

Bug Description

This is MariaDB-5.5.23 (GA, as released). DBT3 data at SF30 loaded into InnoDB tables. Default statistics (fluctuating). Running EXPLAIN for Q13 10 times, restarting the server in between. Yields 10 times the same (bad) plan. Earlier tests resulted in exactly this plan 100 times in 100 tries.

How to reproduce:

login to facebook-maria1

cd ~/benchmark/dbt3/mariadb-tools/dbt3_benchmark

~/benchmark/dbt3/mariadb-tools/dbt3_benchmark> time perl launcher.pl --project-home=/home/mariadb/benchmark/dbt3/ --results-output-dir=/home/mariadb/benchmark/dbt3/results/innodb_s30_test --datadir=/home/mariadb/benchmark/dbt3/db_data/ --test=/home/mariadb/benchmark/dbt3/mariadb-tools/dbt3_benchmark/tests/innodb_test_mariadb_5_5_Q13_for_igor.conf --queries-home=/home/mariadb/benchmark/dbt3/gen_query/ --scale-factor=30
(this is one single line)

after that there will be a new results directory "innodb_s30_test_<timestamp>" below ~/benchmark/dbt3/results.

Attached is the tarred results directory from the run I've done just now.

Axel Schwenke (ahel) wrote :
Axel Schwenke (ahel) wrote :

commandline saved in bug999251.sh. To reproduce:

cd ~/benchmark/dbt3/mariadb-tools/dbt3_benchmark
./bug999251.sh

this runs in about 1 minute

Changed in maria:
status: New → Confirmed
Axel Schwenke (ahel) wrote :

"InnoDB tables" means "XtraDB tables"

Axel Schwenke (ahel) wrote :

The very same execution plan was chosen for ANY run of Q13 at SF30. This includes default (volatile) statistics, persistent (but randomly gathered) statistics and exact statistics from MariaDB-MWL248.

I.e. here is the plan from MariaDB-5.5.23-MWL248

+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Using temporary; Using filesort |
| 2 | DERIVED | customer | ALL | NULL | NULL | NULL | NULL | 4500000 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 15 | 100.00 | Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
+------+-------------+------------+------+---------------+-------------+---------+-------------------------+---------+----------+---------------------------------------------------------------------------------+

Execution plans for all 3 kinds of statistics are archived in lp:~ahel/maria/mariadb-benchmarks in the dbt3-runs/innodb_s30_test_2012-04 subdirectory.

Changed in maria:
milestone: none → 5.5
assignee: nobody → Igor Babaev (igorb-seattle)
importance: Undecided → Critical
status: Confirmed → In Progress
Igor Babaev (igorb-seattle) wrote :

I fixed the problem in MariaDB 5.5.
Probably it makes sense to fix it in MariaDB 5.3 as well (back-porting the patch) as this bug is a serious
performance issue.

I merged the fix into maria-5.5-mwl248.

Changed in maria:
status: In Progress → Fix Committed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers