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 10000, 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 = 20000 # 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-100000, 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 debug_print_plan = true debug_pretty_print = true log_connections = true log_duration = true log_pid = true log_statement = true log_timestamp = true log_hostname = true log_source_port = true
#--------------------------------------------------------------------------- # RUNTIME STATISTICS #---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = true log_planner_stats = true log_executor_stats = true #log_statement_stats = true
# - Query/Index Statistics Collector -
stats_start_collector = true stats_command_string = true stats_block_level = true stats_row_level = true stats_reset_on_server_start = true
#--------------------------------------------------------------------------- # CLIENT CONNECTION DEFAULTS #---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names #check_function_bodies = true #default_transaction_isolation = 'read committed' #default_transaction_read_only = false #statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed lc_messages = 'es_VE.UTF-8' # locale for system error message strings lc_monetary = 'es_VE.UTF-8' # locale for monetary formatting lc_numeric = 'es_VE.UTF-8' # locale for number formatting lc_time = 'es_VE.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true #dynamic_library_path = '$libdir' #max_expr_depth = 10000 # min 10
#--------------------------------------------------------------------------- # LOCK MANAGEMENT #---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#--------------------------------------------------------------------------- # VERSION/PLATFORM COMPATIBILITY #---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true #regex_flavor = advanced # advanced, extended, or basic #sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
BUT THE PERFORMANCE ITīS VERY SLOW
what can do ?????
Thank
Mario Soto
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
