Overview
We know that having the chance to split a table in different and smaller chunk helps.
It helps because performance searches, insert, index handling and data management as well.
All fine we are all happy, and very often we push on our customer to go for partition in order to solve part of their problems.
What happens quite often is that customer (and sometime MySQL dba as well) see partitions as separate tables.
Unfortunately is not like that, there are some operations that are (still?) creating unexpected results.
One of this is the OPTIMIZATION of a partition.
The case
Some time ago, almost 16 months I was at customer site and we were working with MySQL 5.5 rc.
We were really happy with the new version of MySQL, and being working for MySQL/SUN I was quite proud of it as well.
Then we run the ALTER TABLE X OPTIMIZE PARTITION Y;
Suddenly we realize something was not working as expected because all partitions had temporary tables.
We know and were expecting the LOCK while doing the exercise, but this was not expected at all.
I was attending several internal presentations, and I was in many internal calls,
all stating that the operation should not affect all partitions but only one.
It was not nice, and it was a bug (42822) marked non critical, referring to another one 46158,
which was close because referring to the previous as a duplicate.
Funny thing at the end is ... we still have it in 5.5.12.
There is a work around as usual, but CUSTOMERS need to be aware and manual modified according,
unfortunately this is not the case.
So after all this time, it could sounds silly but let us talk how OPTIMIZE partitions doesn't work and what to do.
What I have done to replicate the error
I have set up a simple test instance with two tables, and use my stresstool (java),
to fill the tables and play with the inserting threads.
Here the tables:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
DATABASE changed
root@localhost [test]> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tbtest1 |
| tbtest2 |
+----------------+
4 rows IN SET (0.00 sec)
root@localhost [test]> DESCRIBE tbtest1;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| FIELD | Type | NULL | KEY | DEFAULT | Extra |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| a | int(11) | NO | MUL | NULL | |
| uuid | char(36) | NO | PRI | NULL | |
| b | varchar(100) | NO | | NULL | |
| c | char(200) | NO | | NULL | |
| counter | bigint(20) | YES | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |
| partitonid | int(11) | NO | | 0 | |
| strrecordtype | char(3) | YES | | NULL | |
+---------------+--------------+------+-----+-------------------+-----------------------------+
|
How the data looks like:
1
2
3
4
5
6
7
8
9
10
11
12
|
root@localhost [test]> SELECT * FROM tbtest1 LIMIT 10;
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
| a | uuid | b | c | counter | time | partitonid | strrecordtype |
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
| 793902056 | 00ea7db2babeb | ac hpzenqafpml | ac hpzr
| 366195670 | 07c620fdfab8d | umlfzae zdzbes | umlfzapgnhlvb | 3359819254 | 2011-06-03 12:27:26 | 18 | fai |
| 19501151 | 08271816a1256 | odpp gporogqmg | odpp glpgfgnf | 3010735465 | 2011-06-03 12:27:26 | 5 | odo |
| 1925082207 | 0bf7d042da285 | toammdmqgttlto | toammdeioidzs | 18048741375 | 2011-06-03 12:27:26 | 17 | gvm |
| 631797159 | 0c9d4857f2f27 | lisn vqclglnmm | lisn vggpirza | 40556949738 | 2011-06-03 12:27:26 | 18 | ldc |
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
10 rows IN SET (0.01 sec
|
How the Table looks like before partition:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
root@localhost [test]>
CREATE TABLE `tbtest1` (
`a` int(11) NOT NULL,
`uuid` char(36) COLLATE utf8_unicode_ci NOT NULL,
`b` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`c` char(200) COLLATE utf8_unicode_ci NOT NULL,
`counter` bigint(20) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`partitonid` int(11) NOT NULL DEFAULT '0',
`strrecordtype` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`uuid`),
KEY `IDX_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row IN SET (0.02 sec)
|
Then Insert the partition definition:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
ALTER TABLE tbtest1 DROP PRIMARY KEY, ADD PRIMARY KEY (`uuid`,`partitonid`)
PARTITION BY RANGE (partitonid) (
PARTITION p0 VALUES LESS THAN (2),
PARTITION p1 VALUES LESS THAN (4),
PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN (8),
PARTITION p4 VALUES LESS THAN (10),
PARTITION p5 VALUES LESS THAN (12),
PARTITION p6 VALUES LESS THAN (14),
PARTITION p7 VALUES LESS THAN (16),
PARTITION p8 VALUES LESS THAN (18),
PARTITION p9 VALUES LESS THAN (20),
PARTITION p10 VALUES LESS THAN MAXVALUE
);
SELECT TABLE_SCHEMA,TABLE_NAME,partition_name,TABLE_ROWS AS 'ROWS',
TRUNCATE(DATA_LENGTH/pow(1024,2),2) AS 'DATA (M)',
TRUNCATE(INDEX_LENGTH/pow(1024,2),2) AS 'INDEX (M)',
TRUNCATE((DATA_LENGTH+INDEX_LENGTH)/pow(1024,2),2) AS 'TOTAL(M)'
FROM information_schema.partitions
WHERE TABLE_SCHEMA <> 'information_schema'
AND TABLE_SCHEMA <> 'mysql'
AND TABLE_NAME = 'tbtest1'
ORDER BY `TOTAL(M)` DESC,`DATA (M)` DESC;
+--------------+------------+----------------+------+----------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | partition_name | ROWS | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------+------------+----------------+------+----------+-----------+----------+
| test | tbtest1 | p0 | 244 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p8 | 227 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p7 | 211 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p5 | 187 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p4 | 227 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p3 | 212 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p1 | 204 | 0.12 | 0.01 | 0.14 |
| test | tbtest1 | p6 | 175 | 0.10 | 0.01 | 0.12 |
| test | tbtest1 | p2 | 235 | 0.10 | 0.01 | 0.12 |
| test | tbtest1 | p9 | 229 | 0.10 | 0.01 | 0.12 |
| test | tbtest1 | p10 | 0 | 0.01 | 0.01 | 0.03 |
+--------------+------------+----------------+------+----------+-----------+----------+
|
And fill it with some data with my stresstool, but keeping the partition P10 empty, loading values up to PARTITIONID <=20
then filling P10 with an insert:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
INSERT INTO tbtest1 (a, uuid,b,c,counter,time,partitonid,strrecordtype)
SELECT a, uuid,b,c,counter,time,30,strrecordtype FROM tbtest1 WHERE partitonid <=20;
+--------------+------------+----------------+--------+----------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | partition_name | ROWS | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------+------------+----------------+--------+----------+-----------+----------+
| test | tbtest1 | p10 | 85104 | 106.95 | 10.54 | 117.50 |
| test | tbtest1 | p5 | 95647 | 57.76 | 8.54 | 66.31 |
| test | tbtest1 | p4 | 99459 | 57.76 | 8.54 | 66.31 |
| test | tbtest1 | p3 | 92256 | 57.76 | 8.54 | 66.31 |
| test | tbtest1 | p2 | 77794 | 57.76 | 8.54 | 66.31 |
| test | tbtest1 | p9 | 79871 | 57.71 | 8.54 | 66.26 |
| test | tbtest1 | p7 | 100681 | 57.76 | 7.54 | 65.31 |
| test | tbtest1 | p1 | 102414 | 56.76 | 8.54 | 65.31 |
| test | tbtest1 | p6 | 92961 | 56.76 | 7.54 | 64.31 |
| test | tbtest1 | p8 | 93872 | 56.76 | 7.54 | 64.31 |
| test | tbtest1 | p0 | 80510 | 56.76 | 7.54 | 64.31 |
+--------------+------------+----------------+--------+----------+-----------+----------+
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
| 144 | root | localhost | test | Sleep | 285 | | NULL |
| 195 | root | tusacentral03:56633 | test | Sleep | 55 | | NULL |
| 197 | root | tusacentral07.LOCAL:53368 | test | Sleep | 1 | | NULL |
| 199 | root | tusacentral07.LOCAL:53370 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("2f...95-bc |
| 200 | root | tusacentral07.local:53371 | test | Query | 1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("0f...d5-a9 |
| 201 | root | tusacentral07.LOCAL:53372 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("ce...94-9d |
| 202 | root | tusacentral07.local:53373 | test | Query | 1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("32...ec-98 |
| 203 | root | tusacentral07.LOCAL:53374 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("c3...7d-86 |
| 204 | root | tusacentral07.local:53375 | test | Query | 1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("63...02-ba |
| 1031 | root | localhost | NULL | Query | 0 | NULL | SHOW processlist |
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
10 rows IN SET (0.00 sec)
|
Time to run the optimize, and discover that nothing was changed in all this time.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> ALTER TABLE tbtest1 OPTIMIZE PARTITION p10;
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
| 144 | root | localhost | test | Sleep | 844 | | NULL |
| 195 | root | tusacentral03:56633 | test | Query | 92 | copy TO tmp TABLE | ALTER TABLE tbtest1 OPTIMIZE PARTITION p10 |
| 197 | root | tusacentral07.LOCAL:53368 | test | Sleep | 0 | | NULL |
| 199 | root | tusacentral07.LOCAL:53370 | test | Query | 90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4cdd-a8 |
| 200 | root | tusacentral07.LOCAL:53371 | test | Query | 90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-427f-be |
| 201 | root | tusacentral07.LOCAL:53372 | test | Query | 90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4694-9d |
| 202 | root | tusacentral07.LOCAL:53373 | test | Query | 90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4a5a-a0 |
| 203 | root | tusacentral07.LOCAL:53374 | test | Query | 91 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-48de-b8 |
| 204 | root | tusacentral07.LOCAL:53375 | test | Query | 90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4a0f-9c |
| 1031 | root | localhost | NULL | Query | 0 | NULL | SHOW processlist |
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
10 rows IN SET (0.00 sec)
|
MySQL, when using InnoDB, still do the process on all partitions and not only on the selected one.
This is probably not critical for the developer but a customer running it on a huge table could strongly disagree.
Immagine running it against a TABLE with 1.2TB and each partiton of 20-25GB.
It is not funny...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
total 1031896
drwx------ 2 mysql mysql 4096 2011-06-03 13:50 ./
drwxr-xr-x 11 mysql mysql 4096 2011-06-03 13:21 ../
-rw-rw---- 1 mysql mysql 8578 2011-03-31 22:44 bbb.frm
-rw-rw---- 1 mysql mysql 163840 2011-03-31 22:44 bbb.MYD
-rw-rw---- 1 mysql mysql 1024 2011-03-31 22:44 bbb.MYI
-rw-rw---- 1 mysql mysql 8578 2011-03-31 22:34 ccc.frm
-rw-rw---- 1 mysql mysql 425984 2011-03-31 22:39 ccc.ibd
-rw-rw---- 1 mysql mysql 61 2011-03-04 09:47 db.opt
-rw-rw---- 1 mysql mysql 8788 2011-06-03 13:50 #sql-3205_c3.frm
-rw-rw---- 1 mysql mysql 64 2011-06-03 13:50 #sql-3205_c3.par
-rw-rw---- 1 mysql mysql 58720256 2011-06-03 13:51 #sql-3205_c3#P#p0.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p10.ibd
-rw-rw---- 1 mysql mysql 54525952 2011-06-03 13:51 #sql-3205_c3#P#p1.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p2.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p3.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p4.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p5.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p6.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p7.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p8.ibd
-rw-rw---- 1 mysql mysql 114688 2011-06-03 13:50 #sql-3205_c3#P#p9.ibd
-rw-rw---- 1 mysql mysql 8788 2011-06-03 13:47 tbtest1.frm
-rw-rw---- 1 mysql mysql 64 2011-06-03 13:47 tbtest1.par
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p0.ibd
-rw-rw---- 1 mysql mysql 75497472 2011-06-03 13:49 tbtest1#P#p10.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p1.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p2.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p3.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p4.ibd
-rw-rw---- 1 mysql mysql 88080384 2011-06-03 13:51 tbtest1#P#p5.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p6.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p7.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p8.ibd
-rw-rw---- 1 mysql mysql 96468992 2011-06-03 13:51 tbtest1#P#p9.ibd
-rw-rw---- 1 mysql mysql 8630 2011-06-03 12:36 tbtest2.frm
-rw-rw---- 1 mysql mysql 9437184 2011-06-03 13:51 tbtest2.ibd
root@tusacentral03:/home/mysql/instances/master/data/test#
|
So what to do?
Use REBUILD instead.
This drives me crazy, why the mapping could not be done inside the code?
If InnoDB then OPTIMIZE = REBUILD ... and go ahead.
To me this sounds not user friendly at all.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> ALTER TABLE tbtest1 REBUILD PARTITION p10;
Query OK, 126994 rows affected (16.50 sec)
Records: 126994 Duplicates: 0 Warnings: 0
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
| 144 | root | localhost | test | Sleep | 357 | | NULL |
| 195 | root | tusacentral03:56633 | test | Query | 3 | setup | ALTER TABLE tbtest1 REBUILD PARTITION p10 |
| 197 | root | tusacentral07.LOCAL:53368 | test | Sleep | 0 | | NULL |
| 199 | root | tusacentral07.LOCAL:53370 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit22da-4c7d-94 |
| 200 | root | tusacentral07.LOCAL:53371 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit8459-477b-94 |
| 201 | root | tusacentral07.LOCAL:53372 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit93b7-413f-a8 |
| 202 | root | tusacentral07.LOCAL:53373 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit6cd0-4e4b-85 |
| 203 | root | tusacentral07.LOCAL:53374 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit79c1-4da5-8c |
| 204 | root | tusacentral07.LOCAL:53375 | test | Query | 2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partitff17-4be7-b1 |
| 1031 | root | localhost | NULL | Query | 0 | NULL | SHOW processlist |
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
10 rows IN SET (0.00 sec)
|
Anyhow checking the file system, this time all works as expected and ONLY the partiton p10 was REBUILD.
Please take mental note of the STATE, is saying "Waiting for table metadata lock", this sounds interesting.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
drwx------ 2 mysql mysql 4096 2011-06-03 13:47 ./
drwxr-xr-x 11 mysql mysql 4096 2011-06-03 13:21 ../
-rw-rw---- 1 mysql mysql 8578 2011-03-31 22:44 bbb.frm
-rw-rw---- 1 mysql mysql 163840 2011-03-31 22:44 bbb.MYD
-rw-rw---- 1 mysql mysql 1024 2011-03-31 22:44 bbb.MYI
-rw-rw---- 1 mysql mysql 8578 2011-03-31 22:34 ccc.frm
-rw-rw---- 1 mysql mysql 425984 2011-03-31 22:39 ccc.ibd
-rw-rw---- 1 mysql mysql 61 2011-03-04 09:47 db.opt
-rw-rw---- 1 mysql mysql 8788 2011-06-03 13:47 #sql-tbtest1.frm
-rw-rw---- 1 mysql mysql 64 2011-06-03 13:47 #sql-tbtest1.par
-rw-rw---- 1 mysql mysql 8788 2011-06-03 13:44 tbtest1.frm
-rw-rw---- 1 mysql mysql 64 2011-06-03 13:44 tbtest1.par
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p0.ibd
-rw-rw---- 1 mysql mysql 75497472 2011-06-03 13:46 tbtest1#P#p10.ibd
-rw-rw---- 1 mysql mysql 606208 2011-06-03 13:47 tbtest1#P#p10#TMP#.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p1.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p2.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p3.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p4.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p5.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p6.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p7.ibd
-rw-rw---- 1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p8.ibd
-rw-rw---- 1 mysql mysql 92274688 2011-06-03 13:47 tbtest1#P#p9.ibd
-rw-rw---- 1 mysql mysql 8630 2011-06-03 12:36 tbtest2.frm
-rw-rw---- 1 mysql mysql 9437184 2011-06-03 13:46 tbtest2.ibd
root@tusacentral03:/home/mysql/instances/master/data/test#
|
While there and to avoid doubt I also checked what about CHECKING a partition,
Who knows may be some surprise as well
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> ALTER TABLE tbtest1 CHECK PARTITION p10;
+--------------+-------+----------+----------+
| TABLE | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.tbtest1 | CHECK | STATUS | OK |
+--------------+-------+----------+----------+
1 row IN SET (36.64 sec)
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
| 144 | root | localhost | test | Sleep | 1114 | | NULL |
| 195 | root | tusacentral03:56633 | test | Query | 15 | NULL | ALTER TABLE tbtest1 CHECK PARTITION p10 |
| 197 | root | tusacentral07.LOCAL:53368 | test | Sleep | 0 | | NULL |
| 199 | root | tusacentral07.LOCAL:53370 | test | Query | 0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-39e0-4296-97 |
| 200 | root | tusacentral07.LOCAL:53371 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-f508-4ec8-87 |
| 201 | root | tusacentral07.LOCAL:53372 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-3a5f-4444-81 |
| 202 | root | tusacentral07.LOCAL:53373 | test | Query | 1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-970e-48bb-a2 |
| 203 | root | tusacentral07.LOCAL:53374 | test | Query | 0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-62ea-4c1f-81 |
| 204 | root | tusacentral07.LOCAL:53375 | test | Query | 0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-6277-4dd5-89 |
| 1215 | root | localhost | NULL | Query | 0 | NULL | SHOW processlist |
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
10 rows IN SET (0.00 sec)
|
But luckly no it works fine and no lock on the table.
Conclusions
1) We still have to deal with this non critical bug, and we must remember
DO NOT USE:
ALTER TABLE <table_name> OPTIMIZE PARTITION A,B,C
BUT
ALTER TABLE <table_name> REBUILD PARTITION A,B,C
Not yet clear to me why a simple mapping between REBUILD and OPTIMIZATION is not possible.
2) The level of lock is related to METADATA because the ALTER command, looking around for a better explanation I have seen that there is not too much material about it, but the presentation from Konstantin Osipov for sure helps to better understand.
http://en.oreilly.com/mysql2011/public/schedule/detail/17340
My dream
The OPTIMIZATION of a partition is a special case, where data, is not modified but eventually physically re-organized.
Having the status of the lock declared as METADATA lock, brings me to think we can safely insert/modify all data not related to that partition.
What I am feeling from all this, is the possibility to have in the near future this lock release, I see issues with the Index merge and others so some digging in the code will require..
It would be nice ...to have it.
Reference
17.3.3. Maintenance of Partitions - http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html
Metadata locking and deadlock detection in MySQL 5.5 - http://en.oreilly.com/mysql2011/public/schedule/detail/17340
optimize partition recreates full table instead just partition - http://bugs.mysql.com/bug.php?id=42822
Make partition maintenance commands more efficient - http://bugs.mysql.com/bug.php?id=46158