About a month ago, I started receiving quite a few pgstat timeouts on my production database. PostgreSQL 9.0.3 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit I am not sure where to start looking for the cause. Database has been up since march without any restart db1# ps -aux | grep stat pgsql 1287 1.0 0.0 22832 4724 ?? Ss 11Mar11 4265:13.87 postgres: stats collector process (postgres) It appears that these timeouts are affecting the performance of the application – sometimes it hangs for a few seconds before data gets returned, which was not the case before this. 2011-12-22 03:45:04 MST WARNING: pgstat wait timeout 2011-12-22 03:45:04 MST WARNING: pgstat wait timeout 2011-12-22 03:45:04 MST WARNING: pgstat wait timeout 2011-12-22 03:45:04 MST WARNING: pgstat wait timeout 2011-12-22 03:45:41 MST WARNING: pgstat wait timeout 2011-12-22 03:45:41 MST WARNING: pgstat wait timeout 2011-12-22 03:45:41 MST WARNING: pgstat wait timeout 2011-12-22 03:46:18 MST WARNING: pgstat wait timeout 2011-12-22 03:46:18 MST WARNING: pgstat wait timeout 2011-12-22 03:47:32 MST WARNING: pgstat wait timeout 2011-12-22 03:47:32 MST WARNING: pgstat wait timeout 2011-12-22 03:47:32 MST WARNING: pgstat wait timeout 2011-12-22 03:48:09 MST WARNING: pgstat wait timeout 2011-12-22 03:48:09 MST WARNING: pgstat wait timeout 2011-12-22 03:48:09 MST WARNING: pgstat wait timeout 2011-12-22 03:49:26 MST WARNING: pgstat wait timeout Runtime statistics setting for the database are as follows: #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - #track_activities = on #track_counts = on #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) # On FreeBSD, this is a performance hog, so keep it off if you need speed update_process_title = off #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off |
