MySQL5 : MySQL Optimizations - Partioning by range

2013-08-05
Laurent Bientz

Since MySQL version 5.1 has many happy guys because finally provides support for the partitioning.

To put in context, by analogy with a hard disk, the partitioning of tables allows three main functionalities:

  • Properly organize their data
  • Faster access to data
  • To simply delete data

In our case, we use the partitioning primarily for performance reasons.

I will not go into all the possibilities MySQL matter (horizontal, vertical, etc..), You can consult the official documentation at the following address:

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

We had to set up the table partitioning of the agency to deal with performance issues.

If you work with large log tables and / or reporting, you should be aware that the more time passes, your tables grow and your queries are long to execute, well what are properly indexed.

To overcome this, we can implement the partioning.

To be effective, we must analyze your queries to see which column you partitioning. The idea is to significantly reduce the scope of the query.

In our present case, we had tables reporting more than 6M recordsets, constituate in just 3 months. Requests began to put more than 2 seconds to execute and it does would get worse.

Our reporting queries allow to extract statistics between two dates, the idea was to partitioning per row based on the date of adding the recordset.

By default, the two bounds were our application works was a week, and after analysis, we had about 500K recordsets week.

The calculation was simple: 2M per month recordsets> 25M almost a year, we partitioning week on the date of addition.

Our database table 6M recordsets as follows:

CREATE TABLE IF NOT EXISTS `like` (
  `like_id` bigint(20) NOT NULL COMMENT 'Id',  
  `like_ip` varchar(15) NOT NULL COMMENT 'Address IP',
  `like_user_agent` longtext COMMENT 'User Agent',
  `like_date_add` datetime NOT NULL COMMENT 'Date added',
  `news_id` bigint(20) NOT NULL COMMENT 'News',
  PRIMARY KEY (`like_id`,`like_date_add`),  
  KEY `news_id` (`news_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='News - Likes';

Notice that the primary key is composed of true PK and the column on which you want Partitioning: MySQL is unfortunately a requirement, the column on which the partitioning is performed must be unique or be part of a clause unique on multiple columns or you take this error trying to partitioning:

#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

The request for Partitioning (generated with a PHP script):

ALTER TABLE like PARTITION BY RANGE (to_days(like_date_add))(
||t||PARTITION Y2013W01 VALUES LESS THAN (to_days('2013-01-07')),
||t||PARTITION Y2013W02 VALUES LESS THAN (to_days('2013-01-14')),
||t||PARTITION Y2013W03 VALUES LESS THAN (to_days('2013-01-21')),
||t||PARTITION Y2013W04 VALUES LESS THAN (to_days('2013-01-28')),
||t||PARTITION Y2013W05 VALUES LESS THAN (to_days('2013-02-04')),
||t||PARTITION Y2013W06 VALUES LESS THAN (to_days('2013-02-11')),
||t||PARTITION Y2013W07 VALUES LESS THAN (to_days('2013-02-18')),
||t||PARTITION Y2013W08 VALUES LESS THAN (to_days('2013-02-25')),
||t||PARTITION Y2013W09 VALUES LESS THAN (to_days('2013-03-04')),
||t||PARTITION Y2013W10 VALUES LESS THAN (to_days('2013-03-11')),
||t||PARTITION Y2013W11 VALUES LESS THAN (to_days('2013-03-18')),
||t||PARTITION Y2013W12 VALUES LESS THAN (to_days('2013-03-25')),
||t||PARTITION Y2013W13 VALUES LESS THAN (to_days('2013-04-01')),
||t||PARTITION Y2013W14 VALUES LESS THAN (to_days('2013-04-08')),
||t||PARTITION Y2013W15 VALUES LESS THAN (to_days('2013-04-15')),
||t||PARTITION Y2013W16 VALUES LESS THAN (to_days('2013-04-22')),
||t||PARTITION Y2013W17 VALUES LESS THAN (to_days('2013-04-29')),
||t||PARTITION Y2013W18 VALUES LESS THAN (to_days('2013-05-06')),
||t||PARTITION Y2013W19 VALUES LESS THAN (to_days('2013-05-13')),
||t||PARTITION Y2013W20 VALUES LESS THAN (to_days('2013-05-20')),
||t||PARTITION Y2013W21 VALUES LESS THAN (to_days('2013-05-27')),
||t||PARTITION Y2013W22 VALUES LESS THAN (to_days('2013-06-03')),
||t||PARTITION Y2013W23 VALUES LESS THAN (to_days('2013-06-10')),
||t||PARTITION Y2013W24 VALUES LESS THAN (to_days('2013-06-17')),
||t||PARTITION Y2013W25 VALUES LESS THAN (to_days('2013-06-24')),
||t||PARTITION Y2013W26 VALUES LESS THAN (to_days('2013-07-01')),
||t||PARTITION Y2013W27 VALUES LESS THAN (to_days('2013-07-08')),
||t||PARTITION Y2013W28 VALUES LESS THAN (to_days('2013-07-15')),
||t||PARTITION Y2013W29 VALUES LESS THAN (to_days('2013-07-22')),
||t||PARTITION Y2013W30 VALUES LESS THAN (to_days('2013-07-29')),
||t||PARTITION Y2013W31 VALUES LESS THAN (to_days('2013-08-05')),
||t||PARTITION Y2013W32 VALUES LESS THAN (to_days('2013-08-12')),
||t||PARTITION Y2013W33 VALUES LESS THAN (to_days('2013-08-19')),
||t||PARTITION Y2013W34 VALUES LESS THAN (to_days('2013-08-26')),
||t||PARTITION Y2013W35 VALUES LESS THAN (to_days('2013-09-02')),
||t||PARTITION Y2013W36 VALUES LESS THAN (to_days('2013-09-09')),
||t||PARTITION Y2013W37 VALUES LESS THAN (to_days('2013-09-16')),
||t||PARTITION Y2013W38 VALUES LESS THAN (to_days('2013-09-23')),
||t||PARTITION Y2013W39 VALUES LESS THAN (to_days('2013-09-30')),
||t||PARTITION Y2013W40 VALUES LESS THAN (to_days('2013-10-07')),
||t||PARTITION Y2013W41 VALUES LESS THAN (to_days('2013-10-14')),
||t||PARTITION Y2013W42 VALUES LESS THAN (to_days('2013-10-21')),
||t||PARTITION Y2013W43 VALUES LESS THAN (to_days('2013-10-28')),
||t||PARTITION Y2013W44 VALUES LESS THAN (to_days('2013-11-04')),
||t||PARTITION Y2013W45 VALUES LESS THAN (to_days('2013-11-11')),
||t||PARTITION Y2013W46 VALUES LESS THAN (to_days('2013-11-18')),
||t||PARTITION Y2013W47 VALUES LESS THAN (to_days('2013-11-25')),
||t||PARTITION Y2013W48 VALUES LESS THAN (to_days('2013-12-02')),
||t||PARTITION Y2013W49 VALUES LESS THAN (to_days('2013-12-09')),
||t||PARTITION Y2013W50 VALUES LESS THAN (to_days('2013-12-16')),
||t||PARTITION Y2013W51 VALUES LESS THAN (to_days('2013-12-23')),
||t||PARTITION Y2013W52 VALUES LESS THAN (to_days('2013-12-30')),
||t||PARTITION unknown VALUES LESS THAN MAXVALUE
);

So instead of typing on 6M recordsets, we type on partitions of 500K records.

Note that the unknown partition is the partition where trash MySQL "put" recordsets that no match.

From a pure performance perspective, this has reduced to 80% of query execution time because this time is exponential with respect to the size of the table and the tables are small, better MySQL uses its index.

By the way, do not believe that the partitioning solves everything, you must of course combine with intelligent use of indexes.

Think carefully ranks partitioning is the key! If your application can draw stats by half, it is aberrant to partition a week because MySQL conduct a UNION ALL on 24 partitions.

Note that MySQL handles very well the following three cases:

  • A declaration of partitioning, it automatically arranges all the data in the relevant partitions.
  • Upon inserting (INSERT), it fits into the correct partition.
  • A reading (SELECT), it analyzes your where clauses to go in the correct partition(s).

It does not, however, manages the automatic creation of partition depending on your range. In our case, we need to launch an annual cron will create all partitions of the year N+1 using a MySQL alter.

Finally, the partitioning is also useful in the case of cron cleaning obsolete data. If you want to delete all the data of one week, the following query is enough:

ALTER TABLE like DROP PARTITION Y2013W10

It will be much faster than a conventional:

DELETE 
FROM 
||t||news n
WHERE
||t||DATE_FORMAT(n.news_date_add,'%Y-%v') = '2013-10'

 

Sirada - 2013-09-02 09:39:57
The functionality I'd like to see is remnniag without reorganizing it. If you have a range part'd table and your last one is a maxvalue partition, it seems like you should be able to something like this
ALTER TABLE ex RENAME PARTITION ex_maxvalue TO ex_20090915, ADD PARTITION ex_maxvalue VALUES LESS THAN MAXVALUE;
In other words, it should work like table remnniag.
Doing a REORGANIZE locks the table while the data is being shuffled, which is operationally disruptive.
belovedKa - 2013-09-09 18:29:46
Bravo, c'est simplement magnifique !
Ruud H.G. van Tol - 2015-02-09 19:02:47
In 5.7 you can rename a range partition in a few fast steps:
ALTER TABLE MyTable EXCHANGE PARTITION pBAD WITH TABLE MyTable_pGOOD;
ALTER TABLE MyTABLE REORGANIZE PARTITION pBAD (pGOOD VALUES LESS THAN ...);
ALTER TABLE MyTable EXCHANGE PARTITION pGOOD WITH TABLE MyTable_pGOOD WITHOUT VALIDATION;

Comment

USER EXPERIENCE

Wandi invites you to discover a new approach to navigating a website...
Are you ready?

Launch the experience