Re: [MonetDB-users] Slowdowns after Delete

On Thu, Feb 25, 2010 at 12:42:15PM -0500, Matthew Jones wrote:

This has actually bitten me too, I have to perform a nightly operation of dumping the table, dropping it, re-create the table then batch load all of the data back into it which can take some time.

hm, IMHO, you can do all this in SQL inside the server, i.e., without dump/reload: sql>select * from t; +------+ | a | +======+ | 1 | | 2 | | 3 | +------+ 3 tuples sql>start transaction; auto commit mode: off sql>create temporary table tt as select * from t where a > 1 with data; sql>drop table t; operation successful sql>create table t as select * from tt with data; sql>drop table tt; operation successful sql>commit; auto commit mode: on sql>select * from t; +------+ | a | +======+ | 2 | | 3 | +------+ 2 tuples Right, it still needs to copy the data (even twice, since we don't support table renames (yet?), and unless we optimize the second copy away), but all inside the server. i.e., without serializing and re-parsing the data ... ... might be worth trying ... Stefan

On Tue, Feb 23, 2010 at 1:47 PM, Martin Kersten wrote:

...

Reeves, Matthew J. wrote:

...

Hello,

I have a MonetDB database that contains a table of rolling events. There are 200 million records with 500,000 bulk loaded each day with the oldest 500,000 deleted at midnight. I'm finding the database gets slower as time goes on. It gets to the point of being almost unusable after a week. Is there something I should be doing different? Hello Matthew, Thanks for using MonetDB. Interesting application target.

This is a known issue. The system keeps the deleted tuples and rebuilds the table. A vacuum-like feature is needed and on the wishlist. For now, taking a copy of the table would 'solve' your problem.

regards, Martin

...

Thanks, Matt

------------------------------------------------------------------------------

...

Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |