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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
