Re: [PERFORM] [GENERAL] performance very slow
OK. Thank fou your help. In this moment the size of database its 2GB. And the machine it´s only to postgresql. Gracias Mario Soto wrote: Hi. i hava a postresql 7.4.2 in a production server. tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0. Mario, Start with reading this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Without knowing anything about the size of your database, your usage patterns, or your disk subsystem (the most important part of a database server, imho) I would suggest you first increase the number of shared_buffers allocated to Postgres. Most recommend keeping this number below 1, but I've found I get the best performance with about 24000 shared_buffers with a ~5GB database on a machine with 4GB of ram, dedicated to Postgres. You'll have to experiment to see what works best for you. Also, make sure you VACUUM and ANALYZE on a regular basis. Again, the frequency of this really depends on your data and usage patterns. More frequent write operations require more frequent vacuuming. Good luck. Best Regards, Bill Montgomery The postresql.conf say: #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each sort_mem = 1024 # min 64, size in KB vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~50 bytes each # - Kernel Resource Usage - max_files_per_process = 1000# min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - fsync = true# turns forced synchronization on or off wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 8 # min 4, 8KB each # - Checkpoints - checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds checkpoint_warning = 30 # 0 is off, in seconds commit_delay = 0# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 #--- # QUERY TUNING #--- # - Planner Method Enabling - enable_hashagg = true enable_hashjoin = true enable_indexscan = true enable_mergejoin = true enable_nestloop = true enable_seqscan = true enable_sort = true enable_tidscan = true # - Planner Cost Constants - 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) # - Genetic Query Optimizer - geqo = true geqo_threshold = 11 geqo_effort = 1 geqo_generations = 0 geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - default_statistics_target = 100 # range 1-1000 from_collapse_limit = 30 join_collapse_limit = 30# 1 disables collapsing of explicit JOINs #--- # ERROR REPORTING AND LOGGING #--- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #log_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #log_error_verbosity = default # terse, default, or verbose messages #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #log_min_duration_statement = -1 # Log all statements whose # execution time exceeds the value, in # milliseconds. Zero prints all queries. # Minus-one disables. #silent_mode = false # DO NOT USE without Syslog! # - What to Log - debug_print_parse = true
Re: [PERFORM] [GENERAL] performance very slow
Mario Soto wrote: Hi. i hava a postresql 7.4.2 in a production server. tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0. Mario, Start with reading this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Without knowing anything about the size of your database, your usage patterns, or your disk subsystem (the most important part of a database server, imho) I would suggest you first increase the number of shared_buffers allocated to Postgres. Most recommend keeping this number below 1, but I've found I get the best performance with about 24000 shared_buffers with a ~5GB database on a machine with 4GB of ram, dedicated to Postgres. You'll have to experiment to see what works best for you. Also, make sure you VACUUM and ANALYZE on a regular basis. Again, the frequency of this really depends on your data and usage patterns. More frequent write operations require more frequent vacuuming. Good luck. Best Regards, Bill Montgomery The postresql.conf say: #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each sort_mem = 1024 # min 64, size in KB vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~50 bytes each # - Kernel Resource Usage - max_files_per_process = 1000# min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - fsync = true# turns forced synchronization on or off wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 8 # min 4, 8KB each # - Checkpoints - checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds checkpoint_warning = 30 # 0 is off, in seconds commit_delay = 0# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 #--- # QUERY TUNING #--- # - Planner Method Enabling - enable_hashagg = true enable_hashjoin = true enable_indexscan = true enable_mergejoin = true enable_nestloop = true enable_seqscan = true enable_sort = true enable_tidscan = true # - Planner Cost Constants - 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) # - Genetic Query Optimizer - geqo = true geqo_threshold = 11 geqo_effort = 1 geqo_generations = 0 geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - default_statistics_target = 100 # range 1-1000 from_collapse_limit = 30 join_collapse_limit = 30# 1 disables collapsing of explicit JOINs #--- # ERROR REPORTING AND LOGGING #--- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #log_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #log_error_verbosity = default # terse, default, or verbose messages #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #log_min_duration_statement = -1 # Log all statements whose # execution time exceeds the value, in # milliseconds. Zero prints all queries. # Minus-one disables. #silent_mode = false # DO NOT USE without Syslog! # - What to Log - debug_print_parse = true debug_print_rewritten = true
