In the Nginx + FastCGI (PHP-FPM) environment, when the length of the data in the log table is in GB, get the last page of the log list, and the response timeout: 504 Gateway time-out analysis solution
1. When the data length of PA_LOG is: 6.16 GB, as shown in Figure 1
Figure 1
2. Get the first page of the log list, the response time is about 1000ms, which is acceptable, as shown in Figure 2
Figure 2
3. Get the last page of the log list, the response timeout: 504 gateway time-out, as shown in Figure 3
Figure 3
<html>
<head>
<title>504 Gateway Time-out</title>
</head>
<body bgcolor="white">
<center>
<h1>504 Gateway Time-out</h1>
</center>
<hr>
<center>nginx/1.10.1</center>
</body>
</html>
4. Manually execute SQL, the query time length is: 57.715s, as shown in Figure 4
Figure 4
5. Realize the automatic timing deletion of log messages, refer to the URL:https://www.shuijingwanwq.com/en/2019/10/09/11549/, to avoid infinitely increasing the amount of data in the log table.
6. Reference URL:https://easycloudsupport.zendesk.com/hc/en-us/articles/360002057472-How-to-Fix-504-Gateway-Timeout-using-Nginx, look at C:\php-7.2.14\php.ini , max_execution_time = 300, which sets the maximum execution time allowed before the script is terminated by the parser, in seconds, without adjustment.
7. Add the fastcgi_read_timeout variable to the nginx virtual host configuration, edit c:\nginx-1.10.1\conf\vhosts\pcs-api.conf
location ~ \.php$ {
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_pass 127.0.0.1:9000;
#fastcgi_pass unix:/var/run/php5-fpm.sock;
try_files $uri =404;
fastcgi_read_timeout 300;
}
8. Reload the nginx configuration and execute the command
PS C:\nginx-1.10.1> .\nginx -s reload
9. Get the last page of the log list, the response time is about 1M, and there is no response timeout, as shown in Figure 5
Figure 5
10. The front-end URL is accessed in the browser. The front-end is based on the nginx reverse proxy to the interface, and the front-end is accessed in postman, and the last page of the log list is obtained. The response time is about 1M, and there is no response timeout, as shown in Figure 6
Figure 6
11. In the production environment, based on Docker deployment, the operating system is CentOS, get the last page of the log list, and the response timeout: 504 Gateway time-out, as shown in Figure 7
Figure 7
<html>
<body>
<h1>504 Gateway Time-out</h1>
The server didn't respond in time.
</body>
</html>
12. Check phpinfo(), the value of max_execution_time is 60, edit the dockerfile, and then edit /usr/local/php/etc/php.ini , max_execution_time = 300, which sets the maximum execution time allowed before the script is terminated by the parser, in seconds. as shown in Figure 8
8
RUN sed -i 's/open_basedir = .\/:\/sobey:\/tmp:\/data:\/webtv/;open_basedir = .\/:\/sobey:\/tmp:\/data:\/webtv:\/usr\/local\/php/g' /usr/local/php/etc/php.ini && \
sed -i 's/allow_url_fopen = Off/allow_url_fopen = On/g' /usr/local/php/etc/php.ini && \
sed -i 's/disable_functions = exec,system,passthru,shell_exec,escapeshellarg,escapeshellcmd,proc_close,proc_open,dl,popen,show/disable_functions = system,shell_exec,escapeshellarg,escapeshellcmd,dl,popen,show/g' /usr/local/php/etc/php.ini && \
sed -i 's/max_execution_time = 60/max_execution_time = 300/g' /usr/local/php/etc/php.ini && \
sed -i 's/pm.max_children = 20/pm.max_children = 40/g' /usr/local/php/etc/php-fpm.conf
13. Add the fastcgi_read_timeout variable in the nginx virtual host configuration, edit /etc/nginx/conf.d/pcs-api.conf
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
fastcgi_read_timeout 300;
}
14. Get the last page of the log list, still responds to the timeout, and does not meet the expectations, edit the dockerfile, and then edit /usr/local/php/etc/php-fpm.conf , request_terminate_timeout = 300, as shown in Figure 9
Figure 9
RUN sed -i 's/open_basedir = .\/:\/sobey:\/tmp:\/data:\/webtv/;open_basedir = .\/:\/sobey:\/tmp:\/data:\/webtv:\/usr\/local\/php/g' /usr/local/php/etc/php.ini && \
sed -i 's/allow_url_fopen = Off/allow_url_fopen = On/g' /usr/local/php/etc/php.ini && \
sed -i 's/disable_functions = exec,system,passthru,shell_exec,escapeshellarg,escapeshellcmd,proc_close,proc_open,dl,popen,show/disable_functions = system,shell_exec,escapeshellarg,escapeshellcmd,dl,popen,show/g' /usr/local/php/etc/php.ini && \
sed -i 's/max_execution_time = 60/max_execution_time = 300/g' /usr/local/php/etc/php.ini && \
sed -i 's/;request_terminate_timeout = 0/request_terminate_timeout = 300/g' /usr/local/php/etc/php-fpm.conf && \
sed -i 's/pm.max_children = 20/pm.max_children = 40/g' /usr/local/php/etc/php-fpm.conf
15. Get the last page of the log list, and still respond to the timeout: 504 gateway time-out, check the response header, and find that before reaching the interface server, it has passed the gateway: kong, as shown in Figure 10
Figure 10
X-Kong-Upstream-Latency: 50005
X-Kong-Proxy-Latency: 0
Via: kong/0.12.3
16. Obtain the last page of the log list inside the container, and there is no timeout. Therefore, it can be confirmed that the reason is on the gateway: Kong, which is to be solved by the operation and maintenance personnel. as shown in Figure 11
Figure 11
curl "http://127.0.0.1/v1/logs?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=121f3d1986aa1c0df8f34793d928e042&page=41780"
17. After restoring the changes made in step 14, the last page of the log list is obtained inside the container, and there is still no timeout.
![运行 SQL 文件时，报错：[ERR] 2013 - Lost connection to MySQL server during query](https://www.shuijingwanwq.com/wp-content/uploads/2017/08/1-3-520x245.png)