Good day to all ...
I hope any one can give me an advice ... to optimize my database...
im having a problem when i'm backing up my DB and also using vacuum for my DB...
my CPU load goes up.... and no one can use my DB... most of the transaction comes from web server..
I just need ur good advice ...
thank you
please check my postgresql.conf -- # # # Connection Parameters # tcpip_socket = true #ssl = false
max_connections = 300 superuser_reserved_connections = 100
port = 5432 #hostname_lookup = false #show_source_port = false
#unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal
#virtual_host = ''
#krb_server_keyfile = ''
# # Shared Memory Size # shared_buffers = 600 # min max_connections*2 or 16, 8KB each #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8 # min 4, typically 8KB each
# # Non-shared Memory Sizes # #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB
# # Write-ahead log (WAL) # #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # #fsync = true #wal_sync_method = fsync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16
# # Optimizer Parameters # enable_seqscan = true enable_indexscan = true enable_tidscan = true enable_sort = true enable_nestloop = true enable_mergejoin = true enable_hashjoin = true
effective_cache_size = 1000 # typically 8KB each random_page_cost = 4 # 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)
default_statistics_target = 10 # range 1-1000
# # GEQO Optimizer Parameters # geqo = true geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_threshold = 11 geqo_pool_size = 1024 # default based on tables in statement, # range 128-1024 geqo_effort = 1 geqo_generations = 0 geqo_random_seed = -1 # auto-compute seed
# # Message display # #server_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #silent_mode = false
log_connections = true #log_pid = false log_statement = true log_duration = true log_timestamp = true
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)#debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false
#explain_pretty_print = true
# requires USE_ASSERT_CHECKING #debug_assertions = true
# # Syslog # syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres'
# # Statistics # show_parser_stats = false show_planner_stats = false show_executor_stats = false show_statement_stats = false
# requires BTREE_BUILD_STATS #show_btree_build_stats = false
# # Access statistics collection # stats_start_collector = false stats_reset_on_server_start = false stats_command_string = false stats_row_level = false stats_block_level = false
# # Lock Tracing # #trace_notify = false
# requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0
# # Misc # autocommit = true #dynamic_library_path = '$libdir' #search_path = '$user,public' #datestyle = 'iso, us' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #client_encoding = sql_ascii # actually, defaults to database encoding #authentication_timeout = 60 # 1-600, in seconds #deadlock_timeout = 1000 # in milliseconds #default_transaction_isolation = 'read committed' #max_expr_depth = 10000 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = true #sql_inheritance = true #transform_null_equals = false #statement_timeout = 0 # 0 is disabled, in milliseconds #db_user_namespace = false
# # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8'
-----------------
here is my diskspace.. /dev/sdb1 3526172 1132784 2214268 34% / /dev/sda1 248895 8796 227249 4% /boot none 2005700 0 2005700 0% /dev/shm /dev/md0 65757260 50992580 11424376 82% /var /dev/sdc1 17409840 13521548 3003916 82% /backup ----------------
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
