No.. Not in /var/log/messages, not in /var/log/mysql/mysql-bin.err - /var/log/mysql.log is empty.

It is said that MYSQL logging can be turned on while running..? I don't know how to do it nor found something when googlin'..

Further investigation gave:

/etc/postfix/mysql-virtual_client.cf has access to the Postfix blacklist. The blacklist contained only three rows, but I have deleted them.

I think it could have been a race condition: IPSConfig or Postfix throws two queries at MYSQL at the same time: Maybe to check "sender" with one query and "client" with another almost simultaneously. As there are many postfix processes (anvil, pickup, qmgr etc.) this could likely happen.

And the postfix timeout must be very low.

And mysql_virtual_client.cf is not the only spot the problems occur. Must be similar every time postfix interacts with MySQL. Besides, I have no problem with MySQL. However, 20% of the queries get smashed (!):

I think it could have been a race condition: IPSConfig or Postfix throws two queries at MYSQL at the same time: Maybe to check "sender" with one query and "client" with another almost simultaneously. As there are many postfix processes (anvil, pickup, qmgr etc.) this could likely happen.

Click to expand...

I dont think that there is any general problem in this setup as it works quite nicely on many large mailservers and I dont see such problems on our mailsystems. On website scripts in busy websites you might have hundreds of simultanious accesses to the same database table, mysql is made to handle this.

How many mail accounts do you have on that server?

Also queries in state "Abgebrochen" dont have to be same queries that were logged in the postfix table.

I dont think that there is any general problem in this setup as it works quite nicely on many large mailservers and I dont see such problems on our mailsystems.

Click to expand...

OK - could you point me to an example my.cnf please that runs fine with ISConfig3?

On website scripts in busy websites you might have hundreds of simultanious accesses to the same database table, mysql is made to handle this.

Click to expand...

Exactly. With ISPConfig 2 on a much weaker machine and the same occupation, I've almost never had these logs.

How many mail accounts do you have on that server?

Click to expand...

25 so far. ISPConfig2 handled more that 100.

Also queries in state "Abgebrochen" dont have to be same queries that were logged in the postfix table.

Click to expand...

True, but at the moment I see what is hitting on the MySQL and it is almost exclusively ispconfig. ispconfig alone has usually more than 10 processes open / sleeping with 50% of them over 10 seconds duration. Is that normal?

OK - could you point me to an example my.cnf please that runs fine with ISConfig3?

Click to expand...

the default configuration of all supported Linux distributions is fine. No need to alter them, jsut add:

max_connections = 500
max_user_connections = 500

in the [mysqld] section of the file.

Exactly. With ISPConfig 2 on a much weaker machine and the same occupation, I've almost never had these logs.

Click to expand...

Comparuing ispconfig 2 and 3 makes no sense. The setup is not comparable. Thats is as if you compare windows with Linux.

ISPConfig 3 handles much higher loads then ispconfig 2.

25 so far. ISPConfig2 handled more that 100.

Click to expand...

Thats a very small setup which can definately not have a bottleneck in mysql. ISPConfig 3 is known to handle a few thousand accounts per server and even on our server here we have a few hundred in the default configuration.

True, but at the moment I see what is hitting on the MySQL and it is almost exclusively ispconfig. ispconfig alone has usually more than 10 processes open / sleeping with 50% of them over 10 seconds duration. Is that normal?

I have no f***ing clue what it is and I need a workaround. As far as I see, it it mostly postfix's trivial-rewrite programm that dies.

I've seen two differences in you my.cnf setup: I did not have skip-bdb and skip-innodb . "skip-innodb" is working - I have no InnoDB here. But as soon as I say "skip-bdb" mysql does not restart! I have no idea why.

I've tried to give MYSQL even more resources than you said, but to no avail. We NEVER have more that 0.1 server load when the problems occur. It is very often at the very first contact of a mailserver coming via SMTP.

Next, I've avoided to use postfix's proxy command proxy:mysql:/etc/postfix.. but mysql:/etc/postfix instead so the commands do not get queued but can hit the mysqlserver with more threads (why not, we have 4 cpus?). No difference.

What I'd suggest is:

Postfix uses no longer MySQL to ask for data, but flat files.

Is there a chance that ISPConfig produces those flat file on demand? I think it was like this back in IPSConfig 2...?

I have restarted the whole server just in case..

As long as we cannot identify the error (and it does definitely not look like), I really need a workaround. My Clients are beginning to realize something is wrong.

Is there a chance that ISPConfig produces those flat file on demand? I think it was like this back in IPSConfig 2...?

Click to expand...

Youcan write a plugiun for that. But we dont plan to change the postfix setup in ipconfig 3. I really understand that you have a problem there with your installation but if there are thousands of installations which are much larger then your setup and none of these have this problem, then there is no reason to change the setup.

So instead of talking to change a etup which has prooven to work reliably an many systems, you shoul try to debug your mysql server by enabling mysql logging to see why postfix is not able to connect to mysql.

So instead of talking to change a etup which has prooven to work reliably an many systems, you shoul try to debug your mysql server by enabling mysql logging to see why postfix is not able to connect to mysql.

Click to expand...

I did that already and have seen and when that happens, I see not even a query in the MYSQL log. The lock must have happened before. Postfix shown an error without having passed the query to MYSQL - that is what I think I see.

If it would be your server and your customers, what would you do instead?

3) E.g. create a new server from scratch as virtual server (works fine on a desktop pc with vmware or other free virtualisation software) and then check if you can reproduce the problem there and if not, compare the relevant config files for postfix and mysql.

I can confirm that increasing wait_time in mysql config seems to help with this issue. I recently had this problem and after setting wait_time to 3600 (i.e. 1 hour), "table lookup problem" and "MySQL server has gone away" errors stopped appearing.

From what I have found, the default behaviour of auto reconnecting has changed between mysql 5.0 and 5.1 and with 5.1 the default client behaviour is not to reconnect after losing connection, which can be changed by enabling auto reconnections but the library used by postfix to connect to mysql probably counts on the auto reconnection and while with mysql 5.0 it reconnects automatically, with 5.1 it doesn't and the query can't be executed and it ends with an error.