Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute

On Thu, 2012-06-28 at 21:41 +0700, tuanhoanganh wrote:

Hello
I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit.
When i view Postgresql status, there are some autovaccum alway run.
Ex VACUUM ANALYZE pg_catalog.pg_attribute.

Is it problem of PostgreSQL? Please help me.

Do you have activity on the database? If so, autovacuum is normal.

Regards,
Jeff Davis

Yes I have more than 10 activity connect on the database.
But VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type,
pg_catalog.pg_depend run all time of day.

Is it normal in Postgres?

Tuan Hoang Anh

On Fri, Jun 29, 2012 at 1:34 AM, Jeff Davis <pgsql@j-davis.com> wrote:

tuanhoanganh <hatuan05@gmail.com> wrote:

VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type,
pg_catalog.pg_depend run all time of day.

Is it normal in Postgres?

Do you create and drop a lot of tables (including temporary tables)?

What are the results of running the query on this page?:

http://wiki.postgresql.org/wiki/Server_Configuration

How is overall performance on the machine?

-Kevin

Thanks for your help

I don't create and drop alot of tables.7
Here is my config
"version";"PostgreSQL 9.0.6, compiled by Visual C++ build 1500, 64-bit"
"autovacuum_analyze_threshold";"250"
"autovacuum_vacuum_threshold";"1000"
"bytea_output";"escape"
"checkpoint_segments";"64"
"client_encoding";"UNICODE"
"custom_variable_classes";"symmetric"
"effective_cache_size";"4GB"
"enable_seqscan";"off"
"extra_float_digits";"2"
"lc_collate";"English_United States.1252"
"lc_ctype";"English_United States.1252"
"listen_addresses";"*"
"log_destination";"stderr"
"log_line_prefix";"%t "
"logging_collector";"on"
"maintenance_work_mem";"1GB"
"max_connections";"100"
"max_locks_per_transaction";"2000"
"max_stack_depth";"2MB"
"max_wal_senders";"5"
"port";"5433"
"server_encoding";"UTF8"
"shared_buffers";"2GB"
"shared_preload_libraries";"$libdir/plugins/plugin_debugger.dll"
"TimeZone";"Asia/Bangkok"
"wal_buffers";"2MB"
"wal_keep_segments";"128"
"wal_level";"hot_standby"
"work_mem";"64MB"

Tuan Hoang Anh

On Fri, Jun 29, 2012 at 9:13 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov

[Please don't top-post. http://www.idallen.com/topposting.html ]

tuanhoanganh wrote:

Kevin Grittner > tuanhoanganh wrote:

VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type,
pg_catalog.pg_depend run all time of day.

What are the results of running the query on this page?:

http://wiki.postgresql.org/wiki/Server_Configuration

Here is my config
"version";"PostgreSQL 9.0.6, compiled by Visual C++ build 1500,
64-bit"

If you can, an update would be good.

http://www.postgresql.org/support/versioning/

That said, I don't see any particular bug fixes that would be
related.

"autovacuum_analyze_threshold";"250"
"autovacuum_vacuum_threshold";"1000"

My experience is that setting these smaller helps autovacuum keep
small tables from bloating, reducing the overall work done by
autovacuum and improving overall performance. This *might* be a
contributing factor to you issue.

"enable_seqscan";"off"

This is a very bad idea. A seqscan if often the fastest way to get
a set of data. If seqscans where being chosen when some other
technique actually was faster, you probably have a heavily cached
active data set, and would do much better to turn this back on and
set:

seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.03

I have found these settings to work well with my data and workloads,
you may need to make adjustments to find the best settings for your
environment.

Again, this doesn't seem related to your question, but if you are
cncerned about performance, it should help.

"shared_buffers";"2GB"

Benchmarks have shown that 16MB generally helps performance.

How is overall performance on the machine?

It would help to know whether the autovacuum processes you are seeing
are actually using a lot of CPU or disk IO. Do they reappear after a
restart? Are these processes involved in any blocking (ni sp_locks)?

-Kevin

"Kevin Grittner" wrote:

"shared_buffers";"2GB"

Benchmarks have shown that 16MB generally helps performance.

Yikes! Editing error there -- I meant to put that comment under the
wal_buffers setting.

-Kevin