Hi

I use postfix 20010610 with mysql support enabled. I mainly use the
mysql dicts for envelope address manipulation. The problem that I
have is that when postfix is under heavy load it tries to establish
hundreds of mysql connections. I've looked at the log file of the
mysql server and saw that postfix does a lot of unnecessary sql
queries.

Background information: As I see the mysql dict mainly useful for
realtime postfix configuration. So I don't have a single table that is
sepecially designed to match postfix's needs for a virtual map. But
I have multiple tables, each of those for logically different stuff
(virtual domains, virtual users, virtual forwards etc). Because mysql
doesn't (yet) support triggers/stored procedures (I could then let the
mysql server automatically generate/maintain a single table for postfix,
and no, I can't change the configuration application) I have a bunch
of different mysql dicts as virtual maps.

Now, have a lock how many sql queries postfix does for an email
(for infovirtual1.tld) passing the postfix system:

cleanup
        i) a dozen queries to find 'infovirtual1.tld' (fails)
        ii) a couple of queries to find 'virtual1.tld'
               -> joevirtual1.tld.v.isp.tld
        iii) a dozen queries to find 'joevirtual1.tld.v.isp.tld' (fails)
        iv) a dozen queries to find 'virtual1.tld.v.isp.tld' (fails)

qmgr
        v) [unimportant] some quries to get the routing information
             for 'virtual1.tld.v.isp.tld' -> cyrus:123.45.67.89
        vi) a dozen queries to find 'virtual1.tld.v.isp.tld' (fails)

Why not implement a better envelope addresse manipulation lookup method
for postfix? Why does all this logically different stuff have to go in a
single map?

My proposal is to split the 'virtal_maps' into (or to enhance postfix
with the following maps):

map name (may be changed ;) | for lookups with keys like
------------------------------------+---------------------------
'virtual_host_maps' | 'domain'
'virtual_user_forward_maps' | 'userdomain'
'virtual_nonselective_forward_maps' | 'domain'

This would result in much fewer queries and less mysql connections.

What to you think?

-Jade.

PS: Hmm.. smtpd does probably also too much queries, just to find
    'virtual1.tld' when the email doesn't enter the postfix system
    via the pickup daemon.
-
To unsubscribe, send mail to majordomopostfix.org with content
(not subject): unsubscribe postfix-users

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]