Re: postgresql 8 abort with signal 10

On Tue, 2005-05-03 at 12:25, Alexandre Biancalana wrote:
> On 5/3/05, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > On Tue, 2005-05-03 at 11:36, Alexandre Biancalana wrote:
> > > >>You need to find out what's triggering that.  Turning on query logging
> > > >>would be a good way of investigating.
> > >
> > >  Which directives can I use to enable this ?
> > > debug_print_parse ? debug_print_rewritten ? debug_print_plan ?
> > > debug_pretty_print ?
> > >
> > >
> > > >>Rather large, shared buffers for a machine with only 1 gig of ram.  640
> > > >>Meg of RAM means the kernel is basically double buffering everything.
> > > >>have you tested with smaller settings and this setting was the best?
> > >
> > > I had 256 of RAM then I increase to 1GB thinking this could be a
> > > problem of out of memory or a buggy memory...... After this "upgrade"
> > > I increase the numbers of shared buffers,etc
> > >
> > > It's important to say that the max memory usage reach to only 80%.
> > >
> > > What values do you suggest ?
> >
> > Generally 25% of the memory or 256 Megs, whichever is less. In your
> > case, they're the same.  The Reasoning being that the kernel caches,
> > while postgresql only really holds onto data as long as it needs it,
> > then frees it, so having a really huge buffer space lets postgresql
> > flush the kernel cache, then the next access, after postgresql has freed
> > the memory that was holding the data, now has to go to disk.
> >
> > The kernel is generally a lot better at caching than most apps.
> >
> > So, 32768 is about as big as i'd normally go, and even that may be more
> > than you really need.  Note that there's overhead in managing such a
> > large buffer as well.  With pgsql 8.x and the new caching algorithms in
> > place, such overhead may be lower, and larger buffer settings may be in
> > order.  But if testing hasn't shown them to be faster, i'd avoid them
> > for now and see if your signal 10 errors start going away.
> >
> > If they do, then you've likely got a kernel bug in there somewhere.  If
> > they don't, I'd suspect bad hardware.
> >
> > > >>You might want to look in your signal man page on BSD and see what
> > > >>signal 10 means.  On solaris it's a bus error.  Not a clue what it is in
> > > >>FreeBSD myself though.
> > >
> > > FreeBSD man page say: 10    SIGBUS
> > >
> > > The system does not generate core dump file for this error.....
> >
> >
>
> Hi Michael,
>
> Here is my /etc/sysctl.conf:
>
> kern.corefile="/var/coredumps/%N.%P.core"
> kern.sugid_coredump=1
>
> and how I said before, there is no one core file in /var/coredumps....
> I should say that this structure to store core files it's ok, in past
> I used this a lot....
>
> Thanks Scott I will lower shared_buffers to 32768 and try again, but
> how about work_mem, maintenance_work_mem, effective_cache_size ??

work_mem is how much memory things like sorts can allocate.  It really
kind of depends on the kind of parallel load you're looking at possibly
handling.  If you'll never have more than a dozen or so open connections
that could be doing sorts (select distinct, order by, union, etc...)
then having it be 10 to 20 meg is fine.  If you're going to handle
hundreds or even thousands of connections, you have to be careful it's
not big enough to run your machine out of memory, or you'll start
getting swap storms.

maintenance_work_mem is used by processes like vacuum, which tend to be
run one at a time, so having it be fairly large, like 32 to 64 meg is no
big issue.  Note that you can set either of these settings higher for
one shot things, like nightly maintenance, if you need to keep them
lower during the day to ensure proper operation.

effective_cache_size is a setting that simply tells the query planner
about how much the kernel / OS is caching of your data set.  Generally
the cached value shown in top or some other system monitor on a
dedicated machine is about right.

work_mem and maintenance_work_mem are in 1k increments, while the other
two, (buffers and effective_cache_size) are in 8k increments, btw.