Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group