vacuum_cost_delay & VACUUM holding locks on GIST indexes

Поиск
Список
Период
Сортировка
От Ron Mayer
Тема vacuum_cost_delay & VACUUM holding locks on GIST indexes
Дата
Msg-id Pine.LNX.4.58.0502281135400.13272@greenie.cheapcomplexdevices.com
обсуждение исходный текст
Ответы Re: vacuum_cost_delay & VACUUM holding locks on GIST indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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)

В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: PGconn
Следующее
От: Wes
Дата:
Сообщение: Vacuum time degrading