Обсуждение: Fw: Spin lock error

Hi,

Thanks for responding.
This is a copy of my message which has all details which I could think of to
start with.
I would be glad to receive any suggesstions.

Bye,

Murali
-----Original Message-----
From: Differentiated Software Solutions Pvt. Ltd. <diffs@vsnl.com>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>;
vadim@krs.ru <vadim@krs.ru>; pgsql-interfaces@postgresql.org
<pgsql-interfaces@postgresql.org>
Cc: Kimi <kimi@intercept.co.in>
Date: 11 December 1999 12:33
Subject: Spin lock error


>Hi,
>
>We are getting spinlock errors under the following conditions
>
>Background :
>We have developed a high performance application using postgres database
 a
>3rd party application server).
>This application should be scalanble to server 50 requests a second.
>Application is written in perl 5.005 using DBI-1.12, DBD-Pg 0.92 and Redhat
>linux 6.0
>A request consists of one singleton select on a table of 200,000 rows on
>it's primary key, a heavy duty cursor which is a 4 table join but which
>returns 100 rows from tables containing maximum of 250 rows, a light sql
>opening a cursor on a table containing 500 rows, 2 inserts into tables
which
>don't have primary key.
>
>We are connecting to the database with autoconnect off.
>
>Problem description :
>On a light load there are no problems. As we have increased the load on the
>application, the requests get piled up. We keep monitoring the requests
>getting piled up using ps ax | grep post.
>Somewhere when the requests crosses 20(ps returns more than 20 rows)....
>suddenly postmaster comes down !! In the server log the following message
is
>written out.
>FATAL: s_lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
>
>FATAL: s_lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
>
>FATAL: s_lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
>
>FATAL: s_lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
>
>FATAL: s_lock(40179f44) at bufmgr.c:490, stuck spinlock. Aborting.
>After this postmaster shutdown.
>
>One observation If we try to recreate this in another linux machine...
>multiple postmasters run in the linux machine when the number of requests
>become more.
>I'm also attaching a file containing 'ps' at the instance postmaster
>crashed.
>
>Can anybody please help
>
>Thanks and regards,
>
>Murali
>
>Differentiated Software Solutions Pvt. Ltd.,
>176, Gr. Floor, 6th Main
>2nd Block RT Nagar
>Bangalore - 560 032
>India
>Ph: 91 80 3431470
>

I seem to still be missing what version of PostgreSQL you are running...:(

On Wed, 15 Dec 1999, Differentiated Software Solutions Pvt. Ltd. wrote:

> Hi,
>
> Thanks for responding.
> This is a copy of my message which has all details which I could think of to
> start with.
> I would be glad to receive any suggesstions.
>
> Bye,
>
> Murali
> -----Original Message-----
> From: Differentiated Software Solutions Pvt. Ltd. +ADw-diffs+AEA-vsnl.com+AD4-
> To: pgsql-general+AEA-postgresql.org +ADw-pgsql-general+AEA-postgresql.org+AD4AOw-
> vadim+AEA-krs.ru +ADw-vadim+AEA-krs.ru+AD4AOw- pgsql-interfaces+AEA-postgresql.org
> +ADw-pgsql-interfaces+AEA-postgresql.org+AD4-
> Cc: Kimi +ADw-kimi+AEA-intercept.co.in+AD4-
> Date: 11 December 1999 12:33
> Subject: Spin lock error
>
>
> +AD4-Hi,
> +AD4-
> +AD4-We are getting spinlock errors under the following conditions
> +AD4-
> +AD4-Background :
> +AD4-We have developed a high performance application using postgres database
>  a
> +AD4-3rd party application server).
> +AD4-This application should be scalanble to server 50 requests a second.
> +AD4-Application is written in perl 5.005 using DBI-1.12, DBD-Pg 0.92 and Redhat
> +AD4-linux 6.0
> +AD4-A request consists of one singleton select on a table of 200,000 rows on
> +AD4-it's primary key, a heavy duty cursor which is a 4 table join but which
> +AD4-returns 100 rows from tables containing maximum of 250 rows, a light sql
> +AD4-opening a cursor on a table containing 500 rows, 2 inserts into tables
> which
> +AD4-don't have primary key.
> +AD4-
> +AD4-We are connecting to the database with autoconnect off.
> +AD4-
> +AD4-Problem description :
> +AD4-On a light load there are no problems. As we have increased the load on the
> +AD4-application, the requests get piled up. We keep monitoring the requests
> +AD4-getting piled up using ps ax +AHw- grep post.
> +AD4-Somewhere when the requests crosses 20(ps returns more than 20 rows)....
> +AD4-suddenly postmaster comes down +ACEAIQ- In the server log the following message
> is
> +AD4-written out.
> +AD4-FATAL: s+AF8-lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
> +AD4-
> +AD4-FATAL: s+AF8-lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
> +AD4-
> +AD4-FATAL: s+AF8-lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
> +AD4-
> +AD4-FATAL: s+AF8-lock(4000b011) at spin.c:125, stuck spinlock. Aborting.
> +AD4-
> +AD4-FATAL: s+AF8-lock(40179f44) at bufmgr.c:490, stuck spinlock. Aborting.
> +AD4-After this postmaster shutdown.
> +AD4-
> +AD4-One observation If we try to recreate this in another linux machine...
> +AD4-multiple postmasters run in the linux machine when the number of requests
> +AD4-become more.
> +AD4-I'm also attaching a file containing 'ps' at the instance postmaster
> +AD4-crashed.
> +AD4-
> +AD4-Can anybody please help
> +AD4-
> +AD4-Thanks and regards,
> +AD4-
> +AD4-Murali
> +AD4-
> +AD4-Differentiated Software Solutions Pvt. Ltd.,
> +AD4-176, Gr. Floor, 6th Main
> +AD4-2nd Block RT Nagar
> +AD4-Bangalore - 560 032
> +AD4-India
> +AD4-Ph: 91 80 3431470
> +AD4-
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

On Wed, 15 Dec 1999, diffs@vsnl.com wrote:

>6184  p2 R    0:00 /usr/local/bin/postgres localhost postgres adcept SELECT
>20964  p6 S    0:00 su - postgres
>23112  p4 S    0:00 su - postgres
>27664  p2 R    0:03 /usr/local/bin/postmaster -i
>
>bash: /bin/ps: Too many open files in system

I was getting this recently on a production server, I solved it by increasing
the number of file descriptors per process in the kernel. Do a ulimit -a to see
what your limits are.

Lincoln



--
------------------------------------------------------------------------------

Lincoln Spiteri

Manufacturing Systems
STMicroelectronics, Malta

e-mail: lincoln.spiteri@st.com

------------------------------------------------------------------------------