Partitioning Overview
Contents
- Uses for Partitioning
- Partitioning Types
- Enabling Partitioning
- Using Partitions
- Adding Partitions
- Coalescing Partitions
- Converting Partitions to/from Tables
- Dropping Partitions
- Exchanging Partitions
- Removing Partitioning
- Reorganizing Partitions
- Truncating Partitions
- Analyzing Partitions
- Checking Partitions
- Repairing Partitions
- Optimizing Partitions
- Partitioning for Specific Storage Engines
- See Also
In MariaDB, a table can be split in smaller subsets. Both data and indexes are partitioned.
Uses for Partitioning
There can be several reasons to use this feature:
- Very big tables and indexes can be slow even with optimized queries. But if the target table is partitioned, queries that read a small number of partitions can be much faster.
- Partitioning allows one to distribute files over multiple storage devices. For example, we can have historical data on slower, larger disks (historical data are not supposed to be frequently read); and current data can be on faster disks, or SSD devices.
- In case we separate historical data from recent data, we will probably need to take regular backups of one partition, not the whole table.
Partitioning Types
When partitioning a table, the use should decide:
- a partitioning type;
- a partitioning expression.
A partitioning type is the method used by MariaDB to decide how rows are distributed over existing partitions. Choosing the proper partitioning type is important to distribute rows over partitions in an efficient way.
With some partitioning types, a partitioning expression is also required. A partitioning function is an SQL expression returning an integer or temporal value, used to determine which row will contain a given row. The partitioning expression is used for all reads and writes on involving the partitioned table, thus it should be fast.
MariaDB supports the following partitioning types:
Enabling Partitioning
By default, MariaDB permits partitioning. You can determine this by using the SHOW PLUGINS statement, for example:
SHOW PLUGINS; ... | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +-------------------------------+----------+--------------------+---------+---------+
If partition is listed as DISABLED:
| partition | DISABLED | STORAGE ENGINE | NULL | GPL | +-------------------------------+----------+--------------------+---------+---------+
MariaDB has either been built without partitioning support, or has been started with the the --skip-partition option, or one of its variants:
--skip-partition --disable-partition --partition=OFF
and you will not be able to create partitions.
Using Partitions
It is possible to create a new partitioned table using CREATE TABLE.
ALTER TABLE allows one to:
- Partition an existing table;
- Remove partitions from a partitioned table (with all data in the partition);
- Add/remove partitions, or reorganize them, as long as the partitioning function allows these operations (see below);
- Exchange a partition with a table;
- Perform administrative operations on some or all partitions (analyze, optimize, check, repair).
Adding Partitions
ADD PARTITION [IF NOT EXISTS] (partition_definition)
ALTER TABLE ... ADD PARTITION
can be used to add partitions to an existing table:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); ALTER TABLE t1 ADD PARTITION ( PARTITION p4 VALUES LESS THAN (2017), PARTITION p5 VALUES LESS THAN (2018) );
With RANGE partitions, it is only possible to add a partition to the high end of the range, not the low end. For example, the following results in an error:
ALTER TABLE t1 ADD PARTITION ( PARTITION p0a VALUES LESS THAN (2012) ); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
You can work around this by using REORGANIZE PARTITION to split the partition instead. See Splitting Partitions.
Coalescing Partitions
COALESCE PARTITION number
ALTER TABLE ... COALESCE PARTITION
is used to reduce the number of HASH or KEY partitions by the specified number. For example, given the following table with 5 partitions:
CREATE OR REPLACE TABLE t1 (v1 INT) PARTITION BY KEY (v1) PARTITIONS 5;
The following statement will reduce the number of partitions by 2, leaving the table with 3 partitions:
ALTER TABLE t1 COALESCE PARTITION 2;
Converting Partitions to/from Tables
MariaDB starting with 10.7
CONVERT PARTITION partition_name TO TABLE tbl_name CONVERT TABLE normal_table TO partition_definition
ALTER TABLE ... CONVERT PARTITION
can, from MariaDB 10.7, be used to convert partitions in an existing table to a standalone table:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11'); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | | 2015-11-11 00:00:00 | +---------------------+ ALTER TABLE t1 CONVERT PARTITION p3 TO TABLE t2; SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | +---------------------+ SELECT * FROM t2; +--------------+ | dt | +--------------+ | 2015-11-11 00:00:00 | +---------------------+ SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (year(`dt`)) (PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB, PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB) SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CONVERT TABLE
does the reverse, converting a table into a partition:
ALTER TABLE t1 CONVERT TABLE t2 to PARTITION p3 VALUES LESS THAN (2016); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | | 2015-11-11 00:00:00 | +---------------------+ 3 rows in set (0.001 sec) SELECT * FROM t2; ERROR 1146 (42S02): Table 'test.t2' doesn't exist SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (year(`dt`)) (PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB, PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)
CONVERT TABLE ... WITH / WITHOUT VALIDATION
MariaDB starting with 11.4
When converting tables to a partition, validation is performed on each row to ensure it meets the partition requirements. This can be very slow in the case of larger tables.
From MariaDB 11.4, it is possible to disable this validation by specifying the WITHOUT VALIDATION
option.
CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]
WITH VALIDATION
will result in the validation being performed, and is the default behaviour.
An alternative (and the only method prior to MariaDB 10.7) to convert partitions to tables is to use ALTER TABLE ... EXCHANGE PARTITION
. This requires having to manually do the following steps:
- create an empty table with the same structure as the partition
- exchange the table with the partition
- drop the empty partition
For example:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11'); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | | 2015-11-11 00:00:00 | +---------------------+ CREATE OR REPLACE TABLE t2 LIKE t1; ALTER TABLE t2 REMOVE PARTITIONING; ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2; ALTER TABLE t1 DROP PARTITION p3; SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | +---------------------+ SELECT * FROM t2; +--------------+ | dt | +--------------+ | 2015-11-11 00:00:00 | +---------------------+ SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (year(`dt`)) (PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB, PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB) SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
Similarly, to do the reverse and convert a table into a partition ALTER TABLE ... EXCHANGE PARTITION
can also be used, with the following manual steps required:
- create the partition
- exchange the partition with the table
- drop the old table:
For example:
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2016)); ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2; DROP TABLE t2; SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-11 00:00:00 | | 2014-11-11 00:00:00 | | 2015-11-11 00:00:00 | +---------------------+ SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PARTITION BY RANGE (year(`dt`)) (PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB, PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB, PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)
Dropping Partitions
DROP PARTITION [IF EXISTS] partition_names
ALTER TABLE ... DROP PARTITION
can be used to drop specific partitions (and discard all data within the specified partitions) for RANGE and LIST partitions. It cannot be used on HASH or KEY partitions. To rather remove all partitioning, while leaving the data unaffected, see Removing Partitioning.
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); INSERT INTO t1 VALUES ('2012-11-15'); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2012-11-15 00:00:00 | +---------------------+ ALTER TABLE t1 DROP PARTITION p0; SELECT * FROM t1; Empty set (0.002 sec)
Exchanging Partitions
<= MariaDB 11.3
EXCHANGE PARTITION partition_name WITH TABLE tbl_name
>= MariaDB 11.4
EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2
permits one to exchange a partition or subpartition with another table.
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014) ); CREATE OR REPLACE TABLE t2 ( dt DATETIME NOT NULL ) ENGINE = InnoDB; INSERT INTO t1 VALUES ('2012-01-01'),('2013-01-01'); INSERT INTO t2 VALUES ('2013-02-02'); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2012-01-01 00:00:00 | | 2013-01-01 00:00:00 | +---------------------+ SELECT * FROM t2; +--------------+ | dt | +--------------+ | 2013-02-02 00:00:00 | +---------------------+ ALTER TABLE t1 EXCHANGE PARTITION p1 with TABLE t2; SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2012-01-01 00:00:00 | | 2013-02-02 00:00:00 | +---------------------+ SELECT * FROM t2; +--------------+ | dt | +--------------+ | 2013-01-01 00:00:00 | +---------------------+
The following requirements must be met:
- Table t1 must be partitioned, and table t2 cannot be partitioned
- Table t2 cannot be a temporary table
- Table t1 and t2 must otherwise be identica
- Any existing row in t2 must match the conditions for storage in the exchanged partition p1 unless, from MariaDB 11.4, the
WITHOUT VALIDATION
option is specified.
MariaDB will by default perform the validation to see that each row meets the partition requirements, and the statement will fail if a row does not fit.
This attempted exchange fails, as the value already in t2, 2015-05-05
is outside of the partition conditions:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014) ); CREATE OR REPLACE TABLE t2 ( dt DATETIME NOT NULL ) ENGINE = InnoDB; INSERT INTO t1 VALUES ('2012-02-02'),('2013-03-03'); INSERT INTO t2 VALUES ('2015-05-05'); ALTER TABLE t1 EXCHANGE PARTITION p1 with TABLE t2; ERROR 1526 (HY000): Table has no partition for value 0
WITH / WITHOUT VALIDATION
MariaDB starting with 11.4
This validation is performed for each row, and can be very slow in the case of larger tables.
From MariaDB 11.4, it is possible to disable this validation by specifying the WITHOUT VALIDATION
option.
ALTER TABLE t1 EXCHANGE PARTITION p1 with TABLE t2 WITHOUT VALIDATION; Query OK, 0 rows affected (0.048 sec)
WITH VALIDATION
will result in the validation being performed, and is the default behaviour.
Removing Partitioning
REMOVE PARTITIONING
ALTER TABLE ... REMOVE PARTITIONING
will remove all partitioning from the table, while leaving the data unaffected. To rather drop a particular partition (and discard all of its data), see Dropping Partitions.
ALTER TABLE t1 REMOVE PARTITIONING;
Reorganizing Partitions
REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
Reorganizing partitions allows one to adjust existing partitions, without losing data. Specifically, the statement can be used for:
- Splitting an existing partition into multiple partitions
- Merging a number of existing partitions into a new, single, partition
- Changing the ranges for a subset of existing partitions defined using VALUES LESS THAN
- Changing the value lists for a subset of partitions defined using VALUES I.
- Renaming partitions
Splitting Partitions
An existing partition can be split into multiple partitions. This can also be used to add a new partition at the low end of a RANGE partition (which is not possible by Adding Partitions).
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); ALTER TABLE t1 REORGANIZE PARTITION p0 INTO ( PARTITION p0a VALUES LESS THAN (2012), PARTITION p0b VALUES LESS THAN (2013) );
Similarly, if MAXVALUE binds the high end:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016), PARTITION p4 VALUES LESS THAN MAXVALUE ); ALTER TABLE t1 REORGANIZE PARTITION p4 INTO ( PARTITION p4 VALUES LESS THAN (2017), PARTITION p5 VALUES LESS THAN MAXVALUE );
Merging Partitions
A number of existing partitions can be merged into a new partition, for example:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); ALTER TABLE t1 REORGANIZE PARTITION p2,p3 INTO ( PARTITION p2 VALUES LESS THAN (2016) );
Changing Ranges
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); ALTER TABLE t1 REORGANIZE PARTITION p3 INTO ( PARTITION p3 VALUES LESS THAN (2017) );
Renaming Partitions
The REORGANIZE PARTITION statement can also be used for renaming partitions. Note that this creates a copy of the partition:
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); ALTER TABLE t1 REORGANIZE PARTITION p3 INTO ( PARTITION p3_new VALUES LESS THAN (2016) );
Truncating Partitions
TRUNCATE PARTITION partition_names
ALTER TABLE ... TRUNCATE PARTITION
will remove all data from the specified partition/s, leaving the table and partition structure unchanged. Partitions don't need to be contiguous.
CREATE OR REPLACE TABLE t1 ( dt DATETIME NOT NULL ) ENGINE = InnoDB PARTITION BY RANGE (YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2013), PARTITION p1 VALUES LESS THAN (2014), PARTITION p2 VALUES LESS THAN (2015), PARTITION p3 VALUES LESS THAN (2016) ); INSERT INTO t1 VALUES ('2012-11-01'),('2013-11-02'),('2014-11-03'),('2015-11-04'); SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2012-11-01 00:00:00 | | 2013-11-02 00:00:00 | | 2014-11-03 00:00:00 | | 2015-11-04 00:00:00 | +---------------------+ ALTER TABLE t1 TRUNCATE PARTITION p0,p2; SELECT * FROM t1; +--------------+ | dt | +--------------+ | 2013-11-02 00:00:00 | | 2015-11-04 00:00:00 | +---------------------+
Analyzing Partitions
Similar to ANALYZE TABLE, key distributions for specific partitions can also be analyzed and stored, for example:
ALTER TABLE t1 ANALYZE PARTITION p0,p1,p3; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+
Checking Partitions
CHECK PARTITION {ALL | partition [,partition2 ...]}
Similar to CHECK TABLE, specific partitions can be checked for errors, for example:
ALTER TABLE t1 CHECK PARTITION p1,p3; +---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t1 | check | status | OK | +---------+-------+----------+----------+
The ALL
keyword can be used in place of the list of partition names, and the check operation will be performed on all partitions.
Repairing Partitions
REPAIR PARTITION {ALL | partition [,partition2 ...]} [QUICK] [EXTENDED]
Similar to REPAIR TABLE, specific partitions can be repaired, for example:
ALTER TABLE t1 REPAIR PARTITION p0,p3; +---------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+----------+ | test.t1 | repair | status | OK | +---------+--------+----------+----------+
As with REPAIR TABLE, the QUICK
and EXTENDED
options are available. However, the USE_FRM
option cannot be used with this statement on a partitioned table.
REPAIR PARTITION
will fail if there are duplicate key errors. ALTER IGNORE TABLE ... REPAIR PARTITION
can be used in this case.
The ALL
keyword can be used in place of the list of partition names, and the repair operation will be performed on all partitions.
Optimizing Partitions
OPTIMIZE PARTITION {ALL | partition [,partition2 ...]}
Similar to OPTIMIZE TABLE, specific partitions can be checked for errors, for example:
ALTER TABLE t1 OPTIMIZE PARTITION p0,p3; +---------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+----------+ | test.t1 | optimize | status | OK | +---------+----------+----------+----------+
OPTIMIZE PARTITION
does not support per-partition optimization on InnoDB tables, and will issue a warning and cause the entire table to rebuilt and analyzed. ALTER TABLE ... REBUILD PARTITION
and ALTER TABLE ... ANALYZE PARTITION
can be used instead.
The ALL
keyword can be used in place of the list of partition names, and the optimize operation will be performed on all partitions.
Partitioning for Specific Storage Engines
Some MariaDB storage engines allow more interesting uses for partitioning.
The MERGE storage engine allows one to:
- Treat a set of identical defined MyISAM tables as one.
- A MyISAM table can be in many different MERGE sets and also used separately.
SPIDER allows one to:
- Move partitions of the same table on different servers. In this way, the workload can be distributed on more physical or virtual machines (data sharding).
- All partitions of a SPIDER table can also live on the same machine. In this case there will be a small overhead (SPIDER will use connections to localhost), but queries that read multiple partitions will use parallel threads.
CONNECT allows one to:
- Build a table whose partitions are tables using different storage engines (like InnoDB, MyISAM, or even engines that do not support partitioning).
- Build an indexable, writeable table on several data files. These files can be in different formats.
See also: Using CONNECT - Partitioning and Sharding
See Also
- ALTER TABLE
- INFORMATION_SCHEMA.PARTITIONS contains information about existing partitions.
- Partition Maintenance for suggestions on using partitions