Bruce Momjian wrote:
> > > Mark Rae <m.rae@inpharmatica.co.uk> writes:
> > > > Normally the backend process would 'swap in' all 512M of shared
> > > > memory when loading, but occasionally after dropping the previous
>
> OK, let me jump in here. First, I am unsure how the various OS's
> determine memory usage when using shared memory. Do they report the
> 512mb shared buffers for each backend, none of them, one of them, or
> spread the 512mb evenly among all attached backends?
>
> Second, the idea of shared memory being paged out is not attractive to
> me. On FreeBSD, I believe sysctl will allow you to control if the
> shared memory is paged out. Not sure about Linux. I recommend turning
> off shared memory paging and see what happens. I can imagine
> performance taking a major hit if any shared memory is not in RAM.
Firstly, I should apologise for causing a bit of confusion, I meant to
say 'mapped' not 'swapped'.
The actual shared memory area was resident in memory, It was just that
the backend process stats were reporting less than this was mapped into
the address space of the process, implying that it was not being used
like it normally would be.
Having reread what I wrote, I seem to have obscured the facts
with my speculation as to the cause of the problem. So I shall
have another go.
The only things I know for certain are that loading processes which
I have run many times before, have very occasionally slowed down by a
factor of 20 or more as the database size has grown, but were behaving
as expected when small. This happened 3 or 4 times with 7.1.2 a few
months ago, and most recently last week with 7.2b4
Restarting the loading, which means a new backend process, does not
fix the problem, however restarting the database does.
With v7.1.2 on FreeBSD I also noticed that the backend was not using
all of the shared memory that it potentially had available to it, as
it normally does. With 7.2b4 on Linux I did not see this effect with
the memory, but the behaviour was otherwise the same.
I realise that this is not much to go on, and unfortunately quite
rare and unpredictable so I can't really narrow it down any further
at the moment.
But such a drastic slowdown is quite worrying, so what I was really
hoping for is some advice on the best way of figuring out what is
going on when it happens again.
-Mark
--
Mark Rae Tel: +44(0)20 7074 4648
Inpharmatica Fax: +44(0)20 7074 4700
m.rae@inpharmatica.co.uk http://www.inpharmatica.co.uk/