Thread: PostgreSQL 8.0.3 limiting max_connections to 64 ?

PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
eu
Date:
Hello, i need help...

i have a postgresql 8.0.3 database running on the backend of a postfix
setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1,
serving email to a dozen different virtual domains ( including this one
i'm using right now )...

however, this setup takes a whole lot of simultaneous connections to the
database
( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail,
courier-authlib and courier-imap ), all those services were configured
to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too
many short-lived connections were kept on CLOSE_WAIT for too long while
connecting via TCP ), and, despite i have made max_connections on the
postgresql.conf as high as 500, after ( around ) 64 simultaneous
connections i start having a lot of "sorry too many clients already"
errors on my postgresql logfile...
then, my users go nuts complaining about how slow the server is and/or
having to retype their passwords too many times ( of course, since
authlib can't pick a connection to authenticate against the database ).

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.

thanks.




Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Sebastian Hennebrueder
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

eu schrieb:

> Hello, i need help...
>
> i have a postgresql 8.0.3 database running on the backend of a postfix
> setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1,
> serving email to a dozen different virtual domains ( including this
> one i'm using right now )...
>
> however, this setup takes a whole lot of simultaneous connections to
> the database
> ( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail,
> courier-authlib and courier-imap ), all those services were configured
> to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too
> many short-lived connections were kept on CLOSE_WAIT for too long
> while connecting via TCP ), and, despite i have made max_connections
> on the postgresql.conf as high as 500, after ( around ) 64
> simultaneous connections i start having a lot of "sorry too many
> clients already" errors on my postgresql logfile...
> then, my users go nuts complaining about how slow the server is and/or
> having to retype their passwords too many times ( of course, since
> authlib can't pick a connection to authenticate against the database ).
>
> 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.
>
> thanks.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
500 parallel connections are very very much. You should verify if one
application is not closing connections or if you can create an
connection pool.
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.

- --
Best Regards / Viele Grüße

Sebastian Hennebrueder

- ----

http://www.laliluna.de

Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB

Get support, education and consulting for these technologies -
uncomplicated and cheap.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDA3vRpqx3RdCs+9kRAqgKAKCyJcq/Zpr9YttAfXlUEhb1rfz89gCfYqVt
tEukiGbcNbDmMgt0iED2NPg=
=Otj6
-----END PGP SIGNATURE-----

--
Best Regards / Viele Grüße

Sebastian Hennebrueder

----

http://www.laliluna.de

Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB

Get support, education and consulting for these technologies -
uncomplicated and cheap.

Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Sven Willenberger
Date:
On Wed, 2005-08-17 at 13:05 -0300, eu wrote:
> Hello, i need help...
>
> i have a postgresql 8.0.3 database running on the backend of a postfix
> setup ( i didn't trust Mysql for the job ) on Linux kernel 2.6.8.1,
> serving email to a dozen different virtual domains ( including this one
> i'm using right now )...
>
> however, this setup takes a whole lot of simultaneous connections to the
> database
> ( postfix, amavis-new, clamav + spamassassin, apache+squirrelmail,
> courier-authlib and courier-imap ), all those services were configured
> to use Unix Domain Sockets, instead of TCP ( netstat -anvp showed too
> many short-lived connections were kept on CLOSE_WAIT for too long while
> connecting via TCP ), and, despite i have made max_connections on the
> postgresql.conf as high as 500, after ( around ) 64 simultaneous
> connections i start having a lot of "sorry too many clients already"
> errors on my postgresql logfile...
> then, my users go nuts complaining about how slow the server is and/or
> having to retype their passwords too many times ( of course, since
> authlib can't pick a connection to authenticate against the database ).
>
> 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.
>

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)? 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


Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
"Joshua D. Drake"
Date:
>>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?


>>
>
>
> 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)? 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Tom Lane
Date:
eu <alexandre@vectorx.com.br> 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

Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Alexandre Barros
Date:
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@vectorx.com.br> 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

Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Martijn van Oosterhout
Date:
Can't help you with the rest of your problem, but you need to up this
by a factor of 10 at least. This is 8MB of shared buffers for 500
connections...

On Thu, Aug 18, 2005 at 12:43:49PM -0300, Alexandre Barros wrote:
> shared_buffers = 1004   # min 16, at least max_connections*2, 8KB each

Hope this helps,

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?

From
Tom Lane
Date:
Alexandre Barros <alexandre@vectorx.com.br> writes:
> [ can't get more than 64 connections ]

Have you looked into the postmaster log (stderr or syslog output) to
see if any error conditions are being reported?

It occurs to me that the postmaster might be running in an account that
has a max-number-of-user-processes limit.  On machines where such limits
are enforced, 70 or so is a pretty common value, so that would fit with
your experience.  I can't tell you exactly how to check or change that
limit, because it's different on different variants of Unix, but it's
something to look into.  If this is the problem, you'd be seeing "fork
failed" messages in the postmaster log.

            regards, tom lane