Working in the Big Data industry brings a lot of satisfaction as well as many challenges. You deal with an (almost) uncountable amount of data. As it may be guessed, the main challenge here is the following: how fast can you manage the data? How long does it take to insert a row in a table with billions of rows? How fast can you look for a row among billions of records?
The answer to these questions would be: very fast as long as the database partitioning is correctly used within a data system.
The database partition concept is explained in the next lines, based on a real-life example (or challenge) I worked on. Please note that this is not the only solution to the problem I faced, but it’s one that I experimented which yielded good results.
The database management system (DBMS) mentioned in the article is MySQL. The partitioning concept exists in other DBMSs too and they are similar, but for the ease of understanding, all examples are limited to MySQL.
As mentioned in the intro, I faced a project within the Big Data industry. The amount of new data reached 27 million records per day (and 10 billion per year). Please note that a record was more than a simple row in a table, but in this example let it be one record = one table row. So far no problem, but the way data has been stored was really a problem. Having all the records in a simple table wasn’t the right solution (due to huge indexes and slow queries) so it was decided to have a single table redefined for each day. It ended up in hundreds of same-structure tables but with different data. This was just the first problem, among others:
- Searching for something was very, very slow, considering that a lot of tables had to be scanned for matching data.
- Trying to alter the tables’ structure was out of the discussion since running an altered query over billions of records would lock any data and would take an unreasonable amount of time.
- Tables may go away. In other words, data is lost.
So what was the solution? Read on.
The above problem has been fixed by putting all data in a single partitioned table. Although the single table solution wasn’t the first option (as stated in the problem description), the single partitioned table fixed the problem. What are the gains?
- All data is in a single place (table).
- Queries are very fast (this will be explained how in the next section, as some examples are at the end of the article).
- No data is lost (due to accidental table drop).
- Altering the table structure is possible in a timely fashion.
But first, let’s see some facts about partitioning.
What is database partitioning?
Database partitioning means splitting tables into smaller and more manageable ones, called partitions. How many? Up to 8192 in MySQL.
But that’s not all. Database partitioning also helps with:
- Data manageability – Internally there are multiple tables. This is somehow transparent for the user. I said “somehow” because the user knows that a table is actually a collection of smaller tables (since the user defines it), but they don’t need to know which one is using at a moment as long as the SQL queries contain correct criteria. Then MySQL takes care of it.
- Performance – Partitions that don’t satisfy a certain rule are not scanned (that’s known as partition pruning).
- Availability – Data is in place all the time and available for management.
But how does it work?
- The partitioning criteria/rules as well the number of the partitions are contained in the table definition. That is, on creating a table, the partitions (internal tables) are created right after.
- The partitioning criteria/rules as they are defined, use a single partition at a moment of query’s execution.
- On executing a query, the partition is determined based on the WHERE criteria (mandatory). After figuring out where to go, the query is executed. It has no effects on the rest of the partitions.
There are a few constraints, though. MySQL allows partitioning on MyISAM and InnoDB storage engines only. Then if a table has a unique key then it must be in the partitioning criteria (MySQL doesn’t create the partitioning unless dealing with it).
Two types of partitioning
Universally, there are two possible options to partition a table: horizontally and vertically. In the horizontal partitioning, different rows are stored in different tables. Hence the structure is the same among all internal tables (partitions). This has a good support in MySQL. This is a visual illustration of the horizontal partitioning.
The vertical partitioning puts different fields in different tables, resulting in internal tables not having the same structure. See this illustration:
Unfortunately, MySQL doesn’t know about vertical partitioning. So all other information about partitioning in the rest of the article is related to the horizontal partitioning.
Let’s see different methods of doing the horizontal partitioning in MySQL.
When the data is not equally divided in each partition then it’s about unbalanced partitioning.
This means that a partition can have more rows compared to another partition (from the same table). MySQL doesn’t intervene in distributing the rows so it’s up to the user to insert data in such a manner that table is correctly balanced.
Range partitioning is the easiest way of defining a partition. Each partition contains rows for which the partitioning criteria value lies within a given range. This is a small and simple example of this method:
CREATE TABLE accounts ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), countryId TINYINT(1) NOT NULL ) PARTITION BY RANGE (countryId) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN MAXVALUE );
What is above: a table with four fields and a defined range partition which internally is composed of four tables:
- First table stores all values less than 50
- Second table stores values between 50 and 99
- Third table stores values between 100 and 149
- The fourth and last table stores all other values
So, assuming that next records are inserted into the table (not particularly in this order),
The data is arranged in partitions as follows:
Some rules to keep in mind:
- The partition expression ((countryId)) must return an integer value. The expression can be an integer field (like in this example) or a function which returns an integer. That is, date/datetime fields can be used if functions like YEAR(), DATEOFMONTH() etc are applied.
- Defined ranges should be contiguous but not overlapping. The following partition definition is not possible and will throw an error when creating the partition:
PARTITION BY RANGE (countryId) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p2 VALUES LESS THAN (150), PARTITION p1 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN MAXVALUE );
- A single column is allowed in the partitioning expression.
- LESS THAN MAXVALUE could be interpreted as the default partition. All values which don’t match other’s partition criteria are stored in the default partition. But this is optional (it can be skipped in the definition) and any record which doesn’t match the other’s partition criteria will not be inserted and an error is thrown. So attention must be paid when defining a table with no catch-all/default partitions.
- On inserting data, if the partitioning column is not set then the partition is determined based on the default value of that column. This is available for next partitioning methods, too.
Another unbalanced partitioning method is the columns partitioning. It’s similar with range partition but with some differences:
- Partition is selected based on columns matching a set of discrete (fixed) values.
- Each partition should be explicitly defined but they do not need to be declared in any order.
- There is no default partition. As a side effect, data must match fixed values and any data outside of the partition criteria isn’t allowed (MySQL throws an error).
This example defines a table with two partitions:
CREATE TABLE products ( id INT NOT NULL, productName VARCHAR(30), price DOUBLE, isEnabled TINYINT(1) NOT NULL ) PARTITION BY LIST (isEnabled) ( PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1) );
Field isEnabled (for all records) must be either 0 or 1. Setting any other value will throw an error.
Even if two partitions are defined, it’s possible that a single partition can store all data and the other may remain empty. So no performance is achieved here.
The last unbalanced partitioning method is called column partitioning. It’s a variant of range and list partitioning with the approximate the same constraints and rules. It must be used in combination with range or list and not as stand alone. However, it relaxes some of the rules:
- Multiple columns can be used in the partitioning expression.
- Non-integer types are allowed by the partitioning expression. But only DATE, DATETIME as well as some string types (CHAR, VARCHAR, BINARY, and VARBINARY only, no TEXT or BLOG).
This is an example with multiple range integer columns:
CREATE TABLE priceRanges ( minPrice INT, maxPrice INT ) PARTITION BY RANGE COLUMNS(minPrice, maxPrice) ( PARTITION p0 VALUES LESS THAN (5, 12), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );
And another one with string columns in the list partitioning criteria:
CREATE TABLE phoneNumbers ( phoneNumber BIGINT, `status` VARCHAR(30) ) PARTITION BY LIST COLUMNS(`status`) ( PARTITION pA VALUES IN (‘AVAILABLE’, ‘ASSIGNED’), PARTITION pB VALUES IN (‘BLOCKED’, ‘DISABLED’) );
The previous partitioning methods may be inefficient if not correctly used, in terms of even distribution. The following methods fix this issue, since:
- No set of values has to be defined for the partitioning column.
- No records are dropped since they fit a partition.
- MySQL determines the partition at runtime based on partition’s criteria value.
- However, the number of partitions must be explicitly set.
The hash partitioning is a method of splitting data based on a non-negative integer value. The value may come out as an expression evaluation. In other words, custom functions can be used as long as they return a non-negative integer value.
CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, accountId INT NOT NULL, dateCreated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, orderStatus TINYINT(1) NOT NULL, PRIMARY KEY (id) ) PARTITION BY HASH (id) PARTITIONS 4099;
In above example, MySQL takes care of the partitioning computation based on the value of id field (because id is the primary key, it must be in the partitioning criteria).
The other balanced partitioning method is the key method. It’s hash’s sister, with two major differences:
- Partitioning expression can be a non-integer value.
- MySQL applies its own hash function to compute the partition, the function varying among the storage engines (depending on the storage type it can be md5() or a password() like function).
The definition syntax is similar with the hash syntax:
CREATE TABLE orderDetails ( orderId INT NOT NULL, productId INT NOT NULL, dateCreated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, quantity INT NOT NULL, price DOUBLE NOT NULL, PRIMARY KEY (orderId, productId) ) PARTITION BY KEY (orderId) PARTITIONS 4;
In this example, the primary key consists of two fields but the partition is defined on a single column. The partitioning rules require that the partition field should be part of the primary key, so whatever number of fields compose the primary key, a partition must employ at least one.
Words versus facts
To check whether there is a difference between a non-partitioned table and a partitioned one (in terms of execution time), next tables have been progressively populated with a huge amount of data.
(the non-partitioned table)
CREATE TABLE `policies` ( `customerId` int(11) NOT NULL, `policyId` int(11) NOT NULL, `startDate` datetime NOT NULL, `endDate` datetime NOT NULL, `otherDetails` varchar(45) NOT NULL, PRIMARY KEY (`customerId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
(the partitioned table):
CREATE TABLE `policiesPartitioned` ( `customerId` int(11) NOT NULL, `policyId` int(11) NOT NULL, `startDate` datetime NOT NULL, `endDate` datetime NOT NULL, `otherDetails` varchar(45) NOT NULL, PRIMARY KEY (`customerId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (customerId) PARTITIONS 4099 */;
Both tables have been filled with the same amount of data (number of records) and between ranges (see below) the same query ran against both tables. For the partitioned table, an extra query which doesn’t contain the partitioning criteria has been run to observe the difference of querying a single partition (the necessary one) or all (99% unnecessary).
Above test result chart highlights two things:
- For small sets of data (less than 10000), searching in a partitioned table takes a little bit more than searching an unpartitioned table. So unless the table’s intent is to store many records, don’t use partitions.
- If the WHERE clause doesn’t contain the partitioning field, the search takes a lot compared to the same query over a given partition. The larger is the table, the more time to run a non-partitioned query it takes. In this case in the given example, the search is performed on 4099 internal tables.
Instead of a conclusion, a few considerations
The partitioning expression/column(s) should always be included in the search query. When not included, the search will be performed against multiple partitions, possibly all (and that will be really slow, see previous examples).
The number of partitions isn’t final during a table’s lifetime. They can be increased but decreased too (this comes with data loss so be aware of it). The advantage of the latter is that tables can be designed in such way that data removal (on purpose, as part of a controlled cleanup) is smooth and fast.
Partitions can be partitioned themselves – that’s called subpartitioning. But keep in mind that the maximum number of partitions and subpartitions altogether is 8192.
As usual, indexes speed up queries, so take advantage of them.
In the end, the disk storage isn’t infinite so the amount of data in a partitioned table will reach a max capacity at a point. Think in advance about backing up data too (e.g. replication).
Consider also database sharding as a proper way to store data, not as a replacement but in combination with partitioning.
The following sources have been consulted in order to prepare this article:
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html – the official MySQL partitioning specifications.
Latest posts by Adrian
- Database Partitioning: Big Data, Fast Queries - August 9, 2018