Reorganize last partition table in mysql EXPLAINED!!!

When you new data start to use last partition in table that you partitioned it is time that from that last partition create new partitions.

It is neccesery to this because when you create partitions your last partition has parameter MAXVALUE.
Check this post for introduction http://sysadmin-tricks.blogspot.com/2014/03/partitioning-of-mysql-database-table.html .

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.

When you start to use partition p2014, this means that previuos partition are done with entering data. So for instance, all your partition stores data for one day period but this last partition can store data for much larger period of time because it has MAXVALUE  parameter defined during creation of it.

When this happens it is time to reorganize that last partition. Why is necceseru to do this? Because after some time, this partition becames so large and at that time point of creating partitions on that table loose sence. If your partitions store data for one day and they are 1GB big, this last would be much, much bigger because in it you would have (this is example) data for more that one day.

OK, so here we go!

#mysql
mysql>use database1
mysql>alter table SystemEvents reorganize partition p2014 into
 ( partition p20130907 values less than (to_days('2013-09-07')),
   partition p20130908 values less than (to_days('2013-09-08')),
   partition p20130909 values less than (to_days('2013-09-09')),
   partition p20130910 values less than (to_days('2013-09-10')),
   partition p20130911 values less than (to_days('2013-09-11')),
   partition p20130912 values less than (to_days('2013-09-12')),
   partition p20130913 values less than (to_days('2013-09-13')),
   partition p20130914 values less than (to_days('2013-09-14')),
   partition p20130915 values less than (to_days('2013-09-15')),
   partition p20130916 values less than (to_days('2013-09-16')),
   partition p20130917 values less than (to_days('2013-09-17')),
   partition p20130918 values less than (to_days('2013-09-18')),
   partition p20130919 values less than (to_days('2013-09-19')),
   partition p20130920 values less than (to_days('2013-09-20')),
   partition p20130921 values less than (to_days('2013-09-21')),
   partition p20130922 values less than (to_days('2013-09-22')),
   partition p20130923 values less than (to_days('2013-09-23')),
   partition p20130924 values less than (to_days('2013-09-24')),
   partition p20130925 values less than (to_days('2013-09-25')),
   partition p20130926 values less than (to_days('2013-09-26')),
   partition p20130927 values less than (to_days('2013-09-27')),
   partition p20130928 values less than (to_days('2013-09-28')),
   partition p20130929 values less than (to_days('2013-09-29')),
   partition p20130930 values less than (to_days('2013-09-30')),
   partition p2014 values less than (MAXVALUE));

This will create new partition that will store data for one day from that big partition p2014.
As you can see last partition has again MAXVALUE parameter.
 

Redusing file system usage from large mysql table with no partition table

Ok, so you do not have table partition so when you want to do delete old data your file system is still used by large mysql table.

I will show you how to delete old data and clean your file system.

1. Backup your mysql database

#mysqldump --all-databases > mysql_30082013.dump

2. Delete old data from specific table. In my case I deleted data from table SystemEvents older than 5 days. In my case this means a reducing data for 95% of space.


mysql> DELETE FROM SystemEvents WHERE DeviceReportedTime <  DATE_SUB(NOW(),INTERVAL 5 DAY);

 3. So we deleted data from table but table is stil using file system. Mysql do not have automatic mechanism of optimising file system space when you delete data from table. Because of this we have to do myisamchk. In case that your use InnoDB tables, this utility wouldn't help you.

Small description from man pages of myisamchk command.

The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them.  myisamchk works with MyISAM tables

So what we are now doing repairing, checking and optimising of SystemEvents table. You will notice that every table is made of two files. One file is MYD and that is data part of table and second is MYI and that is index part of table.

MYI = index file
MYD = data file

So when we deleted data from data, we delete index from MYI file but MYD file did not change. With myisamchk data from MYD will synchronise with indexes from MYI file.


#cd /var/lib/mysql/database1
# myisamchk  -r SystemEvents.MYI
- recovering (with sort) MyISAM-table 'SystemEvents.MYI'
Data records: 1379674
- Fixing index 1
I after this, size of your MYD file should be much, much smaller.

Things that you should be aware of before you begin with myisamchk. Make shoore that you have enough space for this operation. During myisamchk file with extension TMD is created.

# ls |grep SystemEvents

SystemEvents.MYD
SystemEvents.MYI
SystemEvents.TMD

This is temporaly table created because what myisamchk is basically doing is this: create new TMD table, insert data from MYD with index from MYI and after this is done then do rename of that TMD table to MYD.

Size of this TMD table will depend of how much data you still have in MYD table. So if you table is 100GB big and you have left with 100MB space free it is almost certain that you wouldn't be able to do myisamchk repair. I will about how to solve this problem in my next post.