I'm having trouble with tracking the source of slow web server performance for wordpress site and non-cached pages.
Server OS: Debian 8 "Jesssie)" - Linux version 4.9.58-xxxx-std-ipv6-64 ([email protected]) (gcc version 6.3.0 20170516 (Debian 6.3.0-18) ) #1 SMP Mon Oct 23 11:35:59 CEST 2017 (Ispconfig)
Web server: Server version: Apache/2.4.10 (Debian)
PHP (default): PHP 7.0.26
PHP (selected): PHP Version 7.1.8
Procesor: Intel 2x Xeon E5-2630v3 - 16/32t - 2.4GHz /3.2GHz
RAM: 128GB DDR4 ECC 1866 MHz
Disks: SoftRaid 2x450GB SSD NVMe
apache2.conf:
Timeout 300
KeepAlive On
MaxKeepAliveRequests 300
KeepAliveTimeout 5
Server-status (for last 40 minutes):
Server Version: Apache/2.4.10 (Debian) mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_fcgid/2.3.9 mod_python/3.3.1 Python/2.7.9 OpenSSL/1.0.1t
Server MPM: worker
Server Built: Sep 20 2017 04:37:43
Current Time: Monday, 22-Jan-2018 05:24:45 EST
Restart Time: Monday, 22-Jan-2018 04:43:55 EST
Parent Server Config. Generation: 1
Parent Server MPM Generation: 0
Server uptime: 40 minutes 50 seconds
Server load: 0.06 0.09 0.33
Total accesses: 8359 - Total Traffic: 147.2 MB
CPU Usage: u20.73 s2.61 cu0 cs0 - .953% CPU load
3.41 requests/sec - 61.5 kB/second - 18.0 kB/request
13 requests currently being processed, 237 idle workers
apache2ctl -M:
Loaded Modules:
core_module (static)
so_module (static)
watchdog_module (static)
http_module (static)
log_config_module (static)
logio_module (static)
version_module (static)
unixd_module (static)
access_compat_module (shared)
actions_module (shared)
alias_module (shared)
auth_basic_module (shared)
auth_digest_module (shared)
authn_core_module (shared)
authn_file_module (shared)
authz_core_module (shared)
authz_host_module (shared)
authz_user_module (shared)
autoindex_module (shared)
dav_module (shared)
dav_fs_module (shared)
deflate_module (shared)
dir_module (shared)
env_module (shared)
expires_module (shared)
fastcgi_module (shared)
fcgid_module (shared)
filter_module (shared)
include_module (shared)
mime_module (shared)
mpm_worker_module (shared)
negotiation_module (shared)
proxy_module (shared)
proxy_fcgi_module (shared)
python_module (shared)
reqtimeout_module (shared)
rewrite_module (shared)
setenvif_module (shared)
socache_shmcb_module (shared)
ssl_module (shared)
status_module (shared)
suexec_module (shared)
Server MPM: worker:
<IfModule mpm_worker_module>
StartServers 10
MinSpareThreads 25
MaxSpareThreads 400
ThreadLimit 64
ThreadsPerChild 25
MaxRequestWorkers 500
MaxConnectionsPerChild 0
</IfModule>
my.cnf (MySQL):
innodb_buffer_pool_size = 61440M
innodb_file_per_table=1
innodb_log_file_size = 1024M
innodb_buffer_pool_instances=60
key_buffer = 2048M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# 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 = 0
query_cache_type = 0
#query_cache_size = 8192M
domain.com.vhost-le-ssl.conf (...)
<Directory /var/www/domain.com/web>
# Clear PHP settings of this website
<FilesMatch ".+\.ph(p[345]?|t|tml)$">
SetHandler None
</FilesMatch>
Options +FollowSymLinks
AllowOverride All
Require all granted
</Directory>
<Directory /var/www/clients/client1/web1/web>
# Clear PHP settings of this website
<FilesMatch ".+\.ph(p[345]?|t|tml)$">
SetHandler None
</FilesMatch>
Options +FollowSymLinks
AllowOverride All
Require all granted
</Directory>
# suexec enabled
<IfModule mod_suexec.c>
SuexecUserGroup web1 client1
</IfModule>
# php as fast-cgi enabled
# For config options see: http://httpd.apache.org/mod_fcgid/mod/mod_fcgid.html
<IfModule mod_fcgid.c>
FcgidIdleTimeout 300
FcgidProcessLifeTime 3600
FcgidInitialEnv PHP_FCGI_MAX_REQUESTS 10000
#FcgidMaxProcesses 1000
FcgidMaxRequestsPerProcess 10000
FcgidMinProcessesPerClass 0
FcgidMaxProcessesPerClass 10
FcgidConnectTimeout 20
FcgidIOTimeout 600
FcgidBusyTimeout 3600
FcgidOutputBufferSize 0
# FcgidBusyScanInterval 120
FcgidMaxRequestLen 1073741824
</IfModule>
<Directory /var/www/domain.com/web>
<FilesMatch "\.php[345]?$">
SetHandler fcgid-script
</FilesMatch>
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php3
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php4
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php5
Options +ExecCGI
AllowOverride All
Require all granted
</Directory>
<Directory /var/www/clients/client1/web1/web>
<FilesMatch "\.php[345]?$">
SetHandler fcgid-script
</FilesMatch>
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php3
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php4
FCGIWrapper /var/www/php-fcgi-scripts/web1/.php-fcgi-starter .php5
Options +ExecCGI
AllowOverride All
Require all granted
</Directory>
# add support for apache mpm_itk
<IfModule mpm_itk_module>
AssignUserId web1 client1
</IfModule>
<IfModule mod_dav_fs.c>
# Do not execute PHP files in webdav directory
<Directory /var/www/clients/client1/web1/webdav>
<ifModule mod_security2.c>
SecRuleRemoveById 960015
SecRuleRemoveById 960032
</ifModule>
<FilesMatch "\.ph(p3?|tml)$">
SetHandler None
</FilesMatch>
</Directory>
DavLockDB /var/www/clients/client1/web1/tmp/DavLock
# DO NOT REMOVE THE COMMENTS!
# IF YOU REMOVE THEM, WEBDAV WILL NOT WORK ANYMORE!
# WEBDAV BEGIN
# WEBDAV END
</IfModule>
(...)
Problem:
I host a single wordpress website on this machine. (32) error appeared from time to time even when the website was in development and the only user was me. Currently I have about 5k unique visitors per day (https://domain.com) and my error.log displays multiple (104), (9) and (32) erros, including:
(...)
(104)Connection reset by peer: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: error reading data from FastCGI server
(104)Connection reset by peer: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function
(9)Bad file descriptor: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function
(32)Broken pipe: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function
(104)Connection reset by peer: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: error reading data from FastCGI server, referer: https://domain.com/wp-admin/post.php?post=XXXXXXX&action=edit
(...)
The most common error is:
(32)Broken pipe: [client: XXX.XX.XX.XX:XXXX] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function
I would say that this errors occurs for 5%-10% of all visits and the number is currently low only due to the fact that majority of my pages are cached. This starts to worry me as my website is getting more hits day by day and more visitors are registering and using "search" option or just clicks on "tag" links which are not cached due to its dynamic content.
From what I noticed, these errors occurs mostly when PHP is running. For test I have opened around 20 post in tabs and clicked "edit" button. This caused (104), (9) and (32) errors. (32) appears also when visitor is doing search. In total I have about 500k post in my wordpress.
What I did to try to fix it (NO SUCCESS): 1. I switched from PHP-FPM to Fast-CGI and opened 20 post in tabs and clicked "edit" button. Erros still show up in error.log and and post are not loading with browser displaying "Waiting for available socket" (cached pages works super-fast).
ps aux | grep "php"
root 7554 0.0 0.0 210764 24084 ? Ss 2017 0:47 php-fpm: master process (/etc/php/7.0/fpm/php-fpm.conf)
root 21443 0.0 0.0 280056 8700 ? Ss 05:49 0:00 php-fpm: master process (/opt/php-7.1/etc/php-fpm.conf)
www-data 21444 0.0 0.0 280056 6904 ? S 05:49 0:00 php-fpm: pool www
www-data 21445 0.0 0.0 280056 6904 ? S 05:49 0:00 php-fpm: pool www
web1 21801 1.4 0.0 367796 83900 ? S 05:49 0:05 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21802 0.9 0.0 367716 81768 ? S 05:49 0:03 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21803 0.7 0.0 291716 80684 ? S 05:49 0:02 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21804 0.6 0.0 367656 81500 ? S 05:49 0:02 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21805 0.4 0.0 291716 80304 ? S 05:49 0:01 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21817 0.3 0.0 366944 80224 ? S 05:49 0:01 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21818 0.7 0.0 361524 76512 ? S 05:49 0:02 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 21998 0.8 0.0 369600 85560 ? S 05:50 0:02 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 22192 3.9 0.0 289668 79848 ? S 05:53 0:06 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
web1 22193 0.8 0.0 285392 75288 ? S 05:53 0:01 /opt/php-7.1/bin/php-cgi -d open_basedir=/var/www/clients/client1/web1/web:/var/www/clients/client1/web1/private:/var/www/clients/client1/web1/tmp:/var/www/domain.com/web:/srv/www/domain.com/web:/usr/share/php5:/usr/share/php:/tmp:/usr/share/phpmyadmin:/etc/phpmyadmin:/var/lib/phpmyadmin:/dev/random:/dev/urandom -d upload_tmp_dir=/var/www/clients/client1/web1/tmp -d session.save_path=/var/www/clients/client1/web1/tmp -d sendmail_path=/usr/sbin/sendmail -t -i -f [email protected]
ispconf+ 22268 0.0 0.0 285232 22940 ? Ss 05:54 0:00 /usr/bin/php-cgi -d disable_classes= -d disable_functions= -d magic_quotes_gpc=off -d open_basedir= -d session.save_path=/usr/local/ispconfig/interface/temp
ispconf+ 22270 0.1 0.0 285528 16064 ? S 05:54 0:00 /usr/bin/php-cgi -d disable_classes= -d disable_functions= -d magic_quotes_gpc=off -d open_basedir= -d session.save_path=/usr/local/ispconfig/interface/temp
root 22433 0.0 0.0 13216 2172 pts/0 S+ 05:55 0:00 grep php
root 24127 0.0 0.0 306300 8952 ? Ss 2017 0:37 php-fpm: master process (/usr/local/php-7.1.8/etc/php-fpm.conf)
www-data 24128 0.0 0.0 306640 9228 ? S 2017 0:00 php-fpm: pool www
www-data 24129 0.0 0.0 306300 6980 ? S 2017 0:00 php-fpm: pool www
www-data 29862 0.0 0.0 213180 19708 ? S Jan19 0:04 php-fpm: pool www
www-data 29891 0.0 0.0 213176 21532 ? S Jan19 0:04 php-fpm: pool www
www-data 29948 0.0 0.0 211136 17540 ? S Jan19 0:03 php-fpm: pool www
TOP:
top - 06:02:08 up 27 days, 17:45, 1 user, load average: 2.45, 3.20, 2.26
Tasks: 487 total, 1 running, 486 sleeping, 0 stopped, 0 zombie
%Cpu(s): 12.5 us, 3.6 sy, 0.0 ni, 83.4 id, 0.0 wa, 0.0 hi, 0.5 si, 0.0 st
KiB Mem: 13198007+total, 12718466+used, 4795408 free, 2421808 buffers
KiB Swap: 1046520 total, 0 used, 1046520 free. 10000835+cached Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27655 mysql 20 0 68.220g 0.012t 11640 S 519.8 10.1 3798:47 mysqld
21602 www-data 20 0 2058172 17132 7092 S 0.3 0.0 0:00.77 apache2
(...)
Edit:
These MySQL queries generates 128.9% CPU usage by mysqld:
mysql> show full processlist;
+--------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 284863 | root | localhost | c1DATABASE | Query | 0 | NULL | show full processlist |
| 287063 | c1DATABASE | localhost | c1DATABASE | Sleep | 0 | | NULL |
| 287064 | c1DATABASE | localhost | c1DATABASE | Query | 0 | Sorting result | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND tt.count > 0 ORDER BY tt.count DESC LIMIT 45 |
| 287065 | c1DATABASE | localhost | c1DATABASE | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY RAND() LIMIT 0, 10 |
| 287066 | c1DATABASE | localhost | c1DATABASE | Sleep | 0 | | NULL |
+--------+---------------+-----------+---------------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
2. I have updated and tweaked following options:
FcgidBusyTimeout 3600
FcgidProcessLifeTime 3600
FcgidOutputBufferSize 0
Apache mod_fcgid documentation lists out another possibility which the "mod_fcgid: ap_pass_brigade failed in handle_request_ipc function" error may occur, which is due to limit of PHP_FCGI_MAX_REQUESTS and/or FcgidMaxRequestsPerProcess. PHP FastCGI processes exit after handling 500 requests, and they may exit after this module has already connected to the application and sent the next request, resulting in "mod_fcgid: ap_pass_brigade failed in handle_request_ipc function" error.
As per Apache mod_fcgid documentation and this question, I have also updated following options:
FcgidMaxRequestsPerProcess 10000
FcgidInitialEnv PHP_FCGI_MAX_REQUESTS 10000
Unfortunately, both 1) and 2) did not solved my problem :-/ Any advice how to squiz max out of this machine (Intel 2x Xeon E5-2630v3 - 16/32t - 2.4GHz /3.2GHz + 128GB DDR4 ECC 1866 MHz RAM) and fix the problem with mod_fcgid erros? Cheers!
select * from INFORMATION_SCHEMA.PROCESSLIST where db = 'c1'might help.