PHP-FPM crashing on mySQL backup?

U

urbee

Verified User
Joined
Mar 27, 2009
Messages
118
We are having issues on a server that needs to backup (dump) a database in the size of 18 GB (and a few smaller ones) - or so i am guessing.

The PHP-FPM process keeps crashing every night at around 01:1x just at the time when the backup for this specific user is in progress so i am guessing that this is the issue as there is low traffic at night for the websites.

The error from the crash is:

[02-Aug-2017 01:08:18] WARNING: [pool user] child 12417 exited on signal 9 (SIGKILL) after 3360.646280 seconds from start
[02-Aug-2017 01:08:18] NOTICE: [pool user] child 21628 started
[02-Aug-2017 01:08:18] WARNING: [pool user] child 16758 exited on signal 9 (SIGKILL) after 2309.010300 seconds from start
[02-Aug-2017 01:08:18] NOTICE: [pool user] child 21629 started
[02-Aug-2017 01:08:18] WARNING: [pool user] child 16926 exited on signal 9 (SIGKILL) after 2267.207700 seconds from start
... etc with all the pools.

We are using PHP FPM 7.0.21 / Zend Opcache v7.0.5, Apache 2.4.27 with event MPM, Mariadb 10.0.31.

This is just my guess again but is the problem some kind of timeout when FPM/apache are waiting for mySQL to respond?

PHP.ini max_execution_time is set to 18000 and ProxyTimeout in Apache is 1800. Should we increase ProxyTimeout?

There are no general apache errors, just domain errors:

[Wed Aug 02 01:08:18.340560 2017] [proxy_fcgi:error] [pid 12131:tid 139960107632384] [client xxxx:22467] AH01067: Failed to read FastCGI header
[Wed Aug 02 01:08:18.340618 2017] [proxy_fcgi:error] [pid 12131:tid 139960107632384] (104)Connection reset by peer: [client xxxx:22467] AH01075: Error dispatching request to :
[Wed Aug 02 01:08:18.358700 2017] [proxy_fcgi:error] [pid 12131:tid 139959679604480] [client xxxx:16078] AH01067: Failed to read FastCGI header, referer: https://www.domain.com
[Wed Aug 02 01:08:18.358738 2017] [proxy_fcgi:error] [pid 12131:tid 139959679604480] (104)Connection reset by peer: [client xxxxx:16078] AH01075: Error dispatching request to : , referer: https://www.domain.com

and then

[Wed Aug 02 01:08:35.748875 2017] [proxy_fcgi:error] [pid 21686:tid 139960124417792] [client xxxx:36854] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Zend_Db_Adapter_Exception: Can't connect to database! in /home2/user/domains/....

I am not sure which timeout to increase or how to deal with this. The server should have enough resourses to handle this (32 GB RAM, 8 cores, etc - dedicated server).

So to sum it up, looks like the database stops responding on dump and apache / fpm cannot connect to mySQL and dies. Any ideas how to fix this?

Thanks
 
What is the setting for max_connections for your database in my.cnf? If it is really low (like 1?), your backup will utilize the connection and then all new connections will have to wait it to finish. Since it's taking a huge amount of time because of the size, they timeout waiting... Login to your mariadb with da_admin user and execute

Code: 
SHOW VARIABLES LIKE "max_%";

It's just a guess :)

You should also take a look at the mariadb error log. It looks that it's a database, not a PHP issue.
 
Last edited:
My guess is that your database open_files_limit is too low. Do you run CentOS 6 or 7, or something else? Log into phpMyAdmin as DirectAdmin admin user and click in horisontal menu on "Variables", find the line that says "open files limit". What are the value? On my server I have set it to 65535 wich is very high.

If you are using CentOS 7, you should increase the open files limit in your OS, then MariaDB will take it from there. Check the outputs of these commands:

ulimit -a
ulimit -Hn (shows soft limit)
ulimit -Sn (shows hard limit)

To increase them, edit /etc/security/limits.conf and add these two lines at the bottom in the file:

Code: 
*                soft    nofile          65535
*                hard    nofile          65535

Then it is not enough to restart mariadb, so to see the new values you need to do:
Code: 
cd /usr/local/directadmin/custombuild
./build mariadb

Check open files limit in phpMyAdmin again, to confirm the limit is changed to the new value.

If you are running CentOS 6, then you can add this to /etc/my.cnf (change the value to whatever you want):
Code: 
open_files_limit=30000
Then restart mariadb
Code: 
service mysqld restart

If you still do not see the new value of open files limit in phpMyAdmin, you can try to empty the content of /var/www/tmp
 
Last edited:
The open_files_limit should also correspond to increase in sysctl.conf, elsewhere it will be pointless. In FreeBSD this is controlled with kern.maxfiles and kern.maxfilesperproc.
 
wattie said:
What is the setting for max_connections for your database in my.cnf? If it is really low (like 1?), your backup will utilize the connection and then all new connections will have to wait it to finish. Since it's taking a huge amount of time because of the size, they timeout waiting... Login to your mariadb with da_admin user and execute

Code: 
SHOW VARIABLES LIKE "max_%";

It's just a guess :)

You should also take a look at the mariadb error log. It looks that it's a database, not a PHP issue.
Click to expand...

max_connections is set to 400. I am guessing that if this variable was too low we would get mySQL errors in logs too, but there are none at these hours.

Open files limit is set to 65535 which is default, and this is Debian 8. Do you think this setting is too low? Its an awfully high number i think by default.

*My guess* is that mySQL never really stops working as a process, it just stops accepting connections for a while when doing a dump.. but its just guessing.
 
The backup is probably locking the whole database. Which actually makes some sense. You may need to consider making an alternative backup scenario - like replication for example, then backup on the replicated server.
 
Last edited:
We saw this percona alternative already but there is another problem with it - it seems you cannot do a full restore of the DB, only table by table. The DB is not innodb.

Any alternative?
 
You must log in or register to reply here.
Back
Top