Thread: Please help

Please help

From
Olivier PRENANT
Date:
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)



Re: Please help

From
Tom Lane
Date:
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


Re: Please help

From
Ian Barwick
Date:
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


Re: Please help

From
Olivier PRENANT
Date:
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)



Re: Please help

From
Tom Lane
Date:
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


Re: Please help

From
Olivier PRENANT
Date:
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)



Re: Please help

From
Tom Lane
Date:
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


Re: Please help

From
Olivier PRENANT
Date:
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)



Re: Please help

From
Larry Rosenman
Date:
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



Re: Please help

From
Olivier PRENANT
Date:
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)



Re: Please help

From
Larry Rosenman
Date:
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



Re: Please help

From
Larry Rosenman
Date:
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