Please help
Hi all,
Without modifying anything, postgresql (since today) has a strange
behavior:
All connections are rejected with No space left on device.
There's plenty of space in shm, disk...
It occurs on UNIX as well as on INET socket.
I can't even shut the postmaster down (it doesn't respond) and have a few
defunct process.
SHMAX is at 40000000 here, version is 7.2.3
Heres postgresql.conf:
#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form
#
# name = value
#
# (The `=' is optional.) White space is collapsed, comments are
# introduced by `#' anywhere on a line. The complete list of option
# names and allowed values can be found in the PostgreSQL
# documentation. The commented-out settings shown in this file
# represent the default values.
# Any option can also be given as a command line switch to the
# postmaster, e.g., 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#========================================================================
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false
max_connections = 64
port = 5432
hostname_lookup = true
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
shared_buffers = 512 # 2*max_connections, min 16
#max_fsm_relations = 100 # min 10, fsm is free space map
#max_fsm_pages = 10000 # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4
#
# Non-shared Memory Sizes
#
sort_mem = 10240 # min 32
#vacuum_mem = 8192 # min 1024
#
# Write-ahead log (WAL)
#
#wal_files = 4 # range 0-64
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#fsync = true
#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true
#ksqo = false
#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on #tables in query, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Debug display
#
#silent_mode = false
log_connections = true
#log_timestamp = true
log_pid = true
#debug_level = 0 # range 0-16
debug_print_query = true
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
debug_pretty_print = true
# requires USE_ASSERT_CHECKING
#debug_assertions = true
#
# Syslog
#
# requires ENABLE_SYSLOG
syslog = 2 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#
# Statistics
#
show_parser_stats = false
show_planner_stats = false
show_executor_stats = false
show_query_stats = false
# requires BTREE_BUILD_STATS
#show_btree_build_stats = false
#
# Access statistics collection
#
stats_start_collector = true
stats_reset_on_server_start = true
stats_command_string = true
stats_row_level = true
stats_block_level = true
#
# Lock Tracing
#
#trace_notify = false
# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#dynamic_library_path = '$libdir'
#australian_timezones = false
#authentication_timeout = 60 # min 1, max 600
#deadlock_timeout = 1000
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = false
#sql_inheritance = true
#transform_null_equals = false
Anyone can come with an idea?
It NEVER happend before...
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
Olivier PRENANT <ohp@pyrenet.fr> writes:
Without modifying anything, postgresql (since today) has a strange
behavior:
All connections are rejected with No space left on device.
Could you be out of swap space?
I'd like to see the *exact* context in which you see this error message,
though.
regards, tom lane
On Monday 21 October 2002 15:42, Olivier PRENANT wrote:
Hi all,
Without modifying anything, postgresql (since today) has a strange
behavior:All connections are rejected with No space left on device.
There's plenty of space in shm, disk...
I have no idea whether it's relevant, but maybe you have a problem with
semaphores? See:
http://www.ca.postgresql.org/docs/faq-english.html#3.4
(A lack of available semaphores can also produce the message
"No space left on device.")
Sorry I can't help any further.
Ian Barwick
barwick@gmx.net
Thanks for your reply.
Actually, I just found xhat happened 2 mn ago!
Last week I changed my pg_hba.conf to require an md5 password for one
specific database and updated all my script but ONE.
It seems that connection from php pg_connect not supplying a password
lives the process for a "certain ammount of time" running, then postmaster
just hangs.
Don't really know what happens here, but supplying a password on this
script made the problem go away... (at least for now)
Regards
On Mon, 21 Oct 2002, Tom Lane wrote:
Date: Mon, 21 Oct 2002 11:02:43 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: ohp@pyrenet.fr
Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpOlivier PRENANT <ohp@pyrenet.fr> writes:
Without modifying anything, postgresql (since today) has a strange
behavior:All connections are rejected with No space left on device.
Could you be out of swap space?
I'd like to see the *exact* context in which you see this error message,
though.regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
Olivier PRENANT <ohp@pyrenet.fr> writes:
It seems that connection from php pg_connect not supplying a password
lives the process for a "certain ammount of time" running, then postmaster
just hangs.
That's hard to believe. In 7.2 or later, the backend should give up and
close the connection and exit if the client doesn't finish the
authentication handshake within 60 seconds.
Can anyone else reproduce a problem with lack of a password on a PHP
connection?
regards, tom lane
Hi Tom,
I can assure you that there are no problem anymore now that I have put the
password.
However the modification pg_hba.conf has been done a few days ago and
never noticed the error until today where I had a *LOT* of visits to my
site.
So I still think it's a matter of bad connection delay.
Connexion where comming very fast and postmaster did'nt release them fast
enough.
I now have this problem when I run vacuum analyze:
Script started on Mon Oct 21 18:20:35 2002
~ 18:20:35: psql pyrenet
Password:
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
pyrenet=# VACUUM ANALYZE ;
FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Failed.
!# \q
~ 18:21:21: psql pyrenet
Password:
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
pyrenet=# VACUUM ANALYZE ;
FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \q
script done on Mon Oct 21 18:21:51 2002
What causes (FILES=64).
Again, this is the very first time I have such problems on postgresql!!
It works so well, it's the central point of my system!!
On Mon, 21 Oct 2002, Tom Lane wrote:
Date: Mon, 21 Oct 2002 11:35:33 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: ohp@pyrenet.fr
Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpOlivier PRENANT <ohp@pyrenet.fr> writes:
It seems that connection from php pg_connect not supplying a password
lives the process for a "certain ammount of time" running, then postmaster
just hangs.That's hard to believe. In 7.2 or later, the backend should give up and
close the connection and exit if the client doesn't finish the
authentication handshake within 60 seconds.Can anyone else reproduce a problem with lack of a password on a PHP
connection?regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
Olivier PRENANT <ohp@pyrenet.fr> writes:
pyrenet=# VACUUM ANALYZE ;
FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open files
Hmm. Do you have any idea what sysconf(_SC_OPEN_MAX) returns on your
platform? You could try reducing the max_files_per_process parameter.
regards, tom lane
On Mon, 21 Oct 2002, Tom Lane wrote:
Date: Mon, 21 Oct 2002 12:52:10 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: ohp@pyrenet.fr
Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpOlivier PRENANT <ohp@pyrenet.fr> writes:
pyrenet=# VACUUM ANALYZE ;
FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open filesHmm. Do you have any idea what sysconf(_SC_OPEN_MAX) returns on your
platform? You could try reducing the max_files_per_process parameter.
The point is, it occurs today for the very first time!
Question: does (with 7.2) augmenting max_connection suffice, or do I have
to recompile?
That's the only thing that comes to my mind! I changed max_coneections
(and related parameters) in postgresql.conf only...
I say that, because I tried to change socket_directory in postgresql.conf
and clients didn't work anymore
regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
On Mon, 2002-10-21 at 12:26, Olivier PRENANT wrote:
On Mon, 21 Oct 2002, Tom Lane wrote:
Date: Mon, 21 Oct 2002 12:52:10 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: ohp@pyrenet.fr
Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpOlivier PRENANT <ohp@pyrenet.fr> writes:
pyrenet=# VACUUM ANALYZE ;
FATAL 2: could not open transaction-commit log directory (/usr/local/pgsql/data/pg_clog): Too many open filesHmm. Do you have any idea what sysconf(_SC_OPEN_MAX) returns on your
platform? You could try reducing the max_files_per_process parameter.The point is, it occurs today for the very first time!
Question: does (with 7.2) augmenting max_connection suffice, or do I have
to recompile?
You might need to up the Shared Memory parameters and the Semaphore
Parameters in your OS (UnixWare IIRC).
That's the only thing that comes to my mind! I changed max_coneections
(and related parameters) in postgresql.conf only...I say that, because I tried to change socket_directory in postgresql.conf
and clients didn't work anymore
See above.
regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Hi larry,
Glad to see you around...
On 21 Oct 2002, Larry Rosenman wrote:
Date: 21 Oct 2002 12:34:48 -0500
From: Larry Rosenman <ler@lerctr.org>
To: ohp@pyrenet.fr
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpThe point is, it occurs today for the very first time!
Question: does (with 7.2) augmenting max_connection suffice, or do I have
to recompile?You might need to up the Shared Memory parameters and the Semaphore
Parameters in your OS (UnixWare IIRC).
I did!
That's the only thing that comes to my mind! I changed max_coneections
(and related parameters) in postgresql.conf only...I say that, because I tried to change socket_directory in postgresql.conf
and clients didn't work anymore
Sorry, I mis-explain!
I mean changing socket_directory in postgresql.conf and restart server did
create .s.PGSQL.5432 in the new dir, however clients (like psql) still
want it in /tmp!!
See above.
regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
On Mon, 2002-10-21 at 12:39, Olivier PRENANT wrote:
Hi larry,
Glad to see you around...
On 21 Oct 2002, Larry Rosenman wrote:Date: 21 Oct 2002 12:34:48 -0500
From: Larry Rosenman <ler@lerctr.org>
To: ohp@pyrenet.fr
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpThe point is, it occurs today for the very first time!
Question: does (with 7.2) augmenting max_connection suffice, or do I have
to recompile?You might need to up the Shared Memory parameters and the Semaphore
Parameters in your OS (UnixWare IIRC).I did!
Ok.
That's the only thing that comes to my mind! I changed max_coneections
(and related parameters) in postgresql.conf only...I say that, because I tried to change socket_directory in postgresql.conf
and clients didn't work anymoreSorry, I mis-explain!
I mean changing socket_directory in postgresql.conf and restart server did
create .s.PGSQL.5432 in the new dir, however clients (like psql) still
want it in /tmp!!
That **WOULD** take a recompile.
LER
See above.
regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Mon, 2002-10-21 at 12:57, Larry Rosenman wrote:
On Mon, 2002-10-21 at 12:39, Olivier PRENANT wrote:
Hi larry,
Glad to see you around...
On 21 Oct 2002, Larry Rosenman wrote:Date: 21 Oct 2002 12:34:48 -0500
From: Larry Rosenman <ler@lerctr.org>
To: ohp@pyrenet.fr
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers list <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Please helpThe point is, it occurs today for the very first time!
Question: does (with 7.2) augmenting max_connection suffice, or do I have
to recompile?You might need to up the Shared Memory parameters and the Semaphore
Parameters in your OS (UnixWare IIRC).I did!
Ok.
That's the only thing that comes to my mind! I changed max_coneections
(and related parameters) in postgresql.conf only...I say that, because I tried to change socket_directory in postgresql.conf
and clients didn't work anymoreSorry, I mis-explain!
I mean changing socket_directory in postgresql.conf and restart server did
create .s.PGSQL.5432 in the new dir, however clients (like psql) still
want it in /tmp!!That **WOULD** take a recompile.
Or (IIRC), changing the connect string passed from PHP to PostgreSQL.
LER
See above.
regards, tom lane
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749