[MDEV-3806] Engine independent statistics Created: 2012-01-04  Updated: 2014-01-15  Resolved: 2013-01-25

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: 10.0.1

Type: Task Priority: Major
Reporter: Rasmus Johansson (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-4012 10.0-base merge Closed
Duplicate
duplicates MDEV-204 Use the statistics from persistent st... Closed
PartOf
includes MDEV-504 Statistics: Server crashes in Warning... Closed
Relates
relates to MDEV-3891 Deadlock with statistics tables and A... Closed
relates to MDEV-5204 Wrong result (missing row) with use_s... Closed
relates to MDEV-4678 INDEPENDENT ANALYZE - per database (o... Closed

 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

 Comments   
Comment by Igor Babaev [ 2012-01-04 ]

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

Comment by Igor Babaev [ 2012-01-05 ]

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.

Comment by Sergei Petrunia [ 2012-11-27 ]

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

Comment by Sergei Petrunia [ 2012-11-27 ]

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.
Comment by Sergei Petrunia [ 2012-12-03 ]

Review feedback sent via email.

Comment by Sergei Golubchik [ 2013-01-25 ]

pushed in 10.0-base

Generated at Thu Feb 08 06:51:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.