a8849516
2019-12-15 00:41
采纳率: 75%
浏览 328

关于PGSQL每天晚上12点15卡死的问题

之前服务器用 了10来天后 每天12点15就死机
我以为是服务器问题,就换了台服务器,10来天后又出现同样的问题,晚上12点15就准时卡死
重装PGSQL还原之前备份后不再卡死(10天后不敢保证,date还原后小了10G。原来是27G还原后变17G)
发现每天12点15出现这样的日志
(重装PGSQL后不卡死排除服务器问题。服务器配置是独立服务器16核16G,我这开多个JAVA采集。正常情况是CPU5%以内,内存使用8-10GB,句柄有点高到10W,线程2000,进程250,PGSQl连接150)

2019-12-15 00:15:39 HKT LOG:  checkpoints are occurring too frequently (21 seconds apart)
2019-12-15 00:15:39 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-15 00:15:54 HKT LOG:  checkpoints are occurring too frequently (15 seconds apart)
2019-12-15 00:15:54 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-15 00:16:09 HKT LOG:  checkpoints are occurring too frequently (15 seconds apart)
2019-12-15 00:16:09 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".



2019-12-14 00:15:22 HKT LOG:  checkpoints are occurring too frequently (22 seconds apart)
2019-12-14 00:15:22 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-14 00:15:38 HKT LOG:  checkpoints are occurring too frequently (16 seconds apart)
2019-12-14 00:15:38 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-14 00:15:51 HKT LOG:  checkpoints are occurring too frequently (13 seconds apart)
2019-12-14 00:15:51 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-14 00:23:26 HKT ERROR:  value too long for type character varying(50)
2019-12-13 00:15:07 HKT LOG:  checkpoints are occurring too frequently (7 seconds apart)
2019-12-13 00:15:07 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-13 00:15:18 HKT LOG:  checkpoints are occurring too frequently (11 seconds apart)
2019-12-13 00:15:18 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-13 00:15:34 HKT LOG:  checkpoints are occurring too frequently (16 seconds apart)
2019-12-13 00:15:34 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-13 00:15:40 HKT LOG:  checkpoints are occurring too frequently (6 seconds apart)
2019-12-13 00:15:40 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2019-12-13 00:16:06 HKT LOG:  checkpoints are occurring too frequently (26 seconds apart)
2019-12-13 00:16:06 HKT HINT:  Consider increasing the configuration parameter "checkpoint_segments".

下面是我的配置


#wal_level = minimal            # minimal, archive, or hot_standby
                    # (change requires restart)
#fsync = on             # turns forced synchronization on or off
#synchronous_commit = on        # synchronization level;
                    # off, local, remote_write, or on
#wal_sync_method = fsync        # the default is the first option
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
#full_page_writes = on          # recover from partial page writes
#wal_buffers = -1           # min 256kB, -1 sets based on shared_buffers
                    # (change requires restart)
#wal_writer_delay = 200ms       # 1-10000 milliseconds

#commit_delay = 0           # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

#checkpoint_segments = 32       # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 15min     # range 30s-1h
#checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s       # 0 disables

# - Archiving -

#archive_mode = off     # allows archiving to be done
                # (change requires restart)
#archive_command = ''       # command to use to archive a logfile segment
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0         # same scale as above
#cpu_tuple_cost = 0.01          # same scale as above
#cpu_index_tuple_cost = 0.005       # same scale as above
#cpu_operator_cost = 0.0025     # same scale as above
#effective_cache_size = 2GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5            # range 1-10
#geqo_pool_size = 0         # selects default based on effort
#geqo_generations = 0           # selects default based on effort
#geqo_selection_bias = 2.0      # range 1.5-2.0
#geqo_seed = 0.0            # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100    # range 1-10000
#constraint_exclusion = partition   # on, off, or partition
#cursor_tuple_fraction = 0.1        # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit
                    # JOIN clauses


#------

1:请问为什么每天晚上12点15会出现这样的日志(别的时段我查询了没有出现)

2:请大佬给我一个配置优化方案

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • threenewbee 2019-12-15 09:38
    最佳回答

    除了我之前回答的以外,检查对方服务器是不是在这个时间段比较缓慢,导致你的线程阻塞。

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题