Re: [GENERAL] performance very slow - Mailing list pgsql-performance

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 majordomo@postgresql.org)
>
>