On Wed, Oct 20, 2004 at 08:25:22 -0500,
Josh Close <narshe (AT) gmail (DOT) com> wrote:
You might not need to do the vacuum fulls that often. If the your hourly
vacuums have a high enough fsm setting, they should be keeping the database
from continually growing in size. At that point daily vacuum fulls are
overkill and if they are slowing stuff down you want to run quickly, you
should cut back on them.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Wed, 20 Oct 2004 13:35:43 -0500, Bruno Wolff III <bruno (AT) wolff (DOT) to> wrote:
I have the vacuum_mem set at 32M right now. I haven't changed the fsm
settings at all though.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On 20 Oct 2004 at 13:34, Josh Close wrote:

Hmm, that seems a bit slow. How big are the rows you are inserting? Have you checked
the cpu and IO usage during the inserts? You will need to do some kind of cpu/IO
monitoring to determine where the bottleneck is.

What hardware is this on? Sorry if you specified it earlier, I can't seem to find mention of
it.

Cheers,
Gary.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, 20 Oct 2004 19:59:38 +0100, Gary Doades <gpd (AT) gpdnet (DOT) co.uk> wrote:
The bulk inserts don't take full cpu. Between 40% and 80%. On the
other hand, a select will take 99% cpu.

It's on a P4 HT with 1,128 megs ram.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On 20 Oct 2004 at 14:09, Josh Close wrote:

Is this the select(1) query? Please post an explain analyze for this and any other "slow"
queries.

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 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.

How does this compare?

Disk system??

Regards,
Gary.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Wed, 20 Oct 2004 20:49:54 +0100, Gary Doades <gpd (AT) gpdnet (DOT) co.uk> wrote:
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 think that's what's going on here.

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.

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.

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/Gener...bits/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

On 20 Oct 2004 at 15:36, Josh Close wrote:

No. The OS caches the data as read from the disk. If you need the data to be in memory
for performance then you need to make sure you have enough available RAM to hold
your typical result sets if possible.

This is not the default. The default is 1000. You are telling Postgres to use 32Megs for
*each* sort that is taking place. If you have several queries each performing large sorts
you can quickly eat up available RAM this way. If you will only have a small number of
concurrrent queries performing sorts then this may be OK. Don't forget, a single query
can perform more than one sort operation. If you have 10 large sorts happening at the
same time, you can eat up to 320 megs this way!

You will need to tell us the number of updates/deletes you are having. This will
determine the vacuum needs. If the bulk of the data is inserted you may only need to
analyze frequently, not vacuum.

In order to get more help you will need to supply the update/delete frequency and the
explain analyze output from your queries.

Regards,
Gary.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, 20 Oct 2004 23:43:54 +0100, Gary Doades <gpd (AT) gpdnet (DOT) co.uk> wrote:
I will have to gather this information for you.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly