What's the best way to structure logging application data in MySQL

Question

I may be overthinking this but:

I have an API; I want to log calls to the API for stat/debugging purposes. Ideally the speed of reads should be quick but is not essential (as will primarily be read by me). However, the speed of writes should be good as I don't want to slow the API down.

We'll be starting off from scratch, but the API (and logging) will last years. The older the data gets, the less useful it is. At some point we might want to drop the old data entirely (say, dropping data older than three years).

Running MySql 5.5 at the moment.

What's the best way to structure the table? Partition it by year and just make up a load of future partitions?

PARTITION BY RANGE( YEAR(date) ) (
 PARTITION p0 VALUES LESS THAN (2016),
 PARTITION p1 VALUES LESS THAN (2017),
 PARTITION p2 VALUES LESS THAN (2018),
 PARTITION p3 VALUES LESS THAN (2019),
 PARTITION p4 VALUES LESS THAN MAXVALUE
);

Or is there a way to create automatic rolling partitions?

Or do I just index on year?

• Vérace: Use the ARCHIVE engine for a start. It was designed for this very thing.

Answer 1

Yes, there is. I would change the partition names to something more meaningful.

Let say the table look like this in the mydb database:

CREATE TABLE mytable
(
 id INT NOT NULL AUTO_INCREMENT,
 date DATETIME,
 PRIMARY KEY (id,date),
 KEY (date)
)
PARTITION BY RANGE( YEAR(date) ) (
 PARTITION p2015 VALUES LESS THAN (2016),
 PARTITION p2016 VALUES LESS THAN (2017),
 PARTITION p2017 VALUES LESS THAN (2018),
 PARTITION p2018 VALUES LESS THAN (2019),
 PARTITION p9999 VALUES LESS THAN MAXVALUE
);

To add a partition with the year 2019

ALTER TABLE mytable DROP PARTITION p9999;
ALTER TABLE mytable ADD PARTITION
 (PARTITION p2019 VALUES LESS THAN (2020)),
 (PARTITION p9999 VALUES LESS THAN MAXVALUE)
;

To drop the oldest partition

ALTER TABLE mytable DROP PARTITION p2016;

Want a Stored Procedure to do the following ?

• Drop partitions before this year
• Append new partition 4 years into the future

Here is the code

DELIMITER $$
DROP PROCEDURE IF EXISTS `mydb`.`sp_mytable_Rotate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mydb`.`sp_mytable_Rotate` ()
This_Stored_Procedure:BEGIN
 SET @year0 = YEAR(NOW());
 SET @year4 = @year0 + 4;
 SET @year5 = @year0 + 5;
 SET @OldPartitionToKeep = CONCAT('p',@year0);
 SET @NewPartitionToMake = CONCAT('p',@year4);
 SELECT COUNT(1) INTO @zapcount FROM information_schema.partitions
 WHERE table_schema = DATABASE() AND table_name = 'mytable'
 AND partition_name < @OldPartitionToKeep;
 IF @zapcount = 0 THEN LEAVE This_Stored_Procedure; END IF;
 SELECT GROUP_CONCAT(partition_name) INTO @partitions_to_drop
 FROM information_schema.partitions WHERE table_schema = DATABASE()
 AND table_name = 'mytable' AND partition_name < @OldPartitionToKeep;
 SET @partitions_to_drop= CONCAT(@partitions_to_drop,',p9999');
 SET @sql = CONCAT('ALTER TABLE mytable DROP PARTITION ',@partitions_to_drop);
 PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
 SET @sql = 'ALTER TABLE mytable ADD PARTITION ';
 SET @sql = CONCAT(@sql,'(PARTITION ',@NewPartitionToMake,' VALUES LESS THAN (',@year5,'))');
 PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
 SET @sql = 'ALTER TABLE mytable ADD PARTITION (PARTITION p9999 VALUES LESS THAN MAXVALUE)');
 PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END $$
DELIMITER ;

From here, just call it

USE mydb
CALL sp_mytable_Rotate();

On January 1, you could backup the old partition's data using mysqldump before rotating like this:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_FILE=Data_From_${LASTYEAR}.sql
BACKUP_GZIP=${BACKUP_FILE}.gz
mysqldump ${MYSQL_CONN} mydb mytable --where="date<'%{THISYEAR}-01-01'"| gzip > ${BACKUP_GZIP}
mysql ${MYSQL_CONN} -Dmydb -ANe"CALL sp_mytable_Rotate()"

Based on Vérace's suggestion, if you want to backup the data into a table, an ARCHIVE table can compress the data and have no additional overhead for indexes.

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_TABLE=mydata_${LASTYEAR}
MKDT="MAKEDATE(${THISYEAR},1)"
SQL="CREATE TABLE ${BACKUP_TABLE} ENGINE=ARCHIVE SELECT * FROM mytable WHERE 0=1;"
SQL="${SQL} INSERT INTO ${BACKUP_TABLE} SELECT * FROM mytable WHERE date < ${MKDT}"
SQL="${SQL} CALL sp_mytable_Rotate();"
mysql ${MYSQL_CONN} -Dmydb -ANe"${SQL}"

You can then move .frm and .ARZ files of the ARCHIVE table to a designated backup volume.

GIVE IT A TRY !!!

• Vérace: And the suggestion to use the ARCHIVE storage engine?
• RolandoMySQLDBA: @Vérace it is a good suggestion. Let me script it ...