autovacuum can not remove dead tuples

Поиск
Список
Период
Сортировка
От 石勇虎
Тема autovacuum can not remove dead tuples
Дата
Msg-id aa51d463ae8f410a8d5c4eb120e9bdc1@pingan.com.cn
обсуждение исходный текст
Ответы Re: autovacuum can not remove dead tuples  (Sergei Kornilov <sk@zsrv.org>)
Re: autovacuum can not remove dead tuples  (Sergei Kornilov <sk@zsrv.org>)
Список pgsql-bugs
Hi,all    
  We meet a problem in postgreql 9.5.5.When we run autovacuum or vacuum manually,it always goes " dead row version
cannotbe removed yet".I think it is about Visable MAP,how can we skip the VM,and do the complete vacuum?
 

INFO:  "ela_elifeassist_product": found 0 removable, 187435 nonremovable row versions in 23981 out of 23981 pages
DETAIL:  185659 dead row versions cannot be removed yet.
There were 2993 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.14s/0.10u sec elapsed 0.25 sec.
INFO:  vacuuming "pg_toast.pg_toast_231114"
INFO:  index "pg_toast_231114_index" now contains 35 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_231114": found 0 removable, 35 nonremovable row versions in 6 out of 6 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
====== End: Tue Apr 3 17:23:31 CST 2018

[postgres:5543@ela] [04-03.17:27:09]=# select pid,state,substr(query,0,100) from pg_stat_activity where query ~
'ela_elifeassist_product';
 
  pid  | state  |                                               substr                                                
-------+--------+-----------------------------------------------------------------------------------------------------
 38726 | active | select pid,state,substr(query,0,100) from pg_stat_activity where query ~ 'ela_elifeassist_product' 
 10482 | idle   | SELECT                                                                                             +
       |        |     t.tablename,                                                                                   +
       |        |     indexname,                                                                                     +
       |        |     c.reltuples AS num_rows,                                                                       +
       |        |     pg_size_pretty(pg_relation_
 26023 | idle   | select * from pg_stat_activity where query like '%ela_elifeassist_product%';
 40180 | active | autovacuum: VACUUM public.ela_elifeassist_product
(4 rows)

Time: 2.919 ms
[postgres:5543@ela] [04-03.17:27:13]=# select a.locktype,a.pid,a.relation,a.mode,a.granted,b.relname from pg_locks
a,pg_classb where a.relation=b.oid and relname='ela_elifeassist_product';
 
 locktype |  pid  | relation |           mode           | granted |         relname         
----------+-------+----------+--------------------------+---------+-------------------------
 relation | 40180 |   231114 | ShareUpdateExclusiveLock | t       | ela_elifeassist_product
(1 row)

Time: 1.056 ms

[postgres:5543@ela] [04-03.17:27:57]=# select relname,relpages,relallvisible from pg_class where relname
='ela_elifeassist_product';
         relname         | relpages | relallvisible 
-------------------------+----------+---------------
 ela_elifeassist_product |    23981 |            18
(1 row)

Time: 8.679 ms


********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error
orare not the intended recipient, please immediately notify the sender and delete this message from your computer. Any
use,distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages
sentto and from us may be monitored to ensure compliance with internal policies and to protect our business. 
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed,
orcontain viruses. Anyone who communicates with us by email is taken to accept these risks. 

收发邮件者请注意:
本邮件含涉密信息,请保守秘密,若误收本邮件,请务必通知发送人并直接删去,不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。

********************************************************************************************************************************

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #15141: Faulty ISO 8601 parsing
Следующее
От: Rémi Aubel
Дата:
Сообщение: Re: BUG: Unable to bind a null value typed as a UUID in a PreparedStatement