You are here
What's going on when MySQL does operations on Partitions
Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?
In the MySQL documentation were not to many details mentioned:
ALTER TABLE ... ADD PARTITION creates no temporary table except for MySQL Cluster.
...
ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between changed partitions
...
If other cases, MySQL creates a temporary table, even if the data wouldn't strictly need to be copied ...
So it would be interesting to find out what happens. Because I am not a developer I am not able to read code. So I have to find it out somehow different:
- We need the following Tool: MySQL Super Smack
- With gen-data from MySQL Super Smack we generate some data:
- Then we create a table into MySQL 5.1 were we want to run our tests:
- Now the data can be loaded:
- Now we need an other tool called MySQL Profiler to see what happens. Also mytop could help here. To gather data with MySQL Profiler we first have to start the tracer who collects the data:
- Then immediately after you started the tracer you should start the SQL command. And immediately the SQL command finished you should stop the trace with ^C to not collect to much idling time:
- Now you can analyse the data with the profiler:
- Next test is to add a new index to our partitioned table:
- First we wanted to DROP a partition. But this is not possible with HASH partitions. So we try a COALESCE:
- But we still want to see how MySQL behaves dropping and adding partitions. So we have to convert our table to a RANGE partitioned table:
- Now we are able to first ADD a partition:
- And now we want to drop a full partition:
./gen-data -n 1000000 -f '%n,%d,%12-12s,%5-25s' > rows.txt
CREATE TABLE test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, lauf INT UNSIGNED NOT NULL
, fix CHAR(12) NOT NULL
, dyn VARCHAR(25) NOT NULL
);
LOAD DATA INFILE '~/super-smack/bin/rows.txt' INTO TABLE test FIELDS TERMINATED BY ',';
A short check shows us roughly how long operations are expected to run:
mysql> SELECT COUNT(*) FROM (SELECT * FROM test) x; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (3.30 sec)
./tracer.pl -i 20
ALTER TABLE test
PARTITION BY HASH(id)
PARTITIONS 8;
Query OK, 1000000 rows affected (9.85 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
./profiler.pl --pid=3 -t mysql_processlist.trc.15256 General infos ------------- Slots : 549 Time : 12.107 s Interval : 0.022 s Filters : pid = 3, Lines total : 3088 Lines skipped : 892 Lines matched : 2196 Commands -------- Query 446 9.835 s 81.2 % Sleep 103 2.271 s 18.8 % ------------------------- -------- -------------- ------- Total 549 12.107 s 100.0 % State ----- copy to tmp table 441 9.725 s 80.3 % Idling 103 2.271 s 18.8 % creating table 3 0.066 s 0.5 % rename result table 2 0.044 s 0.4 % ------------------------- -------- -------------- ------- Total 549 12.107 s 100.0 %
We can see here, that most of the time (80.3%) was used to copy data to a temporary table to convert a conventional table to a partitioned table.
Caution: Because we only have rough time slots (here 20 ms) this results are not always 100% correct!
ALTER TABLE test ADD index dyn_i (dyn); Query OK, 1000000 rows affected (14.45 sec) Records: 1000000 Duplicates: 0 Warnings: 0 General infos ------------- Slots : 752 Time : 16.636 s Interval : 0.022 s Filters : pid = 3, Lines total : 2256 Lines skipped : 0 Lines matched : 2256 Commands -------- Query 651 14.401 s 86.6 % Sleep 101 2.234 s 13.4 % ------------------------- -------- -------------- ------- Total 752 16.636 s 100.0 % State ----- copy to tmp table 373 8.251 s 49.6 % Repair by sorting 273 6.039 s 36.3 % Idling 101 2.234 s 13.4 % creating table 3 0.066 s 0.4 % rename result table 2 0.044 s 0.3 % ------------------------- -------- -------------- ------- Total 752 16.636 s 100.0 %
We can see here that coping the data to a temporary table and the following repair (index build) took most of the time. This seems to be still the old bad behaviour of MySQL!
ALTER TABLE test COALESCE PARTITION 2; Query OK, 0 rows affected (1 min 0.87 sec) Records: 0 Duplicates: 0 Warnings: 0 General infos ------------- Slots : 2864 Time : 64.118 s Interval : 0.022 s Filters : pid = 3, Lines total : 8592 Lines skipped : 0 Lines matched : 8592 Commands -------- Query 2715 60.782 s 94.8 % Sleep 149 3.336 s 5.2 % ------------------------- -------- -------------- ------- Total 2864 64.118 s 100.0 % State ----- setup 2709 60.648 s 94.6 % Idling 149 3.336 s 5.2 % end 6 0.134 s 0.2 % ------------------------- -------- -------------- ------- Total 2864 64.118 s 100.0 %
Here we can see a new status never seen before: set-up. This seems to be new 5.1 technology for partitioning?
ALTER TABLE test
PARTITION BY RANGE (id) (
PARTITION p0100k VALUES LESS THAN (0100001)
, PARTITION p0200k VALUES LESS THAN (0200001)
, PARTITION p0300k VALUES LESS THAN (0300001)
, PARTITION p0400k VALUES LESS THAN (0400001)
, PARTITION p0500k VALUES LESS THAN (0500001)
, PARTITION p0600k VALUES LESS THAN (0600001)
, PARTITION p0700k VALUES LESS THAN (0700001)
, PARTITION p0800k VALUES LESS THAN (0800001)
, PARTITION p0900k VALUES LESS THAN (0900001)
, PARTITION p1000k VALUES LESS THAN (1000001)
, PARTITION p1100k VALUES LESS THAN (1100001)
)
;
Query OK, 1000000 rows affected (18.15 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
General infos
-------------
Slots : 1082
Time : 23.997 s
Interval : 0.022 s
Filters : pid = 3,
Lines total : 13893
Lines skipped : 10647
Lines matched : 3246
Commands
--------
Query 819 18.164 s 75.7 %
Sleep 263 5.833 s 24.3 %
------------------------- -------- -------------- -------
Total 1082 23.997 s 100.0 %
State
-----
copy to tmp table 550 12.198 s 50.8 %
Idling 263 5.833 s 24.3 %
Repair by sorting 262 5.811 s 24.2 %
creating table 3 0.067 s 0.3 %
rename result table 3 0.067 s 0.3 %
end 1 0.022 s 0.1 %
------------------------- -------- -------------- -------
Total 1082 23.997 s 100.0 %
ALTER TABLE test
ADD PARTITION (
PARTITION p1200k VALUES LESS THAN (1200001)
);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
General infos
-------------
Slots : 6
Time : 0.111 s
Interval : 0.018 s
Filters : pid = 3,
Lines total : 6
Lines skipped : 0
Lines matched : 6
Commands
--------
Query 6 0.111 s 100.0 %
------------------------- -------- -------------- -------
Total 6 0.111 s 100.0 %
State
-----
setup 6 0.111 s 100.0 %
------------------------- -------- -------------- -------
Total 6 0.111 s 100.0 %
This seems pretty fast. So we can assume that no needles coping around of data is done.
ALTER TABLE test DROP PARTITION p0800k ; Query OK, 0 rows affected (0.52 sec) General infos ------------- Slots : 23 Time : 0.502 s Interval : 0.022 s Filters : pid = 3, Lines total : 23 Lines skipped : 0 Lines matched : 23 Commands -------- Query 23 0.502 s 100.0 % ------------------------- -------- -------------- ------- Total 23 0.502 s 100.0 % State ----- setup 22 0.480 s 95.7 % Writing to net 1 0.022 s 4.3 % ------------------------- -------- -------------- ------- Total 23 0.502 s 100.0 %
This also went pretty fast. So also here we can assume, that no copying is done.
tags: