Working happily on 8.1 (Was: panic on 7.3)

While this little bug was a menace to me at a bad time my biggest
problem was that I didn't have a good enough vacuum strategy and my
reasonable sized database became the size of the world. At that
point it couldn't be vacuumed without jamming up the whole server.
So I have some questions. If this is the wrong place let me know and
I will submit it to general.

1) What about these settings. It is a dual 2.8 ghz xeon box with 6
RAID 5 (I know I should be using 0+1 or something) 15,000 rpm scsi
drives and 2 gigs of ram.

max_connections = 1024
shared_buffers = 15000
work_mem = 1024
maintenance_work_mem = 100000
max_fsm_pages = 1000000
checkpoint_segments = 10
checkpoint_timeout = 1000
effective_cache_size = 50000

My "base" directory is 618 MB. All other performance related
settings I left at the defaults.

I know it depends on my data set and load etc, but it would be great
if someone could tell me if anything in there is a little crazy. The
max_fsm_pages seemed a bit high but I really want vacuum to go fast
and painless and if I read everything right it still doesn't take up
much memory.

2) I didn't touch the Vacuum delay, background writer or autovacuum
settings because I wasn't familiar enough with them. Are the default
values very restricting? I realized a little too late that leaving
some of the 7.3 defaults in place came back to bite me when my load
went up. Since these are performance enhancing features and they
didn't exist in older versions I figured that the defaults would
still be better than 7.3 without those features. Or are the defaults
too conservative and I need to change them ASAP?

3) Several times there were backends running that were just bringing
down the system. Is there a way to signal a single backend to die
without restarting the whole db server? I looked on google, searched
the archives and in the docs and couldn't find any way to do this.

Thanks again,

Rick

On Jan 21, 2006, at 12:05 AM, Rick Gigger wrote:

> Thanks very much!
>
> I've decided to go straight to 8.1 though. There are just too many
> performance improvements at this point that I might regret not
> having and I don't want to do a dump reload again. I am about to
> compile it now. If it isn't a panic grade failure in the latest
> 8.1 code then I'd just assume take the stock release source code.
> I don't care at all if this kills one connection at the ultra-low
> frequency with which it occurs but what I can't have is the whole
> server rebooting itself in the middle of processing hundreds of
> transactions. Once that happens all of the web clients hang onto
> their bad connections and then eventually die. Considering that
> I'm moving to 8.1 and am not too familiar with applying patches am
> I crazy for just going with the stock 8.1 code?
>
> On Jan 20, 2006, at 10:36 PM, Tom Lane wrote:
>
>> Rick Gigger <rick(at)alpinenetworking(dot)com> writes:
>>> I don't know if 2K could have passed since the last checkpoint.
>>> ...
>>> now that I think about it I was getting about 400 pages requests /
>>> minute and each of those would have have been doing at least 2
>>> transactions so yes, I guess that is very likely.
>>
>> Good, 'cause if you didn't have a couple thousand transactions
>> between
>> checkpoints then we need another theory ;-)
>>
>>>> You realize of course that that's pretty old ...
>>
>>> Yes. I will be upgrading immediately.
>>
>> You'll want to include this patch:
>> http://archives.postgresql.org/pgsql-committers/2006-01/msg00289.php
>> (or see adjacent messages if you plan to move to something newer than
>> 7.3.*). We probably will not put out another set of releases until
>> next month, unless something really big comes along. This one
>> doesn't
>> qualify as really big IMHO, because it's not a PANIC-grade failure in
>> the later branches. But having been burnt once, I'm sure you'll want
>> a patched copy ...
>>
>> regards, tom lane
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>