Обсуждение: Memory limits and PostgreSQL 8.4 - Over 63 GB of memory for DB?

On Thu, 2011-03-17 at 17:46 +0200, Tapio Pitkäranta wrote:

> We are running PostgreSQL 8.4 on a server with Linux CentOS with 144
> GB of memory.
>
> We have not been able to assign over 63 GB of memory for the Postgres:
> if we try to do that, we are not able to start the postgresql server
> process.

You are probably hitting kernel.shmmax value in /etc/sysctl.conf .

But... which parameter are you trying to set to 63GB?

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

Hello,

Thank you for the reply. It seems you might be right:

/etc/sysctl.conf

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

We have tried to set shared_buffers over 63 GB.

Do you have any advice on memory settings for servers with large amounts of memory (100-200GB)? It seems there is not
toomuch documentation on that in the net.
 

Br,

Tapsa

--

Tapio Pitkäranta
RELEX Oy
Valimotie 27, 00380 Helsinki
puhelin: 050-5408550
email: tapio.pitkaranta@relex.fi
internet: http://www.relex.fi





-----Original Message-----
From: Devrim GÜNDÜZ [mailto:devrim@gunduz.org] 
Sent: 18. maaliskuuta 2011 9:22
To: Tapio Pitkäranta
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Memory limits and PostgreSQL 8.4 - Over 63 GB of memory for DB?

On Thu, 2011-03-17 at 17:46 +0200, Tapio Pitkäranta wrote:

> We are running PostgreSQL 8.4 on a server with Linux CentOS with 144 
> GB of memory.
> 
> We have not been able to assign over 63 GB of memory for the Postgres:
> if we try to do that, we are not able to start the postgresql server 
> process.

You are probably hitting kernel.shmmax value in /etc/sysctl.conf . 

But... which parameter are you trying to set to 63GB?

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat
CertifiedEngineer
 
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org  Twitter:
http://twitter.com/devrimgunduz

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Tapio Pitkäranta
> Sent: Friday, March 18, 2011 4:10 AM
> To: Devrim GÜNDÜZ
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Memory limits and PostgreSQL 8.4 - Over 63 GB of
> memory for DB?
> 
> Hello,
> 
> Thank you for the reply. It seems you might be right:
> 
> /etc/sysctl.conf
> 
> # Controls the maximum shared segment size, in bytes
> kernel.shmmax = 68719476736
> 
> # Controls the maximum number of shared memory segments, in pages
> kernel.shmall = 4294967296
> 
> We have tried to set shared_buffers over 63 GB.
> 
> Do you have any advice on memory settings for servers with large
> amounts of memory (100-200GB)? It seems there is not too much
> documentation on that in the net.

This is unlikely to work out as you expect.  Values for shared buffers over the 8-10GB range aren't recommended.  It
mayneed to be much lower, depending on your workload. 
 

As far as recommendations - try and gauge the size of your working data set and size the shared buffers for that.  From
there- test with your workload, and watch out for checkpoint spikes.
 

Unused memory will still be available to the filesystem to cache data there.

Brad.

> -----Original Message-----
> From: Tapio Pitkäranta [mailto:Tapio.Pitkaranta@relex.fi]
> Sent: Monday, March 21, 2011 7:53 AM
> To: Nicholson, Brad (Toronto, ON, CA)
> Cc: pgsql-admin@postgresql.org; Devrim GÜNDÜZ
> Subject: RE: [ADMIN] Memory limits and PostgreSQL 8.4 - Over 63 GB of
> memory for DB?
> 
> Hello Brad,
> 
> Thank you for this information.
> 
> We have database tables that are around 50-100 GB each (table). While
> processing such tables, it seems to be crucial that the table fits into
> memory (especially if the database table is not on a SSD drive).
> 
> Until now we have thought "shared_buffers" parameter should be more
> than the size of the biggest table (that requires this kind of batch
> processing).
> 
> Do you think it does not matter what size we set the "shared_buffers"
> parameter, as long as the server has enough memory? (Even if the single
> table is this size: 50-100 GB)
> 
> Why are large shared buffers not recommended?

There is the potential to dirty a lot buffers and have huge pauses at checkpoint time when those get flushed to disk.
Rememberthough that unused memory is going to go to your filesystem cache and there is very good chance that a lot of
thetables you are accessing are still going to be in memory.
 

By all means, test it out.  It may be that it is fine with your workload, particularly if it is read only.  But be
preparedfor it to not work out as expected.
 

Brad.

Hello Brad,

Thank you for this information.

We have database tables that are around 50-100 GB each (table). While processing such tables, it seems to be crucial
thatthe table fits into memory (especially if the database table is not on a SSD drive). 
 

Until now we have thought "shared_buffers" parameter should be more than the size of the biggest table (that requires
thiskind of batch processing).
 

Do you think it does not matter what size we set the "shared_buffers" parameter, as long as the server has enough
memory?(Even if the single table is this size: 50-100 GB) 
 

Why are large shared buffers not recommended?

Br,

Tapsa


--

Tapio Pitkäranta
RELEX Oy
Valimotie 27, 00380 Helsinki
puhelin: 050-5408550
email: tapio.pitkaranta@relex.fi
internet: http://www.relex.fi





-----Original Message-----
From: Nicholson, Brad (Toronto, ON, CA) [mailto:bnicholson@hp.com] 
Sent: 18. maaliskuuta 2011 16:17
To: Tapio Pitkäranta; Devrim GÜNDÜZ
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Memory limits and PostgreSQL 8.4 - Over 63 GB of memory for DB?

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- 
> owner@postgresql.org] On Behalf Of Tapio Pitkäranta
> Sent: Friday, March 18, 2011 4:10 AM
> To: Devrim GÜNDÜZ
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Memory limits and PostgreSQL 8.4 - Over 63 GB of 
> memory for DB?
> 
> Hello,
> 
> Thank you for the reply. It seems you might be right:
> 
> /etc/sysctl.conf
> 
> # Controls the maximum shared segment size, in bytes kernel.shmmax = 
> 68719476736
> 
> # Controls the maximum number of shared memory segments, in pages 
> kernel.shmall = 4294967296
> 
> We have tried to set shared_buffers over 63 GB.
> 
> Do you have any advice on memory settings for servers with large 
> amounts of memory (100-200GB)? It seems there is not too much 
> documentation on that in the net.

This is unlikely to work out as you expect.  Values for shared buffers over the 8-10GB range aren't recommended.  It
mayneed to be much lower, depending on your workload. 
 

As far as recommendations - try and gauge the size of your working data set and size the shared buffers for that.  From
there- test with your workload, and watch out for checkpoint spikes.
 

Unused memory will still be available to the filesystem to cache data there.

Brad.