# listener listen_addresses = '*' port = 5432 # data buffer cache #shared_buffers = 262144 # each 8Kb so depends upon meory available shared_buffers = 125000 # [17:43] Jedirl: effective_cache_size should be on the order of 50%-100% of physical RAM on most modern OSes effective_cache_size = 524288 # [17:44] Jedirl: max_fsm_pages depends on the physical size of your db, and how often you vacuum it, but you should usually set it to _at least_ 200000 on anything except a toy db # [17:52] Jedirl: a more accurate estimate for max_fsm_pages would be to estimate the approximate size of your db on disk, subtract the space consumed by data that never changes (not even updates),divide by the 8k page size, and use that max_fsm_pages = 2000000 max_fsm_relations = 124000 work_mem = 335544 maintenance_work_mem = 1048576 fsync = false # resilience # wal_sync_method = #fdatasync # the default is the first option # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync # http://archives.postgresql.org/pgsql-performance/2004-09/msg00201.php recomienda fdatasync # http://blogs.sun.com/jkshah recomienda fsync wal_buffers = 256 #checkpoints checkpoint_segments = 512 #bgwriter_percent = 0 #bgwriter_maxpages = 0 #commit_delay = 500 # group commit if works #archive_command = 'cp "%p" /var/opt/postgresql/MTRBSS/wal/arch_xlog/"%f"' archive_command = 'cp "%p" /var/opt/postgresql/MTRBSS/archwal/"%f"' full_page_writes = off # server error log log_line_prefix = '%t :' # timestamp log_min_duration_statement = 1000 # log any SQL taking more than 1000ms log_min_messages = info #transaction/locks default_transaction_isolation = 'read committed' #random_page_cost = 3 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits #autovacuum = on # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # vacuum_cost_limit bash-3.00$ bash-3.00$ cat postgresql.conf # http://blogs.sun.com/jkshah, 10 Agosto - 22 Noviembre # listener listen_addresses = '*' port = 5432 # data buffer cache #shared_buffers = 262144 # each 8Kb so depends upon meory available shared_buffers = 125000 # [17:43] Jedirl: effective_cache_size should be on the order of 50%-100% of physical RAM on most modern OSes effective_cache_size = 524288 # [17:44] Jedirl: max_fsm_pages depends on the physical size of your db, and how often you vacuum it, but you should usually set it to _at least_ 200000 on anything except a toy db # [17:52] Jedirl: a more accurate estimate for max_fsm_pages would be to estimate the approximate size of your db on disk, subtract the space consumed by data that never changes (not even updates),divide by the 8k page size, and use that max_fsm_pages = 2000000 max_fsm_relations = 124000 work_mem = 335544 maintenance_work_mem = 1048576 fsync = false # resilience # wal_sync_method = #fdatasync # the default is the first option # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync # http://archives.postgresql.org/pgsql-performance/2004-09/msg00201.php recomienda fdatasync # http://blogs.sun.com/jkshah recomienda fsync wal_buffers = 256 #checkpoints checkpoint_segments = 512 #bgwriter_percent = 0 #bgwriter_maxpages = 0 #commit_delay = 500 # group commit if works #archive_command = 'cp "%p" /var/opt/postgresql/MTRBSS/wal/arch_xlog/"%f"' archive_command = 'cp "%p" /var/opt/postgresql/MTRBSS/archwal/"%f"' full_page_writes = off # server error log log_line_prefix = '%t :' # timestamp log_min_duration_statement = 1000 # log any SQL taking more than 1000ms log_min_messages = info #transaction/locks default_transaction_isolation = 'read committed' #random_page_cost = 3 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits #autovacuum = on # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # vacuum_cost_limit