[MDEV-2986] LP:999251 - Q13 from DBT3 uses table scan instead of covering index scan Created: 2012-05-14  Updated: 2014-06-20  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Axel Schwenke Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug999251.xml     File LPexportBug999251_innodb_s30_test_2012-05-14_210225.tgz    

 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.

 Comments   
Comment by Axel Schwenke [ 2012-05-14 ]

Re: Q13 from DBT3 uses table scan instead of covering index scan

Comment by Axel Schwenke [ 2012-05-14 ]

result from DBT3 run
LPexportBug999251_innodb_s30_test_2012-05-14_210225.tgz

Comment by Axel Schwenke [ 2012-05-14 ]

Re: Q13 from DBT3 uses table scan instead of covering index scan
commandline saved in bug999251.sh. To reproduce:

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

this runs in about 1 minute

Comment by Axel Schwenke [ 2012-05-14 ]

Re: Q13 from DBT3 uses table scan instead of covering index scan
"InnoDB tables" means "XtraDB tables"

Comment by Axel Schwenke [ 2012-05-15 ]

Re: Q13 from DBT3 uses table scan instead of covering index scan
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.

Comment by Igor Babaev [ 2012-05-18 ]

Re: Q13 from DBT3 uses table scan instead of covering index scan
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.

Comment by Rasmus Johansson (Inactive) [ 2012-05-18 ]

Launchpad bug id: 999251

Generated at Thu Feb 08 06:45:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.