Fwd: How to get deleted space back from monetdb
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
-----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@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-----
participants (2)
-
Gaurav Meena -
Sjoerd Mullender