On Mon, Mar 1, 2010 at 11:08 AM, Stefan Manegold
<Stefan.Manegold@cwi.nl> wrote:
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/ |
| 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