Обсуждение: Postgres crash during low-traffic period, need advice.
Hi, We have a front-end server taking care of Nginx, memcached and Postgresql. Last night the postgres daemon crashed (traffic was very low at that time) with the following error: 2010-12-20 03:34:30 EST DETAIL: Failed system call was shmget(key=5432001, size=1124474880, 03600). 2010-12-20 03:34:30 EST HINT: 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 1124474880 bytes), reduce PostgreSQL's shared_buffers parameter (currently 131072) and/or its max_connections parameter (currently 963). Front-end server has 4GB ram. The reason why max_connections and shared buffers were so high was that we recently had to run the server without a connection pooler because our connection pooler died. We have since fixed the connection pooler, and last night it was running normally. I didn't think there would side effects to leaving Postgresql configured with 1024MB shared_buffers and 963 max_connections Anyways, I have now reduced the shared_buffers and max_connections to : 256MB and 140 respectively. Our connection pooler is configured to establish no more than 128 connections at a time so 140 max_connections will be fine. The problem is that I am not sure what is the ideal shared_buffers settings should be. How should we properly "guesstimate" the correct value for shared_buffers? I saw some people using the values max_connections * 16KB, so should I reduce it to 2.3MB ? Also, was this error caused by kernel.shmmax value being too low? (I just noticed this: Failed system call was shmget(key=5432001, size=1124474880, 03600). Thanks, -- Ben
Zitat von Benoit Clennett-Sirois <benoit@lesite.ca>: > Hi, > > We have a front-end server taking care of Nginx, memcached and > Postgresql. Last night the postgres daemon crashed (traffic was very > low at that time) with the following error: > > 2010-12-20 03:34:30 EST DETAIL: Failed system call was > shmget(key=5432001, size=1124474880, 03600). > 2010-12-20 03:34:30 EST HINT: 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 1124474880 > bytes), reduce PostgreSQL's shared_buffers parameter (currently > 131072) and/or its max_connections parameter (currently 963). For me this looks like you have shared_buffers at the default value of 128kb eg. 131072 Byte which would be way to low for 963 connections... Not sure if this is a reason to crash. Regards Andreas
Вложения
On Mon, Dec 20, 2010 at 6:41 AM, Benoit Clennett-Sirois <benoit@lesite.ca> wrote: > Hi, > > We have a front-end server taking care of Nginx, memcached and > Postgresql. Last night the postgres daemon crashed (traffic was very > low at that time) with the following error: > > 2010-12-20 03:34:30 EST DETAIL: Failed system call was > shmget(key=5432001, size=1124474880, 03600). > 2010-12-20 03:34:30 EST HINT: 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 1124474880 > bytes), reduce PostgreSQL's shared_buffers parameter (currently > 131072) and/or its max_connections parameter (currently 963). Are you sure this is the crash and not the symptom of a restart issue or something? I'd look more carefully through the logs for the PANIC that a crash should cause. I'm guessing you got killed by the OOM killer. 4 Gigs is pitiful for a multi-purpose db / web server, my son's laptop has 8 gigs. What do you have work_mem set to? A high setting there can be quickly fatal since it's per-sort, not total. Generally shared_buffers ~1Gig on a 4Gig machine would be reasonable if it was just a db server. If it's shared with other stuff, drop it down to the 100Meg range.
On Mon, Dec 20, 2010 at 9:29 AM, <lst_hoe02@kwsoft.de> wrote: > Zitat von Benoit Clennett-Sirois <benoit@lesite.ca>: > >> Hi, >> >> We have a front-end server taking care of Nginx, memcached and >> Postgresql. Last night the postgres daemon crashed (traffic was very >> low at that time) with the following error: >> >> 2010-12-20 03:34:30 EST DETAIL: Failed system call was >> shmget(key=5432001, size=1124474880, 03600). >> 2010-12-20 03:34:30 EST HINT: 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 1124474880 >> bytes), reduce PostgreSQL's shared_buffers parameter (currently >> 131072) and/or its max_connections parameter (currently 963). > > For me this looks like you have shared_buffers at the default value of 128kb > eg. 131072 Byte which would be way to low for 963 connections... > Not sure if this is a reason to crash. Actually I think that they mean that I have 131072 buffers * (BLCKSZ value set at compile time) bytes each.. or something like that.. no? It seems that in the documentation, in PG 8.1 you specified the number of buffers and in PG 8.4 (the version i'm using) you specify the amount of memory for shared buffers. -- Ben
work_mem is not set in the config file, so it's using the default setting.. On Mon, Dec 20, 2010 at 9:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Mon, Dec 20, 2010 at 6:41 AM, Benoit Clennett-Sirois > <benoit@lesite.ca> wrote: >> Hi, >> >> We have a front-end server taking care of Nginx, memcached and >> Postgresql. Last night the postgres daemon crashed (traffic was very >> low at that time) with the following error: >> >> 2010-12-20 03:34:30 EST DETAIL: Failed system call was >> shmget(key=5432001, size=1124474880, 03600). >> 2010-12-20 03:34:30 EST HINT: 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 1124474880 >> bytes), reduce PostgreSQL's shared_buffers parameter (currently >> 131072) and/or its max_connections parameter (currently 963). > > Are you sure this is the crash and not the symptom of a restart issue > or something? > > I'd look more carefully through the logs for the PANIC that a crash > should cause. I'm guessing you got killed by the OOM killer. 4 Gigs > is pitiful for a multi-purpose db / web server, my son's laptop has 8 > gigs. What do you have work_mem set to? A high setting there can be > quickly fatal since it's per-sort, not total. > > Generally shared_buffers ~1Gig on a 4Gig machine would be reasonable > if it was just a db server. If it's shared with other stuff, drop it > down to the 100Meg range. >