Thread: 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) RegardsOn 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 help > > 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 > -- 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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing
therequest.
The connection to the server was lost. Attempting reset: NOTICE: Message from PostgreSQL backend:The Postmaster has
informedme that some other backenddied abnormally and possibly corrupted shared memory.I have rolled back the current
transactionand amgoing to terminate your database system connection and exit.Please reconnect to the database system
andrepeat 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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing
therequest.
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 help
>
> 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
>
--
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 help > > 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. 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 help > > > > 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. > 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 help > > 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). 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 help > > > 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). > 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 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!! 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 help > > > > 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). > > 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 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!! > 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