Thread: PostgreSQL 8.0.3 limiting max_connections to 64 ?
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.
-----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.
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
>>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/
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
First of all, thanks in advance for all the replies.
Joshua D. Drake wrote:
---
Tom Lane wrote:
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:
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...
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.
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:
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...
;)
i will also study proxymap, thanks !
Alexandre Barros
Joshua D. Drake wrote:
no worries, i didn't take personally. ;)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?
---
Tom Lane wrote:
Er... at least twice, once because a power failure with a defective no-break,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
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.
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: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.
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:
everything not commented out from the postgresql.conf: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)?
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...
aha ! 512 connections ! someone as "exaggerated" as me, i feel happy now !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
;)
i will also study proxymap, thanks !
Alexandre Barros
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
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