requested shared memory size overflows size_t

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 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the instance successfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris 10 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.
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

Sincerely yours,

[Description: Celltick logo_highres]
Yuri Levinsky, DBA
Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel
Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222

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 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, the maximum possible value is 4G. When I decrease the configuration parameters and start the instance successfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris 10 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 overflows size_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 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

On Tue, Mar 4, 2014 at 6:05 AM, Yuri Levinsky <yuril(at)celltick(dot)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 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 with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL 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(at)gmail(dot)com]
Sent: Tuesday, March 04, 2014 4:31 PM
To: Yuri Levinsky
Cc: Heikki Linnakangas; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] requested shared memory size overflows size_t

On Tue, Mar 4, 2014 at 6:05 AM, Yuri Levinsky <yuril(at)celltick(dot)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 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

On Tue, Mar 4, 2014 at 9:53 AM, Yuri Levinsky <yuril(at)celltick(dot)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 with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL 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 with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL 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(at)2ndquadrant(dot)com] /define
Sent: Friday, March 14, 2014 4:41 AM
To: Yuri Levinsky; Robert Haas
Cc: Heikki Linnakangas; pgsql-hackers(at)postgresql(dot)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 with sys admin. He may run 32 bit applications as well. Have I use some special option during compilation to verify that compiled PostgreSQL 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

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(at)2ndquadrant(dot)com]
Sent: Sunday, March 16, 2014 2:40 PM
To: Yuri Levinsky; Robert Haas
Cc: Heikki Linnakangas; pgsql-hackers(at)postgresql(dot)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 environment variable or set architecture-specific CFLAGS.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services