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

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

It's migration time. There was another project that I use often and was still in Launchpad. The Sample Employees Database is now on GitHub, under the same license it had before (CC A-SA 3).

Figure 1 - Employees database
This database is interesting because it is not too small (like Sakila) and not too big. It has enough data to allow you to test in a non trivial way.
Installation and testInstalling the database is easy:

$ git clone https://github.com/datacharmer/test_db.git
$ cd test_db
$ mysql < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO …

Partitioning The advantage of partitioning is that we can drop / truncate the partitions which is real quick and done in seconds. It does lock the table (metadata lock) during this operation but only for few seconds. That duration depends on the number of pages in use in the buffer pool. With MySQL 5.6 it is possible to exchange the partitions , which helps in moving archived data to another table with similar structure.
   
Fully Automated archival / purge 

Consider a fast growing table which is partitioned to help the queries and help quick archival/purge. The queries if specify the partitioning filter can limit the search to relevant partitions and can considerably reduce the query time. Purging old data to reclaim the storage space can be readily achieved by truncating the old partitions. Archival has multiple techniques but with MySQL 5.6 exchanging the partitions make archival a real …

We’ve heard lots in recent years about Big Data and the alternative models of data management and processing, like Hadoop and NoSQL. But truth be told, relational databases are still the workhorses of most of today’s small and medium sized businesses. Relational DBs date back over 40 years and SQL skills are fairly common, and they’re known to be highly secure.

MySQL is the world’s second most popular relational database management system (RDMS) and is the most popular open-source version of the database. It’s easily accessible and is often known to be part of the LAMP web development stack, standing for the ‘M’ in the acronym of popular tools, along with Linux, Apache, and PHP/Perl/Python. The fact that MySQL is free, easy to setup and scales fast are some of the main reasons why it’s the best match for many SMBs.

…

The InnoDB labs release includes a snapshot of the InnoDB Native Partitioning feature.

To better understand why we implemented this, we need to start with some background on tables, storage engines, and handlers. In MySQL an open instance of a table has a handler object as an interface to the table’s storage engine. For a partitioned table there is a main table handler that implements the partitioning feature, but for storage, each partition has its own handler. This worked fairly well, but the more partitions you had the more overhead from the per partition handlers. So to remove this overhead for partitioned InnoDB tables we’re introducing Native Partitioning support! This means a new InnoDB partitioning aware handler, so that we have a single handler object for a partitioned table and not one handler object per partition.

Let us create a simple table with 8k partitions:

In my last post, I gave a technical explanation of the performance characteristics of partitioned collections in TokuMX 1.5 (which is right around the corner) and partitioned tables in relational databases. Given those performance characteristics, in this post, I will present some best practices when using this feature in TokuMX or TokuDB. Note that these best practices are designed for TokuMX and TokuDB only, which use …

In TokuMX 1.5 that is right around the corner, the big feature will be partitioned collections. This feature is similar to partitioned tables in Oracle, MySQL, SQL Server, and Postgres. A question many have is “why should I use partitioned tables?” In short, it’s complicated. The answer depends on your workload, your schema, and your database of choice. For example, this Oracle related post states “Anyone with un-partitioned databases over 500 gigabytes is courting disaster.” That’s not true for TokuDB or TokuMX. Nevertheless, partitioned tables are valuable; it’s why we …

So I recently realized that I have not yet talked much about MySQL partitions.
Many good blog posts on MySQL partitions already exists and I have listed a few below.

• http://glynndba.blogspot.com/2009/10/rolling-time-based-partitions.html
• http://www.mysqlperformanceblog.com/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/
• http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html
• …

When InnoDB transportable tablespaces was introduced in 5.6 it did not support partitions since it could be accomplished in combination with EXCHANGE PARTITION. To make it easier to use transportable tablespaces for partitioned tables we added support for DISCARD/IMPORT TABLESPACE for partitioned tables as well as single partitions in 5.7.4.To get a consistent full copy of the tablespace files one use FLUSH TABLES <list of tables> FOR EXPORT: 

# Flush all dirty pages to the tablespaces and write a .cfg file per tablespace
mysql> FLUSH TABLE …