Re: Parsing VACUUM VERBOSE

On 6/14/07, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com> wrote:
>
> On 6/14/07, Y Sidhu <ysidhu(at)gmail(dot)com> wrote:
> > Can anyone share what value they have set log_min_duration_statement to?
>
> It's OT but we use different values for different databases and needs.
>
> On a very loaded database with a lot of complex queries (lots of join
> on big tables, proximity queries, full text queries), we use 100 ms.
> It logs ~ 300 000 queries. It allows us to detect big regressions or
> new queries which are very slow.
>
> On another database where I want to track transaction leaks, I'm
> forced to put it to 0ms.
>
> Basically, the answer is: set it to the lowest value you can afford
> without impacting too much your performances (and if you use syslog,
> use async I/O or send your log to the network).
>
> --
> Guillaume
>

I am trying to answer the question of how to tell if the cleanup of an index
may be locked by a long transaction. And in the bigger context, why vacuums
are taking long? What triggers them? I came across the following query which
shows one table 'connect_tbl' with high "heap hits" and "low heap buffer %"
Now, 'heap' seems to be a memory construct. Any light shedding is
appreciated.

mydb=# SELECT
mydb-# 'HEAP:'||relname AS table_name,
mydb-# (heap_blks_read+heap_blks_hit) AS heap_hits,
ROUND(((heap_blks_hit)::NUMERIC/(heap_blks_read+heap_blks_hit)*100),
2)
mydb-# ROUND(((heap_blks_hit)::NUMERIC/(heap_blks_read+heap_blks_hit)*100),
2)
mydb-# AS heap_buffer_percentage
mydb-# FROM pg_statio_user_tables
mydb-# WHERE(heap_blks_read+heap_blks_hit)>0
mydb-# UNION
mydb-# SELECT
mydb-# 'TOAST:'||relname,
mydb-# (toast_blks_read+toast_blks_hit),
mydb-#
ROUND(((toast_blks_hit)::NUMERIC/(toast_blks_read+toast_blks_hit)*100), 2)
mydb-# FROM pg_statio_user_tables
mydb-# WHERE(toast_blks_read+toast_blks_hit)>0
mydb-# UNION
mydb-# SELECT
mydb-# 'INDEX:'||relname,
mydb-# (idx_blks_read+idx_blks_hit),
mydb-# ROUND(((idx_blks_hit)::NUMERIC/(idx_blks_read+idx_blks_hit)*100), 2)
mydb-# FROM pg_statio_user_tables
mydb-# WHERE(idx_blks_read+idx_blks_hit)>0;
table_name | heap_hits | heap_buffer_percentage
------------------------------------+--------------+----------------------------------
HEAP:connect_tbl | 890878 | 43.18
HEAP:tblbound_tbl | 43123 | 13.80
HEAP:tblcruel_tbl | 225819 | 6.98
INDEX:connect_tbl | 287224 | 79.82
INDEX:tblbound_tbl | 81640 | 90.28
INDEX:tblcruel_tbl | 253014 | 50.73

--
Yudhvir Singh Sidhu
408 375 3134 cell