The partitioning improvement that almost was at Xaprb

The partitioning improvement that almost was

Today I was looking for the ALTER TABLE EXCHANGE PARTITION feature for a customer, and it looks like it did not get included into MySQL 5.5, although there is a hint of it in the documentation index, and you can find quite a few blog posts and presentations about it. The command simply throws a syntax error:

alter table t exchange partition p1 with table t2;

The worklog is still open, although a related bug report it mentions is closed and pushed into trunk. (It confused me for a moment until I realized that what was pushed into trunk, and released in 5.5, was TRUNCATE PARTITION support.)

Here’s hoping this gets included in a future release — this is a great feature that can make partitions much more amenable to operational tasks such as moving data from one partitioned table to another, or exporting a partition to a table, then exporting the table with xtrabackup and importing it onto another server.

Written by Xaprb

March 22nd, 2011 at 12:46 pm

Posted in SQL

3 Responses to 'The partitioning improvement that almost was'

Subscribe to comments with RSS

Check out a build from mysql-trunk on launchpad… :)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.2-m5-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table t1 (i int primary key, j int) partition by range (i) (partition p1 values less than (100), partition p2 values less than (1000));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1, 1), (200, 200);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2 (i int primary key, j int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t2 values (300,300);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+------+
| i   | j    |
+-----+------+
|   1 |    1 |
| 200 |  200 |
+-----+------+
2 rows in set (0.01 sec)

mysql> select * from t2;
+-----+------+
| i   | j    |
+-----+------+
| 300 |  300 |
+-----+------+
1 row in set (0.00 sec)

mysql> alter table t1 exchange partition p1 with table t2;
ERROR 1711 (HY000): Found row that does not match the partition
mysql> alter table t1 exchange partition p2 with table t2;
Query OK, 0 rows affected (0.34 sec)

mysql> select * from t1;
+-----+------+
| i   | j    |
+-----+------+
|   1 |    1 |
| 300 |  300 |
+-----+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+-----+------+
| i   | j    |
+-----+------+
| 200 |  200 |
+-----+------+
1 row in set (0.00 sec)

Mark Leith

22 Mar 11 at 2:02 pm

Thanks! I never have time to keep up with trunk. This is great. Thank you for posting this extra info!

Xaprb

22 Mar 11 at 2:14 pm

Baron,
good to know that someone else is interested in this feature. I talked about it at the MySQL conference last year and then I wrote a blog post
http://datacharmer.blogspot.com/2010/04/exchanging-partitions-with-tables.html
I am keeping an eye on the code, and I am pleased to say that the test I wrote in that blog post still works correctly for tables of 1 million records in MySQL 5.6.3, which at present you need to compile on your own from the Bazaar tree.

$ ./use -vvv < test_partitions.sql 
--------------
set default_storage_engine=innodb
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
select version()
--------------

+-----------+
| version() |
+-----------+
| 5.6.3-m5  |
+-----------+
1 row in set (0.00 sec)

--------------
drop procedure if exists compare_tables
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create procedure compare_tables (wanted int)
reads sql data
begin
    set @part_table     := (select count(*) from t1);
    set @non_part_table := (select count(*) from t2);
    select @part_table, @non_part_table, 
        if(@non_part_table = wanted, "OK", "error") as expected;
end
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists t1, t2
--------------

Query OK, 0 rows affected (0.04 sec)

--------------
create table t1 (i int) 
partition by range (i) 
    (
partition p01 values less than  (100001), 
partition p02 values less than  (200001), 
partition p03 values less than  (300001), 
partition p04 values less than  (400001), 
partition p05 values less than  (500001), 
partition p06 values less than  (600001), 
partition p07 values less than  (700001), 
partition p08 values less than  (800001), 
partition p09 values less than  (900001), 
partition p10 values less than (1000001), 
partition p11 values less than (maxvalue))
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
create table t2 (i int )
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
select table_name, engine 
from information_schema.tables 
where table_schema='test' and table_type='base table'
--------------

+------------+--------+
| table_name | engine |
+------------+--------+
| t1         | InnoDB |
| t2         | InnoDB |
+------------+--------+
2 rows in set (0.00 sec)

--------------
select 'generating 1 million records. ...' as info
--------------

+-----------------------------------+
| info                              |
+-----------------------------------+
| generating 1 million records. ... |
+-----------------------------------+
1 row in set (0.00 sec)

--------------
create or replace view v3 as select null union all select null union all select null
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create or replace view v10 as select null from v3 a, v3 b union all select null
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create or replace view v1000 as select null from v10 a, v10 b, v10 c
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
set @n = 0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t1 select @n:=@n+1 from v1000 a,v1000 b
--------------

Query OK, 1000000 rows affected (3.84 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

--------------
select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test'
--------------

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p01            |     100623 |
| p02            |     100623 |
| p03            |     100623 |
| p04            |     100623 |
| p05            |     100623 |
| p06            |     100623 |
| p07            |     100623 |
| p08            |     100623 |
| p09            |     100623 |
| p10            |     100623 |
| p11            |          0 |
+----------------+------------+
11 rows in set (0.01 sec)

--------------
call compare_tables(0)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|     1000000 |               0 | OK       |
+-------------+-----------------+----------+
1 row in set (0.41 sec)

Query OK, 0 rows affected (0.41 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.63 sec)

--------------
call compare_tables(100000)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|      900000 |          100000 | OK       |
+-------------+-----------------+----------+
1 row in set (0.37 sec)

Query OK, 0 rows affected (0.37 sec)

--------------
select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test'
--------------

+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p01            |     100623 |
| p02            |     100623 |
| p03            |     100623 |
| p04            |          0 |
| p05            |     100623 |
| p06            |      94334 |
| p07            |     100623 |
| p08            |     100623 |
| p09            |     100623 |
| p10            |     100623 |
| p11            |          0 |
+----------------+------------+
11 rows in set (0.01 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.06 sec)

--------------
call compare_tables(0)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|     1000000 |               0 | OK       |
+-------------+-----------------+----------+
1 row in set (0.41 sec)

Query OK, 0 rows affected (0.41 sec)

--------------
alter table t1 exchange partition p04 with table t2
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
call compare_tables(100000)
--------------

+-------------+-----------------+----------+
| @part_table | @non_part_table | expected |
+-------------+-----------------+----------+
|      900000 |          100000 | OK       |
+-------------+-----------------+----------+
1 row in set (0.38 sec)

Query OK, 0 rows affected (0.38 sec)