Re: how much ram do i give postgres?

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