Hello Hans,
Am Mittwoch, 6. Oktober 2004 um 09:06 schriebst du:
>> 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 have now an average of 51 MB with the new hdd.
>> 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').
dbmail=# explain ANALYZE select * from dbmail_messageblks;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on dbmail_messageblks (cost=0.00..0.00 rows=1 width=58) (actual
time=0.000..190.000 rows=2502 loops=1)
Total runtime: 280.000 ms
(2 rows)
> 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.
It is an index there, however, I saw that in an Oracle database,
it was not used, though there was an index.
> 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
shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
the other both seemed to have disappeared from 8.0beta.
I'll try to tweak the settings to use max available resources.
> 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
How do I do this on Windows?
> I realise now that these are more general pgsql tips than spesifics
> to your problem Gerrit, but hopefully you learned something new.
Thanks,
Gerrit
--
=^..^=
