Re: Parsing VACUUM VERBOSE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 6/14/07, Guillaume Smet <guillaume.smet@xxxxxxxxx> wrote:
On 6/14/07, Y Sidhu <ysidhu@xxxxxxxxx> 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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux