Re: delete timing-feasible?

[Rick James]

Well, let's get some more details...

A MERGE table is often used so that you can DROP one of the tables rather than deleting from them. This makes, say, deleting all the records older than 30 days almost instantaneous.

But you seem to have some other rule for picking which sub-table a record is in. What is it? And the records to delete -- how are they determined?

What INDEXes do you have on it? This may lead to some performance tricks. What is the PRIMARY KEY? INT is one thing, VARCHAR(255) is yet another.

What is key_buffer_size set to? Hopefully about 20% of available RAM. This will be heavily used for caching the index blocks, which total several GB.

Your small db probably did most of the work in cache. Your big one is probably hitting the disk. The ration of 13:1 in speed diff is consistent with other case I have experienced when comparing disk-bound versus ram-based.

"Count the disk hits". In 6.5 minutes, with normal (non-RAID) drives, one can do about 40K disk hits in that time. Now let's count how many hits are needed to do the delete. I'm assuming the rows are randomly scattered.
* ~0 for non-leaf nodes of Index BTrees
* ~1 for leaf node of PRIMARY KEY index.
* ~1 PER secondary index. How many are there?
* ~1 for the data.

And each of the above is a read-modify-write, so (with one secondary key) we have about 3 blocks read and about 3 blocks written. 6 disk hits x 10K is close to what I estimated.

So, what can you do?
* Fewer indexes would help.
* Saving up the deletes and trying to bunch them around similar KEYs.
* Get RAID
* More RAM.
* Increase key_buffer_size (but don't starve the OS of caching of data blocks)
* Redesign the layout of the data.
* Reorg data so delete == drop sub-table