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

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24733 Doc bug: ANALYZE TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-10494 re-implement analyze table for even lower impact

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10555 Server crashes in mysql_admin_table upon killing ANALYZE

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-7196 ANALYZE TABLE should do complete table scan

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7363 bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7829 Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats'

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 8 older comments
            sanja Oleksandr Byelkin added a comment -

            done with innodb relaxed locks

            sanja Oleksandr Byelkin added a comment - done with innodb relaxed locks
            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.

            —

            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
            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.

            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.
            serg Sergei Golubchik added a comment -

            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.