Re: Inconsistent performance

it seems like the difference is probably related to caching. you say
you have 1gb of ram, and the database is 2gb. Obviously the entire
database isn't cached, but maybe your query runs fast when the table is
in memory, and they it gets swapped out of cache because some other
piece of information moves into memory. In that circumstance, it has
to load the information from disk and is therefor slow.

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