Thread: Postfix/Maildrop and too many connections issues
We have a system set up whereby postfix and maildrop gather user info from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance and delivery. I have configured max connections at 512 but I find that this is not enough and I get "connection limit exceeded for non-superusers" errors. I see upon ps ax that there are hundreds of idle connections (state I). Is there any way on the server end to close these connections (the process is apparently for postfix and then maildrop to open a connection, run one select statement, and supposedly close the connection). If not, would pgpool help in this case? Since all the connections are basically 2 users (one for the mail delivery system and another for the mail retrieval), are there any such "connections" limitation by pgpool (i.e. if we get a spike of 700-1000 queries each with their own "connection")? Thanks, Sven
On Tuesday 21 June 2005 12:00 pm, Sven Willenberger wrote: > We have a system set up whereby postfix and maildrop gather user > info from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail > acceptance and delivery. I have configured max connections at 512 > but I find that this is not enough and I get "connection limit > exceeded for non-superusers" errors. I see upon ps ax that there > are hundreds of idle connections (state I). I have a vague recollection from the Postfix list that under some circumstances Postfix does not explicitly close the connection to PG so the back-end may stick around till the tcp connection times out. In addition to ps, try "select * from pg_stat_activity()" and look at connections via netstat (check from both ends) to hunt for clues. I know that using old hardware and limiting Postfix to 200 connections I've handled a constant rate of over 9 deliveries/second. Is your mailserver load such that the need for 500+ simultaneous connections passes the "sniff test"? Cheers, Steve
On Tue, 2005-06-21 at 13:49 -0700, Steve Crawford wrote: > On Tuesday 21 June 2005 12:00 pm, Sven Willenberger wrote: > > We have a system set up whereby postfix and maildrop gather user > > info from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail > > acceptance and delivery. I have configured max connections at 512 > > but I find that this is not enough and I get "connection limit > > exceeded for non-superusers" errors. I see upon ps ax that there > > are hundreds of idle connections (state I). > > I have a vague recollection from the Postfix list that under some > circumstances Postfix does not explicitly close the connection to PG > so the back-end may stick around till the tcp connection times out. > > In addition to ps, try "select * from pg_stat_activity()" and look at > connections via netstat (check from both ends) to hunt for clues. > > I know that using old hardware and limiting Postfix to 200 connections > I've handled a constant rate of over 9 deliveries/second. Is your > mailserver load such that the need for 500+ simultaneous connections > passes the "sniff test"? > > Cheers, > Steve > This happens only occasionally (during severe mail floods) that the connections top 500+ .. usually they hang around 50-150 connections. The problem is that when the connection limit is reached, postfix/maildrop does not fail/defer the delivery gracefully. A cursory look through the code would indicate that the connection is closed, so trying to find out where it hangs open would involve some debugging/time. I was hoping that a way of the server closing the connetion would be possible or that pgpool could handle and pool 1000 connections ... Sven
# sven@dmv.com / 2005-06-21 15:00:12 -0400:
> We have a system set up whereby postfix and maildrop gather user info
> from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance
> and delivery. I have configured max connections at 512 but I find that
> this is not enough and I get "connection limit exceeded for
> non-superusers" errors. I see upon ps ax that there are hundreds of idle
> connections (state I).
>
> Is there any way on the server end to close these connections (the
> process is apparently for postfix and then maildrop to open a
> connection, run one select statement, and supposedly close the
> connection).
You are barking up the wrong software. See proxymap(8). (It's
mentioned in http://www.postfix.org/PGSQL_README.html)
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
I run postfix and have it connected to postgresql for just about everything. Postfix is very sloppy on the database side, or so it seems. I ended up having to configure postfix to limit the number of processes it will start, and then make sure postgres has more than that connections available too. In postfix, I set "default_process_limit = 10" in main.cf. In postgresql, I set max connections to 256, with 4 reserved for super user. My mail volume also isn't too bad, typically, so I can get away with setting this to 10. In some cases, I think each postfix process is opening a new database connection for every lookup. And, if a process needs to lookup 2 or 3 items, it uses new connections for every one. Then, the connections don't get closed until those processes are killed a little while later. Pure speculation, but it's about the only thing I can think of (short of going through and analyzing the source code) that would explain the disparity between postfix processes and postgres connections. With that, I have the following in ps (after running for a while, so things are mostly stable): Postgres Connections (not including stats): 43 Postfix: transport: 15 virtual: 13 mailbox: 8 domains: 2 Courier IMAP/POP courier: 5 These numbers will fluctuate with mail traffic. And, they do not correspond with the open # of postfix processes (19 processes: 1 master, 1 qmgr, 2 trivial rewrite, 7 smtpd, 6 cleanup, 1 pickup, 1 virtual). That leads me to believe that the postfix database interface (for postgres at least) is pretty sloppy. I've been planning to try a pooling program, just to see if that would make a difference, but I haven't had time to do any research or practical testing to find out. Does anyone know if this problem occurs when using postfix with LDAP servers or mysql ? Thanks, Greg On Jun 21, 2005, at 12:00 PM, Sven Willenberger wrote: > We have a system set up whereby postfix and maildrop gather user info > from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail > acceptance > and delivery. I have configured max connections at 512 but I find that > this is not enough and I get "connection limit exceeded for > non-superusers" errors. I see upon ps ax that there are hundreds of > idle > connections (state I). > > Is there any way on the server end to close these connections (the > process is apparently for postfix and then maildrop to open a > connection, run one select statement, and supposedly close the > connection). If not, would pgpool help in this case? Since all the > connections are basically 2 users (one for the mail delivery system > and > another for the mail retrieval), are there any such "connections" > limitation by pgpool (i.e. if we get a spike of 700-1000 queries each > with their own "connection")? > > Thanks, > > Sven > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Thanks for the proxymap tip. I will definitely look into it. However, it probably won't do much for me, since I have user and directory information (i.e. sensitive information) looked up, and proxymap very clearly says not to use it for that. At least, not yet. Though it will undoubtedly help others. On Jun 21, 2005, at 4:30 PM, Roman Neuhauser wrote: > # sven@dmv.com / 2005-06-21 15:00:12 -0400: > >> We have a system set up whereby postfix and maildrop gather user info >> from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail >> acceptance >> and delivery. I have configured max connections at 512 but I find >> that >> this is not enough and I get "connection limit exceeded for >> non-superusers" errors. I see upon ps ax that there are hundreds >> of idle >> connections (state I). >> >> Is there any way on the server end to close these connections (the >> process is apparently for postfix and then maildrop to open a >> connection, run one select statement, and supposedly close the >> connection). >> > > You are barking up the wrong software. See proxymap(8). (It's > mentioned in http://www.postfix.org/PGSQL_README.html) > > -- > How many Vietnam vets does it take to screw in a light bulb? > You don't know, man. You don't KNOW. > Cause you weren't THERE. http://bash.org/?255991 > > ---------------------------(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 >
AM Software Design is proud to announce the 1.0 stable release of PG Lightning Admin for PostgreSQL 8.x. PG Lightning Admin is a Windows GUI (graphical user interface) administration program which will run on Windows 95,98, ME,NT 4SP6, 2000 and XP. A full 30 day demo may be downloaded from: http://www.amsoftwaredesign.com/downloads.php Cost for the registered version is a low $29.99 (US Dollar). For more information and screenshots please see: http://www.amsoftwaredesign.com/lightning_admin.php Features: IDE style editors for functions,queries and views. MDI style Interface which works similar to a word processor. Function Version Control The Editors feature full code completion, Parameter Hints, and full syntax highlighting. The Editors have full drag and drop editing, and will accept drag and drops from the databases schema tree view. Tabbed based Enterprise Manager, each database and it's schema is opened in it's own tab. Built in SSH tunnel capability. Filtering MDI task bar. When a database is selected/opened, the MDI task bar only shows open objects associated with that particular tab, which makes it easy to keep track of where open objects belong. Full backup and restore capabilities. A backup or restore is just a right-click away. Query Editor is threaded and allows long running queries to be canceled. GUI Query Builder, accessed from the Query Editor's menu bar. Query Editor keeps a log/history of executed SQL queries Query Editor saves it's contents and restores the next time you open the editor. Query Editor has a temp table watch window, which allows for easy debugging. Full printing of DDL/Metadata and results sets. Print preview allows the displayed data to be printed or exported as PDF, Word, Excel etc. All Data grids are sortable and show a sort arrow in the column header. All Data grids have a form view, which really helps when viewing large records. All Data grids have full export capabilities such as comma/custom separated, tabbed or fixed width. The query data grid allows the result set to be filtered. Quick View table view which will bring the the Top rows of a table or all rows. Full featured grid based grant manager that supports the with grant option. Server status monitor that allows queries running through the backend to be captured (command string must be enabled). Environment options allow many features to be customized by the user. Plus much more...
On Jun 22, 2005, at 2:07 PM, Tony Caduto wrote: > AM Software Design is proud to announce the 1.0 > stable release of PG Lightning Admin for PostgreSQL 8.x. Shouldn't this be on pgsql-announce instead? According to its description, pgsql-announce is an "Announcement list pertaining to PostgreSQL and various third party software." http://www.postgresql.org/community/lists/ Michael Glaesemann grzm myrealbox com
On Wed, 2005-06-22 at 01:30 +0200, Roman Neuhauser wrote: > # sven@dmv.com / 2005-06-21 15:00:12 -0400: > > We have a system set up whereby postfix and maildrop gather user info > > from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance > > and delivery. I have configured max connections at 512 but I find that > > this is not enough and I get "connection limit exceeded for > > non-superusers" errors. I see upon ps ax that there are hundreds of idle > > connections (state I). > > > > Is there any way on the server end to close these connections (the > > process is apparently for postfix and then maildrop to open a > > connection, run one select statement, and supposedly close the > > connection). > > You are barking up the wrong software. See proxymap(8). (It's > mentioned in http://www.postfix.org/PGSQL_README.html) > Thanks for the tip ... edited my main.cf so that transport,aliases, etc use proxymap so this should help somewhat. Apparently in version 2.2. it is safe to use for UIDs/maildirs/paths etc so I may have to make the upgrade leap. Now to see if maildrop (the local mailer) has a similar feature. Sven
On Thu, 2005-06-23 at 03:39 +0000, Karl O. Pinc wrote: > On 06/22/2005 08:23:43 AM, Sven Willenberger wrote: > > On Wed, 2005-06-22 at 01:30 +0200, Roman Neuhauser wrote: > > > # sven@dmv.com / 2005-06-21 15:00:12 -0400: > > > > We have a system set up whereby postfix and maildrop gather user > > info > > > > from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail > > acceptance > > > > and delivery. I have configured max connections at 512 but I find > > that > > > > this is not enough and I get "connection limit exceeded for > > > > non-superusers" errors. I see upon ps ax that there are hundreds > > of idle > > > > connections (state I). > > > > > > > > Is there any way on the server end to close these connections (the > > > > process is apparently for postfix and then maildrop to open a > > > > connection, run one select statement, and supposedly close the > > > > connection). > > > > > > You are barking up the wrong software. See proxymap(8). (It's > > > mentioned in http://www.postfix.org/PGSQL_README.html) > > > > > Thanks for the tip ... edited my main.cf so that transport,aliases, > > etc > > use proxymap so this should help somewhat. Apparently in version 2.2. > > it > > is safe to use for UIDs/maildirs/paths etc so I may have to make the > > upgrade leap. > > I _believe_ 2.2 is safe to _configure_, but the various daemons > will do lookups directly without going through proxymap. So > there's no "extra" help, you still need to limit the number > of these running daemons. (Although maybe they eache cache > the connection themselves reducing the number of opens and > closes? ?) > > That's how I read the docs. > I guess my understanding was that whichever portions in main.cf I would prefix with proxy: would send their requests through proxymap. I just realized however that once postifx looks up the user in transport it hands the mail off to maildrop which then has to do its own db lookup for the home directory, etc and I cannot see how I would configure this to use proxymap. Well at least I have consolidated some of the lookups ... Sven
# sven@dmv.com / 2005-06-23 17:25:03 -0400:
> On Thu, 2005-06-23 at 03:39 +0000, Karl O. Pinc wrote:
> > On 06/22/2005 08:23:43 AM, Sven Willenberger wrote:
> > > On Wed, 2005-06-22 at 01:30 +0200, Roman Neuhauser wrote:
> > > > # sven@dmv.com / 2005-06-21 15:00:12 -0400:
> > > > > We have a system set up whereby postfix and maildrop gather
> > > > > user info from a pg database (7.4.2 on FreeBSD 5.2.1) to do
> > > > > local mail acceptance and delivery. I have configured max
> > > > > connections at 512 but I find that this is not enough and I
> > > > > get "connection limit exceeded for non-superusers" errors. I
> > > > > see upon ps ax that there are hundreds of idle connections
> > > > > (state I).
[...]
> I just realized however that once postifx looks up the user in
> transport it hands the mail off to maildrop which then has to do its
> own db lookup for the home directory, etc and I cannot see how I would
> configure this to use proxymap.
*That* is where something like
http://pgpool.projects.postgresql.org/ would be useful.
> Well at least I have consolidated some of the lookups ...
True.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991