Обсуждение: was there a change in FreeBSD SHM implementation from 4.4 to 4.6? (postgres trouble)
was there a change in FreeBSD SHM implementation from 4.4 to 4.6? (postgres trouble)
От
Vivek Khera
Дата:
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/
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
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 ==========================================================================
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. > >
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
>>>>> "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/
>>>>> "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/
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
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
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
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
>>>>> "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/
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