Thread: requested shared memory size overflows size_t
<div class="WordSection1"><p class="MsoNormal"><span style="font-family:"Georgia","serif";color:black">Dear Developers,</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">Please help with the followingproblem. I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32G swap and 16G RAM. I usesame configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, themaximum possible value is 4G. When I decrease the configuration parameters and start the instance successfully: some queriesfails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris 10 and big enough. Theonly one difference is: Solaris 9 PostgreSQL version, in opposite to Solaris 10 and Linux, was compiled by me with defaultoptions. </span><p class="MsoNormal"><span style="font-family:"Georgia","serif";color:black">My kernel is:</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semmap=64</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semmni=4096</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semmns=4096</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semmnu=4096</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semume=64</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set semsys:seminfo_semmsl=500</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">set shmsys:shminfo_shmmax=0xffffffffffff</span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">setshmsys:shminfo_shmmin=100</span><p class="MsoNormal"><span style="font-family:"Georgia","serif";color:black">setshmsys:shminfo_shmmni=4096</span><p class="MsoNormal"><span style="font-family:"Georgia","serif";color:black">setshmsys:shminfo_shmseg=100</span><p class="MsoNormal"><span style="font-family:"Georgia","serif";color:black"> </span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black">Config.</span><pclass="MsoNormal">shared_buffers = 3GB<p class="MsoNormal">temp_buffers= 2GB <p class="MsoNormal">work_mem = 1024MB<span style="font-family:"Georgia","serif";color:black"></span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black"> </span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black"> </span><pclass="MsoNormal"><span style="font-family:"Georgia","serif";color:black"> </span><pclass="MsoNormal"><i><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#111111">Sincerelyyours</span></i><span style="color:black">,</span><pclass="MsoNormal"><span style="color:black"> </span><p class="MsoNormal"><span style="color:black"><imgalt="Description: Celltick logo_highres" height="59" id="Picture_x0020_1" src="cid:image002.jpg@01CF37A1.222583E0"width="161" /></span><p class="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:black">YuriLevinsky, DBA</span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:black"></span><pclass="MsoNormal"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:black">CelltickTechnologies Ltd., 32 Maskit St., Herzliya 46733,Israel</span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:black"></span><p class="MsoNormal"><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif";color:black">Mobile: +972 54 6107703, Office:+972 9 9710239; Fax: +972 9 9710222</span><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:black"></span><pclass="MsoNormal"> </div>
On 03/04/2014 11:59 AM, Yuri Levinsky wrote: > Dear Developers, > Please help with the following problem. I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32Gswap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable toset shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the instancesuccessfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris10 and big enough. The only one difference is: Solaris 9 PostgreSQL version, in opposite to Solaris 10 and Linux, was compiled by me with default options. Note that if a query fails with "out of memory", it does *not* mean that you should increase shared_buffers. On the contrary: the higher you set shared_buffers, the less memory there is left for other things. > My kernel is: > set semsys:seminfo_semmap=64 > set semsys:seminfo_semmni=4096 > set semsys:seminfo_semmns=4096 > set semsys:seminfo_semmnu=4096 > set semsys:seminfo_semume=64 > set semsys:seminfo_semmsl=500 > set shmsys:shminfo_shmmax=0xffffffffffff > set shmsys:shminfo_shmmin=100 > set shmsys:shminfo_shmmni=4096 > set shmsys:shminfo_shmseg=100 > > Config. > shared_buffers = 3GB > temp_buffers = 2GB > work_mem = 1024MB temp_buffers = 2GB seems very high. That settings is *per backend*, so if you have 10 backends that all use temporary tables, they will consume 20GB altogether for temp buffers. work_mem works similarly, except that a single query can use many times work_mem even in a single backend, so you need to be even more conservative with that. 1GB seems very high for work_mem. Try resetting these back to the defaults, and see if that works for you. Increase them gradually, and only if you have a query where the higher value really helps. - Heikki
Heikki, I changed postgresql.conf to decrease those parameters but no change: GMT54000FATAL: requested shared memory size overflowssize_t > My kernel is: > set semsys:seminfo_semmap=64 > set semsys:seminfo_semmni=4096 > set semsys:seminfo_semmns=4096 > set semsys:seminfo_semmnu=4096 > set semsys:seminfo_semume=64 > set semsys:seminfo_semmsl=500 > set shmsys:shminfo_shmmax=0xffffffffffff > set shmsys:shminfo_shmmin=100 > set shmsys:shminfo_shmmni=4096 > set shmsys:shminfo_shmseg=100 > shared_buffers = 5GB # min 16 or max_connections*2, 8KB each temp_buffers = 256MB # min 100, 8KB each max_prepared_transactions = 1000 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. work_mem = 256MB # min 64, size in KB maintenance_work_mem = 256MB # min 1024, size in KB max_stack_depth = 4MB [L.Y.] temp_buffers = 2GB seems very high. That settings is *per backend*, so if you have 10 backends that all use temporary tables,they will consume 20GB altogether for temp buffers. work_mem works similarly, except that a single query can use manytimes work_mem even in a single backend, so you need to be even more conservative with that. 1GB seems very high forwork_mem. Try resetting these back to the defaults, and see if that works for you. Increase them gradually, and only ifyou have a query where the higher value really helps. - Heikki
On Tue, Mar 4, 2014 at 6:05 AM, Yuri Levinsky <yuril@celltick.com> wrote: > I changed postgresql.conf to decrease those parameters but no change: GMT54000FATAL: requested shared memory size overflowssize_t I think this means you are running on a 32-bit operating system, or at least on a 32-bit build. That means you can't use more than 4GB of address space per process, which has to fit shared_buffers and everything else. Typically it's best not to set shared_buffers above 2-2.5GB on such systems, but the real solution is to use a 64-bit PostgreSQL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert, Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it withsys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiledPostgreSQL is actually 64 bit app? Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -----Original Message----- From: Robert Haas [mailto:robertmhaas@gmail.com] Sent: Tuesday, March 04, 2014 4:31 PM To: Yuri Levinsky Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On Tue, Mar 4, 2014 at 6:05 AM, Yuri Levinsky <yuril@celltick.com> wrote: > I changed postgresql.conf to decrease those parameters but no change: > GMT54000FATAL: requested shared memory size overflows size_t I think this means you are running on a 32-bit operating system, or at least on a 32-bit build. That means you can't usemore than 4GB of address space per process, which has to fit shared_buffers and everything else. Typically it's bestnot to set shared_buffers above 2-2.5GB on such systems, but the real solution is to use a 64-bit PostgreSQL. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Mar 4, 2014 at 9:53 AM, Yuri Levinsky <yuril@celltick.com> wrote: > Robert, > Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it withsys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiledPostgreSQL is actually 64 bit app? Sorry, I'm not familiar with compiling PostgreSQL on Solaris. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 03/04/2014 10:53 PM, Yuri Levinsky wrote: > Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it withsys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiledPostgreSQL is actually 64 bit app? Many platforms include both 32-bit and 64-bit target toolchains. So you might be on a 64-bit platform, but that doesn't mean you aren't compiling a 32-bit executable. Please run: grep '^#define SIZEOF' config.log and post the results. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Dear Craig, The output is: #define SIZEOF_OFF_T 8 #define SIZEOF_VOID_P 4 #define SIZEOF_SIZE_T 4 #define SIZEOF_LONG 4 Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --enable-shared --enable-languages=c,c++,f77 Thread model: posix gcc version 3.4.6 Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -----Original Message----- From: Craig Ringer [mailto:craig@2ndquadrant.com] /define Sent: Friday, March 14, 2014 4:41 AM To: Yuri Levinsky; Robert Haas Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On 03/04/2014 10:53 PM, Yuri Levinsky wrote: > Please advise me: I just downloaded the source and compiled it. Sun Spark Solaris 9 is always 64 bit, I verified it withsys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiledPostgreSQL is actually 64 bit app? Many platforms include both 32-bit and 64-bit target toolchains. So you might be on a 64-bit platform, but that doesn't meanyou aren't compiling a 32-bit executable. Please run: grep '^#define SIZEOF' config.log and post the results. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 03/16/2014 06:57 PM, Yuri Levinsky wrote: > Dear Craig, > The output is: > #define SIZEOF_OFF_T 8 > #define SIZEOF_VOID_P 4 > #define SIZEOF_SIZE_T 4 OK, that confirms you have done a 32-bit build. You need to figure out how to invoke the 64-bit toolchain on your Solaris version. You might need to alter the PATH environment variable or set architecture-specific CFLAGS. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Craig, Is this output correct now? #define SIZEOF_OFF_T 8 #define SIZEOF_VOID_P 8 #define SIZEOF_SIZE_T 8 #define SIZEOF_LONG 8 Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -----Original Message----- From: Craig Ringer [mailto:craig@2ndquadrant.com] Sent: Sunday, March 16, 2014 2:40 PM To: Yuri Levinsky; Robert Haas Cc: Heikki Linnakangas; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] requested shared memory size overflows size_t On 03/16/2014 06:57 PM, Yuri Levinsky wrote: > Dear Craig, > The output is: > #define SIZEOF_OFF_T 8 > #define SIZEOF_VOID_P 4 > #define SIZEOF_SIZE_T 4 OK, that confirms you have done a 32-bit build. You need to figure out how to invoke the 64-bit toolchain on your Solaris version. You might need to alter the PATH environmentvariable or set architecture-specific CFLAGS. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 16 Mar 2014 22:06, Yuri Levinsky <yuril@celltick.com> wrote: > > Craig, > Is this output correct now? > #define SIZEOF_OFF_T 8 > #define SIZEOF_VOID_P 8 Well, you are getting 64-bit pointers. So if you are trying to produce a 64-bit executable then yes, this looks appropriate.