CODEWITHSUNDEEP
mysqlperformancearchive
Edouard LOISEAU
Edouard LOISEAU

Reputation: 17

MySQL best practice for archiving data

I have a 120Go database with 1 specific very heavy table of 80Go (storing data since +10 years).

I think to move old data in archive, but wonder if it is best :

What would be the result on the performence point of view ?

1/ If I reduce the table to only 8Go and move 72Go in another table from the same database, is the database going to run faster (we won't access the archive table with read/write operations and r/W will be done on a lighter table).

2/ Keeping 72Go of data into the archive table will anyway slow down the database engine ?

3/ Having the 72Go of data into another archive database will have any benefit versus keeping the 72Go into the archive table of the master database ?

Thanks for your answers, Edouard

Upvotes: -1

Views: 874

Answers (1)

Rick James
Rick James

Reputation: 142296

The size of a table may or may not impact the performance of queries against that table. It depends on the query, innodb_buffer_pool_size and RAM size. Let's see some typical queries.

The existence of a big table that is not being used has no impact on queries against other tables.

It may or may not be wise to PARTITION BY RANGE TO_DAYS(...) and have monthly or yearly partitions. The main advantage is where you get around to purging old data, but you don't seem to need that.

If you do split into 72 + 8, I recommend copying the 8 from the 80 into a new table, then use RENAME TABLEs to juggle the table names.

Two TABLEs in one DATABASE is essentially the same as having the TABLEs in different DATABASEs.

I'll update this Answer when you have provided more details.

Upvotes: 1

Related Questions