From: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
To: pgsql-performance@postgresql.org
Subject: Re: Need for speed
Date: Wed, 17 Aug 2005 11:15:39 +0200
Message-ID: <4303003B.3020407@relevanttraffic.se> (raw)
In-Reply-To: <Pine.LNX.4.44.0508162045480.2995-100000@zigo.dhs.org>
References: <Pine.LNX.4.44.0508162045480.2995-100000@zigo.dhs.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ulrich.wisser@relevanttraffic.se
Subject: Re: Need for speed
In-Reply-To: <4303003B.3020407@relevanttraffic.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox