multiple postgresql servers in multiple jails?

[Oliver Peter]

Does anybody have a running system with more than one jail hosting
more than one postgres server?

I can only have one pgsql database on one host at all. I already
tried to increase the shared memory off my machine with additional
kernel [1] and sysctl parameters [2] and I also tried to change
the numeric UID directly in the jails into a seperate one. Same
errors.

Of course I already have defined jail_sysvipc_allow="YES" in
rc.conf. I have this issue on 6.2-RELEASE-p8 and 8.0-CURRENT with
postgresql-server-8.2.5_1.

------------------------------------------------------------------------
% psql
psql: FATAL: semctl(458753, 15, SETVAL, 0) failed: Invalid argument
------------------------------------------------------------------------

Or some fun with perl/DBD
------------------------------------------------------------------------
Out of memory during request for 108 bytes, total sbrk() is 534585344 bytes!
Out of memory during request for 288 bytes, total sbrk() is 534585344 bytes!
Out of memory during request for 288 bytes, total sbrk() is 534585344 bytes!
------------------------------------------------------------------------

[1]
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120

[2] http://www.freebsddiary.org/jail-multiple.php

[Oliver Fromme]

Post by Oliver Peter
Does anybody have a running system with more than one jail hosting
more than one postgres server?

Yes, you must configure them to use different port numbers,
because the SysV IPC IDs are derived from the port number.
If you try to run both servers with the default port, you'll
get a conflict. Configure different port numbers, and it
will work.

By the way, the PostgreSQL developers do _not_ recommend to
run multiple servers on the same machine, because of bad
efficiency. It is much better (performance-wise) to run
all databases within the same server engine. PostgreSQL
has all the authentication and permission features you need
to separate multiple databases within a single server, so
there is really no need to use multiple jails.

Post by Oliver Peter
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120

I have these on a machine with a single PostgreSQL server,
as per recommendations of the developers:

options SHMMAXPGS=65536
options SEMMAP=1024
options SEMMNI=64
options SEMMNS=1024
options SEMUME=64
options SEMMNU=128

Best regards
Oliver
--
Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing b. M.
Handelsregister: Registergericht Muenchen, HRA 74606, Geschäftsfuehrung:
secnetix Verwaltungsgesellsch. mbH, Handelsregister: Registergericht Mün-
chen, HRB 125758, Geschäftsführer: Maik Bachmann, Olaf Erb, Ralf Gebhart

FreeBSD-Dienstleistungen, -Produkte und mehr: http://www.secnetix.de/bsd

"C++ is the only current language making COBOL look good."
-- Bertrand Meyer

[Oliver Peter]

Post by Oliver Fromme

Post by Oliver Peter
Does anybody have a running system with more than one jail hosting
more than one postgres server?

Yes, you must configure them to use different port numbers,
because the SysV IPC IDs are derived from the port number.
If you try to run both servers with the default port, you'll
get a conflict. Configure different port numbers, and it
will work.

Thanks for that hint. After changing the port numbers for each
instance to a different one, it works - but only at first glance.
After making some test creates and inserts to all servers at the same
time I receive the 'out of memory' messages again...
Different UIDs, different ports, same error.

Post by Oliver Fromme
By the way, the PostgreSQL developers do _not_ recommend to
run multiple servers on the same machine, because of bad
efficiency. It is much better (performance-wise) to run
all databases within the same server engine. PostgreSQL
has all the authentication and permission features you need
to separate multiple databases within a single server, so
there is really no need to use multiple jails.

Of course I understand that. But I would like to setup a test server
in a different jail beside my production jail/pgsql-server to be sure
not to crash my production server.
Performance does not matter (at this time in this specific case of
course!).

Post by Oliver Fromme
I have these on a machine with a single PostgreSQL server,
options SHMMAXPGS=65536
options SEMMAP=1024
options SEMMNI=64
options SEMMNS=1024
options SEMUME=64
options SEMMNU=128

Is this a good recommendation for a machine with only 1GB of RAM?

[Oliver Fromme]

Post by Oliver Peter

Post by Oliver Fromme

Post by Oliver Peter
Does anybody have a running system with more than one jail hosting
more than one postgres server?

Yes, you must configure them to use different port numbers,
because the SysV IPC IDs are derived from the port number.
If you try to run both servers with the default port, you'll
get a conflict. Configure different port numbers, and it
will work.

Thanks for that hint. After changing the port numbers for each
instance to a different one, it works - but only at first glance.
After making some test creates and inserts to all servers at the same
time I receive the 'out of memory' messages again...
Different UIDs, different ports, same error.

Can you please give the output from "ipcs -M" and "ipcs -p"?

The latter shows the process IDs of the creator (CPID) and
last user (LPID) of shared memory resources. You can look
up the jails in which those processes are running, so to
make sure that none of the resources was taken over by a
different jail.

The "jps" script could be useful:

http://www.secnetix.de/~olli/scripts/jps

By the way, "ipcs -bm" displays the shared memory segment
sizes.

Post by Oliver Peter

Post by Oliver Fromme
By the way, the PostgreSQL developers do _not_ recommend to
run multiple servers on the same machine, because of bad
efficiency. It is much better (performance-wise) to run
all databases within the same server engine. PostgreSQL
has all the authentication and permission features you need
to separate multiple databases within a single server, so
there is really no need to use multiple jails.

Of course I understand that. But I would like to setup a test server
in a different jail beside my production jail/pgsql-server to be sure
not to crash my production server.
Performance does not matter (at this time in this specific case of
course!).

I see. In that case it is probably OK.

Post by Oliver Peter

Post by Oliver Fromme
I have these on a machine with a single PostgreSQL server,
options SHMMAXPGS=65536
options SEMMAP=1024
options SEMMNI=64
options SEMMNS=1024
options SEMUME=64
options SEMMNU=128

Is this a good recommendation for a machine with only 1GB of RAM?

In fact, the above lines are from a machine with 256 MB RAM.
For 1 GB you can probably double the number of shared memory
pages (SHMMAXPGS value). The semaphore values (SEM*) should
be sufficient in either case, I think.

Best regards
Oliver
--
Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing b. M.
Handelsregister: Registergericht Muenchen, HRA 74606, Geschäftsfuehrung:
secnetix Verwaltungsgesellsch. mbH, Handelsregister: Registergericht Mün-
chen, HRB 125758, Geschäftsführer: Maik Bachmann, Olaf Erb, Ralf Gebhart

FreeBSD-Dienstleistungen, -Produkte und mehr: http://www.secnetix.de/bsd

"C is quirky, flawed, and an enormous success."
-- Dennis M. Ritchie.

[Anton - Valqk]

You can alternatively set the SEM* and SHMMAX in the loader.conf.
I have a server runing two pgs.

#> cat /boot/loader.conf
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.semmap=256

Post by Oliver Fromme

Post by Oliver Peter

Post by Oliver Fromme

Post by Oliver Peter
Does anybody have a running system with more than one jail hosting
more than one postgres server?

Yes, you must configure them to use different port numbers,
because the SysV IPC IDs are derived from the port number.
If you try to run both servers with the default port, you'll
get a conflict. Configure different port numbers, and it
will work.

Thanks for that hint. After changing the port numbers for each
instance to a different one, it works - but only at first glance.
After making some test creates and inserts to all servers at the same
time I receive the 'out of memory' messages again...
Different UIDs, different ports, same error.

Can you please give the output from "ipcs -M" and "ipcs -p"?
The latter shows the process IDs of the creator (CPID) and
last user (LPID) of shared memory resources. You can look
up the jails in which those processes are running, so to
make sure that none of the resources was taken over by a
different jail.
http://www.secnetix.de/~olli/scripts/jps
By the way, "ipcs -bm" displays the shared memory segment
sizes.

Post by Oliver Peter

Post by Oliver Fromme
By the way, the PostgreSQL developers do _not_ recommend to
run multiple servers on the same machine, because of bad
efficiency. It is much better (performance-wise) to run
all databases within the same server engine. PostgreSQL
has all the authentication and permission features you need
to separate multiple databases within a single server, so
there is really no need to use multiple jails.

Of course I understand that. But I would like to setup a test server
in a different jail beside my production jail/pgsql-server to be sure
not to crash my production server.
Performance does not matter (at this time in this specific case of
course!).

I see. In that case it is probably OK.

Post by Oliver Peter

Post by Oliver Fromme
I have these on a machine with a single PostgreSQL server,
options SHMMAXPGS=65536
options SEMMAP=1024
options SEMMNI=64
options SEMMNS=1024
options SEMUME=64
options SEMMNU=128

Is this a good recommendation for a machine with only 1GB of RAM?

In fact, the above lines are from a machine with 256 MB RAM.
For 1 GB you can probably double the number of shared memory
pages (SHMMAXPGS value). The semaphore values (SEM*) should
be sufficient in either case, I think.
Best regards
Oliver

[Oliver Peter]

Post by Anton - Valqk
You can alternatively set the SEM* and SHMMAX in the loader.conf.
I have a server runing two pgs.
#> cat /boot/loader.conf
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.semmap=256

Thanks buddy.
Currently I try it with 4 :) Is this a bad idea?

Do you have the servers running within seperated jails?

[Oliver Peter]

Post by Oliver Fromme
...
Can you please give the output from "ipcs -M" and "ipcs -p"?

Here you go:

% sudo ipcs -M
shminfo:
shmmax: 134217728 (max shared memory segment size)
shmmin: 1 (min shared memory segment size)
shmmni: 192 (max number of shared memory identifiers)
shmseg: 128 (max shared memory segments per process)
shmall: 65536 (max amount of shared memory in pages)


% sudo ipcs -p
Message Queues:
T ID KEY MODE OWNER GROUP LSPID LRPID

Shared Memory:
T ID KEY MODE OWNER GROUP CPID LPID
m 65536 5432001 --rw------- 70 70 1064 1064
m 131073 5473001 --rw------- 1073 70 6293 6293
m 131074 5472001 --rw------- 1072 70 6545 6545
m 131075 5471001 --rw------- 1071 70 6797 6797

Semaphores:
T ID KEY MODE OWNER GROUP
s 65536 5432001 --rw------- 70 70
s 65537 5432002 --rw------- 70 70
s 65538 5432003 --rw------- 70 70
s 65539 5432004 --rw------- 70 70
s 65540 5432005 --rw------- 70 70
s 65541 5432006 --rw------- 70 70
s 65542 5432007 --rw------- 70 70
s 131079 5473001 --rw------- 1073 70
s 131080 5473002 --rw------- 1073 70
s 131081 5473003 --rw------- 1073 70
s 131082 5473004 --rw------- 1073 70
s 131083 5473005 --rw------- 1073 70
s 131084 5473006 --rw------- 1073 70
s 131085 5473007 --rw------- 1073 70
s 131086 5472001 --rw------- 1072 70
s 131087 5471001 --rw------- 1071 70
s 131088 5471002 --rw------- 1071 70
s 131089 5471003 --rw------- 1071 70
s 131090 5471004 --rw------- 1071 70
s 131091 5471005 --rw------- 1071 70
s 131092 5471006 --rw------- 1071 70
s 131093 5471007 --rw------- 1071 70


% sudo ipcs -bm
Shared Memory:
T ID KEY MODE OWNER GROUP SEGSZ
m 65536 5432001 --rw------- 70 70 38076416
m 131073 5473001 --rw------- 1073 70 38076416
m 131074 5472001 --rw------- 1072 70 1654784
m 131075 5471001 --rw------- 1071 70 38076416


% ./jps | grep postgres
1 1064 70 /usr/local/bin/postgres -D /usr/local/pgsql/data
1 1068 70 postgres: writer process (postgres)
1 1069 70 postgres: stats collector process (postgres)
5 6293 1073 /usr/local/bin/postgres -D /usr/local/pgsql/data
5 6295 1073 postgres: writer process (postgres)
5 6296 1073 postgres: stats collector process (postgres)
6 6545 1072 /usr/local/bin/postgres -D /usr/local/pgsql/data
6 6547 1072 postgres: writer process (postgres)
6 6548 1072 postgres: stats collector process (postgres)
7 6797 1071 /usr/local/bin/postgres -D /usr/local/pgsql/data
7 6799 1071 postgres: writer process (postgres)
7 6800 1071 postgres: stats collector process (postgres)

Post by Oliver Fromme
http://www.secnetix.de/~olli/scripts/jps

Excellent stuff there :)

[Marc G. Fournier]

Talk about 'old email', but better then 'use different ports', which means you
have to modify your clients for this ... after you install postgresql within
the jail, vipw the password file and change the uid of the pgsql user (and
chown the appropriate files) ... then you can run all instances on port 5432,
which clients will expect, but without overrunning shared memory ...

Post by Oliver Peter
Does anybody have a running system with more than one jail hosting
more than one postgres server?
I can only have one pgsql database on one host at all. I already
tried to increase the shared memory off my machine with additional
kernel [1] and sysctl parameters [2] and I also tried to change
the numeric UID directly in the jails into a seperate one. Same
errors.
Of course I already have defined jail_sysvipc_allow="YES" in
rc.conf. I have this issue on 6.2-RELEASE-p8 and 8.0-CURRENT with
postgresql-server-8.2.5_1.
------------------------------------------------------------------------
% psql
psql: FATAL: semctl(458753, 15, SETVAL, 0) failed: Invalid argument
------------------------------------------------------------------------
Or some fun with perl/DBD
------------------------------------------------------------------------
Out of memory during request for 108 bytes, total sbrk() is 534585344 bytes!
Out of memory during request for 288 bytes, total sbrk() is 534585344 bytes!
Out of memory during request for 288 bytes, total sbrk() is 534585344 bytes!
------------------------------------------------------------------------
[1]
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120
[2] http://www.freebsddiary.org/jail-multiple.php
--
"Worker bees can leave. Even drones can fly away.
The Queen is their slave."

- ----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . scrappy-rZG+***@public.gmane.org MSN . scrappy-rZG+***@public.gmane.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664