Re: Need for speed

From: Ulrich Wisser
Subject: Re: Need for speed
Date: ,
Msg-id: 4303003B.3020407@relevanttraffic.se
(view: Whole thread, Raw)
In response to: Re: Need for speed  (Dennis Bjorklund)
Responses: Re: Need for speed  (Tom Lane)
Re: Need for speed  ("Jeffrey W. Baker")
Re: Need for speed  (Josh Berkus)
Re: Need for speed  (Ron)
List: pgsql-performance

Tree view

Need for speed  (Ulrich Wisser, )
 Re: Need for speed  (Richard Huxton, )
 Re: Need for speed  (John A Meinel, )
 Re: Need for speed  ("Jeffrey W. Baker", )
 Re: Need for speed  (Alex Turner, )
  Re: Need for speed  ("Jim C. Nasby", )
 Re: Need for speed  (Dennis Bjorklund, )
  Re: Need for speed  (Ulrich Wisser, )
   Re: Need for speed  (Tom Lane, )
   Re: Need for speed  ("Jeffrey W. Baker", )
   Re: Need for speed  (Josh Berkus, )
   Re: Need for speed  (Ron, )
 Re: Need for speed  (Ron, )
  Re: Need for speed  (Matthew Nuzum, )
   Need for speed 2  (Ulrich Wisser, )
    Re: Need for speed 2  (Frank Wiles, )
    Re: Need for speed 2  (Ron, )
     Re: Need for speed 2  (Kelly Burkhart, )
      Re: Need for speed 2  (Alex Turner, )
    Re: Need for speed 2  ("Merlin Moncure", )
     What *_mem to increase when running CLUSTER  (Andrew Lazarus, )
      Re: What *_mem to increase when running CLUSTER  (Steve Poe, )
      Re: What *_mem to increase when running CLUSTER  (Tom Lane, )
 Re: Need for speed  ("Roger Hand", )
  Re: Need for speed  (Christopher Browne, )

Hello,

thanks for all your suggestions.

I can see that the Linux system is 90% waiting for disc io. At that time
all my queries are *very* slow. My scsi raid controller and disc are
already the fastest available. The query plan uses indexes and "vacuum
analyze" is run once a day.

To avoid aggregating to many rows, I already made some aggregation
tables which will be updated after the import from the Apache logfiles.
That did help, but only to a certain level.

I believe the biggest problem is disc io. Reports for very recent data
are quite fast, these are used very often and therefor already in the
cache. But reports can contain (and regulary do) very old data. In that
case the whole system slows down. To me this sounds like the recent data
is flushed out of the cache and now all data for all queries has to be
fetched from disc.

My machine has 2GB memory, please find postgresql.conf below.

Ulrich


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 20000          # min 16, at least max_connections*2,
sort_mem = 4096         # min 64, size in KB
vacuum_mem = 8192               # min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 50000           # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 3000        # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000   # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

fsync = false                   # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
wal_buffers = 128               # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 16        # 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



pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Need for speed
From: John A Meinel
Date:
Subject: Re: Data Selection Slow From VB 6.0