Параметры сервера:
Процессор Intel Xeon E5320 1.86 ГГц
RAM: DDR2 6Gb ECC
2xRAID 0: 64.4Gb SAS
Конфиг постгри
Код: Выделить всё
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 550MB # min 128kB or max_connections*16kB
temp_buffers = 16MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 256MB # min 64kB
maintenance_work_mem = 1024MB # min 1MB
max_stack_depth = 2MB # min 100kB
# - Free Space Map -
#max_fsm_pages = 504800 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 5000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
vacuum_cost_delay = 100 # 0-1000 milliseconds
vacuum_cost_page_hit = 4 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
fsync = off # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
full_page_writes = off # recover from partial page writes
wal_buffers = 2048kB # min 32kB
#wal_writer_delay = 200ms # 1-10000 milliseconds
commit_delay = 1000 # range 0-100000, in microseconds
commit_siblings = 3 # range 1-1000
# - Checkpoints -
checkpoint_segments = 15 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 is off
# - Archiving -
#archive_mode = off # allows archiving to be done
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
enable_nestloop = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
seq_page_cost = 3.0 # measured on an arbitrary scale
random_page_cost = 3.0 # same scale as above
cpu_tuple_cost = 0.005 # 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 = 400MB
# - 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
# - Other Planner Options -
default_statistics_target = 100 # range 1-1000
constraint_exclusion = on
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/pgsql/log/' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
# syslog_facility = 'LOCAL0'
# syslog_ident = 'postgres'
# - When to Log -
client_min_messages = notice # values in order of decreasing detail:
log_min_messages = panic # values in order of decreasing detail:
log_error_verbosity = 'default' # terse, default, or verbose messages
log_min_error_statement = panic
log_min_duration_statement = 0
#silent_mode = off
# - What to Log -
debug_print_parse = off
debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
debug_pretty_print = off
log_checkpoints = on
log_checkpoints = off
log_connections = off
#log_disconnections = off
log_duration = off
log_hostname = off
log_line_prefix = '%d-%t-%c'
log_lock_waits = on # log lock waits >= deadlock_timeout
log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
#log_timezone = unknown # actually, defaults to TZ environment
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#update_process_title = on
# - Statistics Monitoring -
#log_parser_stats = on
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = on
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 4 # max number of autovacuum subprocesses
autovacuum_naptime = 5min # time between autovacuum runs
autovacuum_vacuum_threshold = 200 # min number of row updates before
autovacuum_analyze_threshold = 50 # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = ''
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#session_replication_role = 'origin'
#statement_timeout = 0 # 0 is disabled
#vacuum_freeze_min_age = 100000000
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, dmy'
#timezone = unknown # actually, defaults to TZ environment
#timezone_abbreviations = 'Default'
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
lc_messages = 'ru_RU.UTF-8' # locale for system error message
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.russian'
# - Other Defaults -
#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
deadlock_timeout = 2s
max_locks_per_transaction =200 # min 10
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#add_missing_from = off
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
default_with_oids = on
escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off
#synchronize_seqscans = on
# stats_row_level = off
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
НО как только бухгалтерия запускает перепроведение документов ЛА подскакивает до 17 и выше вешая сервак, опосля чего в /var/log/messages вываливается следующее
Код: Выделить всё
Mar 5 11:08:01 dbnew kernel: postmaster invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0
Mar 5 11:08:25 dbnew kernel:
Mar 5 11:08:25 dbnew kernel: Call Trace:
Mar 5 11:08:27 dbnew kernel: [<ffffffff800c9d6f>] out_of_memory+0x8e/0x2f3
Mar 5 11:08:27 dbnew kernel: [<ffffffff8002dfc7>] __wake_up+0x38/0x4f
Mar 5 11:08:27 dbnew kernel: [<ffffffff8000f677>] __alloc_pages+0x27f/0x308
Mar 5 11:08:27 dbnew kernel: [<ffffffff80013034>] __do_page_cache_readahead+0x96/0x17b
Mar 5 11:08:27 dbnew kernel: [<ffffffff80013971>] filemap_nopage+0x14c/0x360
Mar 5 11:08:27 dbnew kernel: [<ffffffff8000896c>] __handle_mm_fault+0x1fd/0x103b
Mar 5 11:08:27 dbnew kernel: [<ffffffff800671f2>] do_page_fault+0x499/0x842
Mar 5 11:08:27 dbnew kernel: [<ffffffff80028f64>] do_sigaction+0x76/0x199
Mar 5 11:08:27 dbnew kernel: [<ffffffff8005dde9>] error_exit+0x0/0x84
Mar 5 11:08:27 dbnew kernel:
Mar 5 11:08:27 dbnew kernel: Mem-info:
Mar 5 11:08:27 dbnew kernel: Node 0 DMA per-cpu:
Mar 5 11:08:27 dbnew kernel: cpu 0 hot: high 0, batch 1 used:0
Mar 5 11:08:27 dbnew kernel: cpu 0 cold: high 0, batch 1 used:0
Mar 5 11:08:29 dbnew kernel: cpu 1 hot: high 0, batch 1 used:0
Mar 5 11:08:32 dbnew kernel: cpu 1 cold: high 0, batch 1 used:0
Mar 5 11:08:34 dbnew kernel: cpu 2 hot: high 0, batch 1 used:0
Mar 5 11:08:34 dbnew kernel: cpu 2 cold: high 0, batch 1 used:0
Mar 5 11:08:34 dbnew kernel: cpu 3 hot: high 0, batch 1 used:0
Mar 5 11:08:34 dbnew kernel: cpu 3 cold: high 0, batch 1 used:0
Mar 5 11:08:34 dbnew kernel: Node 0 DMA32 per-cpu:
Mar 5 11:08:34 dbnew kernel: cpu 0 hot: high 186, batch 31 used:111
Mar 5 11:08:34 dbnew kernel: cpu 0 cold: high 62, batch 15 used:50
Mar 5 11:08:34 dbnew kernel: cpu 1 hot: high 186, batch 31 used:65
Mar 5 11:08:34 dbnew kernel: cpu 1 cold: high 62, batch 15 used:50
Mar 5 11:08:35 dbnew kernel: cpu 2 hot: high 186, batch 31 used:150
Mar 5 11:08:35 dbnew kernel: cpu 2 cold: high 62, batch 15 used:17
Mar 5 11:08:35 dbnew kernel: cpu 3 hot: high 186, batch 31 used:142
Mar 5 11:08:36 dbnew kernel: cpu 3 cold: high 62, batch 15 used:51
Mar 5 11:09:18 dbnew kernel: Node 0 Normal per-cpu:
Mar 5 11:09:19 dbnew kernel: cpu 0 hot: high 186, batch 31 used:178
Mar 5 11:09:19 dbnew kernel: cpu 0 cold: high 62, batch 15 used:44
Mar 5 11:09:19 dbnew kernel: cpu 1 hot: high 186, batch 31 used:148
Mar 5 11:09:19 dbnew kernel: cpu 1 cold: high 62, batch 15 used:48
Mar 5 11:09:19 dbnew kernel: cpu 2 hot: high 186, batch 31 used:139
Mar 5 11:09:19 dbnew kernel: cpu 2 cold: high 62, batch 15 used:27
Mar 5 11:09:19 dbnew kernel: cpu 3 hot: high 186, batch 31 used:176
Mar 5 11:09:19 dbnew kernel: cpu 3 cold: high 62, batch 15 used:56
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem per-cpu: empty
Mar 5 11:09:19 dbnew kernel: Free pages: 32048kB (0kB HighMem)
Mar 5 11:09:19 dbnew kernel: Active:1416700 inactive:80261 dirty:0 writeback:0 unstable:0 free:8012 slab:5553 mapped-file:25 mapped-anon:1352598 pagetables:8397
Mar 5 11:09:19 dbnew kernel: Node 0 DMA free:10096kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:9676kB pages_scanned:0 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 3000 6030 6030
Mar 5 11:09:19 dbnew kernel: Node 0 DMA32 free:17020kB min:4936kB low:6168kB high:7404kB active:3015704kB inactive:0kB present:3072012kB pages_scanned:5363065 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 3030 3030
Mar 5 11:09:19 dbnew kernel: Node 0 Normal free:4932kB min:4988kB low:6232kB high:7480kB active:2652580kB inactive:319492kB present:3102720kB pages_scanned:6594480 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 0 0
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 0 0
Mar 5 11:09:19 dbnew kernel: Node 0 DMA: 4*4kB 6*8kB 3*16kB 4*32kB 6*64kB 2*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 2*4096kB = 10096kB
Mar 5 11:09:19 dbnew kernel: Node 0 DMA32: 7*4kB 8*8kB 0*16kB 1*32kB 0*64kB 0*128kB 0*256kB 1*512kB 0*1024kB 0*2048kB 4*4096kB = 17020kB
Mar 5 11:09:19 dbnew kernel: Node 0 Normal: 1*4kB 0*8kB 4*16kB 0*32kB 0*64kB 0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 1*4096kB = 4932kB
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem: empty
Mar 5 11:09:19 dbnew kernel: 145378 pagecache pages
Mar 5 11:09:19 dbnew kernel: Swap cache: add 0, delete 0, find 0/0, race 0+0
Mar 5 11:09:19 dbnew kernel: Free swap = 0kB
Mar 5 11:09:19 dbnew kernel: Total swap = 0kB
Mar 5 11:09:19 dbnew kernel: Free swap: 0kB
Mar 5 11:09:19 dbnew kernel: 1835008 pages of RAM
Mar 5 11:09:19 dbnew kernel: 308543 reserved pages
Mar 5 11:09:19 dbnew kernel: 3658 pages shared
Mar 5 11:09:19 dbnew kernel: 0 pages swap cached
Mar 5 11:09:19 dbnew kernel: Out of memory: Killed process 3027, UID 26, (postmaster).
Mar 5 11:09:19 dbnew kernel: postmaster invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0
Mar 5 11:09:19 dbnew kernel:
Mar 5 11:09:19 dbnew kernel: Call Trace:
Mar 5 11:09:19 dbnew kernel: [<ffffffff800c9d6f>] out_of_memory+0x8e/0x2f3
Mar 5 11:09:19 dbnew kernel: [<ffffffff8002dfc7>] __wake_up+0x38/0x4f
Mar 5 11:09:19 dbnew kernel: [<ffffffff8000f677>] __alloc_pages+0x27f/0x308
Mar 5 11:09:19 dbnew kernel: [<ffffffff80013034>] __do_page_cache_readahead+0x96/0x17b
Mar 5 11:09:19 dbnew kernel: [<ffffffff80013971>] filemap_nopage+0x14c/0x360
Mar 5 11:09:19 dbnew kernel: [<ffffffff8000896c>] __handle_mm_fault+0x1fd/0x103b
Mar 5 11:09:19 dbnew kernel: [<ffffffff80012671>] may_open+0x65/0x233
Mar 5 11:09:19 dbnew kernel: [<ffffffff800671f2>] do_page_fault+0x499/0x842
Mar 5 11:09:19 dbnew kernel: [<ffffffff80027719>] do_filp_open+0x2a/0x38
Mar 5 11:09:19 dbnew kernel: [<ffffffff8005dde9>] error_exit+0x0/0x84
Mar 5 11:09:19 dbnew kernel:
Mar 5 11:09:19 dbnew kernel: Mem-info:
Mar 5 11:09:19 dbnew kernel: Node 0 DMA per-cpu:
Mar 5 11:09:19 dbnew kernel: cpu 0 hot: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 0 cold: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 1 hot: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 1 cold: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 2 hot: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 2 cold: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 3 hot: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: cpu 3 cold: high 0, batch 1 used:0
Mar 5 11:09:19 dbnew kernel: Node 0 DMA32 per-cpu:
Mar 5 11:09:19 dbnew kernel: cpu 0 hot: high 186, batch 31 used:112
Mar 5 11:09:19 dbnew kernel: cpu 0 cold: high 62, batch 15 used:50
Mar 5 11:09:19 dbnew kernel: cpu 1 hot: high 186, batch 31 used:65
Mar 5 11:09:19 dbnew kernel: cpu 1 cold: high 62, batch 15 used:51
Mar 5 11:09:19 dbnew kernel: cpu 2 hot: high 186, batch 31 used:150
Mar 5 11:09:19 dbnew kernel: cpu 2 cold: high 62, batch 15 used:17
Mar 5 11:09:19 dbnew kernel: cpu 3 hot: high 186, batch 31 used:157
Mar 5 11:09:19 dbnew kernel: cpu 3 cold: high 62, batch 15 used:51
Mar 5 11:09:19 dbnew kernel: Node 0 Normal per-cpu:
Mar 5 11:09:19 dbnew kernel: cpu 0 hot: high 186, batch 31 used:153
Mar 5 11:09:19 dbnew kernel: cpu 0 cold: high 62, batch 15 used:60
Mar 5 11:09:19 dbnew kernel: cpu 1 hot: high 186, batch 31 used:165
Mar 5 11:09:19 dbnew kernel: cpu 1 cold: high 62, batch 15 used:25
Mar 5 11:09:19 dbnew kernel: cpu 2 hot: high 186, batch 31 used:139
Mar 5 11:09:19 dbnew kernel: cpu 2 cold: high 62, batch 15 used:45
Mar 5 11:09:19 dbnew kernel: cpu 3 hot: high 186, batch 31 used:160
Mar 5 11:09:19 dbnew kernel: cpu 3 cold: high 62, batch 15 used:58
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem per-cpu: empty
Mar 5 11:09:19 dbnew kernel: Free pages: 32064kB (0kB HighMem)
Mar 5 11:09:19 dbnew kernel: Active:1426487 inactive:70506 dirty:0 writeback:0 unstable:0 free:8016 slab:5549 mapped-file:24 mapped-anon:1352572 pagetables:8398
Mar 5 11:09:19 dbnew kernel: Node 0 DMA free:10096kB min:12kB low:12kB high:16kB active:0kB inactive:0kB present:9676kB pages_scanned:0 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 3000 6030 6030
Mar 5 11:09:19 dbnew kernel: Node 0 DMA32 free:17020kB min:4936kB low:6168kB high:7404kB active:3015772kB inactive:0kB present:3072012kB pages_scanned:7229878 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 3030 3030
Mar 5 11:09:19 dbnew kernel: Node 0 Normal free:4948kB min:4988kB low:6232kB high:7480kB active:2690224kB inactive:282104kB present:3102720kB pages_scanned:8817504 all_unreclaimable? yes
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 0 0
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem free:0kB min:128kB low:128kB high:128kB active:0kB inactive:0kB present:0kB pages_scanned:0 all_unreclaimable? no
Mar 5 11:09:19 dbnew kernel: lowmem_reserve[]: 0 0 0 0
Mar 5 11:09:19 dbnew kernel: Node 0 DMA: 4*4kB 6*8kB 3*16kB 4*32kB 6*64kB 2*128kB 0*256kB 0*512kB 1*1024kB 0*2048kB 2*4096kB = 10096kB
Mar 5 11:09:19 dbnew kernel: Node 0 DMA32: 7*4kB 8*8kB 0*16kB 1*32kB 0*64kB 0*128kB 0*256kB 1*512kB 0*1024kB 0*2048kB 4*4096kB = 17020kB
Mar 5 11:09:19 dbnew kernel: Node 0 Normal: 1*4kB 2*8kB 4*16kB 0*32kB 0*64kB 0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 1*4096kB = 4948kB
Mar 5 11:09:19 dbnew kernel: Node 0 HighMem: empty
Mar 5 11:09:19 dbnew kernel: 145374 pagecache pages
Mar 5 11:09:19 dbnew kernel: Swap cache: add 0, delete 0, find 0/0, race 0+0
Mar 5 11:09:19 dbnew kernel: Free swap = 0kB
Mar 5 11:09:19 dbnew kernel: Total swap = 0kB
Mar 5 11:09:19 dbnew kernel: Free swap: 0kB
Mar 5 11:09:19 dbnew kernel: 1835008 pages of RAM
Mar 5 11:09:19 dbnew kernel: 308543 reserved pages
Mar 5 11:09:19 dbnew kernel: 3625 pages shared
Mar 5 11:09:19 dbnew kernel: 0 pages swap cached
Mar 5 11:09:19 dbnew kernel: Out of memory: Killed process 3031, UID 26, (postmaster).
Так-же сегодня заметил что некоторые процессы постгри иногда умудряются занять по 3.5 Gb виртуальной памяти.
Как я понял постгря съедает всю память и убивается системой.
Что можно сделать в данной ситуации?
