Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Not repeatable in maria 5.2. The following query against the DBT-3 scale 1 set:
SELECT COUNT( * ) FROM lineitem
WHERE ( l_commitDATE >= '1998-12-22' AND l_commitDATE IN ( '1993-12-11' , '1994-12-09' )
OR l_suppkey = 10 );
Causes the Innodb_rows_read counter to increase by 1204 , which is twice the number of rows examined by the query.
In maria-5.2, the counter increases by 602
Explain:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
type: range
possible_keys: i_l_suppkey,i_l_commitdate
key: i_l_suppkey
key_len: 5
ref: NULL
rows: 601
Extra: Using where; Rowid-ordered scan
1 row in set (0.00 sec)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
The table was Innodb, with innodb_stats_sample_pages = 128 and ANALYZE TABLE
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Performance regression with Rowid-ordered scan - twice as many Innodb_rows_read
I cannot say it's a performance regression, though: on hot buffer pool, the query executes in 0.00 sec both with MRR and without.
Re: Performance regression with Rowid-ordered scan - twice as many Innodb_rows_read
A note about the WHERE clause:
( l_commitDATE >= '1998-12-22' AND l_commitDATE IN ( '1993-12-11' , '1994-12-09' )
OR l_suppkey = 10 )
the part with l_commitDATE is always FALSE, however, if we remove it, the query will use ref(const) and not range with MRR.
Re: Performance regression with Rowid-ordered scan - twice as many Innodb_rows_read
Actually, there is little sense to use MRR in this query, because range access is done over a single
range, which covers all key components. This way, the records it reads will already be in rowid order.
We could have had special handling for this kind of range scans but we didn't because we assumed that they will be converted to ref(const), which does not use MRR.
Re: Performance regression with Rowid-ordered scan - twice as many Innodb_rows_read
As for greater counter increments, this is expected. Increment of Innodb_rows_read counter cannot be used to judge performance. For example, the increment will be the same irrespectively of whether the scan uses "Using index", while performance of a scan with "Using index" is typically better.
Re: Twice as many Innodb_rows_read with Rowid-ordered scan
To sum up, we don't consider this a performance/etc bug. I've added a note into documentation:
http://kb.askmonty.org/en/multi-range-read-optimization#why-using-multi-range-read-can-cause-higher-values-in-status-variables.
We may need to re-work server status variables, though, because now their correct interpretation is counter-intuitive (more reads == better, why???)
Launchpad bug id: 771761
Re: Performance regression with Rowid-ordered scan - twice as many Innodb_rows_read
The effect is repeatable on the latest 5.3: setting
@@optimizer_switch='mrr=on' causes "Rowid-ordered scan" to used, and twice as many Innodb_rows_read counter increments.