[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: [GENERAL] vacuum_cost_delay & VACUUM holding locks on GIST indexes
From: Ron Mayer <rm_pg () cheapcomplexdevices ! com>
Date: 2005-02-28 23:00:53
Message-ID: Pine.LNX.4.58.0502281135400.13272 () greenie ! cheapcomplexdevices ! com
[Download RAW message or body]
When you VACUUM a table with postgis indexes (perhaps GIST indexes
in general?) it seems a lock is held on the table. Setting
vacuum_cost_delay seems to make vacuum hold this lock much longer.
Is this true?
If so, I assume that's not desirable behavior, right? It makes
autovacuum harder to use on tables that have these indexes.
Any clever workarounds?
Ron
fli-lin1 /home/pg> while (1)
while? echo " explain analyze SELECT * from lines2 WHERE the_geom && \
setSRID('BOX3D(-84.31043 30.44341,-84.2954 30.45372)'::BOX3D, -1 );" | psql fli fli | \
grep runtime while? sleep 5
while? end
Total runtime: 23.355 ms
Total runtime: 32.276 ms [ "vacuum verbose" starts ]
Total runtime: 36.080 ms
Total runtime: 28.373 ms
Total runtime: 114679.281 ms [ bad but not horrible]
Total runtime: 30.823 ms
[...]
Total runtime: 22.867 ms [ "set vacuum_cost_delay=20"]
Total runtime: 22.808 ms
Total runtime: 23.288 ms [ "vacuum vebose" again ]
Total runtime: 23.366 ms
[ dozens of lines ]
Total runtime: 23.337 ms
Total runtime: 764133.163 ms [ YIPES ]
Total runtime: 23.722 ms
fli=# select * from pg_locks;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+--------------------------+---------
36677268 | 17230 | | 29039 | AccessShareLock | t
36677268 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
36677268 | 17230 | | 29039 | AccessExclusiveLock | t
33620188 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
| | 66414 | 30758 | ExclusiveLock | t
36677268 | 17230 | | 30731 | AccessShareLock | f
33620188 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
36677269 | 17230 | | 29039 | ShareUpdateExclusiveLock | t
16839 | 17230 | | 30758 | AccessShareLock | t
| | 66412 | 30731 | ExclusiveLock | t
33620188 | 17230 | | 30731 | AccessShareLock | t
| | 66372 | 29039 | ExclusiveLock | t
(12 rows)
fli=#
fli=# select * from pg_class where oid=36677268;
relname | relnamespace | reltype | relowner | relam | relfilenode | \
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | \
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | \
relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | \
relacl
------------------+--------------+---------+----------+-------+-------------+--------- \
------+----------+------------+---------------+---------------+-------------+--------- \
----+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
tmp_lines2__gist | 2758256 | 0 | 100 | 783 | 36677268 | \
0 | 34623 | 6.1128e+06 | 0 | 0 | f | f \
| i | 1 | 0 | 0 | 0 | 0 | 0 | f \
| f | f | f | (1 row)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic