From gauravmeena0708@gmail.com Wed May  1 00:43:20 2013
From: Gaurav Meena <gauravmeena0708@gmail.com>
To: users-list@monetdb.org
Subject: Fwd: How to get deleted space back from monetdb
Date: Tue, 30 Apr 2013 19:11:57 +0530
Message-ID:
 <CAEqZKg8Q6C5B4ZRvQXMupdLbvRdxk9-ATD1p+NNKvoOcTeZWjA@mail.gmail.com>
In-Reply-To:
 <CAEqZKg_ZK9=Qu72ug7TOvBD=veBEAMtgCm78cieGQypCVVJRng@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0076769143087837803=="

--===============0076769143087837803==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

I have searched entire archive, and this question have been asked many
times i.e. to get the space back from monetdb after deleting some rows in
monetdb.

The answer I found was not consistent
one of them was

COPY SELECT * FROM <table> INTO <file>;
DELETE FROM <table>;
COPY INTO <table> FROM <file>;

Which suggest that deleting rows (or cleaning??) table will get back the spac=
e.

One was from this question
http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html


1) copy all table contents to another table, drop old table, copy again


I have tried both deleting rows and then droping the table but the
result was inconsistant.
May be monetdb cleans up after some interval



Other Related Questions were
1. http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html

2. FEB 23, 2010

Reeves, Matthew J. wrote:
>* Hello,*>* *>*    I have a MonetDB database that contains a table of rollin=
g 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 aft=
er 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*>*

*


Gaurav Meena



--===============0076769143087837803==
Content-Type: text/html
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="attachment.html"
MIME-Version: 1.0

PGRpdiBkaXI9Imx0ciI+PGJyPjxkaXYgY2xhc3M9ImdtYWlsX3F1b3RlIj48YnI+PGJyPjxkaXYg
ZGlyPSJsdHIiPjxkaXY+PGRpdj5JIGhhdmUgc2VhcmNoZWQgZW50aXJlIGFyY2hpdmUsIGFuZCB0
aGlzIHF1ZXN0aW9uIGhhdmUgYmVlbiBhc2tlZCBtYW55IHRpbWVzIGkuZS4gdG8gZ2V0IHRoZSBz
cGFjZSBiYWNrIGZyb20gbW9uZXRkYiBhZnRlciBkZWxldGluZyBzb21lIHJvd3MgaW4gbW9uZXRk
Yi48YnI+Cgo8YnI+PC9kaXY+VGhlIGFuc3dlciBJIGZvdW5kIHdhcyBub3QgY29uc2lzdGVudDxi
cj4KPC9kaXY+b25lIG9mIHRoZW0gd2FzIDxicj48cHJlPkNPUFkgU0VMRUNUICogRlJPTSAmbHQ7
dGFibGUmZ3Q7IElOVE8gJmx0O2ZpbGUmZ3Q7OwpERUxFVEUgRlJPTSAmbHQ7dGFibGUmZ3Q7OwpD
T1BZIElOVE8gJmx0O3RhYmxlJmd0OyBGUk9NICZsdDtmaWxlJmd0Ozs8YnI+PGJyPjwvcHJlPjxw
cmU+V2hpY2ggc3VnZ2VzdCB0aGF0IGRlbGV0aW5nIHJvd3MgKG9yIGNsZWFuaW5nPz8pIHRhYmxl
IHdpbGwgZ2V0IGJhY2sgdGhlIHNwYWNlLjxicj48YnI+PC9wcmU+PHByZT5PbmUgd2FzIGZyb20g
dGhpcyBxdWVzdGlvbjxicj48YSBocmVmPSJodHRwOi8vbWFpbC5tb25ldGRiLm9yZy9waXBlcm1h
aWwvdXNlcnMtbGlzdC8yMDExLUp1bHkvMDA1MDM2Lmh0bWwiIHRhcmdldD0iX2JsYW5rIj5odHRw
Oi8vbWFpbC5tb25ldGRiLm9yZy9waXBlcm1haWwvdXNlcnMtbGlzdC8yMDExLUp1bHkvMDA1MDM2
Lmh0bWw8L2E+PGJyPgoKCjxicj4xKSBjb3B5IGFsbCB0YWJsZSBjb250ZW50cyB0byBhbm90aGVy
IHRhYmxlLCBkcm9wIG9sZCB0YWJsZSwgY29weSBhZ2Fpbjxicj48YnI+PGJyPjwvcHJlPjxwcmU+
SSBoYXZlIHRyaWVkIGJvdGggZGVsZXRpbmcgcm93cyBhbmQgdGhlbiBkcm9waW5nIHRoZSB0YWJs
ZSBidXQgdGhlIHJlc3VsdCB3YXMgaW5jb25zaXN0YW50LiA8YnI+TWF5IGJlIG1vbmV0ZGIgY2xl
YW5zIHVwIGFmdGVyIHNvbWUgaW50ZXJ2YWw8YnI+CgoKPGJyPjxicj48L3ByZT48cHJlPk90aGVy
IFJlbGF0ZWQgUXVlc3Rpb25zIHdlcmU8YnI+MS4gPGEgaHJlZj0iaHR0cDovL21haWwubW9uZXRk
Yi5vcmcvcGlwZXJtYWlsL3VzZXJzLWxpc3QvMjAxMC1NYXJjaC8wMDM5NTIuaHRtbCIgdGFyZ2V0
PSJfYmxhbmsiPmh0dHA6Ly9tYWlsLm1vbmV0ZGIub3JnL3BpcGVybWFpbC91c2Vycy1saXN0LzIw
MTAtTWFyY2gvMDAzOTUyLmh0bWw8L2E+PGJyPgoKMi4gPGZvbnQgc2l6ZT0iNiI+RkVCIDIzLCAy
MDEwPC9mb250Pjxicj4KUmVldmVzLCBNYXR0aGV3IEouIHdyb3RlOgomZ3Q7PGk+IEhlbGxvLAo8
L2k+Jmd0OzxpPiAKPC9pPiZndDs8aT4gICAgSSBoYXZlIGEgTW9uZXREQiBkYXRhYmFzZSB0aGF0
IGNvbnRhaW5zIGEgdGFibGUgb2Ygcm9sbGluZyBldmVudHMuCjwvaT4mZ3Q7PGk+IFRoZXJlIGFy
ZSAyMDAgbWlsbGlvbiByZWNvcmRzIHdpdGggNTAwLDAwMCBidWxrIGxvYWRlZCBlYWNoIGRheSB3
aXRoIHRoZQo8L2k+Jmd0OzxpPiBvbGRlc3QgNTAwLDAwMCBkZWxldGVkIGF0IG1pZG5pZ2h0LiBJ
JiMzOTttIGZpbmRpbmcgdGhlIGRhdGFiYXNlIGdldHMgc2xvd2VyCjwvaT4mZ3Q7PGk+IGFzIHRp
bWUgZ29lcyBvbi4gSXQgZ2V0cyB0byB0aGUgcG9pbnQgb2YgYmVpbmcgYWxtb3N0IHVudXNhYmxl
IGFmdGVyIGEKPC9pPiZndDs8aT4gd2Vlay4gSXMgdGhlcmUgc29tZXRoaW5nIEkgc2hvdWxkIGJl
IGRvaW5nIGRpZmZlcmVudD8KPC9pPkhlbGxvIE1hdHRoZXcsClRoYW5rcyBmb3IgdXNpbmcgTW9u
ZXREQi4gSW50ZXJlc3RpbmcgYXBwbGljYXRpb24gdGFyZ2V0LgoKVGhpcyBpcyBhIGtub3duIGlz
c3VlLiBUaGUgc3lzdGVtIGtlZXBzIHRoZSBkZWxldGVkIHR1cGxlcyBhbmQgcmVidWlsZHMgdGhl
IHRhYmxlLgpBIHZhY3V1bS1saWtlIGZlYXR1cmUgaXMgbmVlZGVkIGFuZCBvbiB0aGUgd2lzaGxp
c3QuIEZvciBub3csIHRha2luZyBhIGNvcHkKb2YgdGhlIHRhYmxlIHdvdWxkICYjMzk7c29sdmUm
IzM5OyB5b3VyIHByb2JsZW0uPGJyPqByZWdhcmRzLCBNYXJ0aW4KJmd0OzxpPiAKPC9pPiZndDs8
aT4gVGhhbmtzLAo8L2k+Jmd0OzxpPiBNYXR0CjwvaT4mZ3Q7PGk+IDxzcGFuIGNsYXNzPSJIT0Vu
WmIiPjxmb250IGNvbG9yPSIjODg4ODg4Ij48YnI+PGJyPjwvZm9udD48L3NwYW4+PC9pPjwvcHJl
PjxzcGFuIGNsYXNzPSJIT0VuWmIiPjxmb250IGNvbG9yPSIjODg4ODg4Ij48YnIgY2xlYXI9ImFs
bCI+PGRpdj48ZGl2PjxkaXY+PGRpdj48ZGl2IGRpcj0ibHRyIj5HYXVyYXYgTWVlbmE8YnI+PGJy
Pjxicj48L2Rpdj48L2Rpdj4KPC9kaXY+PC9kaXY+PC9kaXY+PC9mb250Pjwvc3Bhbj48L2Rpdj4K
PC9kaXY+PGJyPjwvZGl2Pgo=

--===============0076769143087837803==--


From sjoerd@acm.org Wed May  1 10:01:02 2013
From: Sjoerd Mullender <sjoerd@acm.org>
To: users-list@monetdb.org
Subject: Re: Fwd: How to get deleted space back from monetdb
Date: Wed, 01 May 2013 10:01:02 +0200
Message-ID: <5180CBBE.9090305@acm.org>
In-Reply-To:
 <CAEqZKg8Q6C5B4ZRvQXMupdLbvRdxk9-ATD1p+NNKvoOcTeZWjA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============3205337679101375648=="

--===============3205337679101375648==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The way MonetDB works with respect to updates to a table (deletes and
changes) is to remember what was changed.  The original tables are
unaffected when it comes to these types of changes (the tables are
effectively append-only).  In a separate structure we remember the
tuples that were deleted so that you don't see them in query results.

This means that the only way to get back the space that was (and still
is!) occupied by deleted or changed tuples is to re-create the table.
 This can be done by copying to a new table, deleting everything from
the old table (or drop and recreate the table) and then copying back.
 This is tough if other tables have foreign key constraints that refer
to the table (and dropping a table is not possible if a view or
function refers to the table).  It can also be done by dumping the
database and restoring it.

There is no vacuum command.  See
http://bugs.monetdb.org/show_bug.cgi?id=2541 .

On 2013-04-30 15:41, Gaurav Meena wrote:
> 
> 
> 
> I have searched entire archive, and this question have been asked
> many times i.e. to get the space back from monetdb after deleting
> some rows in monetdb.
> 
> The answer I found was not consistent one of them was
> 
> COPY SELECT * FROM <table> INTO <file>; DELETE FROM <table>; COPY
> INTO <table> FROM <file>;
> 
> Which suggest that deleting rows (or cleaning??) table will get
> back the space.
> 
> One was from this question 
> http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html
> 
> 
> 
> 
> 1) copy all table contents to another table, drop old table, copy
> again
> 
> 
> I have tried both deleting rows and then droping the table but the
> result was inconsistant. May be monetdb cleans up after some
> interval
> 
> 
> 
> 
> 
> Other Related Questions were 1.
> http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html
>
> 
> 
> 2. FEB 23, 2010
> 
> 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 />/
> 
> /
> 
> 
> Gaurav Meena
> 
> 
> 
> 
> 
> _______________________________________________ users-list mailing
> list users-list(a)monetdb.org 
> http://mail.monetdb.org/mailman/listinfo/users-list
> 


- -- 
Sjoerd Mullender
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.13 (GNU/Linux)
Comment: Using GnuPG with undefined - http://www.enigmail.net/

iQCVAwUBUYDLuT7g04AjvIQpAQLKUQP7B+aii9uW004mWTEDke1MylavsXNkTJeH
PXacUROHTxgijvWG23J2coSuhNsSkaw7H/KKQKqfizHBBZxygEqHdhvzzRuARWHR
A16bY7JYoXDZkgxUJrCBRtj6YHSwl604eUVi81DDVuOZl2Ygp68D3xKrAu3d70TS
9dVvsK0pdfg=
=OiAL
-----END PGP SIGNATURE-----

--===============3205337679101375648==--