15.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables

ANALYZE TABLE complexity for InnoDB tables is dependent on:

The number of pages sampled, as defined by innodb_stats_persistent_sample_pages.
The number of indexed columns in a table
The number of partitions. If a table has no partitions, the number of partitions is considered to be 1.

Using these parameters, an approximate formula for estimating ANALYZE TABLE complexity would be:

The value of innodb_stats_persistent_sample_pages * number of indexed columns in a table * the number of partitions

Typically, the greater the resulting value, the greater the execution time for ANALYZE TABLE.

Note

innodb_stats_persistent_sample_pages defines the number of pages sampled at a global level. To set the number of pages sampled for an individual table, use the STATS_SAMPLE_PAGES option with CREATE TABLE or ALTER TABLE. For more information, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

If innodb_stats_persistent=OFF, the number of pages sampled is defined by innodb_stats_transient_sample_pages. See Section 15.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters” for additional information.

For a more in-depth approach to estimating ANALYZE TABLE complexity, consider the following example.

In Big O notation, ANALYZE TABLE complexity is described as:

 O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

where:

n_sample is the number of pages sampled (defined by innodb_stats_persistent_sample_pages)
n_cols_in_uniq_i is total number of all columns in all unique indexes (not counting the primary key columns)
n_cols_in_non_uniq_i is the total number of all columns in all nonunique indexes
n_cols_in_pk is the number of columns in the primary key (if a primary key is not defined, InnoDB creates a single column primary key internally)
n_non_uniq_i is the number of nonunique indexes in the table
n_part is the number of partitions. If no partitions are defined, the table is considered to be a single partition.

Now, consider the following table (table t), which has a primary key (2 columns), a unique index (2 columns), and two nonunique indexes (two columns each):

CREATE TABLE t (
a INT,
b INT,
c INT,
d INT,
e INT,
f INT,
g INT,
h INT,
PRIMARY KEY (a, b),
UNIQUE KEY i1uniq (c, d),
KEY i2nonuniq (e, f),
KEY i3nonuniq (g, h)
);

For the column and index data required by the algorithm described above, query the mysql.innodb_index_stats persistent index statistics table for table t. The n_diff_pfx% statistics show the columns that are counted for each index. For example, columns a and b are counted for the primary key index. For the nonunique indexes, the primary key columns (a,b) are counted in addition to the user defined columns.

Note

For additional information about the InnoDB persistent statistics tables, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”

mysql> SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats WHERE
database_name='test' AND
table_name='t' AND
stat_name like 'n_diff_pfx%';
+------------+--------------+------------------+
| index_name | stat_name | stat_description |
+------------+--------------+------------------+
| PRIMARY | n_diff_pfx01 | a |
| PRIMARY | n_diff_pfx02 | a,b |
| i1uniq | n_diff_pfx01 | c |
| i1uniq | n_diff_pfx02 | c,d |
| i2nonuniq | n_diff_pfx01 | e |
| i2nonuniq | n_diff_pfx02 | e,f |
| i2nonuniq | n_diff_pfx03 | e,f,a |
| i2nonuniq | n_diff_pfx04 | e,f,a,b |
| i3nonuniq | n_diff_pfx01 | g |
| i3nonuniq | n_diff_pfx02 | g,h |
| i3nonuniq | n_diff_pfx03 | g,h,a |
| i3nonuniq | n_diff_pfx04 | g,h,a,b |
+------------+--------------+------------------+

Based on the index statistics data shown above and the table definition, the following values can be determined:

n_cols_in_uniq_i, the total number of all columns in all unique indexes not counting the primary key columns, is 2 (c and d)
n_cols_in_non_uniq_i, the total number of all columns in all nonunique indexes, is 4 (e, f, g and h)
n_cols_in_pk, the number of columns in the primary key, is 2 (a and b)
n_non_uniq_i, the number of nonunique indexes in the table, is 2 (i2nonuniq and i3nonuniq))
n_part, the number of partitions, is 1.

You can now calculate innodb_stats_persistent_sample_pages * (2 + 4 + 2 * (1 + 2)) * 1 to determine the number of leaf pages that are scanned. With innodb_stats_persistent_sample_pages set to the default value of 20, and with a default page size of 16 KiB (innodb_page_size=16384), you can then estimate that 20 * 12 * 16384 bytes are read for table t, or about 4 MiB.

Note

All 4 MiB may not be read from disk, as some leaf pages may already be cached in the buffer pool.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-analyze-table-complexity.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut