CODEWITHSUNDEEP
phpmysqlwordpressserver
uvishere
uvishere

Reputation: 430

wordpress php and mysql connection frequently disconnected

I have a ubuntu 15.04 server with 160GB of RAM and 40 cpu cores. I am running a high traffic wordpress site with nearly 200k visitors per day. The webserver is nginx and cache server is varnish. My www.conf php configuration is

pm = ondemand
pm.max_children = 300
pm.start_servers = 80
pm.min_spare_servers = 50
pm.max_spare_servers = 300 
pm.process_idle_timeout = 10s
pm.max_requests = 1000

The Mysql Connection configuration is

sam-recover         = BACKUP
max_connections        = 2000
query_cache_limit   = 0
query_cache_size    = 0
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 100M
innodb_log_file_size=268435456
innodb_log_buffer_size = 4G
innodb_buffer_pool_size = 8G
innodb_fast_shutdown = 0
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names

[mysql]
[isamchk]
key_buffer              = 16M
sort_buffer_size = 32M

Main problem is, The php and mysql database connection disconnects frequently and it shows "Error establishing Database connection". Mysql service is running properly. When I restart php5-fpm service, then the site starts working, but sometimes later, same thing happens.

I have tried different values for pm configurations but no any tweak actually worked.

There is nothing in the logs too. How can I debug connection problem between these two services?

Upvotes: 1

Views: 668

Answers (2)

uvishere
uvishere

Reputation: 430

So, this is how I solved this problem. Actually the mysql connection was only 214 even though I set it 2000. I checked it using the command in mysql:

mysql> show variables like 'max_connections';

And the result was:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

The problem is that the maximum number of open files allowed is too small, by default 1024. So I used open files limit to increase the file open limit:

[mysqld]
.........
open_files_limit = 8192
max_connections = 2000
.........

... and mysqld_safe will increase the ulimit on its own before invoking the actual mysqld daemon.

And the running MySQL server will reveal the desired max_connections setting stuck this time:

mysql> show variables like 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

Hence the problem solved.

Upvotes: 1

Joe Beans
Joe Beans

Reputation: 294

Use mysqltuner to tune your mysql configuration. You are most likely running out of allocated resources and that is causing the database connection to timeout/fail.

Upvotes: 0

Related Questions