I have a table on which I would like to create an auto-partitioning scheme by first partitioning the table by month and then sub-partitioning by id.
I recently learned that mysql does not support auto-partitioning and the only way to create dynamic partitions is by stored-procedures and event schedulers.
My requirement is that new entries are automatically created into new partitions and after a year,the old records are deleted automatically.
How do I do this?
Please suggest me some methods on how to implement stored procedures and event schedulers to support dynamic partitioning and also to delete the old records automatically.
This is the schema:
CREATE TABLE `ORDER_HISTORY` (
`Id` bigint(20) NOT NULL,
`Invoice_Number` varchar(16) NOT NULL,
`User_Id` int(10) NOT NULL,
`Store_ID` mediumint(6) NOT NULL,
`Store_Entity_Id` mediumint(8) NOT NULL,
`Item_List` blob NOT NULL,
`Order_Time` datetime NOT NULL,
`Payment_Time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Payment_Type` tinyint(2) NOT NULL,
`Payment_Retry_Attempts` tinyint(1) NOT NULL,
`Payment_TransactionID` varchar(32) NOT NULL,
`Sub_Total_Amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`CGST_Tax_Amount` decimal(6,2) NOT NULL DEFAULT '0.00',
`SGST_Tax_Amount` decimal(6,2) NOT NULL DEFAULT '0.00',
`Other_Tax_Amount` decimal(6,2) NOT NULL DEFAULT '0.00',
`Service_Fee` decimal(6,2) NOT NULL DEFAULT '0.00',
`Earned_Cashback_Amount` decimal(4,2) NOT NULL DEFAULT '0.00',
`Used_Cashback_Amount` decimal(4,2) NOT NULL DEFAULT '0.00',
`Used_Coupon` mediumint(8) DEFAULT NULL,
`Used_Coupon_Discount` decimal(4,2) NOT NULL DEFAULT '0.00',
`Grand_Total_Amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`Status` tinyint(3) NOT NULL,
`Manager_Id` smallint(5) DEFAULT NULL,
`Store_Name` varchar(32) DEFAULT NULL,
`User_Name` varchar(32) DEFAULT NULL,
`User_Phone_Number` varchar(10) DEFAULT NULL,
`Manager_Phone_Number` varchar(10) NOT NULL,
`Manager_Name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `Id_UNIQUE` (`Id`),
KEY `Store_ID_idx` (`Store_ID`),
KEY `Table_ID_idx` (`Store_Entity_Id`),
KEY `Phone_Number_idx` (`User_Id`),
KEY `oh_payment_type_id_fk_idx` (`Payment_Type`),
KEY `oh_coupon_id_fk_idx` (`Used_Coupon`),
KEY `oh_status_id_fk_idx` (`Status`),
KEY `oh_manager_id_fk` (`Manager_Id`),
CONSTRAINT `oh_coupon_id_fk` FOREIGN KEY (`Used_Coupon`) REFERENCES `COUPONS` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oh_manager_id_fk` FOREIGN KEY (`Manager_Id`) REFERENCES `MANAGER` (`Id`),
CONSTRAINT `oh_payment_type_id_fk` FOREIGN KEY (`Payment_Type`) REFERENCES `PAYMENT_TYPES` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oh_status_id_fk` FOREIGN KEY (`Status`) REFERENCES `STATUS` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oh_store_entity_id_fk` FOREIGN KEY (`Store_Entity_Id`) REFERENCES `STORE_ENTITY` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oh_store_id_fk` FOREIGN KEY (`Store_ID`) REFERENCES `STORE` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oh_user_id_fk` FOREIGN KEY (`User_Id`) REFERENCES `USERS` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Thanks in advance!
First of all partitioning is the best use case for the issue which you faced. The reason is the storage engine which you use innodb is transaction based engine. So it records all the actions done in a log results in higher I/O resource. And for delete even it requires more resource. But partition with sub-partition is overkill because you need to clean the table data yearly once so you can use RANGE COLUMN clause with cron job which run yearly once.
You can check your MySQL supports partition by executing the following command:
SHOW PLUGINS;
This will show a tabular data in that please check you have partition in name column with ACTIVE in the Status column.
Partition Types:
There are two type of partition. They are vertical partition (partition by column) which is not supported by MySQL and the second one is horizontal partition (partition by row).
Partition Definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
SOLUTION:
Step 1: Alter the table schema with partioning like this (Reference link 3)
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
/*PARTITION y2013 VALUES LESS THAN ('2014-01-01'),
PARTITION y2014 VALUES LESS THAN ('2015-01-01'),
PARTITION y2015 VALUES LESS THAN ('2016-01-01'),
PARTITION y2016 VALUES LESS THAN ('2017-01-01'),*/ #This are older partition it may deleted before 2017 itself
PARTITION y2017 VALUES LESS THAN ('2018-01-01'),
PARTITION future VALUES LESS THAN MAXVALUE
);
Step 2: You can use any programming language like PHP, Ruby, Python, Perl to create this query dynamically. Consider this is running in 01 January 2019 using cron.
ALTER TABLE members
DROP PARTITION y2017;
ALTER TABLE members
REORGANIZE PARTITION future INTO
y2018 VALUES LESS THAN ('2019-01-01'),
future VALUES LESS THAN MAXVALUE;
Query to get previous years with format:
SELECT concat('Y', YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))) as prev_year
change the interval to get further older year.
Step 3: Execute the above query yearly once using any scheduler like cron. For e.g.
@yearly /home/meenu/ubuntu/bin/annual-maintenance.sh
REFERENCES:
More On Partition-ing:
Cron:
Big Deletes:
Partition Maintenance: