This article covers
First, my Opinions on PARTITIONing
Taken from Rick's RoTs - Rules of Thumb
It is so tempting to believe that PARTITIONing will solve performance problems. But it is so often wrong.
PARTITIONing splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.
A common fallacy: "Partitioning will make my queries run faster". It won't. Ponder what it takes for a 'point query'. Without partitioning, but with an appropriate index, there is a BTree (the index) to drill down to find the desired row. For a billion rows, this might be 5 levels deep. With partitioning, first the partition is chosen and "opened", then a smaller BTree (of say 4 levels) is drilled down. Well, the savings of the shallower BTree is consumed by having to open the partition. Similarly, if you look at the disk blocks that need to be touched, and which of those are likely to be cached, you come to the conclusion that about the same number of disk hits is likely. Since disk hits are the main cost in a query, Partitioning does not gain any performance (at least for this typical case). The 2D case (below) gives the main contradiction to this discussion.
Use case #1 -- time series. Perhaps the most common use case where PARTITIONing shines is in a dataset where "old" data is periodically deleted from the table. RANGE PARTITIONing by day (or other unit of time) lets you do a nearly instantaneous DROP PARTITION plus REORGANIZE PARTITION instead of a much slower DELETE. Much of this blog is focused on this use case. This use case is also discussed in Big DELETEs
The big win for Case #1: DROP PARTITION is a lot faster than DELETEing a lot of rows.
Use case #2 -- 2-D index. INDEXes are inherently one-dimensional. If you need two "ranges" in the WHERE clause, try to migrate one of them to PARTITIONing.
Finding the nearest 10 pizza parlors on a map needs a 2D index. Partition pruning sort of gives a second dimension. See Latitude/Longitude Indexing That uses PARTITION BY RANGE(latitude) together with PRIMARY KEY(longitude, ...)
The big win for Case #2: Scanning fewer rows.
Use case #3 -- hot spot. This is a bit complicated to explain. Given this combination:
The big win for Case #3: Improving caching to decrease I/O to speed up operations.
Use case #4 -- transportable tablespace. Using EXPORT/IMPORT partition for quickly archiving or importing data. (IMPORTing could be tricky because of the partition key.) See also Transportable Tablespaces for InnoDB Partitions That link talks about 5.7, but has a section "But how to do this in 5.6?"
The big win for Case #4: Quickly moving a partition in between tables (or servers).
Use case #5 -- I have yet to find a 5th use case.
Note that almost always, these use cases involve RANGE partitioning, not the other forms.
Examples (where id is AUTO_INCREMENT):
Let's focus on the maintenance task involved in Case #1, as described above.
You have a large table that is growing on one end and being pruned on the other. Examples include news, logs, and other transient information. PARTITION BY RANGE is an excellent vehicle for such a table.
Depending on the type of data, and how long before it expires, you might have daily or weekly or hourly (etc) partitions.
There is no simple SQL statement to "drop partitions older than 30 days" or "add a new partition for tomorrow". It would be tedious to do this by hand every day.
ALTER TABLE tbl DROP PARTITION from20120314; ALTER TABLE tbl REORGANIZE PARTITION future INTO ( PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')), PARTITION future VALUES LESS THAN MAXVALUE);
After which you have...
CREATE TABLE tbl ( dt DATETIME NOT NULL, -- or DATE ... PRIMARY KEY (..., dt), UNIQUE KEY (..., dt), ... ) PARTITION BY RANGE (TO_DAYS(dt)) ( start VALUES LESS THAN (0), from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')), from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')), ... from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')), from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')), future VALUES LESS THAN MAXVALUE );
Perhaps you noticed some odd things in the example. Let me explain them.
Why the bogus "start" partition? If an invalid datetime (Feb 31) were to be used, the datetime would turn into NULL. NULLs are put into the first partition. Since any SELECT could have an invalid date (yeah, this stretching things), the partition pruner always includes the first partition in the resulting set of partitions to search. So, if the SELECT must scan the first partition, it would be slightly more efficient if that partition were empty. Hence the bogus "start" partition. Longer discussion, by The Data Charmer 5.5 eliminates the bogus check, but only if you switch to a new syntax:
PARTITION BY RANGE COLUMNS(dt) ( PARTITION day_20100226 VALUES LESS THAN ('2010-02-27'), ...
More on the "future" partition. Sooner or later the cron/EVENT to add tomorrow's partition will fail to run. The worst that could happen is for tomorrow's data to be lost. The easiest way to prevent that is to have a partition ready to catch it, even if this partition is normally always empty.
Having the "future" partition makes the ADD PARTITION script a little more complex. Instead, it needs to take tomorrow's data from "future" and put it into a new partition. This is done with the REORGANIZE command shown. Normally nothing need be moved, and the ALTER takes virtually zero time.
As I have said many times, in many places, BY RANGE is perhaps the only useful variant. And a time series is the most common use for PARTITIONing.
How many partitions?
The complexity of the code is in the discovery of the PARTITION names, especially of the oldest and the 'next'.
To run the demo,
The program will generate and execute (when needed) either of these:
ALTER TABLE tbl REORGANIZE PARTITION future INTO ( PARTITION from20150606 VALUES LESS THAN (736121), PARTITION future VALUES LESS THAN MAXVALUE ) ALTER TABLE tbl DROP PARTITION from20150603
Original writing -- Oct, 2012; Use cases added: Oct, 2014; Refreshed: June, 2015; 8.0: Sep, 2016
PARTITIONing requires at least MySQL 5.1
The tips in this document apply to MySQL, MariaDB, and Percona.
Future (as envisioned in 2016):
MySQL 8.0, released Sep, 2016, not yet GA)
Native partitioning will give:
Rick James graciously allowed us to use this article in the Knowledge Base.
Rick James' site has other useful tips, how-tos, optimizations, and debugging tips.
Original source: http://mysql.rjweb.org/doc.php/partitionmaint
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.