Обсуждение: VACUUM ANALYZE block the whole database
hi guys,
we have database containing 3 three tables.
1. some static tables with 300 records.
2. vbv
3. vbv_denorm
4. summary
here is how stuff works:
1. one process is receiving data from our sensor network and inserts it to vbv
2. there is trigger on vbv which add the new records with some other information into vbv_denorm --> we use this for our reporting software
3. there is trigger on vbv_denorm which aggregates the data in vbv_denorm and inserts into summary
4. another process is sending summary records to other company in periodic bases. it sends records one by one. (I me it opens a cursor and send one record. after confirmation sends another record)
this operation runs 24*7.
because the database is growing too much(beyond tra byte in less than 6 months) we need to delete the oldest data. in each delete we remove about 80 million records from vbv(there is a trigger to delete the records from vbv_denorm and summary). after delete autovacuum starts on vbv. at the same time because summary is small (at maximum 2 million records) I ran VACUUM ANALYZE on it(it is not VACUUM FULL). but this cause the database to completely block. the process inserting to vbv blocks. the process sending summary records blocks. Why this is happening? autovacuum and VACUUM ANALYZE should not lock tables.
Majid Azimi <majid.merkava@gmail.com> wrote: > ran VACUUM ANALYZE on it(it is not VACUUM FULL). but this cause > the database to completely block. Please show the results from running the query here: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin
On Sun, Jul 29, 2012 at 11:42 PM, Majid Azimi <majid.merkava@gmail.com> wrote: > because the database is growing too much(beyond tra byte in less than 6 > months) we need to delete the oldest data. in each delete we remove about 80 > million records from vbv(there is a trigger to delete the records from > vbv_denorm and summary). after delete autovacuum starts on vbv. at the same > time because summary is small (at maximum 2 million records) I ran VACUUM > ANALYZE on it(it is not VACUUM FULL). but this cause the database to > completely block. the process inserting to vbv blocks. the process sending > summary records blocks. Why this is happening? autovacuum and VACUUM ANALYZE > should not lock tables. Vacuum by itself does not block read write to tables, however DDL can block behind it, and then read and writing to tables can block behind that DDL. When this is happening, do the queries on this page: http://wiki.postgresql.org/wiki/Lock_Monitoring tell you anything useful?