MySQL Forums
Forum List  »  Performance

Re: Number of table Partitions
Posted by: Rick James
Date: July 24, 2015 03:14PM

Here's my "analysis" of your unusual situation. But first, let me paraphrase what you are doing --

* Data is flowing into the table all the time.
* At (say) midnight, a length process is started, it does:
* Summarize yesterday's data (while ignoring today's)
* Then (possibly hours later), deleted yesterday's data.

That is, the table is never empty.

You may have a good use case for 2 partitions.

I have a similar technique in
"Using a Staging Table for Efficient MySQL Data Warehouse Ingestion" --
http://mysql.rjweb.org/doc.php/staging_table
It uses 2 _tables_ instead of 2 _partitions_. It avoids any "downtime" by doing an atomic RENAME TABLE to ping-pong the two tables.

There is probably little or no difference in performance, assuming one more thing...

Make it 3 partitions:
Yesterday, Today, and Future.
The goal is to
* Summarize Yesterday, then drop it.
* Load into Today
* `Future` is kept empty, and use REORGANIZE PARTITION to split Future into a new Future, plus a new Today. More discussion of "future" is in
http://mysql.rjweb.org/doc.php/partitionmaint

Keep in mind that most partition operations take an amount of time proportional to the number of rows that need to be shoveled around. I assume you want next to zero downtime for the summarization and partition reorganization.

(Yes, there may be some other way to do the partition maintenance for your 2-partition use case.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Number of table Partitions
2448
July 24, 2015 03:14PM
1240
July 31, 2015 02:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.