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

re-implement analyze table for low impact

Details

    • 10.1.6-1, 10.1.6-2, 10.1.7-1, 10.2.0-5, 10.2.0-6, 5.5.51 & 10.2.2

    Description

      MDEV-7829 and MDEV-7363 where attempts to work around the implementation of analyze table because holding a read lock only on the entire table while gathers statistics. This can be up to an hour or so on larger tables as witnessed in production databases. This level of total read only on a table isn't acceptable.

      MDEV-7196 suggested by jplindst indicated that perhaps a full scan is more appropriate to give better statistics. Obviously this isn't going to help the locking scenario at all.

      So perhaps, instead of locking the entire table, the analyze table takes a full table scan in incremental blocks. These block sizes should try to scale to something that keeps the lock time less that a goal time, a new variable max_analyze_table_lock_time (locks can be dropped in analyze table gets there too soon). Analyze table being a long running operation should drop to read committed (or uncommitted?) mode to keep the undo logs from growing because of the intensive read operations.

      Attachments

        Issue Links

          Activity

            Removing analyze-stmt label . That label is for ANALYZE statement feature (https://mariadb.com/kb/en/mariadb/analyze-statement ), which has nothing to do with "ANALYZE table" statements.s

            psergei Sergei Petrunia added a comment - Removing analyze-stmt label . That label is for ANALYZE statement feature ( https://mariadb.com/kb/en/mariadb/analyze-statement ), which has nothing to do with "ANALYZE table" statements.s

            I don't see why ANALYZE TABLE that collects engine-independent statistics needs to take table locks at all. It could do a lock-less consistent read on InnoDB, just like a normal SELECT * does.

            serg Sergei Golubchik added a comment - I don't see why ANALYZE TABLE that collects engine-independent statistics needs to take table locks at all. It could do a lock-less consistent read on InnoDB, just like a normal SELECT * does.

            Agree with Sergei Golubchick; For InnoDB and XtraDB using lock-less select and even running in not-repeatable mode should allow analyze to run without any notable performance degredation.

            When it comes to MyISAM and Aria, there is two possibilties:

            • Run in ALLOW_READ_ALLOW_WRITE mode; It doesn't matter if we miss a few rows while doing a scan. It should not be hard to extend MyISAM and Aria to do this.
            • Run things in blocks, 10000 rows at a time; If there is another table waiting for the lock, unlock, relock and continue from the original position.
            monty Michael Widenius added a comment - Agree with Sergei Golubchick; For InnoDB and XtraDB using lock-less select and even running in not-repeatable mode should allow analyze to run without any notable performance degredation. When it comes to MyISAM and Aria, there is two possibilties: Run in ALLOW_READ_ALLOW_WRITE mode; It doesn't matter if we miss a few rows while doing a scan. It should not be hard to extend MyISAM and Aria to do this. Run things in blocks, 10000 rows at a time; If there is another table waiting for the lock, unlock, relock and continue from the original position.

            Would be nice to introduce ISET Independent Storage Engine Throttling API. We can upgrade EITS system tables via such plugin, but can also get an auto schedule backup by streaming chunk and Binlog Row Events between chunks. Some plugin can be use for maintenance like deleting rows based on on expiration date and column name

            stephane@skysql.com VAROQUI Stephane added a comment - Would be nice to introduce ISET Independent Storage Engine Throttling API. We can upgrade EITS system tables via such plugin, but can also get an auto schedule backup by streaming chunk and Binlog Row Events between chunks. Some plugin can be use for maintenance like deleting rows based on on expiration date and column name
            danblack Daniel Black added a comment -

            Was any progress made? Can this be pushed to a public branch so continuation work can be done?

            danblack Daniel Black added a comment - Was any progress made? Can this be pushed to a public branch so continuation work can be done?

            We found it too big changes to squeeze it just before RC release.

            sanja Oleksandr Byelkin added a comment - We found it too big changes to squeeze it just before RC release.
            danblack Daniel Black added a comment -

            We found it too big changes to squeeze it just before RC release.

            Is that due to the MyISAM/Aria complexity monty mentioned? If so can that part be put as a independent deferred request? After all, in a very large proportion of the time, innodb tables are the only ones that impact operations.

            danblack Daniel Black added a comment - We found it too big changes to squeeze it just before RC release. Is that due to the MyISAM/Aria complexity monty mentioned? If so can that part be put as a independent deferred request? After all, in a very large proportion of the time, innodb tables are the only ones that impact operations.

            Mostly because very probable delay of stable version due to fixing errors in this part.

            sanja Oleksandr Byelkin added a comment - Mostly because very probable delay of stable version due to fixing errors in this part.

            done with innodb relaxed locks

            sanja Oleksandr Byelkin added a comment - done with innodb relaxed locks

            revision-id: fc42991720838e165ad448b6707602cded92faa4 (mariadb-10.2.1-9-gfc42991)
            parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481
            committer: Oleksandr Byelkin
            timestamp: 2016-08-01 19:25:45 +0200
            message:

            MDEV-7901: re-implement analyze table for low impact

            Table before collecting engine independent statistics now is reopened in read mode,
            InnoDB allow write operations in this case.

            —

            sanja Oleksandr Byelkin added a comment - revision-id: fc42991720838e165ad448b6707602cded92faa4 (mariadb-10.2.1-9-gfc42991) parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481 committer: Oleksandr Byelkin timestamp: 2016-08-01 19:25:45 +0200 message: MDEV-7901 : re-implement analyze table for low impact Table before collecting engine independent statistics now is reopened in read mode, InnoDB allow write operations in this case. —
            danblack Daniel Black added a comment -

            Thank you so much sanja

            danblack Daniel Black added a comment - Thank you so much sanja

            revision-id: 810e5ae2eb8d7bdc1dc5549865dc9177360b8e62 (mariadb-10.2.1-9-g810e5ae)
            parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481
            committer: Oleksandr Byelkin
            timestamp: 2016-08-03 17:26:55 +0200
            message:

            MDEV-7901: re-implement analyze table for low impact

            Table before collecting engine independent statistics now is reopened in read mode,
            InnoDB allow write operations in this case.

            sanja Oleksandr Byelkin added a comment - revision-id: 810e5ae2eb8d7bdc1dc5549865dc9177360b8e62 (mariadb-10.2.1-9-g810e5ae) parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481 committer: Oleksandr Byelkin timestamp: 2016-08-03 17:26:55 +0200 message: MDEV-7901 : re-implement analyze table for low impact Table before collecting engine independent statistics now is reopened in read mode, InnoDB allow write operations in this case.

            ok to push

            serg Sergei Golubchik added a comment - ok to push

            People

              sanja Oleksandr Byelkin
              danblack Daniel Black
              Votes:
              7 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.