Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)

Hi,

On Fri, 2007-08-31 at 18:11 -0700, Darek Czarkowski wrote:

when I try to increase number of connections to 200 I get
"IpcSemaphoreCreate: semget(key=5432120, num=17, 03600) failed: No
space left on device" Error

Now, I dont think the default system settings are too low, default
settings for semafores are kernel.sem = 250 32000 100 128

You should increase shmmax. Add this to sysctl.conf:

kernel.shmmax=$TheValueYouNeed

(and then run sysctl -p)

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

I am have installed postgresql-7.3.4 on Linux server running Red Hat Enterp=
rise Linux Server release 5 (Tikanga).

Why in heavens name would you be wanting to run 7.3.4? Even if you have
a good reason for wanting 7.3.x, you should be installing 7.3.19. We
don't update back branches just because we have nothing better to do.
Read the release notes starting here:
http://developer.postgresql.org/pgdocs/postgres/release-7-3-19.html
and then get onto a more recent version.

Postmaster runs fine with the default settings, but when I try to increase =
number of connections to 200 I get
"IpcSemaphoreCreate: semget(key=5432120, num=17, 03600) failed: No spac=
e left on device" Error

Hmm ... I suppose you are hitting the SEMMNI limit, but unless there is
something else eating semaphores in the system, you should be able to go
way higher than that with the default Red Hat settings. (I can run more
than 2000 connections without changing the defaults on a Fedora 6
machine.)

What I suspect is that there is something you didn't tell us about this
machine, like that it's not a vanilla x86-architecture machine, and that
PG 7.3.4 doesn't have any spinlock code for it. In that case your build
would have fallen back to using semaphores for spinlocks, which aside
from being horrendously slow eats many more semaphores than normal (I
think two per shared buffer, for one thing). In this connection it is
worth pointing to the 7.3.16 release notes ...

regards, tom lane

Thank you for comments, did I mention the system is a beast?
# cat /proc/sys/kernel/shmmax
68719476736

It can not be the resource limit, it has to be something else. I assume this version of postgres is incompatible with RedHat ES 5. Changing to a newer version of postgres is not an option for now. It would take too much time to rewrite the application to work with the changes introduced in the lastest realeses. I have tried PG 7.3.17 version an it works fine, I just can not use it.

--
Darek Czarkowski

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

It can not be the resource limit, it has to be something else. I assume thi=
s version of postgres is incompatible with RedHat ES 5. Changing to a newer=
version of postgres is not an option for now. It would take too much time =
to rewrite the application to work with the changes introduced in the laste=
st realeses. I have tried PG 7.3.17 version an it works fine, I just can no=
t use it.

What changes would those be? If your app works on 7.3.4 it should work
with 7.3.17.

regards, tom lane

On 9/6/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

It can not be the resource limit, it has to be something else. I assume thi=
s version of postgres is incompatible with RedHat ES 5. Changing to a newer=
version of postgres is not an option for now. It would take too much time =
to rewrite the application to work with the changes introduced in the laste=
st realeses. I have tried PG 7.3.17 version an it works fine, I just can no=
t use it.

What changes would those be? If your app works on 7.3.4 it should work
with 7.3.17.

Actually, from what he wrote, I take it that 7.3.17 works fine, but
some insane policy where he works demands he use 7.3.4

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

On 9/6/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

What changes would those be? If your app works on 7.3.4 it should work
with 7.3.17.

Actually, from what he wrote, I take it that 7.3.17 works fine, but
some insane policy where he works demands he use 7.3.4

Nah, if they were that troglodytic they'd hardly let him use an OS as
newfangled as RHEL-5 (even RHEL-4 shipped with PG 7.4.x). I read him to
say that PG 7.4 and up contain changes that break his app, which could
be a fair complaint. But if it doesn't work on 7.3.latest then there's
something pretty wrong with it.

regards, tom lane

I am sorry, it seams that my last replay never reached the list.

I was looking for the list of changes for each version, but this document is bundled with the source. I believe it is the fix introduced in version 7.3.5:
"* Fix insertion of expressions containing subqueries into rule bodies"
Above line is from HISTORY file, and our application is just abusing this feature. Upgrade to newest version of Postgres is on our development list for the last few years, but we are just too busy with new projects, and don't have enough resources to commit to it. You have to understand that an upgrade would halt any development for an unspecified time.

Getting new version of operating system is not that much of a problem, we trust Redhat to deliver reliable operating system, and we do build PostgresSQL from source anyway.

I have settled for 250 connections and 810 shared buffers, this is just to get the server ready for deployment. Rising any of the settings makes postmaster fail. I think there must be something that makes Postgres register server settings incorrectly because they exceed the available resources as compared to average system from the time of the release of the database.

regards,
Darek Czarkowski

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

I am sorry, it seams that my last replay never reached the list.
I was looking for the list of changes for each version, but this document i=
s bundled with the source. I believe it is the fix introduced in version 7.=
3.5:
"* Fix insertion of expressions containing subqueries into rule bodies"
Above line is from HISTORY file, and our application is just abusing this f=
eature.

BTW, I checked the CVS logs, and AFAICT this entry refers to the bug
and patch shown in this thread:
http://archives.postgresql.org/pgsql-general/2003-10/msg00888.php
I'd be interested to know how your application is depending on the
ability to get an "ExecEvalExpr: unknown expression type 108" failure.

regards, tom lane

Tom Lane wrote:

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

I am sorry, it seams that my last replay never reached the list.
I was looking for the list of changes for each version, but this document i=
s bundled with the source. I believe it is the fix introduced in version 7.=
3.5:
"* Fix insertion of expressions containing subqueries into rule bodies"
Above line is from HISTORY file, and our application is just abusing this f=
eature.

BTW, I checked the CVS logs, and AFAICT this entry refers to the bug
and patch shown in this thread:
http://archives.postgresql.org/pgsql-general/2003-10/msg00888.php
I'd be interested to know how your application is depending on the
ability to get an "ExecEvalExpr: unknown expression type 108" failure.

regards, tom lane

I am not sure what exactly are you looking for? No one is writing code
that depends on failures :-)

--
Darek Czarkowski

Darek Czarkowski <dczarkowski@infinitesource.ca> writes:

Tom Lane wrote:

BTW, I checked the CVS logs, and AFAICT this entry refers to the bug
and patch shown in this thread:
http://archives.postgresql.org/pgsql-general/2003-10/msg00888.php
I'd be interested to know how your application is depending on the
ability to get an "ExecEvalExpr: unknown expression type 108" failure.

I am not sure what exactly are you looking for? No one is writing code
that depends on failures :-)

I'm looking for the reason you're not using 7.3.something-recent,
because that one isn't a good reason...

regards, tom lane