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

Engine independent statistics

        Details

        • Type: Task
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Fix Version/s: 10.0.1
        • Component/s: None
        • Labels:
          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

            Gliffy Diagrams

              Attachments

                Issue Links

                duplicates

                Task - A task that needs to be done. MDEV-204 Use the statistics from persistent statistical tables instead of the statistics provided by engine (MWL#250)

                • Major - Major loss of function.
                • Closed
                includes

                Bug - A problem which impairs or prevents the functions of the product. MDEV-504 Statistics: Server crashes in Warning_info::current_row_for_warning on concurrent ANALYZE TABLE and DML with reconnecting threads

                • Major - Major loss of function.
                • Closed
                is blocked by

                Task - A task that needs to be done. MDEV-4012 10.0-base merge

                • Blocker - Blocks development and/or testing work, production could not run.
                • Closed
                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-3891 Deadlock with statistics tables and ANALYZE

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5204 Wrong result (missing row) with use_stat_tables, stale statistics, STRAIGHT_JOIN

                • Major - Major loss of function.
                • Closed

                Task - A task that needs to be done. MDEV-4678 INDEPENDENT ANALYZE - per database (or per table)

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

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  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.

                  Show
                  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.
                  Hide
                  Permalink
                  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.

                  Show
                  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.
                  Hide
                  Permalink
                  psergey 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 Kristian Nielsen. ha_innobase::store_lock() analyzes lex->sql_command, and sets LOCK_S for ANALYZE's scans. Should I make it set LOCK_NONE instead?

                  Show
                  psergey 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 Kristian Nielsen . ha_innobase::store_lock() analyzes lex->sql_command, and sets LOCK_S for ANALYZE's scans. Should I make it set LOCK_NONE instead?
                  Hide
                  Permalink
                  psergey 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.
                  Show
                  psergey 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.
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  Review feedback sent via email.

                  Show
                  psergey Sergei Petrunia added a comment - Review feedback sent via email.
                  Hide
                  Permalink
                  serg Sergei Golubchik added a comment -

                  pushed in 10.0-base

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

                    People

                    • Assignee:
                      igor Igor Babaev
                      Reporter:
                      ratzpo Rasmus Johansson
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - 0 minutes
                        0m
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 10 weeks, 2 days, 1 hour, 30 minutes
                        10w 2d 1h 30m