MySQL Forums
Forum List  »  Partitioning

Re: IP vs Date Partition
Posted by: Rick James
Date: May 14, 2013 09:59PM

Are these the ONLY queries you use?...

INSERT one row ON DUPLICATE KEY UPDATE last_time=NOW(), ...;
SELECT ... WHERE ip = ...;
DELETE ... WHERE last_time < NOW() - INTERVAL 30 DAY;
(or variants of those)

I ask because we need to see ALL the queries before judging how partitioning will / won't work.

From what you have said, PARTITIONing is of no use. Not by `ip`, not by `last_time`, not by any other way.

I would
* Have a non-partitioned table with IP as the primary key.
* Write a "crawler" script to continually walk through the table, purging old entries.
* No other indexes are needed.
* I would use InnoDB so that the data is clustered on IP, thereby making the crawler more efficient.

This document discusses how to design the crawler:
http://mysql.rjweb.org/doc.php/deletebig
Your "chunks" would work off `ip`, not `id`.
When your crawler gets to the end of the table, it starts over.
I would tune the crawler (with sleeps) so that it takes a day to make a pass.

The goal of the crawler is to get the job done, but without having any impact on the rest of the application.

How big will the table become? Bigger than can be cached in RAM?

Options: ReplyQuote


Subject
Views
Written By
Posted
3187
May 13, 2013 10:28AM
Re: IP vs Date Partition
1857
May 14, 2013 09:59PM
1821
May 15, 2013 10:06AM


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.