> To answer all the other mails now: Yes, it is a very slow box, PII
> with 300 MHz, 256MB RAM, W2K OS, the harddisk is very old & slow too,
> the HD Tach test says average 10 MB which is not too bad.
That would probably explain most of the problems then. With a HDD
upgrade you can probably atleast double the speed (of DB).
> I figured that running the same query on all rows instead of fetching
> only the first 100 last just few second longer, and when I execute it
> from a remote host it last only 20 seconds which is still slow, but I
> can live with that as long as I know that the box is simply too slow
> to act as a database server at all. I'll install a faster harddisk
> now, maybe that helps.
Ok, could you just for the fun of it try this in front of your query?
explain analyze
ex:
dbmail=# explain ANALYZE select * from messageblks;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on messageblks (cost=0.00..304.92 rows=1792 width=913)
(actual time=0.015..3.249 rows=1830 loops=1)
Total runtime: 4.527 ms
(2 rows)
Btw, I ran that from the psql console ('psql dbmail postgres').
That is on a Dual 1Ghz P3, 1gb ram, softraid-1 ide disks running
linux. So it's clearly a faster box, not enough to warrant SECONDS
in difference.
Oh, and btw.. 'cost' is _NOT_ the same as predicted 'time'.
If you run that with a few queries that uses a where clause, you
can immedeately see wether the db uses the indexes or not.
And it's also good for simple benchmarking of db schema.
Another thing I'd look into if I were you is the postgresql.conf
file. I did some benchmarketing(!) of dbmail with pgsql 7.4 and these
were the only settings I had to change from default that made a
positive difference.
shared_buffers = 15200 # min 16, at least max_connections*2, 8KB each
sort_mem = 65536 # min 64, size in KB
vacuum_mem = 65536 # min 1024, size in KB
That requires shared buffers of 122MB. To get that to work in
linux I added this line to the postgresql start script (in rc.d)
echo 256000000 > /proc/sys/kernel/shmmax
I realise now that these are more general pgsql tips than spesifics
to your problem Gerrit, but hopefully you learned something new.
-=Dead2=-
