I have many PHP-Instances (250 to 500 and more), which open a DB-Connection and do SELECT's, UPDATE's and INSERT's. After few seconds i get the following Error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (4)' in /var/www/xxx/vendor/propel/propel/src/Propel/Runtime/Connection/PdoConnection.php:49
I spend at least 30 hours to find a solution for that. In my opinion, the fault lies in a wrong configuration of MySQL or Debian. I found various configuration-options, but none of them work.
Additional informations
- The error only occurs on remote servers - when I start the scripts on the DB-Server directly, i get no errors.
Current configs
sysctl.conf
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0
net.ipv4.ip_forward = 1
net.ipv4.neigh.default.gc_thresh1 = 4096
net.ipv4.neigh.default.gc_thresh2 = 8192
net.ipv4.neigh.default.gc_thresh3 = 16384
net.ipv4.neigh.default.gc_interval = 5
net.ipv4.neigh.default.gc_stale_time = 120
net.core.netdev_max_backlog = 262144
#net.core.rmem_default = 16777216
net.core.rmem_max = 108544
net.core.somaxconn = 32768
net.core.wmem_max = 108544
net.netfilter.nf_conntrack_max = 10000000
net.netfilter.nf_conntrack_tcp_timeout_established = 40
net.netfilter.nf_conntrack_tcp_timeout_close = 10
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 10
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 10
net.netfilter.nf_conntrack_tcp_timeout_last_ack = 10
net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 10
net.netfilter.nf_conntrack_tcp_timeout_syn_sent = 10
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 10
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_max_orphans = 262144
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_sack = 0
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_wmem = 4096 16384 16777216
etc/mysql/my.cf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[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
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = {local server ip}
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 1100
myisam-recover = BACKUP
max_connections = 1000
#table_cache = 64
#thread_concurrency = 10
query_cache_limit = 2G
query_cache_size = 32M
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes
#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_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
load modules - modules.conf
nf_conntrack
nf_conntrack_ipv4
script start on startup
echo "1" > /proc/sys/net/ipv4/tcp_tw_reuse
echo "1" > /proc/sys/net/ipv4/tcp_tw_recycle
echo "15" > /proc/sys/net/ipv4/tcp_fin_timeout
/etc/init.d/networking restart
My System
- Debian 8.2
- MySQL 5.5.44-0+deb8u1
- php5-mysql 5.6.13+dfsg-0+deb8u1
- Virtual Machine with 64GB RAM, 48 CPU's (Intel Xeon E7540 at 2.0 GHz)
- Run at an ESxi-Host v. 6.0.0 2494585
load Testing
start x PHP-instances / loadTester.php
<?php
require_once __DIR__ . "/autoloader.php";
$numberOfInstances = 100;
$index = 0;
$numberOfDataset = 500;
while($index < $numberOfInstances) {
@system( "php " . __DIR__ . "/loadTest.php $index $numberOfDataset > /dev/null &" );
$index++;
}
one instance SELECT / loadTest.php
<?php
require_once __DIR__ . "/autoloader.php";
$skip = $argv[ 1 ];
$numberOfDataset = $argv[ 2 ];
$loops = 100;
$index = 0;
$skip = $skip * $numberOfDataset;
while ( $index < $loops ) {
$adresses = AddressQuery::create()->offset( $skip )->limit( $numberOfDataset )->find();
}
