Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

[Alexandre Barros <alexandre@xxxxxxxxxxxxxx>]

First of all, thanks in advance for all the replies.

Joshua D. Drake wrote:

max_connections, shared_buffers, shmmax were tweaked, but the server does not seems to respect the 500 max_connections...
i *know* i'm missing something obvious, but, what could be wrong ?... i'm lost... any help would be most appreciated... please.

A completely stupid response but don't take it pseronally.

Did you remove the # from in front of the max_connections parameter and
do a COMPLETE restart?

no worries, i didn't take personally. ;)

---
Tom Lane wrote:

Alexandre Barros <alexandre@xxxxxxxxxxxxxx> writes:
  

max_connections, shared_buffers, shmmax were tweaked, but the server 
does not seems to respect the 500 max_connections...
    

Er ... did you restart the postmaster after increasing those parameters?

			regards, tom lane

  

Er... at least twice, once because a power failure with a defective no-break,
and the second time i restarted all services ( and before that, i kept using -- over and over -- the "kill -HUP" on postmaster, and trusted the "reloading parameters" message on the logfile... );

---
Sebastian Hennebrueder wrote:

500 parallel connections are very very much. 

i agree... but i needed to test how ( and why ) things were ( not ) working...  and the "sorry too many clients already" message was driving me crazy...

You should verify if one
application is not closing connections or if you can create an
connection pool.
  

the connection pool ( was testing pgpool for that ) was a possibility, but i need things at least "barely working" before... and an obscene value on "max_connections" was my best try.

Use select * from pg_stat_activity to see wheach connections are open
from which client and if they are iddle or not. See the postgreSQL doc
for more information on these queries.

  

i have lots of idle connections showing on a "ps|grep" from squirrel ( apache with persistent connections ) amavisd, courier-authlib all keep idle connections for a long time, but that pg_stat query only shows stuff like that:

postfix=# select * from pg_stat_activity ;
     datid |   datname    | procpid | usesysid |   usename    |        current_query         | query_start
    -------+--------------+---------+----------+--------------+------------------------------+-------------
     17230 | postfix      |   29852 |      100 | postfix      | <command string not enabled> |
       (...)
    (47 rows)

i'm yet not sure what this means, we'll be looking over postgresql docs...

---
Sven Willenberger wrote:

Can you post the relevent portions of your postgresql.conf file? Do you
see any error messsages when starting the database up (perhaps about
shared memory or such)? 

everything not commented out from the postgresql.conf:

max_connections = 500
        # note: increasing max_connections costs about 500 bytes of shared
        # memory per connection slot, in addition to costs from shared_buffers
        # and max_locks_per_transaction.
superuser_reserved_connections = 2
shared_buffers = 1004   # min 16, at least max_connections*2, 8KB each

debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_statement = 'none'

log_parser_stats        = false
log_planner_stats       = false
log_executor_stats      = false
log_statement_stats     = false

statement_timeout = 60000       # 0 is disabled, in milliseconds


/proc/sys/kernel/shmmax  == 33554432

and no error messages at all on the startup logfile...

We also run a postfix mailserver (with maildrop,
courier-imap/vauthchkpw, etc) against a postgresql database with
max_connections set to 512 (FreeBSD 5.x machine). On the delivery end of
things we pool the connections from the postfix server using proxymap(8)
(which helped ease the connections load).

Sven

  

aha ! 512 connections ! someone as "exaggerated" as me, i feel happy now !
;)

i will also study proxymap, thanks !

Alexandre Barros