how busy is the system? what other programs are running on the machine? how big (on disk) is the table in question? what kind of load does the system have? is it a single 80gb ide drive? Even though you have 4 CPU's a small amount of memory and bad IO system will kill the database.
On Monday, September 15, 2003, at 05:28 PM, Joseph Bove wrote:
Stephan,
I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds
Attached is a typical output
QUERY PLAN
-------------------------------------
Aggregate (cost=9993.92..9993.92 rows=1 width=0)
(actual time=7575.59..7575.59 rows=1 loops=1)
-> Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0)
(actual time=0.06..7472.20 rows=88910 loops=1)
Total runtime: 7575.67 msec
(3 rows)
The only things changing are the actual time. The costs are constant.
The relpages from pg_class for vetapview (the table in question) is 8881.
At the end of this message is the exhaustive contents of postgresql.conf. The only settings I have attempted tuning are as follows:
tcpip_socket = true max_connections = 100 shared_buffers = 5000 sort_mem = 8192 fsync = false
I did have shared_buffers and sort_mem both set higher originally (15000, 32168) but decreased them in case over-utilization of memory was the problem.
The kernel setting shmmax is set to 256,000,000 (out of 1 gig)
Regards,
Joseph
postgresql.conf
# # Connection Parameters # tcpip_socket = true #ssl = false
max_connections = 100 #superuser_reserved_connections = 2
#port = 5432 #hostname_lookup = false #show_source_port = false
#unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
#shared_buffers = 15000 # min max_connections*2 or 16, 8KB each
shared_buffers = 5000
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
#sort_mem = 32168 # min 64, size in KB
sort_mem = 8192
#vacuum_mem = 8192 # min 1024, size in KB
#
# Write-ahead log (WAL)
#
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#
fsync = false
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
# # Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true#enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true
#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)
#default_statistics_target = 10 # range 1-1000
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_effort = 1
#geqo_generations = 0#geqo_random_seed = -1 # auto-compute seed
#
# Message display
#
#server_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#silent_mode = false
#log_connections = false #log_pid = false #log_statement = false #log_duration = false #log_timestamp = false
#log_min_error_statement = error # Values in order of increasing severity:
#log_min_error_statement = error # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
#debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false
#explain_pretty_print = true
# requires USE_ASSERT_CHECKING #debug_assertions = true
# # Syslog # #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres'
# # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_statement_stats = false
# requires BTREE_BUILD_STATS #show_btree_build_stats = false
# # Access statistics collection # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false
# # Lock Tracing # #trace_notify = false
# requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0
#
# Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown # actually, defaults to TZ environment setting
#datestyle = 'iso, us'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#client_encoding = sql_ascii # actually, defaults to database encoding
#authentication_timeout = 60 # 1-600, in seconds
#deadlock_timeout = 1000 # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
#db_user_namespace = false
# # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8'
At 03:49 PM 9/15/2003 -0700, Stephan Szabo wrote:
On Mon, 15 Sep 2003, Joseph Bove wrote:
> Stephan, > > Actually, it's inconsistent with the exact same command. I've now > replicated the problem by doing the following command: > > select count (*) from table; > > The table in question has 88899 rows. > > The response time is anywhere from 1 second to 12 seconds. Different > response times can occur in the same minute of testing!
Well, that's really only got one valid plan right now (seqscan and
aggregate). It'd be mildly interesting to see what explain analyze says in
slow and fast states, although I'd be willing to bet that it's just going
to effectively show that the seqscan is taking more or less time.
I think we're going to need to see the configuration settings for the
server and possibly some info on how big the table is (say relpages for
the pg_class row associated with the table after a vacuum full).
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
