Обсуждение: 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