Dear all,

there is no OPTIMIZE, TRUNCATE, or VACUUM in MonetDB.

Deleted tuples are only marked as such, and thus ignored during query evaluation, but they do remain in the tables on disk. In highly dynamic environments with many tuples being deleted and new ones added, this means that tables do grow, and performance might degrade once tables become too large for the given system.

There a various ways to achieve a "clean-up" of deleted tuples, e.g.:

- dump, drop, and restore the entire database; cf., http://www.monetdb.org/Documentation/UserGuide/DumpRestore

- dump, delete and restore the respective table(s): DECLARE cnt BIGINT; SELECT count(*) INTO cnt FROM <table>; COPY SELECT * FROM <table> INTO <filename>; DELETE FROM <table>; -- as opposed to deletes with where clause, this unconditional delete of all tuples will also physically remove all tuples COPY cnt RECORDS INTO <table> FROM <filename>;

- copy, delete and restore the respective table(s): CREATE TEMPORARY TABLE <tmp_table> AS SELECT * FROM <table> WITH DATA; DELETE FROM <table>; INSERT INTO <table> SELECT * FROM <tmp_table>; DROP TABLE <tmp_table>;

Of course, things get more complicated in case there are foreign keys referencing the table(s) that are to be cleaned-up ...

Hope this help.

Best, Stefan

----- Original Message -----

(sorry my english)

i have the same bug in my script. I often run : CREATE TABLE new_table AS (SELECT * FROM old_table) with_data new_table is very fast

You can anticipate the problems with : SELECT * FROM storage() WHERE "table" IN ('old_table', 'new_table') . Look heapsize column heapsize is big for old_table (slow table), small for new table (speed table).

I have not yet found the OPTIMIZE command as in mysql

Pierre

-- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

De: "Edgar Mejia" omejiasq@gmail.com À: users-list@monetdb.org Envoyé: Mercredi 27 Novembre 2013 13:48:21 Objet: Table very slow

HI, i have one table as 15 millions of rows of all 2013. I all days delete the las 2 months and return to reload them again. Today is table is very slow, one select count takes more that 2 minutes, when it would normally to a few seconds. I migrate this information to a new table and this table if works fine and is very fast. What i need to do for to fix this and how i to fix this table slow?

Thansk

Edgar M

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list