Message-ID: <4303003B.3020407@relevanttraffic.se>
Date: Wed, 17 Aug 2005 11:15:39 +0200
From: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
Organization: Relevant Traffic AB
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Need for speed
References: <Pine.LNX.4.44.0508162045480.2995-100000@zigo.dhs.org>
In-Reply-To: <Pine.LNX.4.44.0508162045480.2995-100000@zigo.dhs.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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