Jul
27
2017
--

What is MySQL Partitioning?

MySQL Partitioning

MySQL PartitioningIn this blog, we’ll quickly look at MySQL partitioning.

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer.

When partitioning, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather, and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along with the auto increment, i.e., PRIMARY KEY (part_id,id). If you don’t have well-designed and small columns for this composite primary key, it could enlarge all of your secondary indexes.

You can partition by range or hash. Range is great because you have groups of known IDs in each table, and it helps when querying across partition IDs. This still can create hotspots in the newest partition, as all new inserts go there. Partitioning by hash “load balances” the table, and allows you to write to partitions more concurrently. This makes range queries on the partition key a bad idea.

In MySQL 5.7, partitioning became native to the store engine and deprecated the old method where MySQL itself had to handle the partitions. This means InnoDB partitions (and a larger amount of partitions) are a better choice than in the past.

As with all features and recommendations, this only makes sense if it helps your data and workload!

Jan
10
2017
--

How to Move a MySQL Partition from One Table to Another

Move a MySQL Partition

Move a MySQL PartitionIn this blog post we’ll look at how to move a MySQL partition from one table to another, for MySQL versions before 5.7.

Up to version 5.7, MySQL had a limitation that made it impossible to directly exchange partitions between partitioned tables. Now and then, we get questions about how to import an .ibd for use as a partition in a table, as well as how to exchange partitions with another partitioned table. Below is step-by-step instructions on how to move a partition from one table to another.

In this example, one of our customers had two tables with the following structures:

CREATE TABLE live_tbl (
some_id bigint(20) NOT NULL DEFAULT '0',
summary_date date NOT NULL,
PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201203 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

CREATE TABLE archive_tbl (
some_id bigint(20) NOT NULL DEFAULT '0',
summary_date date NOT NULL,
PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201109 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

And their (likely obvious) goal is to move (not copy) the oldest partition from live_tbl to archive_tbl. To achieve this, we came up with the following procedure:

For the following, we assume:

  • The datadir is “/var/lib/mysql/”
  • MySQL Server is run by “mysql” Linux user
  • “p201203” is the partition name you want to move
  • “live_tbl is the source table from where you want to move the partition
  • “archive_tbl” is the destination table to where you want to move the partition
  • “dest_tbl_tmp” is the temporary table we will create, using the same CREATE TABLE criteria as in the live_tbl
  • “thedb” is the database name

1. Copy the .ibd data file from that particular partition

First, make sure you flush any pending changes to disk and that the table is locked, so that binary table copies can be made while the server is running. Keep in mind that the table will be locked while you copy the .ibd file. All reads/writes during that time will be blocked.

Important: Don’t close this session or the lock will be released.

mysql> USE thedb
mysql> FLUSH TABLE live_tbl FOR EXPORT;

Open another session, and copy the .ibd file to a temporary folder.

shell> cp /var/lib/mysql/thedb/live_tbl#P#p201203.ibd /tmp/dest_tbl_tmp.ibd

After you copy the .ibd file to the temporary folder, go back to the MySQL session and unlock the table so that all reads and writes to that particular table are allowed again.

mysql> UNLOCK TABLES;

2. Prepare a temporary table to import the tablespace

Create a temporary table exactly like the one into which you want to import the partition. Remove the partitioning on it and discard the tablespace so that it is ready for the .ibd import.

mysql> CREATE TABLE dest_tbl_tmp LIKE archive_tbl;
mysql> ALTER TABLE dest_tbl_tmp REMOVE PARTITIONING;
mysql> ALTER TABLE dest_tbl_tmp DISCARD TABLESPACE;

3.  Import the tablespace to the temporary table

Place the .ibd file in the appropriate folder, set the correct permissions and ownership and then import the tablespace to the temporary table.

shell> cp /tmp/dest_tbl_tmp.ibd /var/lib/mysql/thedb/
shell> chmod 660 /var/lib/mysql/thedb/dest_tbl_tmp.ibd
shell> chown mysql.mysql /var/lib/mysql/thedb/dest_tbl_tmp.ibd
mysql> ALTER TABLE dest_tbl_tmp IMPORT TABLESPACE;

4. Swap the tablespace with the destination table’s partition tablespace

Partition according to your own schema. (This is just an example using date values. In our case, we have to REORGANIZE PARTITION to accommodate a new LESS THAN range before the MAXVALUE.)

mysql> ALTER TABLE archive_tbl REORGANIZE PARTITION future INTO (
PARTITION p201203 VALUES LESS THAN ('2012-04-01'),
PARTITION future VALUES LESS THAN (MAXVALUE)
);
mysql> ALTER TABLE archive_tbl EXCHANGE PARTITION p201203 WITH TABLE dest_tbl_tmp;

5. Check that the partitions are correctly exchanged before dropping the one from the source table

SELECT * FROM archive_tbl;
SELECT * FROM dest_tbl_tmp;
SELECT * FROM live_tbl;
ALTER TABLE live_tbl DROP PARTITION p201203;

For more information on why these steps are needed, please check the following documentation link for ALTER TABLE … EXCHANGE PARTITION:
https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

In MySQL version 5.7, it is possible to exchange partitions without the unpartitioned table step, as described in the following link:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

There are bugs related to the steps in this guide that might be useful to take into consideration:

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com