On Wed, 20 Oct 2004 20:49:54 +0100, Gary Doades <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
