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
