Re: Thoughts on partitioning by hour - and which partition type?

[Christian Cunlif]

Hi All,

I'm running into exactly the same situation as Adrian and Harsh... To add my 2 cents to the discussion:
- We've read the same article than Harsh about MyIsam being said as more feeted than InnoDB.
- But as with Adrian, we had many DB writers.
- Volumetry and need of history are totally equivalent with Adrian's.

What we did as a first step to improve things is to create a centralized daemon to write to the DB. So from a situation with "many DB writers", we've now a single writer which expose a public synchronized queue, into which the ex-writers enqeue their record. The daemon then insert these records by batch (for further speed). Hence we managed to use MyIsam.

Now, we're going to experiment partitioning, and we're even thinkin of using the Archive DB rather than MyIsam...

I would be glad to get your advice on what we should take care of, or event if you have script or anything to share - that would be welcome!

Adrian, more specifically, if I understand well, you move at the end of day the data from partitions 0-23 to 'previous day' partition, right? How do you do it (what kind of script or SP)? How long does it take and what do you do with new incoming data during this period (ie, isn't the DB unavailable)?

Cheers,
Chris
Aulofee.com



Edited 1 time(s). Last edit at 08/10/2008 12:48PM by Christian Cunlif.