Thread: was there a change in FreeBSD SHM implementation from 4.4 to 4.6? (postgres trouble)

I have a dual cpu box with 2GB RAM dedicated to running Postgres.
Last week, I upgraded FreeBSD from 4.4-STABLE to 4.6-RELEASE-p1.  When
I went to restart postgres, it complained that it could not allocate
the shared memory segment.  I'm running Postgres 7.2.1.

For those familiar with postgres, I was using shared_buffers=100000
with 4.4, but had to back that down to 32000 for 4.6.  This is
obviously impacting performance...

The kern.ipc.* settings have not changed.  In my /etc/sysctl.conf
file, I set

kern.ipc.shmmax=268435456
kern.ipc.shmall=65535
kern.ipc.shm_use_phys=1

With FreeBSD 4.6, I even upped the shmmax to 1073741824 to no avail.
I also set this in the kernel (so as to eliminate any issues with
setting it at boot time).

However, it does produce a most peculiar error message when running
postgres:

-- cut here --
IpcMemoryCreate: shmget(key=5432001, size=665346048, 03600) failed: Cannot allocate memory

This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space.
To reduce the request size (currently 665346048 bytes), reduce
PostgreSQL's shared_buffers parameter (currently 80000) and/or
its max_connections parameter (currently 48).
-- cut here --


Now, by my arithmetic, 665346048 is certainly less than 1073741824 by
quite a bit.

I did not recompile postgres after the FreeBSD upgrade.

Has something changed from 4.4-STABLE that would cause such a failure?

The funny thing is that it worked just fine with FBSD 4.4 and the
lower setting of shmmax (even with 100000 shared_buffers), so
something is making postgres try to allocate a larger hunk of memory.
I just don't know what.

Any advice would be appreciated.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: was there a change in FreeBSD SHM implementation from

From
Curt Sampson
Date:
On Wed, 10 Jul 2002, Vivek Khera wrote:

> I have a dual cpu box with 2GB RAM dedicated to running Postgres.
> Last week, I upgraded FreeBSD from 4.4-STABLE to 4.6-RELEASE-p1.  When
> I went to restart postgres, it complained that it could not allocate
> the shared memory segment.  I'm running Postgres 7.2.1.

This came up on the list a few days ago. FreeBSD 4.6 apparently has
some sort of system limit on how much shared memory it will allocate,
probably to keep those using shared memory from eating up too much
of the available VM. This is not a problem because you want to allocate
a fairly small amount of shared memory.

> For those familiar with postgres, I was using shared_buffers=100000
> with 4.4, but had to back that down to 32000 for 4.6.  This is
> obviously impacting performance...

Yes, I'm sure your performance improved. By going from 780 MB to
25 MB of shared memory, you just increased the amount of data the
OS can buffer from about 1.1 GB to 1.8 GB. You've just increased
your cache size by about 60%.

See previous discussions on this (postgresql-general) list over
the past couple of weeks for details. (Is this in the FAQ yet?)

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: was there a change in FreeBSD SHM implementation from

From
Vince Vielhaber
Date:
On Wed, 10 Jul 2002, Vivek Khera wrote:

> -- cut here --
> IpcMemoryCreate: shmget(key=5432001, size=665346048, 03600) failed: Cannot allocate memory
>
> This error usually means that PostgreSQL's request for a shared
> memory segment exceeded available memory or swap space.
> To reduce the request size (currently 665346048 bytes), reduce
> PostgreSQL's shared_buffers parameter (currently 80000) and/or
> its max_connections parameter (currently 48).
> -- cut here --
>
>
> Now, by my arithmetic, 665346048 is certainly less than 1073741824 by
> quite a bit.
>
> I did not recompile postgres after the FreeBSD upgrade.
>
> Has something changed from 4.4-STABLE that would cause such a failure?
>
> The funny thing is that it worked just fine with FBSD 4.4 and the
> lower setting of shmmax (even with 100000 shared_buffers), so
> something is making postgres try to allocate a larger hunk of memory.
> I just don't know what.
>
> Any advice would be appreciated.

Have you looked at the FreeBSD section here:

   http://www.postgresql.org/idocs/index.php?kernel-resources.html

Most notably, I had to increase SHMMAXPGS on one of my FreeBSD machines.
There doesn't appear to be a sysctl variable for it.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Re: was there a change in FreeBSD SHM implementation from 4.4 to

From
Doug Silver
Date:
For what it's worth, when I upgraded to 4.6, I came across this same
problem and I think asked the same question.  Anyway, recompiling the
kernel with the following worked (note the "#PG" are specific numbers that
I put in for Postgres):

# Added for Postgres operation
options         SHMMAXPGS=4096  #PG
options         SHMSEG=256      #PG
options         SYSVMSG                 #SYSV-style message queues
options         SYSVSEM                 #SYSV-style semaphores
options         SEMMNI=256      #PG
options         SEMMNS=512      #PG
options         SEMMNU=256      #PG
options         SEMMAP=256      #PG

YMMV

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Urchin Software Corp.    http://www.urchin.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Wed, 10 Jul 2002, Vivek Khera wrote:

> I have a dual cpu box with 2GB RAM dedicated to running Postgres.
> Last week, I upgraded FreeBSD from 4.4-STABLE to 4.6-RELEASE-p1.  When
> I went to restart postgres, it complained that it could not allocate
> the shared memory segment.  I'm running Postgres 7.2.1.
>
> For those familiar with postgres, I was using shared_buffers=100000
> with 4.4, but had to back that down to 32000 for 4.6.  This is
> obviously impacting performance...
>
> The kern.ipc.* settings have not changed.  In my /etc/sysctl.conf
> file, I set
>
> kern.ipc.shmmax=268435456
> kern.ipc.shmall=65535
> kern.ipc.shm_use_phys=1
>
> With FreeBSD 4.6, I even upped the shmmax to 1073741824 to no avail.
> I also set this in the kernel (so as to eliminate any issues with
> setting it at boot time).
>
> However, it does produce a most peculiar error message when running
> postgres:
>
> -- cut here --
> IpcMemoryCreate: shmget(key=5432001, size=665346048, 03600) failed: Cannot allocate memory
>
> This error usually means that PostgreSQL's request for a shared
> memory segment exceeded available memory or swap space.
> To reduce the request size (currently 665346048 bytes), reduce
> PostgreSQL's shared_buffers parameter (currently 80000) and/or
> its max_connections parameter (currently 48).
> -- cut here --
>
>
> Now, by my arithmetic, 665346048 is certainly less than 1073741824 by
> quite a bit.
>
> I did not recompile postgres after the FreeBSD upgrade.
>
> Has something changed from 4.4-STABLE that would cause such a failure?
>
> The funny thing is that it worked just fine with FBSD 4.4 and the
> lower setting of shmmax (even with 100000 shared_buffers), so
> something is making postgres try to allocate a larger hunk of memory.
> I just don't know what.
>
> Any advice would be appreciated.
>
>


Re: was there a change in FreeBSD SHM implementation from 4.4 to

From
Igor Sysoev
Date:
On Wed, 10 Jul 2002, Vivek Khera wrote:

> I have a dual cpu box with 2GB RAM dedicated to running Postgres.
> Last week, I upgraded FreeBSD from 4.4-STABLE to 4.6-RELEASE-p1.  When
> I went to restart postgres, it complained that it could not allocate
> the shared memory segment.  I'm running Postgres 7.2.1.
>
> For those familiar with postgres, I was using shared_buffers=100000
> with 4.4, but had to back that down to 32000 for 4.6.  This is
> obviously impacting performance...
>
> The kern.ipc.* settings have not changed.  In my /etc/sysctl.conf
> file, I set
>
> kern.ipc.shmmax=268435456
> kern.ipc.shmall=65535
> kern.ipc.shm_use_phys=1

I do not know why your settings worked in 4.4-STABLE but
4.6-RELEASE behaves correctly.
If you use standard 8K tuple then shared_buffers=100000
means 100,000*8192=819,200,000 bytes.
You set kern.ipc.shmall=65535, so whole shared memory can be no
more then 65535*4096=268,431,360 bytes.
When you set shared_buffers=32000 then you ask for 262,144,000 bytes
and it's worked.

> With FreeBSD 4.6, I even upped the shmmax to 1073741824 to no avail.
> I also set this in the kernel (so as to eliminate any issues with
> setting it at boot time).
>
> However, it does produce a most peculiar error message when running
> postgres:
>
> -- cut here --
> IpcMemoryCreate: shmget(key=5432001, size=665346048, 03600) failed: Cannot allocate memory
>
> This error usually means that PostgreSQL's request for a shared
> memory segment exceeded available memory or swap space.
> To reduce the request size (currently 665346048 bytes), reduce
> PostgreSQL's shared_buffers parameter (currently 80000) and/or
> its max_connections parameter (currently 48).
> -- cut here --
>
>
> Now, by my arithmetic, 665346048 is certainly less than 1073741824 by
> quite a bit.

Did you up shmmax or shmall ?
You need also increase shmall to 262144.

Igor Sysoev
http://sysoev.ru


Re: was there a change in FreeBSD SHM implementation from

From
Vivek Khera
Date:
>>>>> "CS" == Curt Sampson <cjs@cynic.net> writes:

>> For those familiar with postgres, I was using shared_buffers=100000
>> with 4.4, but had to back that down to 32000 for 4.6.  This is
>> obviously impacting performance...

CS> Yes, I'm sure your performance improved. By going from 780 MB to
CS> 25 MB of shared memory, you just increased the amount of data the
CS> OS can buffer from about 1.1 GB to 1.8 GB. You've just increased
CS> your cache size by about 60%.

Actually performance went down.  Way down.  I disagree with your
argument that increasing the cache will help, since the cache is not
needed if you don't pushd out your SHM pages in the first place and
need to reload them quickly.

What it turned out to be was that SHMMAX was larger than my SHMALL.
Why the kernel let me build it that way without warnings is a hole in
the config system, but not fatal.  Bumping up SHMALL fixed my issue,
and now my performance is back to normal.

Perhaps Postgres could identify the smaller of SHMMAX and SHMALL when
reporting the failure to get the memory, and indicate which one is too
small.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: was there a change in FreeBSD SHM implementation from

From
Vivek Khera
Date:
>>>>> "VV" == Vince Vielhaber <vev@michvhf.com> writes:

VV> Have you looked at the FreeBSD section here:

VV>    http://www.postgresql.org/idocs/index.php?kernel-resources.html

VV> Most notably, I had to increase SHMMAXPGS on one of my FreeBSD machines.
VV> There doesn't appear to be a sysctl variable for it.

Thanks for the tip.  Turns out my SHMALL was less than SHMMAX.  Seems
silly that the kernel lets you build it that way, but it does.  Upping
SHMALL to be commensurate with SHMMAX fixed my problem.  I assume I
did it manually via sysctl before (was nearly a year ago since this
machine was rebooted, so I never noticed it wasn't set in sysctl.conf
or the kernel).

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: was there a change in FreeBSD SHM implementation from

From
Curt Sampson
Date:
On Thu, 11 Jul 2002, Vivek Khera wrote:

> Actually performance went down.  Way down.  I disagree with your
> argument that increasing the cache will help, since the cache is not
> needed if you don't pushd out your SHM pages in the first place and
> need to reload them quickly.

Ah, so your entire working data set can fit into your shared memory? In
that case, yes, you are better off not decreasing it. I guess we should
add a note to the FAQ for people using very small databases....

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: was there a change in FreeBSD SHM implementation from

From
Bruce Momjian
Date:
Curt Sampson wrote:
> On Thu, 11 Jul 2002, Vivek Khera wrote:
>
> > Actually performance went down.  Way down.  I disagree with your
> > argument that increasing the cache will help, since the cache is not
> > needed if you don't pushd out your SHM pages in the first place and
> > need to reload them quickly.
>
> Ah, so your entire working data set can fit into your shared memory? In
> that case, yes, you are better off not decreasing it. I guess we should
> add a note to the FAQ for people using very small databases....

Curt, I haven't heard anyone else who agrees with your idea of making
PostgreSQL shared buffers smaller and kernel cache bigger.

Now, you have potent argument that having 3X of kernel buffer and 1X of
PostgreSQL buffers is better than having 2X of kernel buffers and 2X of
PostgreSQL buffers.  However, you are assuming the extra 1X of kernel
buffer size will be used often _and_ that the copying from kernel buffer to
PostgreSQL is minimal.

I understand your idea that the disk I/O is clearly slower than the copy
from kernel to PostgreSQL buffer, but with the 3X/1X solution, is moving
more buffers from kernel to PostgreSQL slower than the number of times
you are doing more I/O in the 2X/2X case.  My guess is that the 2X/2X
case is the winner, but it is an interesting idea.

The case of this user was going from XXX megabytes to 25MB, which is a
drastic change, and there the buffer copying is probably much more of a
performance hit than the larger kernel buffer.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: was there a change in FreeBSD SHM implementation from

From
Curt Sampson
Date:
On Thu, 11 Jul 2002, Bruce Momjian wrote:

> Curt, I haven't heard anyone else who agrees with your idea of making
> PostgreSQL shared buffers smaller and kernel cache bigger.

Well, I seem to recall that Tom Lane agrees with me. The archives server
is still generating broken HTML (no </table> tag), though, so it's
difficult for me to find messages.

But I've seen nobody explain just how giving more buffers to postgres
is going to be better than less, in the general case.

> Now, you have potent argument that having 3X of kernel buffer and 1X of
> PostgreSQL buffers is better than having 2X of kernel buffers and 2X of
> PostgreSQL buffers.  However, you are assuming the extra 1X of kernel
> buffer size will be used often _and_ that the copying from kernel buffer to
> PostgreSQL is minimal.

I'm not assuming that copying is minimal. Copying could be very
heavy; it's still much, much, much cheaper than disk I/O.

But yes, I am assuming that the working data set is not significantly
smaller than the amount of memory in your machine. Thank you to the
person who pointed this out to me.

> I understand your idea that the disk I/O is clearly slower than the copy
> from kernel to PostgreSQL buffer, but with the 3X/1X solution, is moving
> more buffers from kernel to PostgreSQL slower than the number of times
> you are doing more I/O in the 2X/2X case.  My guess is that the 2X/2X
> case is the winner, but it is an interesting idea.

I'm working on the basis that you can do several hundred copies of a
block in memory in the time it would take to do a single disk I/O. Do
you disagree? What figure are you using? Why?

Also, note that I am not advocating the very minimal number of buffers;
you do want enough to ensure that, say, a bunch of simultaneous update
requests that touch various data and index pages several times during
the update can have all of those buffers remain in postgres' shared
memory. At a rough go, I'd say off-hand you probably want 30-50 shared
memory buffers per simultanous active query. So given, say, 200
connections, of which 20% will be active simultaneously, 2000 buffers
(16 MB) seems reasonable.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC




Re: was there a change in FreeBSD SHM implementation from

From
Andrew Sullivan
Date:
On Fri, Jul 12, 2002 at 02:38:16PM +0900, Curt Sampson wrote:

> Also, note that I am not advocating the very minimal number of buffers;
> you do want enough to ensure that, say, a bunch of simultaneous update
> requests that touch various data and index pages several times during
> the update can have all of those buffers remain in postgres' shared
> memory.

The problem with this approach is that if there are some tables which
get hit much less frequently than others, but which are crucial for
an application, decreasing the buffer size means that they'll need,
at the very least, to be copied from OS buffers.

The cost of that is significant, as I believe I noted recently, if
you're trying to shave milliseconds off your query times: lots of
microseconds add up.

The real answer to that problem, of course, is being able to lock
certain tables in memory.  But in the absence of such a feature, a
little experimenting might reveal that very large buffers are called
for.

I think the administrator docs have it right: the only way to set the
value correctly is by experimentation; a rule of thumb, used
uncritically, is as likely as not to cause that digit to be under the
hammer head.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


Re: was there a change in FreeBSD SHM implementation from

From
Vivek Khera
Date:
>>>>> "CS" == Curt Sampson <cjs@cynic.net> writes:

CS> On Thu, 11 Jul 2002, Vivek Khera wrote:
>> Actually performance went down.  Way down.  I disagree with your
>> argument that increasing the cache will help, since the cache is not
>> needed if you don't pushd out your SHM pages in the first place and
>> need to reload them quickly.

CS> Ah, so your entire working data set can fit into your shared memory? In
CS> that case, yes, you are better off not decreasing it. I guess we should
CS> add a note to the FAQ for people using very small databases....

Sometimes it does, sometimes it does not.  But I still disagree that
relying on the OS to cache pages that you could (and should) just as
well keep in the SHM segments already makes no sense.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: was there a change in FreeBSD SHM implementation from

From
Curt Sampson
Date:
On Fri, 12 Jul 2002, Vivek Khera wrote:

> Sometimes it does, sometimes it does not.  But I still disagree that
> relying on the OS to cache pages that you could (and should) just as
> well keep in the SHM segments already makes no sense.

It's not "just as well," it's "also." If you're caching two copies
of a lot of pages, you're not going to have as many pages in memory.
Very simple.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC