Re: [HACKERS] 8.1beta, SunOS and shmget
"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes: > On Aug 29, 2005, at 12:41 PM, Tom Lane wrote: >> I just finished going through the initialization sequence to trace the >> calculation of shared memory size, and what I find in CVS tip is that >> it works out like this: > Should the new formulation be sent to pgsql-docs? This looks like it > could be worked into a patch pretty easily. Seems like it would make > sense to update the docs for 8.1... Already done. http://developer.postgresql.org/docs/postgres/kernel-resources.html#SYSVIPC regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.1beta, SunOS and shmget
On Aug 29, 2005, at 12:41 PM, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote: 20 buffers ... ugh. Obviously we are on the hairy edge of no longer functioning at all in 1MB shared memory. I'm not sure there is a whole lot we can do about this, but it's a tad irritating that clog, subtrans, and multixact are eating the equivalent of about 16 buffers (nonconfigurable) while the main buffer pool is so badly starved. 8 buffers each, I think, no? That's 32 buffers total. You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's now 8. Did we bump that up on the basis of any solid evidence? There's 256K of shared memory going into those four dedicated buffer areas, which is kind of a lot when you're hoping to fit into 1MB. I just finished going through the initialization sequence to trace the calculation of shared memory size, and what I find in CVS tip is that it works out like this: shared_buffers * 8314 max_connections * (217.68 * max_locks_per_transaction + 356) max_prepared_transactions * (217.68 * max_locks_per_transaction + 576) wal_buffers * 8192 max_fsm_relations * 70 max_fsm_pages * 6 plus about 500K fixed space (These numbers are on a 32-bit machine, some of the multipliers would be a little higher on 64-bit.) The formula given in the docs doesn't seem to have been updated since 7.2: 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections Most of the bloat since then seems to be accounted for by 2PC and the addition of subtrans and multixact buffers. Maybe we could make them allocate them automatically based on shared_buffers, with a ceiling of 8? Seems like it'd be reasonable to skinny down the number of dedicated buffers when shared_buffers is tiny, but I'm not sure about the particular equation to use. regards, tom lane Should the new formulation be sent to pgsql-docs? This looks like it could be worked into a patch pretty easily. Seems like it would make sense to update the docs for 8.1... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, Aug 29, 2005 at 01:28:22PM -0400, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > On Mon, 29 Aug 2005, Tom Lane wrote: > >> Yes; mostly from 2PC support I think. Try reducing > >> max_prepared_transactions. (We might want to debate whether the default > >> setting should be smaller than 50 --- it looks to me like that's adding > >> over 700K to the default shared memory block size.) > > > In fact, 0 might be reasonable default. Not many people need 2PC. > > Good point, but if we set it to 0 then the prepared-xacts regression > test won't work. We could set it to just enough to run the test, > say five or ten. ISTM it would be better to allow the regression tests to use their own config. It might even be useful to allow the regression tests to use multiple postgresql.conf files as a means to test different things. Of course this might not make it into 8.1, but it seems somewhat backwards to be setting the default config just to satisfy make check. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1beta, SunOS and shmget
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Of course this might not make it into 8.1, but it seems somewhat > backwards to be setting the default config just to satisfy make check. Some of us prefer "make installcheck" ... so I'd still resist setting the defaults to values that would make the regression tests fail. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1beta, SunOS and shmget
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > On Mon, 29 Aug 2005, Tom Lane wrote: >> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer >> functioning at all in 1MB shared memory. I'm not sure there is a whole >> lot we can do about this, but it's a tad irritating that clog, subtrans, >> and multixact are eating the equivalent of about 16 buffers >> (nonconfigurable) while the main buffer pool is so badly starved. > Would it be a good idea to make them share a single pool? (in 8.2, of > course). Possibly, but I'd be worried about increasing contention. Right now each of those modules has a separate lock and so accesses don't interfere with each other. With a single pool I think there'd have to be just one lock. Hard to guess if the effect would be noticeable, though. > And how about making the slru page size smaller? clog, subtrans and > multixact are quite randomly, I think. No, I disagree. The traffic to all these is concentrated heavily on active and recent-past transactions. So mostly what you need is the newest and next-to-newest pages, plus a few free slots for occasional historical lookups. Making the page size smaller would mean that we'd more frequently need to traverse the zero-out-a-new-page logic, which is bad news (particularly for ExtendCLOG, which has to do that while holding XidGenLock). The real question in my mind is whether it's worth expending any effort at all in this area. I think it's inevitable that we'll blow past the magic 1MB mark in a release or two anyway, and it's certainly already true that performance is going to suffer terribly if you have to run in a shared memory segment of that size. So I'm disinclined to put more than minimal work into supporting such configurations. I think we should just document 2MB, or even 4MB, as the minimum usable SHMMAX ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1beta, SunOS and shmget
I wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> 8 buffers each, I think, no? That's 32 buffers total. > You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's > now 8. Did we bump that up on the basis of any solid evidence? Never mind, looks like that goes all the way back: NUM_CLOG_BUFFERS was 8 in 7.2, and we just kept that value. I doubt there's been any testing of different possible values at all ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1beta, SunOS and shmget
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote: >> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer >> functioning at all in 1MB shared memory. I'm not sure there is a whole >> lot we can do about this, but it's a tad irritating that clog, subtrans, >> and multixact are eating the equivalent of about 16 buffers >> (nonconfigurable) while the main buffer pool is so badly starved. > 8 buffers each, I think, no? That's 32 buffers total. You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's now 8. Did we bump that up on the basis of any solid evidence? There's 256K of shared memory going into those four dedicated buffer areas, which is kind of a lot when you're hoping to fit into 1MB. I just finished going through the initialization sequence to trace the calculation of shared memory size, and what I find in CVS tip is that it works out like this: shared_buffers * 8314 max_connections * (217.68 * max_locks_per_transaction + 356) max_prepared_transactions * (217.68 * max_locks_per_transaction + 576) wal_buffers * 8192 max_fsm_relations * 70 max_fsm_pages * 6 plus about 500K fixed space (These numbers are on a 32-bit machine, some of the multipliers would be a little higher on 64-bit.) The formula given in the docs doesn't seem to have been updated since 7.2: 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections Most of the bloat since then seems to be accounted for by 2PC and the addition of subtrans and multixact buffers. > Maybe we could make them allocate them automatically based on > shared_buffers, with a ceiling of 8? Seems like it'd be reasonable to skinny down the number of dedicated buffers when shared_buffers is tiny, but I'm not sure about the particular equation to use. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, 29 Aug 2005, Tom Lane wrote: "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: Yes, the decreasing of max_prepared_transaction helped (after some testing, I've found that the max_prepared_transactions=3 max_connections=10 shared_buffers=20 was well enough to fit 1mb of shared memory) 20 buffers ... ugh. Obviously we are on the hairy edge of no longer functioning at all in 1MB shared memory. I'm not sure there is a whole lot we can do about this, but it's a tad irritating that clog, subtrans, and multixact are eating the equivalent of about 16 buffers (nonconfigurable) while the main buffer pool is so badly starved. It'd be better to reduce their allocations. Would it be a good idea to make them share a single pool? (in 8.2, of course). And how about making the slru page size smaller? clog, subtrans and multixact are quite randomly, I think. - Heikki ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1beta, SunOS and shmget
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > On Mon, 29 Aug 2005, Tom Lane wrote: >> Yes; mostly from 2PC support I think. Try reducing >> max_prepared_transactions. (We might want to debate whether the default >> setting should be smaller than 50 --- it looks to me like that's adding >> over 700K to the default shared memory block size.) > In fact, 0 might be reasonable default. Not many people need 2PC. Good point, but if we set it to 0 then the prepared-xacts regression test won't work. We could set it to just enough to run the test, say five or ten. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, 29 Aug 2005, Tom Lane wrote: "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: So, are the shared memory requirements increased for 8.1 ? Yes; mostly from 2PC support I think. Try reducing max_prepared_transactions. (We might want to debate whether the default setting should be smaller than 50 --- it looks to me like that's adding over 700K to the default shared memory block size.) In fact, 0 might be reasonable default. Not many people need 2PC. Those that do, are arguably better off if they're forced to read the manual they might not otherwise read :). The manual can then give some good advice, like that the application server becomes a critical component with 2PC. And perhaps discuss alternatives application designs to avoid 2PC in the first place. 50 is a bad default anyway. If you have max_connections connections that simultanously issue a prepare, you need to have max_prepared_transactions >= max_connections. You can get away with a smaller value most of the time, but if for example another resource manager "clogs" for a moment, so that the transaction manager can't finish any transactions, you can easily reach the limit. Assuming the application server doesn't reuse a connection before 2nd phase commit (I don't have any hard data on this, but I believe it to be the norm. Please correct me if I'm wrong), max_prepared_transactions = max_connections will make the new incoming transactions queue until a connection becomes available, while max_prepared_transactions < max_connections make the incoming transactions fail at prepare. - Heikki ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote: > "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > > Yes, the decreasing of max_prepared_transaction helped (after some > > testing, I've found that the max_prepared_transactions=3 > > max_connections=10 shared_buffers=20 was well enough to fit 1mb of > > shared memory) > > 20 buffers ... ugh. Obviously we are on the hairy edge of no longer > functioning at all in 1MB shared memory. I'm not sure there is a whole > lot we can do about this, but it's a tad irritating that clog, subtrans, > and multixact are eating the equivalent of about 16 buffers > (nonconfigurable) while the main buffer pool is so badly starved. > It'd be better to reduce their allocations. 8 buffers each, I think, no? That's 32 buffers total. Maybe we could make them allocate them automatically based on shared_buffers, with a ceiling of 8? For example Min(8, ceil(2*log(shared_buffers))) seems to behave nicely. That'd mean 3*4 = 12 buffers when shared_buffers is below 100; and 8*4 = 32 buffers when shared_buffers is above 1. -- Alvaro Herrera () "La Primavera ha venido. Nadie sabe como ha sido" (A. Machado) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, 29 Aug 2005, Andrew Dunstan wrote: > > >unfortunatly max_connections=10 is not enough to run > >fully the make check :(, but I cannot raise more the max_connections > >parameter > > > You can limit the number of connections that "make check" runs. We built > that facility in for cygwin, but it looks like you could use it. > > Try something like: > > MAX_CONNECTIONS=8 make check Thanks, now it works -- and it passed all the tests. By the way, now it seems that Postgres 8.1beta on SunOS 5.8 on Sun Sparc works well (passes all the regression tests) using both gcc (3.2.1), and sun compiler cc (Forte Developer 7 C 5.4). (despite SunOS is not supported platform following the documentation). With Best regards, Sergey * Sergey E. Koposov Max-Planck Institut fuer Astronomie Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1beta, SunOS and shmget
Sergey E. Koposov wrote: unfortunatly max_connections=10 is not enough to run fully the make check :(, but I cannot raise more the max_connections parameter You can limit the number of connections that "make check" runs. We built that facility in for cygwin, but it looks like you could use it. Try something like: MAX_CONNECTIONS=8 make check cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1beta, SunOS and shmget
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > Yes, the decreasing of max_prepared_transaction helped (after some > testing, I've found that the max_prepared_transactions=3 > max_connections=10 shared_buffers=20 was well enough to fit 1mb of > shared memory) 20 buffers ... ugh. Obviously we are on the hairy edge of no longer functioning at all in 1MB shared memory. I'm not sure there is a whole lot we can do about this, but it's a tad irritating that clog, subtrans, and multixact are eating the equivalent of about 16 buffers (nonconfigurable) while the main buffer pool is so badly starved. It'd be better to reduce their allocations. How many people still care about small-shmmax systems? Is it worth flailing around about this, when there are things on the TODO list (like moving LISTEN/NOTIFY signaling into memory) that will certainly push our usage irretrievably past 1MB? One thing I think we should do, though, is teach initdb to modify max_prepared_transactions along with the other principal knobs. I believe that the original intention of the default value of 50 was "half of max_connections", and so I'd be inclined to just make initdb silently set max_prepared_transactions to half of whatever it's setting max_connections to. As is, 2PC is eating a lot more than its fair share of resources --- I've noticed for instance on OS X, with 4MB shmmax by default, that initdb is currently defaulting to just 200 buffers, which is bad. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1beta, SunOS and shmget
On Mon, 29 Aug 2005, Tom Lane wrote: > "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > > So, are the shared memory requirements increased for 8.1 ? > > Yes; mostly from 2PC support I think. Try reducing > max_prepared_transactions. (We might want to debate whether the default > setting should be smaller than 50 --- it looks to me like that's adding > over 700K to the default shared memory block size.) Yes, the decreasing of max_prepared_transaction helped (after some testing, I've found that the max_prepared_transactions=3 max_connections=10 shared_buffers=20 was well enough to fit 1mb of shared memory)(unfortunatly max_connections=10 is not enough to run fully the make check :(, but I cannot raise more the max_connections parameter) But I'm a bit surprised. Since, the initdb didn't worked from the very beginning it was not possible to change postgresql.conf, and I was forced to change the postgres sources initdb.c and guc.c, than recompile and check whether initdb works. Is that normal way to do it? With Best regards, Sergey * Sergey E. Koposov Max-Planck Institut fuer Astronomie Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1beta, SunOS and shmget
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > So, are the shared memory requirements increased for 8.1 ? Yes; mostly from 2PC support I think. Try reducing max_prepared_transactions. (We might want to debate whether the default setting should be smaller than 50 --- it looks to me like that's adding over 700K to the default shared memory block size.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1beta, SunOS and shmget
""Sergey E. Koposov"" <[EMAIL PROTECTED]> wrote > > selecting default max_connections ... 10 > selecting default shared_buffers ... 50 > DETAIL: Failed system call was shmget(key=1, size=1957888, 03600). > > selecting default max_connections ... 10 > selecting default shared_buffers ... 20 > DETAIL: Failed system call was shmget(key=1, size=1712128, 03600). > > selecting default max_connections ... 5 > selecting default shared_buffers ... 16 > DETAIL: Failed system call was shmget(key=1, size=1613824, 03600). > > To me it seems quite strange, since that behaviour does not relate with > the formulae "250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections > up to infinity" from documentation. > If the linear relationship is still held, the shm formula induced from your test is: 1384 kB + 8 kB * shared_buffers + 12.8 kB * max_connections Seems in 8.1, the base shm requirement is increased. But I am not where they are from. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] 8.1beta, SunOS and shmget
Hello, I just found quite a strange behaviour of 8.1beta on SunOS: uname -a: SunOS sun46 5.8 Generic_108528-14 sun4u sparc SUNW,Ultra-80 gcc -v: Reading specs from /systools/bin/../lib/gcc-lib/sparc-sun-solaris2.7/3.2.1/specs Configured with: /disk-c/hiller/gcc-3.2.1/configure --prefix=/systools/misc/gcc/gcc_3.2.1 Thread model: posix gcc version 3.2.1 The shmmax value is around 1 Mb (and I cannot change it, since I'm not root on that machine). It compiles without problems but fails on initdb with the shmget error. DETAIL: Failed system call was shmget(key=1, size=1957888, 03600) So, I tried to lower the max_con and number of shared buffers in initdb.c but it still require too much shared memory: selecting default max_connections ... 10 selecting default shared_buffers ... 50 DETAIL: Failed system call was shmget(key=1, size=1957888, 03600). selecting default max_connections ... 10 selecting default shared_buffers ... 20 DETAIL: Failed system call was shmget(key=1, size=1712128, 03600). selecting default max_connections ... 5 selecting default shared_buffers ... 16 DETAIL: Failed system call was shmget(key=1, size=1613824, 03600). To me it seems quite strange, since that behaviour does not relate with the formulae "250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity" from documentation. Actually this is strange too because with 8.0.3 I don't have such a problems: With the default settings initdb fails with the: default: 1130496 selecting default max_connections ... 10 selecting default shared_buffers ... 50 DETAIL: Failed system call was shmget(key=1, size=1130496, 03600). but when I set number of connections in initdb.c to 15 and number of shared buffers to 30, the initdb works and make check works fine too. So, are the shared memory requirements increased for 8.1 ? or I just see a bug? With Best regards, Sergey Output of configure: ./configure --prefix=$PWD/../pg_inst/ checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ANSI C... none needed checking if gcc supports -Wdeclaration-after-statement... no checking if gcc supports -Wold-style-definition... no checking if gcc supports -Wendif-labels... no checking if gcc supports -fno-strict-aliasing... yes configure: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-str checking whether the C compiler still works... yes checking how to run the C preprocessor... gcc -E checking allow thread-safe client libraries... no checking whether to build with Tcl... no checking whether to build Perl modules... no checking whether to build Python modules... no checking whether to build with Kerberos 5 support... no checking whether to build with PAM support... no checking whether to build with Bonjour support... no checking whether to build with OpenSSL support... no checking for egrep... egrep configure: using CPPFLAGS=-I/systools/include configure: using LDFLAGS=-L/systools/lib/ checking for gawk... gawk checking for flex... /systools/bin/flex checking whether ln -s works... yes checking for ld used by GCC... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for ranlib... ranlib checking for lorder... lorder checking for tar... /bin/tar checking for strip... strip checking whether it is possible to strip libraries... no checking for bison... bison -y configure: WARNING: *** If you are going to modify the grammar files or build from CVS, the install *** version of Bison is too old. Bison version 1.875 or later is required. checking for perl... /bin/perl checking for main in -lbsd... no checking for setproctitle in -lutil... no checking for main in -lm... yes checking for main in -ldl... yes checking for main in -lnsl... yes checking for main in -lsocket... yes checking for main in -lipc... no checking for main in -lIPC... no checking for main in -llc... no checking for main in -ldld... no checking for main in -lld... no checking for main in -lcompat... no checking for main in -lBSD... no checking for main in -lgen... yes checking for main in -lPW... no checking for main in -lresolv... yes checking for library containing getopt_long... no checking for main in -lunix... no checking for library containing crypt... none required checking for library containing fdatasync... -lrt checking for shmget in -lcygipc... no checking for rea
