于 2013/9/15 1:06, Kevin Grittner 写道:
Rural Hunter <ruralhunter@xxxxxxxxx> wrote:
Why in the world would you want to use VACUUM FULL in this circumstance?
the db name in the error message wrong?
I just googled around and found the solution. What's the other option?
There are two possibilities -- either you had a long-running
transaction in the cluster or your autovacuum is not configured to
be aggressive enough to keep you out of trouble.
I checked the running transactions before I stopped the instance. There
was no long running transaction exception one auto-vacuum running for
about 1 hour.
We would need a description of the machine (cores, RAM, storage
system) and the output of these queries to be able to make good
suggestions on tuning autovacuum:
SELECT version();
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
I'm on Ubuntu 12.04.1 64bit with 32 cores and 377G memory. The data is
stored on several rai10 SAS 15k disks.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)
postgres=# SELECT name, current_setting(name), source
postgres-# FROM pg_settings
postgres-# WHERE source NOT IN ('default', 'override');
name |
current_setting | source
-----------------------------+------------------------------------------------------------------------------------+----------------------
application_name | psql.bin | client
archive_command | test ! -f /dbbk/postgres/logarch/%f.gz
&& gzip -c %p >/dbbk/postgres/logarch/%f.gz | configuration file
archive_mode | on | configuration file
autovacuum | on | configuration file
autovacuum_freeze_max_age | 2000000000 | configuration file
checkpoint_segments | 20 | configuration file
client_encoding | UTF8 | client
DateStyle | ISO, YMD | configuration file
default_text_search_config | chinesecfg | configuration file
effective_cache_size | 100GB | configuration file
full_page_writes | off | configuration file
lc_messages | zh_CN.utf8 | configuration file
lc_monetary | zh_CN.utf8 | configuration file
lc_numeric | zh_CN.utf8 | configuration file
lc_time | zh_CN.utf8 | configuration file
listen_addresses | * | configuration file
log_autovacuum_min_duration | 30min | configuration file
log_destination | stderr | configuration file
log_line_prefix | %t [%p]: [%l-1]
user=%u,db=%d,host=%h |
configuration file
log_min_duration_statement | 10s | configuration file
log_statement | ddl | configuration file
log_timezone | PRC | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 20GB | configuration file
max_connections | 2500 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_senders | 1 | configuration file
port | 3500 | configuration file
shared_buffers | 16GB | configuration file
synchronous_commit | off | configuration file
TimeZone | PRC | configuration file
track_activities | on | configuration file
track_counts | on | configuration file
vacuum_freeze_table_age | 1000000000 | configuration file
wal_buffers | 16MB | configuration file
wal_level | hot_standby | configuration file
work_mem | 8MB | configuration file
(37 rows)
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
