Skip site navigation (1) Skip section navigation (2)

Re: Parsing VACUUM VERBOSE

From: "Y Sidhu" <ysidhu(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Parsing VACUUM VERBOSE
Date: 2007-06-14 21:34:13
Message-ID: b09064f30706141434t530844a3x5ad09a1f7ef2aac7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Craig A. JamesDate: 2007-06-14 23:12:58
Subject: Replication
Previous:From: Guillaume SmetDate: 2007-06-14 21:01:54
Subject: Re: Parsing VACUUM VERBOSE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group