Linux tips and tricks for faster and easier administration Linux servers and applications! Troubleshooting and solutions!

Monday, 24 March 2014

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.

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

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

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 namesYou can turn off this feature to get a quicker startup with -A

Database changedmysql>
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.

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.

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.

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.