Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
Hi all,

We've been using Postfix for years with good results, but in recent
years have moved to a load-balanced HAProxy front-end with multiple
backend relay nodes. I've consulted various sources during that time to
perform the initial setup and light tuning since then.

The health checks are run often and simulate a full email delivery
session in an attempt to exercise the full configuration (including
alias resolution and other related db queries).

This setup works pretty well, but occasionally there is enough of a
delay between one of the steps in the simulated delivery that the health
check fails and the node is marked as down.

When this occurs I have been unable to determine exactly why the issue
occurs. I've adjusted various timeout and timing settings within HAProxy
and Postfix, so I assume that our mostly stock MariaDB 10.0.x
installation is likely to blame.

Do you have any recommendations for guides that cover tuning Postfix and
MySQL? I'd like to start there and work through the steps before turning
back to the configuration as a whole.

Another option I'm considering is replicating the database contents
(where applicable) in MySQL to local SQLite databases that are synced to
the relay nodes, cutting MySQL/MariaDB out of the picture entirely.

Our client node count is currently less than 100.

Thanks in advance for any guides that you can reference!
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Wietse Venema
deoren:

> Hi all,
>
> We've been using Postfix for years with good results, but in recent
> years have moved to a load-balanced HAProxy front-end with multiple
> backend relay nodes. I've consulted various sources during that time to
> perform the initial setup and light tuning since then.
>
> The health checks are run often and simulate a full email delivery
> session in an attempt to exercise the full configuration (including
> alias resolution and other related db queries).
>
> This setup works pretty well, but occasionally there is enough of a
> delay between one of the steps in the simulated delivery that the health
> check fails and the node is marked as down.
>
> When this occurs I have been unable to determine exactly why the issue
> occurs. I've adjusted various timeout and timing settings within HAProxy
> and Postfix, so I assume that our mostly stock MariaDB 10.0.x
> installation is likely to blame.

Have you looked in Postfix LOGs? For example, if there is a delay
from the start of the probe to the first Postfix logfile record,
then that would indicate a delay with looking up the client hostname,
and then the address for that hostname.

If there is a hiccup with MySQL, then you might see a Postfix warning
around that time.

etc. etc.

        Wietse

> Do you have any recommendations for guides that cover tuning Postfix and
> MySQL? I'd like to start there and work through the steps before turning
> back to the configuration as a whole.
>
> Another option I'm considering is replicating the database contents
> (where applicable) in MySQL to local SQLite databases that are synced to
> the relay nodes, cutting MySQL/MariaDB out of the picture entirely.
>
> Our client node count is currently less than 100.
>
> Thanks in advance for any guides that you can reference!
>
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Phil Stracchino
In reply to this post by deoren
On 8/21/18 4:17 PM, deoren wrote:

> Hi all,
>
> We've been using Postfix for years with good results, but in recent
> years have moved to a load-balanced HAProxy front-end with multiple
> backend relay nodes. I've consulted various sources during that time to
> perform the initial setup and light tuning since then.
>
> The health checks are run often and simulate a full email delivery
> session in an attempt to exercise the full configuration (including
> alias resolution and other related db queries).
>
> This setup works pretty well, but occasionally there is enough of a
> delay between one of the steps in the simulated delivery that the health
> check fails and the node is marked as down.
>
> When this occurs I have been unable to determine exactly why the issue
> occurs. I've adjusted various timeout and timing settings within HAProxy
> and Postfix, so I assume that our mostly stock MariaDB 10.0.x
> installation is likely to blame.
>
> Do you have any recommendations for guides that cover tuning Postfix and
> MySQL? I'd like to start there and work through the steps before turning
> back to the configuration as a whole.
>
> Another option I'm considering is replicating the database contents
> (where applicable) in MySQL to local SQLite databases that are synced to
> the relay nodes, cutting MySQL/MariaDB out of the picture entirely.
>
> Our client node count is currently less than 100.

I wouldn't use SQLite.

There is no such thing to my knowledge as a tuning guide for MySQL
specifically to work with Postfix.  The same general MySQL tuning
practices apply to tuning for any load scenario.  You said "mostly
stock" MariaDB - have you done any database tuning *at all*?  (And for
that matter, what are you running it on?  Red Hat, for example, has
historically shipped default MySQL/MariaDB configuration files that are
at best worthless even when they're not actively harmful.)


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Noel Jones-2
In reply to this post by deoren
On 8/21/2018 3:17 PM, deoren wrote:

> Hi all,
>
> We've been using Postfix for years with good results, but in recent
> years have moved to a load-balanced HAProxy front-end with multiple
> backend relay nodes. I've consulted various sources during that time
> to perform the initial setup and light tuning since then.
>
> The health checks are run often and simulate a full email delivery
> session in an attempt to exercise the full configuration (including
> alias resolution and other related db queries).
>
> This setup works pretty well, but occasionally there is enough of a
> delay between one of the steps in the simulated delivery that the
> health check fails and the node is marked as down.
>
> When this occurs I have been unable to determine exactly why the
> issue occurs. I've adjusted various timeout and timing settings
> within HAProxy and Postfix, so I assume that our mostly stock
> MariaDB 10.0.x installation is likely to blame.
>
> Do you have any recommendations for guides that cover tuning Postfix
> and MySQL? I'd like to start there and work through the steps before
> turning back to the configuration as a whole.
>
> Another option I'm considering is replicating the database contents
> (where applicable) in MySQL to local SQLite databases that are
> synced to the relay nodes, cutting MySQL/MariaDB out of the picture
> entirely.
>
> Our client node count is currently less than 100.
>
> Thanks in advance for any guides that you can reference!

Are you using the proxymap service with your table lookups?  That
can greatly reduce the load on the MySQL server and improve
performance, sometimes dramatically.

http://www.postfix.org/postconf.5.html#proxy_read_maps
http://www.postfix.org/proxymap.8.html


  -- Noel Jones
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Wietse Venema
On 8/21/2018 6:25 PM, Wietse Venema wrote:

> Have you looked in Postfix LOGs? For example, if there is a delay
> from the start of the probe to the first Postfix logfile record,
> then that would indicate a delay with looking up the client hostname,
> and then the address for that hostname.

Thank you for the response.

I find entries like these:

2018-08-21T08:11:19.456927-05:00 relay1 postfix/smtpd[30722]: connect
from unknown[192.168.2.199]
2018-08-21T08:11:34.332901-05:00 relay1 postfix/smtpd[30852]: connect
from unknown[192.168.2.199]
2018-08-21T08:11:43.392959-05:00 relay1 postfix/smtpd[30853]: connect
from unknown[192.168.2.199]
2018-08-21T08:11:52.424982-05:00 relay1 postfix/smtpd[30863]: connect
from unknown[192.168.2.199]
2018-08-21T08:12:01.472960-05:00 relay1 postfix/smtpd[30865]: connect
from unknown[192.168.2.199]

2018-08-21T08:12:07.465312-05:00 relay1 postfix/smtpd[30863]: lost
connection after MAIL from unknown[192.168.2.199]
2018-08-21T08:12:07.466254-05:00 relay1 postfix/smtpd[30863]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
2018-08-21T08:12:11.816850-05:00 relay1 postfix/smtpd[30722]: lost
connection after MAIL from unknown[192.168.2.199]
2018-08-21T08:12:11.817737-05:00 relay1 postfix/smtpd[30722]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
2018-08-21T08:12:12.884866-05:00 relay1 postfix/smtpd[30865]: connect
from unknown[192.168.2.199]

192.168.2.199 represents the HAProxy load-balancer IP.


> If there is a hiccup with MySQL, then you might see a Postfix warning
> around that time.

I found cases of messages similar to this one (borrowed from a thread
last month on this list):

2018-07-02 15:11:06 8052 [Warning] Aborted connection 8733691 to db:
'my_database' user: 'my_user' host: 'localhost' (Got an error reading
communication packets)

You noted then that Postfix does not log out of database connections.
It's admittedly a tangent, but is that design choice due to performance
reasons, reliability concerns or something else?
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Phil Stracchino
On 8/21/2018 7:48 PM, Phil Stracchino wrote:

> On 8/21/18 4:17 PM, deoren wrote:
>> Hi all,
>>
>> We've been using Postfix for years with good results, but in recent
>> years have moved to a load-balanced HAProxy front-end with multiple
>> backend relay nodes. I've consulted various sources during that time to
>> perform the initial setup and light tuning since then.
>>
>> The health checks are run often and simulate a full email delivery
>> session in an attempt to exercise the full configuration (including
>> alias resolution and other related db queries).
>>
>> This setup works pretty well, but occasionally there is enough of a
>> delay between one of the steps in the simulated delivery that the health
>> check fails and the node is marked as down.
>>
>> When this occurs I have been unable to determine exactly why the issue
>> occurs. I've adjusted various timeout and timing settings within HAProxy
>> and Postfix, so I assume that our mostly stock MariaDB 10.0.x
>> installation is likely to blame.
>>
>> Do you have any recommendations for guides that cover tuning Postfix and
>> MySQL? I'd like to start there and work through the steps before turning
>> back to the configuration as a whole.
>>
>> Another option I'm considering is replicating the database contents
>> (where applicable) in MySQL to local SQLite databases that are synced to
>> the relay nodes, cutting MySQL/MariaDB out of the picture entirely.
>>
>> Our client node count is currently less than 100.
>
> I wouldn't use SQLite.

May I ask why? Are there performance problems with using it? I've only
ever used it for small jobs where I/O and simultaneous access concerns
did not apply. Any feedback you have regarding the downsides or better
alternatives are welcome.

>
> There is no such thing to my knowledge as a tuning guide for MySQL
> specifically to work with Postfix.  The same general MySQL tuning
> practices apply to tuning for any load scenario.  You said "mostly
> stock" MariaDB - have you done any database tuning *at all*?  (And for
> that matter, what are you running it on?  Red Hat, for example, has
> historically shipped default MySQL/MariaDB configuration files that are
> at best worthless even when they're not actively harmful.)

All three nodes use Ubuntu 16.04 x64 and use stock Ubuntu-provided
Postfix 3.1 packages. The database node uses MariaDB 10.0.x from the
upstream MariaDB repo. I've done light configuration updates only based
on material I read over at the time, but I still consider myself a
newbie in regards to configuring MySQL/MariaDB. Any suggestions you have
for updating the configuration are welcome.

The load-balancer node uses the HAProxy 1.7 PPA from Vincent Bernat
(many thanks to Vincent for providing it). I'm unsure whether the checks
being applied are too aggressive, or perhaps are incomplete, but the
primary goal of the checks is to exercise the entire stack:

* alias resolution via db lookups
* sender/receiver actions based on lookup table results
* verify connectivity
* etc

These health checks are combined with Nagios mail queue threshold checks
that alert us to any mail which is getting hung up on the relays or the
sending nodes that we're responsible for.

I've uploaded the configuration files for our frontend (HAProxy),
backend (Postfix) and database (MariaDB) roles to a GitHub repo here:

https://github.com/deoren/postfix-examples

Here is a direct link to the config file with comments:
https://github.com/deoren/postfix-examples/blob/master/database-server/mysql/my.cnf

and a direct link to the config file with minimal comments:

https://github.com/deoren/postfix-examples/blob/pruned-comments/database-server/mysql/my.cnf


The HAProxy config file is available here:
https://github.com/deoren/postfix-examples/blob/master/load-balancer/etc/haproxy/haproxy.cfg
https://github.com/deoren/postfix-examples/blob/pruned-comments/load-balancer/etc/haproxy/haproxy.cfg

and the Postfix config files are available here:

https://github.com/deoren/postfix-examples/tree/master/backend-relay/etc/postfix
https://github.com/deoren/postfix-examples/tree/pruned-comments/backend-relay/etc/postfix

Any feedback that you have is welcome!
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Noel Jones-2
On 8/22/2018 10:29 AM, Noel Jones wrote:

> On 8/21/2018 3:17 PM, deoren wrote:
>> Hi all,
>>
>> We've been using Postfix for years with good results, but in recent
>> years have moved to a load-balanced HAProxy front-end with multiple
>> backend relay nodes. I've consulted various sources during that time
>> to perform the initial setup and light tuning since then.
>>
>> The health checks are run often and simulate a full email delivery
>> session in an attempt to exercise the full configuration (including
>> alias resolution and other related db queries).
>>
>> This setup works pretty well, but occasionally there is enough of a
>> delay between one of the steps in the simulated delivery that the
>> health check fails and the node is marked as down.
>>
>> When this occurs I have been unable to determine exactly why the
>> issue occurs. I've adjusted various timeout and timing settings
>> within HAProxy and Postfix, so I assume that our mostly stock
>> MariaDB 10.0.x installation is likely to blame.
>>
>> Do you have any recommendations for guides that cover tuning Postfix
>> and MySQL? I'd like to start there and work through the steps before
>> turning back to the configuration as a whole.
>>
>> Another option I'm considering is replicating the database contents
>> (where applicable) in MySQL to local SQLite databases that are
>> synced to the relay nodes, cutting MySQL/MariaDB out of the picture
>> entirely.
>>
>> Our client node count is currently less than 100.
>>
>> Thanks in advance for any guides that you can reference!
>
> Are you using the proxymap service with your table lookups?  That
> can greatly reduce the load on the MySQL server and improve
> performance, sometimes dramatically.
>
> http://www.postfix.org/postconf.5.html#proxy_read_maps
> http://www.postfix.org/proxymap.8.html
>
>
>    -- Noel Jones
>

We are, thank you for asking and for suggesting that. I've got a TODO
item stubbed out for me to return to later though regarding how I should
APPEND to the list instead of overwriting the default values. I'm not
aware of any support for doing the shell equivalent of:

VARIABLE="$VARIABLE;newvalue"

but please let me know if I'm overlooking anything. Because of my
ignorance on that point, I plan to return to the config after we upgrade
to Ubuntu 18.04 (and thus a newer version of Postfix) to see if there
are any new stock proxy_read_maps entries that I need to add to my list.
Please let me know if I am completely misunderstanding how defining
values like that work (perhaps append is the default behavior?).

The primary configuration file for our Postfix backend relays can be
found here:

https://github.com/deoren/postfix-examples/blob/master/backend-relay/etc/postfix/main.cf

and here if you want to strip out the verbose comments:

https://github.com/deoren/postfix-examples/blob/pruned-comments/backend-relay/etc/postfix/main.cf

The other config file fragments are in the same directory as that file,
both with verbose comments and with a Git branch that only includes
minimal comments.

Thanks in advance for any advice/corrections you can offer.
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Wietse Venema
In reply to this post by deoren
deoren:

> On 8/21/2018 6:25 PM, Wietse Venema wrote:
>
> > Have you looked in Postfix LOGs? For example, if there is a delay
> > from the start of the probe to the first Postfix logfile record,
> > then that would indicate a delay with looking up the client hostname,
> > and then the address for that hostname.
>
> Thank you for the response.
>
> I find entries like these:
>
> 2018-08-21T08:11:19.456927-05:00 relay1 postfix/smtpd[30722]: connect
> from unknown[192.168.2.199]
> 2018-08-21T08:11:34.332901-05:00 relay1 postfix/smtpd[30852]: connect
> from unknown[192.168.2.199]
> 2018-08-21T08:11:43.392959-05:00 relay1 postfix/smtpd[30853]: connect
> from unknown[192.168.2.199]
> 2018-08-21T08:11:52.424982-05:00 relay1 postfix/smtpd[30863]: connect
> from unknown[192.168.2.199]
> 2018-08-21T08:12:01.472960-05:00 relay1 postfix/smtpd[30865]: connect
> from unknown[192.168.2.199]
>
> 2018-08-21T08:12:07.465312-05:00 relay1 postfix/smtpd[30863]: lost
> connection after MAIL from unknown[192.168.2.199]
> 2018-08-21T08:12:07.466254-05:00 relay1 postfix/smtpd[30863]: disconnect
> from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
> 2018-08-21T08:12:11.816850-05:00 relay1 postfix/smtpd[30722]: lost
> connection after MAIL from unknown[192.168.2.199]
> 2018-08-21T08:12:11.817737-05:00 relay1 postfix/smtpd[30722]: disconnect
> from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
> 2018-08-21T08:12:12.884866-05:00 relay1 postfix/smtpd[30865]: connect
> from unknown[192.168.2.199]

'mail=0/1' means that Postfix rejected the MAIL FROM command (the
client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
commands).

You may want to examine the logs a little closer than looking for
'connect'. What else did processes 30863 and 30722 log for that
SMTP session?

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Viktor Dukhovni


> On Aug 23, 2018, at 8:04 PM, Wietse Venema <[hidden email]> wrote:
>
> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
> commands).
>
> You may want to examine the logs a little closer than looking for
> 'connect'. What else did processes 30863 and 30722 log for that
> SMTP session?

IIRC when "MAIL FROM:" is rejected due to size limit overflow, there
is nothing to indicate this in the logs.

--
        Viktor.

Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Phil Stracchino
In reply to this post by deoren
On 8/23/18 7:43 PM, deoren wrote:
>
> Here is a direct link to the config file with comments:
> https://github.com/deoren/postfix-examples/blob/master/database-server/mysql/my.cnf


OK.  Pretty much everything up to skip-name-resolve is compiled-in
default values.  (And so are most of the ones after.)  You don't need
16MB of key buffer unless you're using a lot of MyISAM tables, and  you
shouldn't be; in fact with all InnoDB tables (which you should be doing)
you should be able to get away with 8K of key buffer with room to spare.
 You almost certainly don't need max_allowed_packet = 16M either.

(In fact, futzing with max_allowed_packet without knowing what you're
doing can be enough by itself to cause database connection problems,
because if either the server *OR* the client tries to send a packet
larger than the other end of the connection is prepared to receive, the
connection will drop.)

max_connections = 151 is another compiled-in default.  It may be too
low.  So is table_open_cache = 400.  As a rule, table_definition_cache
should be at least equal to your TOTAL number of tables plus a safety
margin of 20% or so, and table_open_cache should be at minimum the
largest number of tables used by any commonly running query, times your
typical daily peak connections, times two.  Another way to tune it is to
execute SHOW GLOBAL STATUS LIKE 'open%' and increase table_open_cache in
steps until opened_tables stops increasing.  Don't be afraid to set it
to 4000 or 8000 or even higher; all it's caching is file handles, it
doesn't use much memory.

Consider turning off the query cache unless you have a high rate of
EXACT duplicate queries.  To tell if it's doing you any good, execute
the command SHOW GLOBAL STATUS LIKE 'qcache%' in MariaDB; if Qcache_hits
is not at least four or five times Qcache_inserts, you're probably
better off turning it off.  (Let's just say that cache invalidation is
one of the hard things in computer science.  Also, the solitary query
cache mutex is a bottleneck that will kill you at high query rates.)

Aaaaand it looks like your InnoDB configuration is also probably at
compiled-in defaults, which is to say, about big enough to keep your
pinochle score sheet in.


Some first steps:

- Use all InnoDB tables.  It's not hard to do, it's the default storage
engine since MySQL 5.5.  Do not increase any MyISAM-specific buffers
above compiled-in defaults unless you have a good reason.
- Don't touch, or even set, join_buffer_size.  It does not do what you
think it does.  Unless you know what you're doing and have a good
reason, don't mess with it.
- Ideally, set innodb_buffer_pool_size at least sufficient to hold your
entire DB data volume plus about 30%, but in any case at least 1GB.
- Ideally, set innodb_buffer_pool_instances to 1 per core you allow
MySQL to use, BUT NOT if this would bring individual InnoDB partitions
below 1GB.
- For performance reasons, you probably want to set
innodb_flush_log_at_trx_commit = 2 and innodb_autoinc_lock_mode = 2.

(The first makes InnoDB flush its write-ahead logs to disk once per
second instead of after every commit.  The second allows mysqld to
interleave rows inserted by different transactions, which means multiple
transactions can append to the same table simultaneously.)


Install a recent version of mysqltuner, run it, then go to dev.mysql.com
and look up everything it tells you so that you understand what it's
telling you.  If you have specific questions you can't find answers to
after you've done that, you can contact me offline and I'll try to help.
 Remember that almost all SQL database performance problems come down to
one of three things - bad queries, poor indexing, not enough RAM
allocated to the database - and of those, if you're using anything close
to the out-of-the-box configuration, you ALMOST CERTAINLY aren't
allocating enough RAM to the database unless you're barely using it.



--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Wietse Venema
On 8/23/2018 7:04 PM, Wietse Venema wrote:

> deoren:
>> On 8/21/2018 6:25 PM, Wietse Venema wrote:
>>
>>> Have you looked in Postfix LOGs? For example, if there is a delay
>>> from the start of the probe to the first Postfix logfile record,
>>> then that would indicate a delay with looking up the client hostname,
>>> and then the address for that hostname.
>>
>> Thank you for the response.
>>
>> I find entries like these:
>>
>> 2018-08-21T08:11:19.456927-05:00 relay1 postfix/smtpd[30722]: connect
>> from unknown[192.168.2.199]
>> 2018-08-21T08:11:34.332901-05:00 relay1 postfix/smtpd[30852]: connect
>> from unknown[192.168.2.199]
>> 2018-08-21T08:11:43.392959-05:00 relay1 postfix/smtpd[30853]: connect
>> from unknown[192.168.2.199]
>> 2018-08-21T08:11:52.424982-05:00 relay1 postfix/smtpd[30863]: connect
>> from unknown[192.168.2.199]
>> 2018-08-21T08:12:01.472960-05:00 relay1 postfix/smtpd[30865]: connect
>> from unknown[192.168.2.199]
>>
>> 2018-08-21T08:12:07.465312-05:00 relay1 postfix/smtpd[30863]: lost
>> connection after MAIL from unknown[192.168.2.199]
>> 2018-08-21T08:12:07.466254-05:00 relay1 postfix/smtpd[30863]: disconnect
>> from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
>> 2018-08-21T08:12:11.816850-05:00 relay1 postfix/smtpd[30722]: lost
>> connection after MAIL from unknown[192.168.2.199]
>> 2018-08-21T08:12:11.817737-05:00 relay1 postfix/smtpd[30722]: disconnect
>> from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
>> 2018-08-21T08:12:12.884866-05:00 relay1 postfix/smtpd[30865]: connect
>> from unknown[192.168.2.199]
>
> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
> commands).

Thank you for the response and for going into detail.

I suspect I completely overlooked it, but do you recall offhand where
one can read more about the specific fields used in the messages that
Postfix logs?

Regarding the rejection for the MAIL FROM command, HAProxy noted the
same thing and declared the health check a failure. Here is the relevant
line in the HAProxy config:

https://github.com/deoren/postfix-examples/blob/1f954130ee89032b3e5e3151a35f12dbd44e593b/load-balancer/etc/haproxy/haproxy.cfg#L479

>
> You may want to examine the logs a little closer than looking for
> 'connect'.

That is a valid suggestion. I originally tried to find all log entries
within the same 1 minute time frame as when the health check failure was
noted by HAProxy. I don't recall for sure why the first block of log
entries was recorded in our ticket, other than perhaps just an
illustration of the type of messages that were logged around that time
or, perhaps to confirm what systems were actively making connections at
that time.

What else did processes 30863 and 30722 log for that
> SMTP session?

$ sudo zgrep -E '30863|30722' mail.log-20180822.gz | grep '8:12'

2018-08-21T08:12:07.465312-05:00 relay1 postfix/smtpd[30863]: lost
connection after MAIL from unknown[192.168.2.199]
2018-08-21T08:12:07.466254-05:00 relay1 postfix/smtpd[30863]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
2018-08-21T08:12:11.816850-05:00 relay1 postfix/smtpd[30722]: lost
connection after MAIL from unknown[192.168.2.199]
2018-08-21T08:12:11.817737-05:00 relay1 postfix/smtpd[30722]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=0/1 commands=1/2
2018-08-21T08:12:21.012939-05:00 relay1 postfix/smtpd[30863]: connect
from unknown[192.168.2.199]
2018-08-21T08:12:21.020352-05:00 relay1 postfix/smtpd[30863]: NOQUEUE:
permit: RCPT from unknown[192.168.2.199]: action=OK for Client
host=unknown[192.168.2.199] from
proxy:mysql:/etc/postfix/mysql-relay_access.cf;
from=<[hidden email]>
to=<[hidden email]> proto=ESMTP
helo=<relay-lb1.subdomain.example.org>
2018-08-21T08:12:21.037991-05:00 relay1 postfix/smtpd[30863]:
0938F3FCB0: client=unknown[192.168.2.199]
2018-08-21T08:12:21.071673-05:00 relay1 postfix/smtpd[30863]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=1 rcpt=1 data=1 quit=1 commands=5
2018-08-21T08:12:29.204911-05:00 relay1 postfix/smtpd[30722]: connect
from unknown[192.168.2.199]
2018-08-21T08:12:29.209091-05:00 relay1 postfix/smtpd[30722]: NOQUEUE:
permit: RCPT from unknown[192.168.2.199]: action=OK for Client
host=unknown[192.168.2.199] from
proxy:mysql:/etc/postfix/mysql-relay_access.cf;
from=<[hidden email]>
to=<[hidden email]> proto=ESMTP
helo=<relay-lb1.subdomain.example.org>
2018-08-21T08:12:29.226697-05:00 relay1 postfix/smtpd[30722]:
374CF3FCB0: client=unknown[192.168.2.199]
2018-08-21T08:12:29.268665-05:00 relay1 postfix/smtpd[30722]: disconnect
from unknown[192.168.2.199] ehlo=1 mail=1 rcpt=1 data=1 quit=1 commands=5
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Viktor Dukhovni
On 8/23/2018 7:23 PM, Viktor Dukhovni wrote:

>
>
>> On Aug 23, 2018, at 8:04 PM, Wietse Venema <[hidden email]> wrote:
>>
>> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
>> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
>> commands).
>>
>> You may want to examine the logs a little closer than looking for
>> 'connect'. What else did processes 30863 and 30722 log for that
>> SMTP session?
>
> IIRC when "MAIL FROM:" is rejected due to size limit overflow, there
> is nothing to indicate this in the logs.
>

Hi Viktor,

Thank you for your reply.

Is there anything that I can look for in the Postfix config that may
lead to a size limit overflow, or is the cause something outside of our
control?

If the two are related (it doesn't sound like the two are), the
message_size_limit setting is configured as 76800000 and the health
checks being performed are for a very small amount of data.

https://github.com/deoren/postfix-examples/blob/1f954130ee89032b3e5e3151a35f12dbd44e593b/backend-relay/etc/postfix/main.cf#L103
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
In reply to this post by Phil Stracchino
On 8/23/2018 9:28 PM, Phil Stracchino wrote:

> On 8/23/18 7:43 PM, deoren wrote:
>>
>> Here is a direct link to the config file with comments:
>> https://github.com/deoren/postfix-examples/blob/master/database-server/mysql/my.cnf
>
>
> OK.  Pretty much everything up to skip-name-resolve is compiled-in
> default values.  (And so are most of the ones after.)  You don't need
> 16MB of key buffer unless you're using a lot of MyISAM tables, and  you
> shouldn't be; in fact with all InnoDB tables (which you should be doing)
> you should be able to get away with 8K of key buffer with room to spare.
>   You almost certainly don't need max_allowed_packet = 16M either.
>
> (In fact, futzing with max_allowed_packet without knowing what you're
> doing can be enough by itself to cause database connection problems,
> because if either the server *OR* the client tries to send a packet
> larger than the other end of the connection is prepared to receive, the
> connection will drop.)
>
> max_connections = 151 is another compiled-in default.  It may be too
> low.  So is table_open_cache = 400.  As a rule, table_definition_cache
> should be at least equal to your TOTAL number of tables plus a safety
> margin of 20% or so, and table_open_cache should be at minimum the
> largest number of tables used by any commonly running query, times your
> typical daily peak connections, times two.  Another way to tune it is to
> execute SHOW GLOBAL STATUS LIKE 'open%' and increase table_open_cache in
> steps until opened_tables stops increasing.  Don't be afraid to set it
> to 4000 or 8000 or even higher; all it's caching is file handles, it
> doesn't use much memory.
>
> Consider turning off the query cache unless you have a high rate of
> EXACT duplicate queries.  To tell if it's doing you any good, execute
> the command SHOW GLOBAL STATUS LIKE 'qcache%' in MariaDB; if Qcache_hits
> is not at least four or five times Qcache_inserts, you're probably
> better off turning it off.  (Let's just say that cache invalidation is
> one of the hard things in computer science.  Also, the solitary query
> cache mutex is a bottleneck that will kill you at high query rates.)
>
> Aaaaand it looks like your InnoDB configuration is also probably at
> compiled-in defaults, which is to say, about big enough to keep your
> pinochle score sheet in.
>
>
> Some first steps:
>
> - Use all InnoDB tables.  It's not hard to do, it's the default storage
> engine since MySQL 5.5.  Do not increase any MyISAM-specific buffers
> above compiled-in defaults unless you have a good reason.
> - Don't touch, or even set, join_buffer_size.  It does not do what you
> think it does.  Unless you know what you're doing and have a good
> reason, don't mess with it.
> - Ideally, set innodb_buffer_pool_size at least sufficient to hold your
> entire DB data volume plus about 30%, but in any case at least 1GB.
> - Ideally, set innodb_buffer_pool_instances to 1 per core you allow
> MySQL to use, BUT NOT if this would bring individual InnoDB partitions
> below 1GB.
> - For performance reasons, you probably want to set
> innodb_flush_log_at_trx_commit = 2 and innodb_autoinc_lock_mode = 2.
>
> (The first makes InnoDB flush its write-ahead logs to disk once per
> second instead of after every commit.  The second allows mysqld to
> interleave rows inserted by different transactions, which means multiple
> transactions can append to the same table simultaneously.)
>
>
> Install a recent version of mysqltuner, run it, then go to dev.mysql.com
> and look up everything it tells you so that you understand what it's
> telling you.  If you have specific questions you can't find answers to
> after you've done that, you can contact me offline and I'll try to help.
>   Remember that almost all SQL database performance problems come down to
> one of three things - bad queries, poor indexing, not enough RAM
> allocated to the database - and of those, if you're using anything close
> to the out-of-the-box configuration, you ALMOST CERTAINLY aren't
> allocating enough RAM to the database unless you're barely using it.
>

This is immensely helpful, thank you very much for taking the time to
respond in so much detail. I will spend time going over our
configuration, making the changes you indicate and reading over the
settings on dev.mysql.com.

Again, many thanks!
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Wietse Venema
In reply to this post by deoren
deoren:
> > 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
> > client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
> > commands).
>
> Thank you for the response and for going into detail.
>
> I suspect I completely overlooked it, but do you recall offhand where
> one can read more about the specific fields used in the messages that
> Postfix logs?

Sorry, the 'adhoc' logging subsystem is a preliminary implementation
that I wrote to get Postfix out the door. It is therefore not
adequately documented, not even the 'routine' logging.

Here's asuggestion: if all your probes are sent from the same IP
address 192.168.2.199, then please do:

        # postconf debug-peer-list=192.168.2.199
        # postfix reload

(debug_peer_list is used in some DEBUG_README example, and it is
documented in the postconf(5) manpage.

That will log the requests (nmot message content) and most importantly
the responses.

> Regarding the rejection for the MAIL FROM command, HAProxy noted the
> same thing and declared the health check a failure. Here is the relevant
> line in the HAProxy config:
>
> https://github.com/deoren/postfix-examples/blob/1f954130ee89032b3e5e3151a35f12dbd44e593b/load-balancer/etc/haproxy/haproxy.cfg#L479

I see nothing in the MAIL FROM that would offend Postfix, so it
will be interesting to see what the problem is.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

deoren
On 8/24/2018 6:51 AM, Wietse Venema wrote:

> deoren:
>>> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
>>> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
>>> commands).
>>
>> Thank you for the response and for going into detail.
>>
>> I suspect I completely overlooked it, but do you recall offhand where
>> one can read more about the specific fields used in the messages that
>> Postfix logs?
>
> Sorry, the 'adhoc' logging subsystem is a preliminary implementation
> that I wrote to get Postfix out the door. It is therefore not
> adequately documented, not even the 'routine' logging.

Acknowledged.

> Here's asuggestion: if all your probes are sent from the same IP
> address 192.168.2.199, then please do:
>
> # postconf debug-peer-list=192.168.2.199
> # postfix reload

Thank you for the tip, I will do this. I used it once before for an
extended period of time, but couldn't sufficiently make sense of the
verbose output to get anywhere. I should have made the time to post here
with the results to get help deciphering them. I assume that it's
probably best to upload a larger file of the entries to GitHub and link
to the log file like I've done wit the config files?

> (debug_peer_list is used in some DEBUG_README example, and it is
> documented in the postconf(5) manpage.
>
> That will log the requests (nmot message content) and most importantly
> the responses.

On a related note, is there any way to easily filter the messages that
result from using debug-peer-list? We currently send nearly all log
content via syslog to a central receiver and then into a Graylog
instance. The instance is minimally tuned, so it would probably not
handle the load well if all backend relays send their debug messages
over to the central receiver.

>> Regarding the rejection for the MAIL FROM command, HAProxy noted the
>> same thing and declared the health check a failure. Here is the relevant
>> line in the HAProxy config:
>>
>> https://github.com/deoren/postfix-examples/blob/1f954130ee89032b3e5e3151a35f12dbd44e593b/load-balancer/etc/haproxy/haproxy.cfg#L479
>
> I see nothing in the MAIL FROM that would offend Postfix, so it
> will be interesting to see what the problem is.

Thank you for confirming that and for the help. I appreciate you taking
the time to work with me on this.
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Wietse Venema
deoren:

> On 8/24/2018 6:51 AM, Wietse Venema wrote:
> > deoren:
> >>> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
> >>> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
> >>> commands).
> >>
> >> Thank you for the response and for going into detail.
> >>
> >> I suspect I completely overlooked it, but do you recall offhand where
> >> one can read more about the specific fields used in the messages that
> >> Postfix logs?
> >
> > Sorry, the 'adhoc' logging subsystem is a preliminary implementation
> > that I wrote to get Postfix out the door. It is therefore not
> > adequately documented, not even the 'routine' logging.
>
> Acknowledged.
>
> > Here's asuggestion: if all your probes are sent from the same IP
> > address 192.168.2.199, then please do:
> >
> > # postconf debug-peer-list=192.168.2.199
> > # postfix reload
>
> Thank you for the tip, I will do this. I used it once before for an
> extended period of time, but couldn't sufficiently make sense of the
> verbose output to get anywhere. I should have made the time to post here
> with the results to get help deciphering them. I assume that it's
> probably best to upload a larger file of the entries to GitHub and link
> to the log file like I've done wit the config files?

No, you would have to censor the content and I don't want to look
at censored evidence.  Just take the output of

        $ grep 192.168.2.199 the-log-file | grep '[<>] '

(note the space before the closing quote) and then directly email
me the output for one failed HaProxy attempt.

> On a related note, is there any way to easily filter the messages that
> result from using debug-peer-list? We currently send nearly all log
> content via syslog to a central receiver and then into a Graylog
> instance. The instance is minimally tuned, so it would probably not
> handle the load well if all backend relays send their debug messages
> over to the central receiver.

Definitely DO NOT turn on debug logging proactively. The Postfix
logging is quite sufficient for an expert, and for a non-expert,
the noise just drowns out the useful information.

In this case, the mail=0/1 is justification to turn on debug logging,
but only for that client, not everyone.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: Does anyone have any good tips/tricks/guides for tuning MySQL/MariaDB for use with Postfix?

Wietse Venema
Correction for the parameter name: debug-peer-list should be debug_peer_list.
Wietse Venema:

> deoren:
> > On 8/24/2018 6:51 AM, Wietse Venema wrote:
> > > deoren:
> > >>> 'mail=0/1' means that Postfix rejected the MAIL FROM command (the
> > >>> client sent 1 MAIL FROM command, and Postfix accepted 0 MAIL FROM
> > >>> commands).
> > >>
> > >> Thank you for the response and for going into detail.
> > >>
> > >> I suspect I completely overlooked it, but do you recall offhand where
> > >> one can read more about the specific fields used in the messages that
> > >> Postfix logs?
> > >
> > > Sorry, the 'adhoc' logging subsystem is a preliminary implementation
> > > that I wrote to get Postfix out the door. It is therefore not
> > > adequately documented, not even the 'routine' logging.
> >
> > Acknowledged.
> >
> > > Here's asuggestion: if all your probes are sent from the same IP
> > > address 192.168.2.199, then please do:
> > >
> > > # postconf debug-peer-list=192.168.2.199

This should be debug_peer_list.

> > > # postfix reload
> >
> > Thank you for the tip, I will do this. I used it once before for an
> > extended period of time, but couldn't sufficiently make sense of the
> > verbose output to get anywhere. I should have made the time to post here
> > with the results to get help deciphering them. I assume that it's
> > probably best to upload a larger file of the entries to GitHub and link
> > to the log file like I've done wit the config files?
>
> No, you would have to censor the content and I don't want to look
> at censored evidence.  Just take the output of
>
> $ grep 192.168.2.199 the-log-file | grep '[<>] '
>
> (note the space before the closing quote) and then directly email
> me the output for one failed HaProxy attempt.
>
> > On a related note, is there any way to easily filter the messages that
> > result from using debug-peer-list? We currently send nearly all log
> > content via syslog to a central receiver and then into a Graylog
> > instance. The instance is minimally tuned, so it would probably not
> > handle the load well if all backend relays send their debug messages
> > over to the central receiver.
>
> Definitely DO NOT turn on debug logging proactively. The Postfix
> logging is quite sufficient for an expert, and for a non-expert,
> the noise just drowns out the useful information.
>
> In this case, the mail=0/1 is justification to turn on debug logging,
> but only for that client, not everyone.
>
> Wietse
>