[MDEV-3327] LP:771761 - Twice as many Innodb_rows_read with Rowid-ordered scan Created: 2011-04-27  Updated: 2015-02-02  Resolved: 2012-10-04

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

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug771761.xml    

 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)

The table was Innodb, with innodb_stats_sample_pages = 128 and ANALYZE TABLE



 Comments   
Comment by Sergei Petrunia [ 2012-03-02 ]

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.

Comment by Sergei Petrunia [ 2012-03-02 ]

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.

Comment by Sergei Petrunia [ 2012-03-02 ]

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.

Comment by Sergei Petrunia [ 2012-03-02 ]

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

{l_suppkey=10}

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.

Comment by Sergei Petrunia [ 2012-03-02 ]

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.

Comment by Sergei Petrunia [ 2012-03-02 ]

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???)

Comment by Rasmus Johansson (Inactive) [ 2012-03-02 ]

Launchpad bug id: 771761

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