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

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 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)
>>