By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,132 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,132 IT Pros & Developers. It's quick & easy.

performance very slow

P: n/a
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.

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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi,

shared_buffers seems quite low for a server to me. For best performance, you
should read and follow the optimisation articles on
http://techdocs.postgresql.org/.

Regards, Frank



On Wed, 26 May 2004 11:26:30 -0400 (VET) "Mario Soto"
<ma********@venezolanadeavaluos.com> sat down, thought long and then 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.


...



BUT THE PERFORMANCE IT´S VERY SLOW

what can do ?????

Thank


Mario Soto



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)

iD8DBQFAtNgC9yTJ83o5N2cRAqUHAJ41DHj3yTX+jI1MfGvTgS YUsgzJAgCfeMSw
7EjrbouA2RrzsvgEr3HvK08=
=3JHM
-----END PGP SIGNATURE-----

Nov 23 '05 #2

P: n/a
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/Gener...bits/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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

P: n/a
OK.

i see the link and change parameters in postgresql.conf

i.e.

When excecute a insert statement the memory up to 90% to use .
it's normal ???????
Thank for yor help and sorry for my bad englis

Regards

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/Gener...bits/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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.