performance very slow

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)

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)

BUT THE PERFORMANCE IT´S VERY SLOW

what can do ?????

Thank

Mario Soto

On Wed, 26 May 2004, Mario Soto wrote:

tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0.

BUT THE PERFORMANCE IT�S VERY SLOW

How often do you run VACUUM ANALYZE? You might want to do that every
night or every hour (depending on how much updates you have).

Some of your config values could and should be tuned, read something
like

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

Still, if it's very slow it's probably not just a little tweaking of
these variables that solves everything.

If that is the case you need to find a slow query, run EXPLAIN ANALYZE
on it and try to figure out why it is slow. There is a list to help with
performance issues called pgsql-performance that you might want to post
to (and read its archive).

But before anything else, make sure you run VACUUM ANALYZE regulary.

--
/Dennis Bj�rklund