From: Baptiste LHOSTE <blhoste@alaloop.com>
To: pgsql-admin@postgresql.org
Cc: cedric@2ndquadrant.com, Kevin Grittner <kgrittn@mail.com>
Subject: Re: Autoanalyze of the autovacuum daemon ...
Date: Fri, 9 Nov 2012 16:35:23 +0100 (CET)
Message-ID: <1726621870.10089981.1352475323495.JavaMail.root@alaloop.com> (raw)
In-Reply-To: <20121109143139.77870@gmx.com>
Hi,
> That depends on configuration settings and on whether the computer
> (or VM) is so swamped that the autovacuum task is starved for cycles.
> Also on any overrides of statistics targets for those tables.
> Please show us the output from running this query:
> http://wiki.postgresql.org/wiki/Server_Configuration
name | current_setting
-----------------------------+------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
autovacuum | on
autovacuum_naptime | 15s
checkpoint_segments | 80
constraint_exclusion | partition
custom_variable_classes | alaloop
effective_cache_size | 14GB
external_pid_file | /var/run/postgresql/8.4-main.pid
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_autovacuum_min_duration | 0
log_line_prefix | %t
maintenance_work_mem | 1GB
max_connections | 100
max_locks_per_transaction | 256
max_prepared_transactions | 150
max_stack_depth | 5MB
port | 5432
server_encoding | UTF8
shared_buffers | 756MB
ssl | on
temp_buffers | 1536
TimeZone | localtime
unix_socket_directory | /var/run/postgresql
wal_buffers | 1MB
work_mem | 756MB
> Have you overridden any statistics targets?
We have put some specific values to autovacuum parameters for tables which take so much time to be analyzed. To do that we used following kind of query :
ALTER TABLE tablename SET (autovacuum_vacuum_threshold=20, autovacuum_vacuum_scale_factor=0.01, autovacuum_analyze_threshold=10, autovacuum_analyze_scale_factor=0.005, autovacuum_vacuum_cost_delay=10, autovacuum_vacuum_cost_limit=1000);
These tables have two timestamp columns and a btree index on both timestamp column.
Will it be more efficient for us to configure the autovacuum daemon analyze task only on those columns ? If yes, how can we do that ?
> A description of the environment would also be good. Hardware? Load?
Debian 6 64bits
8 Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
16 Go RAM - 4 Go Swap
4 * 300 Go Raid 0
Regards, Baptiste.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: blhoste@alaloop.com, kgrittn@mail.com
Subject: Re: Autoanalyze of the autovacuum daemon ...
In-Reply-To: <1726621870.10089981.1352475323495.JavaMail.root@alaloop.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox