Mysql – Best Practice to Archive Old Data in MySQL

database-designinnodbMySQL

I'm using a MySQL database to store logs from multiple servers. The database's growth on average is 50 GB per day. Its total size is now 1.7 TB.

It has ID and datetime columns.

I want to move data older than one week to another database on the same server.

What are the steps needed for this?

As of now I do this:

Create the Archive Database and table.
Insert into ArchiveDB.table select * from old_tbl where datetime <= Archivedate
Then DELETE FROM old_tbl WHERE datetime <= Archivedate

I want to implement this with the following conditions:

Insert and delete via batches.
Reclaim the space after deleting from the old database.
Update stats without downtime.

…Or if you know of any best practice then let me know.

Best Answer

With InnoDB and PARTITIONs, you could set up daily PARTITION BY RANGE(TO_DAY(...)) and use "Transportable tablespaces" to disassociate a day from the table and move it separately. This will be a lot faster than the queries it would take to do the INSERT...SELECT and DELETE. Once detached, the partition (now a table in its own right) can be moved to another database or server without impacting the ingestion of more data.

One drawback: InnoDB's disk footprint is a lot more than Archive's.

What kinds of queries do you apply to the logs?

Blogs: partitioning and chunking lengthy deletes

Related Solutions

Mysql – Need suggestions for best way to archive MySQL (InnoDB) tables

For compressing...

Do it in the client; that will lead to less traffic between client and server. (OK, they are on the same machine, so this is not much of an issue.)

Use PHP's gzcompress, gzuncomress; don't worry about compression level. Expect about 3:1 compression for regular text.

Yes, the MEDIUMTEXT would need to be MEDIUMBLOB.

Don't "archive" old data; you have not justified the need for it (yet). Caching will generally take care of making 'recent' chapters faster.

Check out Facebook and Percona for "online alters".

innodb_buffer_pool_size should be about 70% of available ram.

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Unfortunately, Yes you have to do every database. I have a step-by-step process I wrote up back on Oct 29, 2010 in StackOverflow as well as thorough explanation why there is nothing else you can do.

You also asked

Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database ?

You cannot reclaim half. It is an all-or-nothing decision. It is good to keep in mind what lives in ibdata1 when innodb_file_per_table is disabled :

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollback Segments
- Undo Space
Table Metadata (Data Dictionary)
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary indexes)
See the Pictorial Representation of ibdata1

Still, if you want to know which InnoDB tables are in ibdata1 and which are in their own tablespace file (.ibd), here is what you do:

STEP 01 : Run this query in the MySQL client

SELECT COUNT(1) InnoDBTableCount
FROM information_schema.tables
WHERE engine='InnoDB';

STEP 02 : Collect the number of .ibd files in the OS

ls -lR | grep -c ".ibd$"

STEP 03 : Subtract those values in the OS

Here is the shell script to compute these things

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

If you have partitioned tables, then do this:

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.partitions WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

Here is what you get:

IB_TOT : Number of InnoDB Tables in the MySQL Instance
IB_IBD : Number of InnoDB Tables Residing Outside ibdata1 as .ibd Files
IB_SYS : Number of InnoDB Tables Residing Inside ibdata1

Best Answer

Related Solutions

Mysql – Need suggestions for best way to archive MySQL (InnoDB) tables

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Related Question