Persistent statistics and partitions

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

• Create and utilize table partitioning

• Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
    -> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
    -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name   |
+----+--------+
|  4 | test04 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 |
|  9 | test09 |
|  2 | test02 |
|  6 | test06 |
| 10 | test10 |
|  3 | test03 |
|  7 | test07 |
| 11 | test11 |
+----+--------+
11 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p1             |          3 |
| p2             |          3 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)

The sequence in the id column looks random, but it isn't. It's in partition order.

mysql> SELECT id,name,MOD(id,4) FROM pfoo;
+----+--------+-----------+
| id | name   | MOD(id,4) |
+----+--------+-----------+
|  4 | test04 |         0 |
|  8 | test08 |         0 |
|  1 | test01 |         1 |
|  5 | test05 |         1 |
|  9 | test09 |         1 |
|  2 | test02 |         2 |
|  6 | test06 |         2 |
| 10 | test10 |         2 |
|  3 | test03 |         3 |
|  7 | test07 |         3 |
| 11 | test11 |         3 |
+----+--------+-----------+
11 rows in set (0.00 sec)

So nothing new or unexpected here.
So now we're going to change the number of partitions.

mysql> ALTER TABLE pfoo PARTITION BY HASH(id) PARTITIONS 6;
Query OK, 11 rows affected (0.32 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          2 |
| p3             |          0 |
| p4             |          0 |
| p5             |          0 |
+----------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM pfoo;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

So we've changed the number of partitions from 4 to 6. There are still 11 rows, but the information in information_schema.partitions seems to be wrong.
This is because innodb_stats_persistent is enabled.

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE 'pfoo#%';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| cert56        | pfoo#P#p0  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p1  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p2  | 2013-10-26 12:12:28 |      2 |                    1 |                        0 |
| cert56        | pfoo#P#p3  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p4  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p5  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.global_variables 
    -> WHERE variable_name='innodb_stats_persistent';
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| INNODB_STATS_PERSISTENT | ON             |
+-------------------------+----------------+
1 row in set (0.01 sec)

The statistics can be updated by running 'ANALYZE TABLE pfoo'. Or by just waiting a few seconds as InnoDB will update the statistics automatically if innodb_stats_auto_recalc is enabled, but this is not instantaneous.