Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-142

Check if Innodb_buffer_pool_read_requests is back to normal in latest 5.3

    Details

    • Type: Task
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      Check if recent fixes in 5.3 have made Innodb_buffer_pool_read_requests to go down to the same level as in 5.2.

      Decision:

      • For 5.3.5: apply Olav's patch
      • After: investigate further and find out why we're making extra Innodb_buffer_pool request

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              igor Igor Babaev added a comment -

              Here's the comment for the patch by Olav Sandstaa from mysql-server/trunk rev 3639.1.70 where he explains why we have aperformance degradation for sysbench.
              <<
              Revision id: olav.sandstaa@oracle.com-20111201141210-e3b0gucoy6oz2asq
              Committer: Olav Sandstaa <olav.sandstaa@oracle.com>
              Timestamp: Thu 2011-12-01 15:12:10 +0100

              Fix for Bug#13430436 PERFORMANCE DEGRADATION IN SYSBENCH ON INNODB DUETO ICP

              When running sysbench on InnoDB there is a performance degradation due to index condition pushdown (ICP). Several of the queries in sysbench have a WHERE condition that the optimizer uses for executing these queries as range scans. The upper and lower limit of the range scan will ensure that the WHERE condition is fulfilled. Still, the WHERE condition is part of the queries' condition and if ICP is enabled the condition will be pushed down to InnoDB as an index condition.

              Due to the range scan's upper and lower limits ensure that the WHERE condition is fulfilled, the pushed index condition will not filter out any records. As a result the use of ICP for these queries results in a
              performance overhead for sysbench. This overhead comes from using resources for determining the part of the condition that can be pushed down to InnoDB and overhead in InnoDB for executing the pushed index condition.
              ...
              />>

              Show
              igor Igor Babaev added a comment - Here's the comment for the patch by Olav Sandstaa from mysql-server/trunk rev 3639.1.70 where he explains why we have aperformance degradation for sysbench. << Revision id: olav.sandstaa@oracle.com-20111201141210-e3b0gucoy6oz2asq Committer: Olav Sandstaa <olav.sandstaa@oracle.com> Timestamp: Thu 2011-12-01 15:12:10 +0100 Fix for Bug#13430436 PERFORMANCE DEGRADATION IN SYSBENCH ON INNODB DUETO ICP When running sysbench on InnoDB there is a performance degradation due to index condition pushdown (ICP). Several of the queries in sysbench have a WHERE condition that the optimizer uses for executing these queries as range scans. The upper and lower limit of the range scan will ensure that the WHERE condition is fulfilled. Still, the WHERE condition is part of the queries' condition and if ICP is enabled the condition will be pushed down to InnoDB as an index condition. Due to the range scan's upper and lower limits ensure that the WHERE condition is fulfilled, the pushed index condition will not filter out any records. As a result the use of ICP for these queries results in a performance overhead for sysbench. This overhead comes from using resources for determining the part of the condition that can be pushed down to InnoDB and overhead in InnoDB for executing the pushed index condition. ... />>
              Hide
              igor Igor Babaev added a comment -

              The patch was not pushed into mysql-5.6.4 that was out as late as on Dec 19 2011. My understanding is that they did not consider this fix as
              a critical one. I think we equally could not to care too much about this degradation when releasing mariadb-5.3.4 RC.

              Of course, the problem should be fixed for mariadb-5.3.5 GA.

              I would suggest a pretty simple fix. You can read about it in my post "On the Innodb_buffer_pool_read_requests problem (part 2)" sent out to dev@sakmonty.org on Feb 10.

              Show
              igor Igor Babaev added a comment - The patch was not pushed into mysql-5.6.4 that was out as late as on Dec 19 2011. My understanding is that they did not consider this fix as a critical one. I think we equally could not to care too much about this degradation when releasing mariadb-5.3.4 RC. Of course, the problem should be fixed for mariadb-5.3.5 GA. I would suggest a pretty simple fix. You can read about it in my post "On the Innodb_buffer_pool_read_requests problem (part 2)" sent out to dev@sakmonty.org on Feb 10.
              Hide
              igor Igor Babaev added a comment -

              or> montywi, spetrunia : when it became clear that the bug is a pure ICP problem, not the problem InnoDB support for ICP that I back-ported from mysql-5.6,
              <igor> should I be still reponsible for this bug?
              <montywi> preferably spetrunia and you should solve it (but better to have spetrunia drive this as it's his code)
              <igor> montywi: then I will reassign the bug to him.
              <montywi> ok

              Show
              igor Igor Babaev added a comment - or> montywi, spetrunia : when it became clear that the bug is a pure ICP problem, not the problem InnoDB support for ICP that I back-ported from mysql-5.6, <igor> should I be still reponsible for this bug? <montywi> preferably spetrunia and you should solve it (but better to have spetrunia drive this as it's his code) <igor> montywi: then I will reassign the bug to him. <montywi> ok
              Hide
              psergey Sergei Petrunia added a comment -

              > So for both mariadb and mysql I observed only 1 extra request returned by Sergey's script: mariadb-5.3.4 vs mariadb-5.2/5.3.2 (14:13), mysql-5.6.4-mysql-5.5(14:13).

              Did I understand you correctly that: you consider counter increments up to 14 not to be a problem?

              Show
              psergey Sergei Petrunia added a comment - > So for both mariadb and mysql I observed only 1 extra request returned by Sergey's script: mariadb-5.3.4 vs mariadb-5.2/5.3.2 (14:13), mysql-5.6.4-mysql-5.5(14:13). Did I understand you correctly that: you consider counter increments up to 14 not to be a problem?
              Hide
              psergey Sergei Petrunia added a comment -

              Olav's patch applied and pushed.

              Show
              psergey Sergei Petrunia added a comment - Olav's patch applied and pushed.

                People

                • Assignee:
                  psergey Sergei Petrunia
                  Reporter:
                  psergey Sergei Petrunia
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated: