On Wed, 20 Oct 2004 20:49:54 +0100, Gary Doades <gpd@gpdnet.co.uk> wrote:
> Is this the select(1) query? Please post an explain analyze for this and any other "slow"
> queries.
I think it took so long 'cause it wasn't cached. The second time I ran
it, it took less than a second. How you can tell if something is
cached? Is there a way to see what's in cache?
>
> I would expect the selects to take 99% cpu if all the data you were trying to select was
> already in memory. Is this the case in general? I can do a "select count(1)" on a 500,000
> row table in about 1 second on a Athlon 2800+ if all the data is cached. It takes about 25
> seconds if it has to fetch it from disk.
I think that's what's going on here.
>
> I have just done a test by inserting (via COPY) of 149,000 rows in a table with 23
> columns, mostly numeric, some int4, 4 timestamps. This took 28 seconds on my
> Windows XP desktop, Athlon 2800+, 7200 rpm SATA disk, Postgres 8.0 beta 2. It used
> around 20% to 40% cpu during the copy. The only index was the int4 primary key,
> nothing else.
Well, there are a 3 text columns or so, and that's why the COPY takes
longer than yours. That hasn't been a big issue though. I copies fast
enough.
>
> How does this compare?
>
> Disk system??
It's in ide raid 1 config I believe. So it's not too fast. It will
soon be on a scsi raid 5 array. That should help speed some things up
also.
>
> Regards,
> Gary.
What about the postgresql.conf config settings. This is what I have and why.
shared_buffers = 21250
This is 174 megs, which is 15% of total ram. I read somewhere that it
should be between 12-15% of total ram.
sort_mem = 32768
This is default.
vacuum_mem = 32768
This is 32 megs. I put it that high because of something I read here
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
#max_fsm_pages = 20000
Default. I would think this could be upped more, but I don't know how much.
effective_cache_size = 105750
This is 846 megs ram which is 75% of total mem. I put it there 'cause
of a reply I got on the performance list.
I made all these changes today, and haven't had much of a chance to
speed test postgres since.
Any thoughs on these settings?
-Josh
