Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Q13 from DBT3 uses table scan instead of covering index scan
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
Re: Q13 from DBT3 uses table scan instead of covering index scan
"InnoDB tables" means "XtraDB tables"
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.
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.
Launchpad bug id: 999251
result from DBT3 run
LPexportBug999251_innodb_s30_test_2012-05-14_210225.tgz