The purpose of this post if to show how you can automate the creation and pruning of partitioned tables. If you want to read about partitioning I recommend reading our manual.
In short partitioning makes it possible to spread your individual tables across a file system according to the partition rules you specify.
Reasons for partition your tables might be:
- Insert performance, smaller index trees for stable insert throughput.
- Select performance, only read data from selected (aka partitioning pruning) partitions.
- Delete performance, drop partitioning is must quicker than doing range deletes of old data.
Partitioning definitions is part for the CREATE/ALTER table statements, in …
10 Older Entries »
In part 1 and part 2 of this blog series, I showed how a DBA could configure MySQL or MariaDB to automatically drop old partitions. Some readers mentioned that they would also like for new partitions to be automatically created. In this blog post, I will show a stored procedure that can automatically create new partitions, and I will tie ... Read More
In a previous blog post, I showed how a DBA could configure MySQL or MariaDB to automatically drop old partitions. Some readers provided some feedback on some issues that they’ve run into while doing similar operations. Specifically: It can sometimes help to maintain an empty first partition when partitioning by dates, since partition pruning cannot always eliminate the first partition. ... Read More
A MariaDB Support customer recently asked how they could automatically drop old partitions after 6 months. MariaDB and MySQL do not have a mechanism to do this automatically out-of-the-box, but it is not too difficult to create a custom stored procedure and an event to call the procedure on the desired schedule. In this blog post, I will show one ... Read More
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 …[Read more]
Sometimes MySQL surprises you in ways you would have never
Would you think that the order in which the indexes appear in a table matters?
It does. Mind you, not the order of the columns - the order of the indexes.
MySQL optimizer can, in specific circumstances, take different paths, sometimes with nefarious effects.
Please consider the following table:
CREATE TABLE `mypartitionedtable ` (
`HASH_ID` char(64) NOT NULL,
`RAW_DATA` mediumblob NOT NULL,
`EXPIRE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `EXPIRE_DATE_IX` (`EXPIRE_DATE`),
KEY `HASH_ID_IX` (`HASH_ID`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_UNCOMPRESSED
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(EXPIRE_DATE))
(PARTITION p2005 VALUES LESS THAN (1487847600) ENGINE = …
MySQL Cluster does an automatic sharding/partitioning to the
tables across data nodes, enabling databases to scale
horizontally to serve read and write-intensive workloads, but
what is the default sharding key used in partitioning the
According to the recent update (Oct, 2016) of the MySQL Cluster white paper, primary key is the default sharding key:
By default, sharding is based on hashing of the primary key, which generally leads to a more even distribution of data and queries across the cluster than alternative approaches such as range partitioning.
However, that is not the case in all MySQL Cluster versions so
In this post, I’ll do some test cases on MySQL Cluster (of 4 datanodes) to confirm the default sharding key.
Testing on MySQL Cluster 7.2.26 …[Read more]
As stated in the MySQL Cluster documentation:
Partition. This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.
According to my understanding for the previous paragraph, if we
have a cluster of 6 datanodes we should have 6 partitions for
each NDB table. I claim that this is not true for all cases – at
least, after the introduction of ndbmtd (Multi-Threaded Daemon) in MySQL
Cluster 7.2 .
In this post, I’ll do some …
One of the main tasks that any DBA has to deal with is certainly
data retention. Especially when your developers like to store
logging and tracking information inside the database. This is not
something that I would recommend (don't try it at home!) but when
you're late to the party (read: someone had taken this path
before you joined) , then you'll have to live with it.
Data retention in MySQL is usually applied by partitioning the table by RANGE on a selected column, usually containing the row creation timestamp. New partitions are created in advance for the coming days or weeks or months, and a script is used that will run nightly and drop partitions that are older than a set retention.
There are many tools that can be used to automate data retention; I have chosen the excellent pdb-parted, a nice and cozy perl script that you can find in the PalominoDB repository on GitHub (since PalominoDB is no longer in existence, …
At Yahoo, we manage a massive number of MySQL databases spread across multiple data centers.
We have thousands of databases and each database has many partitioned tables. In order to efficiently create and maintain partitions we developed a partition manager which automatically manages these for you with minimal pre configuration.
Today, we’re releasing MySQL Partition Manager. You can check out the code on GitHub.
We’re looking forward to interacting with the MySQL community and continue developing new features.
- MySQL Database Engineering Team, Yahoo
10 Older Entries »