Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.1
    • None
    • None

    Description

      In a special mode the ANALYZE TABLE statement will collect statistics on table cardinality, table columns and table indexes. This statistics will be stored in tables table_stat, column_stat and index_stat in the mysql database.

      • For any database table the table table_stat will contain the number of rows in the table.
      • For any table column the table column_stat will contain the minimal and maximal values, the share of null values, the average length of a column value, and the the average frequency of the column values.
      • For any index defined on a database table the table index_stat will contain number of records per every key prefix.

      This task will be implemented in 3 steps:

      1. A pure back-port of the MySQL WL#4777 from mysql-5.4 code line will be performed.
      2. New possibility to collect statistics only on specified columns and/or indexes will be added.
      3. A new possibility to scan only a certain percentage of the table records in a pseudo-random manner will be added.

      References:

      1. WL#248: Collect engine independent statistics on table columns and indexes to store it in persistent system tables
      2. WL#250: Use the statistics from persistent statistical tables instead of the statistics provided by engine

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            Worked on the build for mwl#248 after yesterday back-port of the code from lp:~igorb-seattle/mysql-server/mysql-azalea-wl4777.

            igor Igor Babaev added a comment - Worked on the build for mwl#248 after yesterday back-port of the code from lp:~igorb-seattle/mysql-server/mysql-azalea-wl4777.
            igor Igor Babaev added a comment -

            Successfully created a build with all tests passed.
            Created a tree for the build on Launchpad: maria-5.3-mwl248.
            Registered the tree in buildbot.

            igor Igor Babaev added a comment - Successfully created a build with all tests passed. Created a tree for the build on Launchpad: maria-5.3-mwl248. Registered the tree in buildbot.

            ... Attempted to run ANALYZE with @@tx_isolation='READ-UNCOMMITTED' and with handler->unlock_row() calls. It is is still putting locks on the rows (execution goes through sel_set_rec_lock()), and index scans block updates.

            Discussed the issue with knielsen. ha_innobase::store_lock() analyzes lex->sql_command, and sets LOCK_S for ANALYZE's scans. Should I make it set LOCK_NONE instead?

            psergei Sergei Petrunia added a comment - ... Attempted to run ANALYZE with @@tx_isolation='READ-UNCOMMITTED' and with handler->unlock_row() calls. It is is still putting locks on the rows (execution goes through sel_set_rec_lock()), and index scans block updates. Discussed the issue with knielsen . ha_innobase::store_lock() analyzes lex->sql_command, and sets LOCK_S for ANALYZE's scans. Should I make it set LOCK_NONE instead?

            Notes from the optimizer call:

            • ANALYZE locks tables with TL_READ_NO_INSERT, because that's a requirement by MyISAM. InnoDB should be able to downgrade the lock to TL_READ.
            • If ANALYZE statement is killed, it should save whatever it has already managed to collect:
              = if a table/index scan is finished, their info can already be saved.
              = if the table scan is not finished, we should check how many records we've processed. If we've processed more records than there are in the statistics, use the new statistics.
            psergei Sergei Petrunia added a comment - Notes from the optimizer call: ANALYZE locks tables with TL_READ_NO_INSERT, because that's a requirement by MyISAM. InnoDB should be able to downgrade the lock to TL_READ. If ANALYZE statement is killed, it should save whatever it has already managed to collect: = if a table/index scan is finished, their info can already be saved. = if the table scan is not finished, we should check how many records we've processed. If we've processed more records than there are in the statistics, use the new statistics.

            Review feedback sent via email.

            psergei Sergei Petrunia added a comment - Review feedback sent via email.

            pushed in 10.0-base

            serg Sergei Golubchik added a comment - pushed in 10.0-base

            People

              igor Igor Babaev
              ratzpo Rasmus Johansson (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.