Partitioning of mysql database table - why and how?

We have few applications that use mysql database. Everything works perfectly except that one thing!

We are having trouble with file system usage. Even when we clean old data from tables, file system is still used because even do data are deleted from tables because there is no mechanism to automatically shrink tables. You can do this shrinkage with backup, drop and restore table but that means that you have to take your application down and users will notice that.
You can delay your problem with expanding you file system but after some time problem will be there again.
You will also notice that not all of your tables are using same size on file system(if you are rookie, this is important to know). This means that one or two tables are using 95% of your file system.

We solve this problem by using table partitioning. In this case you have same logical table which is made off smaller fragments of table called partition. Parameter that we used for partitioning was date of record. How big partition can be or how small/big these fragments are? Well that is up to you. We used a month parameter, that is are partition are made of data that are collected within a month period. In this case, when are file system is 95% full we just drop oldest partition. Database and application are online all the time and users do not know anything has happened.

When is best time do to table partition? Best time is after installation of database but in reality you usually do not know file system usage of tables until you are in full production. Even then you need some time to seen what table are biggest, how much it grows on day, week, month period. When you know this, then you can do table partitioning.

STEPS:


1. On file system you can see what table is biggest 

 [root@server ~]# cd /var/lib/mysql/database1/
[root@server ~]#  ls -lh
.
.
.
-rw-rw---- 1 mysql mysql   23M Aug  7 07:55 SystemEvents.MYI
-rw-rw---- 1 mysql mysql  110G Aug 12 01:56 SystemEvents.MYD
.
.

Ok, so this table is giving as a headache.

2.Login and connect to specific database called database1
[root@server ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.5.33-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use database1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
3. Depending of creating rules of table, you can use different parameter for partitioning.
Maybe safest way is to create new table, do partition of it and then insert data from table that you want to partition in to that new partitioned table and than do rename. This way your original table will be intacted in case something goes wrong.
First see table creating parameters.

mysql> SHOW CREATE TABLE SystemEvents;
SystemEvents | CREATE TABLE `SystemEvents` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) DEFAULT NULL,
  `ReceivedAt` datetime DEFAULT NULL,
  `DeviceReportedTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Facility` smallint(6) DEFAULT NULL,
  `Priority` smallint(6) DEFAULT NULL,
  `FromHost` varchar(60) DEFAULT NULL,
  `Message` text,
  `NTSeverity` int(11) DEFAULT NULL,
  `Importance` int(11) DEFAULT NULL,
  `EventSource` varchar(60) DEFAULT NULL,
  `EventUser` varchar(60) DEFAULT NULL,
  `EventCategory` int(11) DEFAULT NULL,
  `EventID` int(11) DEFAULT NULL,
  `EventBinaryData` text,
  `MaxAvailable` int(11) DEFAULT NULL,
  `CurrUsage` int(11) DEFAULT NULL,
  `MinUsage` int(11) DEFAULT NULL,
  `MaxUsage` int(11) DEFAULT NULL,
  `InfoUnitID` int(11) DEFAULT NULL,
  `SysLogTag` varchar(60) DEFAULT NULL,
  `EventLogType` varchar(60) DEFAULT NULL,
  `GenericFileName` varchar(60) DEFAULT NULL,
  `SystemID` int(11) DEFAULT NULL,
  `checksum` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`,`DeviceReportedTime`)
) ENGINE=MyISAM AUTO_INCREMENT=711764694 DEFAULT CHARSET=latin1;

Ok, now create new table with same parameters.

mysql> CREATE TABLE `SystemEvents_new`  ( ` ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) DEFAULT NULL,
  `ReceivedAt` datetime DEFAULT NULL,
  `DeviceReportedTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Facility` smallint(6) DEFAULT NULL,
  `Priority` smallint(6) DEFAULT NULL,
  `FromHost` varchar(60) DEFAULT NULL,
  `Message` text,
  `NTSeverity` int(11) DEFAULT NULL,
  `Importance` int(11) DEFAULT NULL,
  `EventSource` varchar(60) DEFAULT NULL,
  `EventUser` varchar(60) DEFAULT NULL,
  `EventCategory` int(11) DEFAULT NULL,
  `EventID` int(11) DEFAULT NULL,
  `EventBinaryData` text,
  `MaxAvailable` int(11) DEFAULT NULL,
  `CurrUsage` int(11) DEFAULT NULL,
  `MinUsage` int(11) DEFAULT NULL,
  `MaxUsage` int(11) DEFAULT NULL,
  `InfoUnitID` int(11) DEFAULT NULL,
  `SysLogTag` varchar(60) DEFAULT NULL,
  `EventLogType` varchar(60) DEFAULT NULL,
  `GenericFileName` varchar(60) DEFAULT NULL,
  `SystemID` int(11) DEFAULT NULL,
  `checksum` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`,`DeviceReportedTime`)
) ENGINE=MyISAM AUTO_INCREMENT=711764694 DEFAULT CHARSET=latin1

Ok, we created table called SystemEvents_new with same parameter as SystemEvents table.
4. Now create partition based on these parameters DeviceReportedTime and ID. For my application I need to use these parameters, for yours you will maybe need to use different. These parameters are called PRIMARY KEYS. It is important to define these because you would not be able to do partition without them because they are parameters of partitioning.

mysql> alter table SystemEvents_new PARTITION BY RANGE(TO_DAYS(DeviceReportedTime))  ( partition p20130820 values less than (TO_DAYS('2013-08-20 00:00:00')),
partition p20130821 values less than (TO_DAYS('2013-08-21 00:00:00')), 
partition p2014 values less than (MAXVALUE));


Ok, so we created following partitions p20130820 with values that are prior 2013-08-20 00:00:00, p20130821 with values that are prior 2013-08-21 00:00:00 but after last knows partition(in this case that is partition p20130820) and p2014 with all values that are greater then are last known partition(in this case that is p20130821). So important thing here is to have continues value in table SystemEvents_new. As you can see time continuum is not broken. It won't be possible to create partition without creating last partition that have MAXVALUE parameter. This means that database will continue to write data in that last partition when all values that are younger that that last defined date. In are case that means that all date larger that 2013-08-21 00:00:00 is stored in partition with MAXVALUE parameter.

5. Insert data from non-partitioned SystemEvents table into partitioned SystemEvents_new table.

mysql>insert into SystemEvents_new select * from SystemEvents;

6. After you inserted data, you can rename table names

mysql>rename table SystemEvents to SystemEvents_old, SystemEvents_new to SystemEvents;

7. Exit mysql. Check if there are partition on file system.

 [root@server ~]# cd /var/lib/mysql/database1/
[root@server ~]#  ls -lh
.
.
.
-rw-rw---- 1 mysql mysql  144M Aug  22 01:48 SystemEvents#P#p20130820.MYD
-rw-rw---- 1 mysql mysql   13M Aug  22 12:10 SystemEvents#P#p20130820.MYI
-rw-rw---- 1 mysql mysql  143M Aug  22 01:49 SystemEvents#P#p20130821.MYD
-rw-rw---- 1 mysql mysql   13M Aug  22 12:10 SystemEvents#P#p20130821.MYI
-rw-rw---- 1 mysql mysql 1019M Aug  22 01:48 SystemEvents#P#p2014.MYD
-rw-rw---- 1 mysql mysql   89M Aug  22 07:55 SystemEvents#P#p2014.MYI
.
.

Ok, so we have done partitioning part.
Now when you want to free some file system space you just drop oldest partition instead making backup ,drop table, create table, restore data,etc.

8. When your file system is full then drop partition table

mysql>alter table SystemEvents drop partition p20130820;



Off course,this is simple example with only 3 partition created. You can create as much partition as you want with smaller or bigger time interval.
It is important to know that depending of size of that table, insert process can take a long time to finish. During this time it would be good to stop your application because you want insert process to finish as soon as possible. If you can not stop your application, insert can be done but it will take a bit longer to finish because you will have insert, reading, writing,etc. in you database.