Hi,
I have been playing with with a number of large databases in postgres
(several GB in size), over the last 6 months or so, and have
occasionally noticed behaviour which might indicate a possible
problem with the shared memory.
I initially noticed it when using v7.1.2, but have recently been
trying the 7.2b versions which seem to have the same/similar problem.
(My setup is a dual Athlon1800+ with 1GB of memory, 512M of which
is dedicated to postgres shm buffers)
Case 1: This was with v7.1.2/3 on FreeBSD
I would create a database and load some data (2-3Gb) into
it using Perl::DBI. I would then make some changes to the loading
programs, 'DROP' the database, recreate it and reload the data to
measure the effects of the changes.
Normally the backend process would 'swap in' all 512M of shared
memory when loading, but occasionally after dropping the previous
database, the new backend would only seem to be able to use a
small amount of the shared memory. Restarting postgres would
clear the problem.
Case 2: This is with 7.2b4 on Linux
I created one database as before and everything works as expected,
however I then created a second database without dropping the original,
and it also seemed to be working as expected.
However as it grew in size, it slowed down significantly (20-30 times slower),
as though it could not get enough memory for the indexes (The largest of
which can be 0.5G-1G in size).
Although this time the backend process appeared to have the whole 512M
'swapped in'. Again restarting postgres cleared the problem.
Unfortunately the problem does not appear to be reliably repeatable,
so I am just guessing that the problem might be related to the SHM buffers
being 'leaked' or becoming permanently assigned to a specific
database, even when they are no longer needed.
Has anyone else seen similar behaviour, or is there any way to
check how postgres has allocated the buffers to help pinpoint
the problem?
-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/
