Re: Efficient trimming of NDB??? - Adam Dixon - com.mysql.lists.cluster

3 messages in com.mysql.lists.clusterRe: Efficient trimming of NDB???

From	Sent On	Attachments
Kevin Burton	26 Oct 2006 15:08
Adam Dixon	26 Oct 2006 16:07
Kevin Burton	26 Oct 2006 17:32

Subject:	Re: Efficient trimming of NDB???
From:	Adam Dixon (adam...@gmail.com)
Date:	10/26/2006 04:07:58 PM
List:	com.mysql.lists.cluster

Hi Kevin,

Could you not just have the one table, insert as you will, and then nightly trim off (DELETE) the rows which are deemed now too old. I do this with my data, although I have a perl script that manually moves them to another db running MYISAM merge tables for storage and safe keeping. That way it keeps NDB simple, and I get to keep all the data.

The indexes after deleteing so many rows on NDB do get less than perfect, but I still get sub 200ms queries from a 26million row table. Rolling restarts of NDB refresh the indexes (but ive never actually needed to do this, as the system is lightning quick on the index hits)

Adam

On 10/27/06, Kevin Burton <bur...@tailrank.com> wrote:

We want to move to NDB for scalability reasons. NDB should allow us to do much faster INSERTS and not worry about slave replication lag which is really hurting us....

We generally aggregate our DB and then trim it after 7 days...

We were thinking of going with MyISAM and MERGE tables do to this. We'd create one dedicated MyISAM table per day and then alter the merge table to drop the oldest day and then drop that whole DB. This way we could trim the full dataset VERY quickly.

There really isn't any way to do this with NDB though. I mean DELETEing all the data would certainly be much faster but not as fast as just droping one MyISAM table.

If everything were in memory it probably would be VERY fast but I think we're going to use MySQL 5.1's disk-based support....

Thoughts?