[MonetDB-users] Slowdowns after Delete
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?
Thanks, Matt
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
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.
On Tue, Feb 23, 2010 at 1:47 PM, Martin Kersten Martin.Kersten@cwi.nlwrote:
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
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 Martin.Kersten@cwi.nlwrote:
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
In practice this takes a lot longer than a dump/reload. Overall this fits in with nightly maintenance that I have to run to keep Monet from experiencing odd behavior like dead-locks or segfault loops.
73, Matthew W. Jones (KI4ZIB) http://matburt.net
On Mon, Mar 1, 2010 at 11:08 AM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
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 <Martin.Kersten@cwi.nl 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/http://www.cwi.nl/%7Emanegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
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
participants (4)
-
Martin Kersten -
Matthew Jones -
Reeves, Matthew J. -
Stefan Manegold