Postfix e Mysql: ho qualche problema

[DarkJ]

Ciao a tutti,
da qualche giorno a questa parte mi sono accorto di avere uno spiacevole problema al server di posta, e in particolare sulle mail in uscita.

Il server in questione è un i7 con 8GB di RAM, come server di posta uso Postfix.

Vi posto direttamente i log:
mail.err:

Dec 15 14:14:48 deneb postfix/trivial-rewrite[1798]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 14:14:48 deneb postfix/trivial-rewrite[1799]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 14:14:48 deneb postfix/trivial-rewrite[1800]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:05:01 deneb postfix/trivial-rewrite[12159]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:05:03 deneb postfix/trivial-rewrite[12379]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:08:03 deneb postfix/trivial-rewrite[12579]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:08:05 deneb postfix/trivial-rewrite[12600]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem

mail.info

Dec 15 16:08:03 deneb postfix/proxymap[12157]: warning: mysql query failed: MySQL server has gone away
Dec 15 16:08:03 deneb postfix/trivial-rewrite[12579]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:08:04 deneb postfix/master[2003]: warning: process /usr/lib/postfix/trivial-rewrite pid 12579 exit status 1
Dec 15 16:08:05 deneb postfix/proxymap[12157]: warning: mysql query failed: MySQL server has gone away
Dec 15 16:08:05 deneb postfix/trivial-rewrite[12600]: fatal: proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf(0,lock|fold_fix): table lookup problem
Dec 15 16:08:06 deneb postfix/smtpd[12599]: warning: problem talking to service rewrite: Success
Dec 15 16:08:06 deneb postfix/master[2003]: warning: process /usr/lib/postfix/trivial-rewrite pid 12600 exit status 1
Dec 15 16:08:06 deneb postfix/master[2003]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling

La spiacevole conseguenza è che le mail in questione non partono e si perdono.
Il fatto che poi non ci sia una cadenza fissa (succede ogni tanto, durante la giornata, senza uno schema particolare) mi rende ancora più difficile capire perchè succeda.

Il mio main.cf di Postfix:

smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no

# appending .domain is the MUA's job.
append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h

readme_directory = /usr/share/doc/postfix

# TLS parameters
smtpd_tls_cert_file = /etc/ssl/certs/iRedMail_CA.pem
smtpd_tls_key_file = /etc/ssl/private/iRedMail.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache

# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.

myhostname = deneb.xxx.tld
alias_maps = hash:/etc/postfix/aliases
alias_database = hash:/etc/postfix/aliases
myorigin = deneb.xxx.tld
mydestination = $myhostname, localhost, localhost.localdomain, localhost.$myhostname
relayhost =
mynetworks = 127.0.0.0/8
mailbox_size_limit = 15728640
recipient_delimiter = +
inet_interfaces = all
html_directory = /usr/share/doc/postfix/html
mydomain = xxx.tld
mail_name = iRedMail
mail_version = 0.6.1
relay_domains = $mydestination, proxy:mysql:/etc/postfix/mysql_relay_domains.cf
inet_protocols = ipv4
mynetworks_style = subnet
smtpd_data_restrictions = reject_unauth_pipelining
smtpd_reject_unlisted_recipient = yes
smtpd_sender_restrictions = permit_mynetworks, reject_sender_login_mismatch, permit_sasl_authenticated
delay_warning_time = 0h
policy_time_limit = 3600
maximal_queue_lifetime = 1d
bounce_queue_lifetime = 1d
proxy_read_maps = $canonical_maps $lmtp_generic_maps $local_recipient_maps $mydestination $mynetworks $recipient_bcc_maps $recipient_canonical_maps $relay_domains $relay_recipient_maps $relocated_maps $sender_bcc_maps $sender_canonical_maps $smtp_generic_maps $smtpd_sender_login_maps $transport_maps $virtual_alias_domains $virtual_alias_maps $virtual_mailbox_domains $virtual_mailbox_maps
smtpd_helo_required = yes
smtpd_helo_restrictions = permit_mynetworks,permit_sasl_authenticated, check_helo_access pcre:/etc/postfix/helo_access.pcre
queue_run_delay = 300s
minimal_backoff_time = 300s
maximal_backoff_time = 4000s
enable_original_recipient = no
disable_vrfy_command = yes
home_mailbox = Maildir/
allow_min_user = no
virtual_minimum_uid = 5000
virtual_uid_maps = static:5000
virtual_gid_maps = static:5000
virtual_mailbox_base = /var/vmail
transport_maps = proxy:mysql:/etc/postfix/mysql_transport_maps_user.cf, proxy:mysql:/etc/postfix/mysql_transport_maps_domain.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql_virtual_alias_maps.cf, proxy:mysql:/etc/postfix/mysql_domain_alias_maps.cf
sender_bcc_maps = proxy:mysql:/etc/postfix/mysql_sender_bcc_maps_domain.cf, proxy:mysql:/etc/postfix/mysql_sender_bcc_maps_user.cf
recipient_bcc_maps = proxy:mysql:/etc/postfix/mysql_recipient_bcc_maps_domain.cf, proxy:mysql:/etc/postfix/mysql_recipient_bcc_maps_user.cf
relay_recipient_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
smtpd_sender_login_maps = proxy:mysql:/etc/postfix/mysql_sender_login_maps.cf
smtpd_reject_unlisted_sender = yes
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain =
smtpd_sasl_security_options = noanonymous
broken_sasl_auth_clients = yes
smtpd_sasl_authenticated_header = no
smtpd_recipient_restrictions = reject_unknown_sender_domain, reject_unknown_recipient_domain, reject_non_fqdn_sender, reject_non_fqdn_recipient, reject_unlisted_recipient, permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination, reject_non_fqdn_helo_hostname, reject_invalid_helo_hostname
#check_policy_service inet:127.0.0.1:10031
smtpd_tls_security_level = may
smtpd_enforce_tls = no
smtpd_tls_loglevel = 0
tls_random_source = dev:/dev/urandom
tls_daemon_random_source = dev:/dev/urandom
#
# Uncomment the following line to enable policyd sender throttle.
#
#smtpd_end_of_data_restrictions = check_policy_service inet:127.0.0.1:10032
mailbox_command = /usr/lib/dovecot/deliver
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1
smtpd_sasl_type = dovecot
smtpd_sasl_path = dovecot-auth
content_filter = smtp-amavis:[127.0.0.1]:10024

Il mio my.cnf:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
#key_buffer = 16M
#max_allowed_packet = 16M
thread_stack = 256K
#thread_cache_size = 8
# * added values
key_buffer = 128M
max_allowed_packet = 64M
max_connections = 500
myisam_sort_buffer_size = 64M
#join_buffer_size = 1M - 2M
join_buffer_size = 3M
read_buffer_size = 1M
sort_buffer_size = 2M
#table_cache = 2048 - 3072
table_cache = 4096
thread_cache_size = 128
thread_concurrency = 8
wait_timeout = 35
connect_timeout = 10
max_connect_errors = 10
tmp_table_size = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_heap_table_size = 64M
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
#query_cache_limit = 1M
#query_cache_size = 16M
# * added values
query_cache_limit = 4M
query_cache_size = 64MB
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
#key_buffer = 16M
# * added values
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

La cosa più strana è che il problema si presenta solo con una tabella in particolare, che però non presenta problemi.
E non ho visto nessun'altro script in giro sul server che abbia lo stesso problema di connessioni a mysql..

Idee?

Grazie

[DarkJ]

Nessuno che abbia qualche idea? Non so, magari da guardare più verso mysql o postfix, insomma un'impressione

[Antonio]

Nessuno che abbia qualche idea? Non so, magari da guardare più verso mysql o postfix, insomma un'impressione

Penso che "warning: mysql query failed: MySQL server has gone away" sia abbastanza esplicativo del problema

Ti connetti a mysql tramite rete o lo hai in locale? Nel primo caso controllerei anche se non ci son problemi di rete.

[DarkJ]

Penso che "warning: mysql query failed: MySQL server has gone away" sia abbastanza esplicativo del problema

Ti connetti a mysql tramite rete o lo hai in locale? Nel primo caso controllerei anche se non ci son problemi di rete.

In locale.
La cosa che mi lascia un po' in dubbio è: perchè su una sola tabella?

E tra l'altro ci sono diversi siti web sul server, ma non ho mai visto problemi di questo tipo.

[Alex]

attiva il log di mysql e vedi cosa accade quando postfix da' quell'errore

[DarkJ]

attiva il log di mysql e vedi cosa accade quando postfix da' quell'errore

Mi sembra una buona idea, grazie!