When a table becomes too large, queries on that table become slow.
One possible solution is table partitioning. This technique involves splitting a table into several physical files or tablespaces. Each file contains a fraction of the table data and thus becomes faster to read. Both read and write access to individual partitions will be much faster.
In this chapter, we will discuss:
- Partitioning types supported by MariaDB
- Subpartitioning
- How to split each partition into multiple files
- Maintenance of partitioned tables
- How the optimizer takes advantage of partitioning
All versions of MariaDB support version partitioning. However, there are two cases where partitioning is not available for a MariaDB installation:
- If MariaDB has been compiled without support to partitioning—while all official distributions have this support, partitioning is not compiled by default. If we compile the server from sources, we should specify the
-DWITH_PARTITION_STORAGE_ENGINEcompile option. - If MariaDB has been started with the partitioning disabled—the option to do this is
--skip-partition. In this case, we will simply need to restart MariaDB without this option. Disabling partitioning is generally not considered a useful optimization.
Checking whether the MariaDB installation supports partitioning is simple. Since partitioning is implemented as a plugin, we will just need to query the PLUGINS table in the information_schema database:
MariaDB [(none)]> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'partition'G *************************** 1. row *************************** PLUGIN_NAME: partition PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 100010.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB PLUGIN_DESCRIPTION: Partition Storage Engine Helper PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0 1 row in set (0.00 sec)
This example shows the metadata as they appear if partitioning is supported. If the server has been compiled without partitioning, no row will be retrieved by the preceding query. If the server is compiled with the support to partitioning but is started without the support, the PLUGIN_STATUS column will be set to DISABLED.
Partitioning is implemented at the storage engine level, thus not all engines support it. Engines that support partitioning include:
- InnoDB
- TokuDB
- MEMORY
- Aria
- MyISAM
- Archive
- BLACKHOLE
For BLACKHOLE, support for partitioning consists of preserving the partition's definition. When a partitioned table is converted to BLACKHOLE, and then converted back to InnoDB, the table will still be partitioned. This feature is not obvious, because the same procedure does not preserve foreign keys, and it is not allowed at all for tables that have virtual columns.
CONNECT and FederatedX cannot be partitioned, but they can be linked to the remote tables that are partitioned.
When trying to create a partitioned table with a storage engine that does not support partitioning, the following error is produced:
ERROR 1572 (HY000): Engine cannot be used in partitioned tables