Re: Need optimization suggestions (cleaning large table)

Ignoramus23984 <ignoramus23984@NOSPAM.23984.invalid> wrote:
>
> I have a large table, currently about 12 gigabytes. It is alled
> "bincache".

Hmm. I guess it is MyISAM?

> create table bincache (
> id VARCHAR(250) PRIMARY KEY,
> dataclass VARCHAR(32),
> lastdate DATETIME,
> lastused DATETIME,
> dataitem blob
> )
>
> It stores various expensive to obtain (compute or download)
> stuff. Every night I clean it by deleting data items that are over,
> say 2 weeks old (using lastdate field).

I'm quite sure that your datafile is fragmented. Could you post the
result of SHOW TABLE STATUS LIKE 'bincache'? Have a look at the
`Data_length` and `Data_free` columns. If the latter is big (bigger
than the first) your table is fragmented. To defragment it, you can
use OPTIMIZE TABLE.

> My issue is that delete takes too long. I would like to know if, say,
> creating index based on lastdate would alleviate the problem.

You say you DELETE daily, everything older than 2 weeks. That means
you delete 1/15th of your rows on average. Probably an index on
`lastdate` (IMHO `lastused` would be better) could speedup the
selection of those rows. You already have significant costs for
updating the primary key so another index would not be too bad.


I would suggest to delete less often, say once a week and use the
following algorithm:

1. create a second, identical table bincache_new
2. select all rows to be preserved from bincache and
insert them into bincache_new (use INSERT ... SELECT ... WHERE)
3. rename tables: bincache -> bincache_drop, bincache_new -> bincache
4. drop bincache_drop

That way you avoid fragmentation at all. Plus you get a nice clean
fresh index distribution every time.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/