On 12 Sep 2016, at 09:14, Stefano Fioravanzo wrote:

Hello,

In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours.

Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast.

So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue?

If you only delete several tuples from a table, MonetDB never actually deletes them. A delta BAT is kept to denote the deleted tuples, and it’s temporarily merged with the base BAT for every query. We’re aware of its potential drawbacks.

Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue?

There is an unstable vacuum feature: create procedure vacuum(sys string, tab string) Alternatively, (which I think is more recommended), run a delete-all query "DELETE FROM <mytable>;” to clean up the left-overs before inserting/loading data. Jennie

Any suggestion would be much appreciated,

Thank you

Stefano

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list