freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?
Date: 2008-06-27 11:16:22
Message-ID: 20080627111622.GA17406@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi
first of all - i know next to nothing about bsd.

friend asked me to setup replication on their machines, and i noticed
that one of cpus (2 quad xeons) is used to 100%:
pgsql 58241 99.0 0.2 22456 7432 ?? Rs Thu10AM 1530:35.93 postgres: stats collector process (postgres)

what might be the reason?

here are settings:
name | setting
---------------------------------+---------------------------------------
add_missing_from | off
allow_system_table_mods | off
archive_command | (disabled)
archive_mode | off
archive_timeout | 0
array_nulls | on
authentication_timeout | 60
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 20
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
backslash_quote | safe_encoding
bgwriter_delay | 200
bgwriter_lru_maxpages | 100
bgwriter_lru_multiplier | 2
block_size | 8192
bonjour_name |
check_function_bodies | on
checkpoint_completion_target | 0.5
checkpoint_segments | 3
checkpoint_timeout | 300
checkpoint_warning | 30
client_encoding | UTF8
client_min_messages | notice
commit_delay | 0
commit_siblings | 5
config_file | /usr/local/pgsql/data/postgresql.conf
constraint_exclusion | off
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
custom_variable_classes |
data_directory | /usr/local/pgsql/data
DateStyle | ISO, YMD
db_user_namespace | off
deadlock_timeout | 1000
debug_assertions | off
debug_pretty_print | off
debug_print_parse | off
debug_print_plan | off
debug_print_rewritten | off
default_statistics_target | 10
default_tablespace |
default_text_search_config | pg_catalog.simple
default_transaction_isolation | read committed
default_transaction_read_only | off
default_with_oids | off

dynamic_library_path | $libdir
effective_cache_size | 16384
enable_bitmapscan | on
enable_hashagg | on
enable_hashjoin | on
enable_indexscan | on
enable_mergejoin | on
enable_nestloop | on
enable_seqscan | on
enable_sort | on
enable_tidscan | on
escape_string_warning | on
explain_pretty_print | on
external_pid_file |
extra_float_digits | 0
from_collapse_limit | 8
fsync | on
full_page_writes | on
geqo | on
geqo_effort | 5
geqo_generations | 0
geqo_pool_size | 0
geqo_selection_bias | 2
geqo_threshold | 12
gin_fuzzy_search_limit | 0
hba_file | /usr/local/pgsql/data/pg_hba.conf
ident_file | /usr/local/pgsql/data/pg_ident.conf
ignore_system_indexes | off
integer_datetimes | off
join_collapse_limit | 8
krb_caseins_users | off
krb_realm |
krb_server_hostname |
krb_server_keyfile |
krb_srvname | postgres
lc_collate | C
lc_ctype | pl_PL.UTF-8
lc_messages | pl_PL.UTF-8
lc_monetary | pl_PL.UTF-8
lc_numeric | pl_PL.UTF-8
lc_time | pl_PL.UTF-8
listen_addresses | 192.168.0.160
local_preload_libraries |
log_autovacuum_min_duration | -1
log_checkpoints | off
log_connections | off
log_destination | syslog
log_directory | pg_log
log_disconnections | off
log_duration | off
log_error_verbosity | default
log_executor_stats | off
log_filename | postgresql-%Y-%m-%d_%H%M%S.log
log_hostname | off
log_line_prefix |
log_lock_waits | off
log_min_duration_statement | -1
log_min_error_statement | error
log_min_messages | notice
log_parser_stats | off
log_planner_stats | off
log_rotation_age | 1440
log_rotation_size | 10240
log_statement | none
log_statement_stats | off
log_temp_files | -1
log_timezone | Poland
log_truncate_on_rotation | off
logging_collector | off
maintenance_work_mem | 16384
max_connections | 180
max_files_per_process | 1000
max_fsm_pages | 204800
max_fsm_relations | 1000
max_function_args | 100
max_identifier_length | 63
max_index_keys | 32
max_locks_per_transaction | 64
max_prepared_transactions | 5
max_stack_depth | 2048
password_encryption | on
port | 5432
post_auth_delay | 0
pre_auth_delay | 0
random_page_cost | 4
regex_flavor | advanced
search_path | "$user",public
seq_page_cost | 1
server_encoding | UTF8
server_version | 8.3.3
server_version_num | 80303
session_replication_role | origin
shared_buffers | 4096
shared_preload_libraries |
silent_mode | on
sql_inheritance | on
ssl | off
ssl_ciphers | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH
standard_conforming_strings | off
statement_timeout | 0
superuser_reserved_connections | 3
synchronize_seqscans | on
synchronous_commit | on
syslog_facility | LOCAL0
syslog_ident | postgres
tcp_keepalives_count | 0
tcp_keepalives_idle | 0
tcp_keepalives_interval | 0
temp_buffers | 1024
temp_tablespaces |
TimeZone | Poland
timezone_abbreviations | Default
trace_notify | off
trace_sort | off
track_activities | on
track_counts | on
transaction_isolation | read committed
transaction_read_only | off
transform_null_equals | off
unix_socket_directory |
unix_socket_group |
unix_socket_permissions | 511
update_process_title | off
vacuum_cost_delay | 0
vacuum_cost_limit | 200
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
vacuum_freeze_min_age | 100000000
wal_buffers | 8
wal_sync_method | fsync
wal_writer_delay | 200
work_mem | 1024
xmlbinary | base64
xmloption | content
zero_damaged_pages | off
(189 rows)

any ideas?

Best regards,

depesz

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Nagy 2008-06-27 14:03:30 Re: Unreferenced temp tables disables vacuum to update xid
Previous Message Pavel Stehule 2008-06-27 08:33:07 Re: Problem with FOUND