nginx+php+mysql高并发问题调优

高并发下默认的nginx+php+mysql组合,经常出现502,所以做了一次日志排查和调优

按照先查看日志再排查的原理,先去看vim /usr/local/php/etc/php-fpm.conf,看到日志文件是在:error_log = /usr/local/php/var/log/php-fpm.log
日志中报了如下:


		WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 0 idle, and 100 total children
		WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 1591 total children
		WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 27 idle, and 3511 total children
		
这个日志说明了是高并发下PHP的子线程不够用,php出现告警了,要增加php的,pm.start_servers, or pm.min/max_spare_servers的值,不然会产生nginx请求出现很多的502超时. 那么我们在服务器硬件还能支持更多的PHP子线程时,修改如下的参数,以达到增大PHP-FPM线程:

		[root@120-24-111-219 log]# vim /usr/local/php/etc/php-fpm.conf
		pm = dynamic
		 pm.max_children = 5800
		 pm.start_servers = 800
		 pm.min_spare_servers = 40
		 pm.max_spare_servers = 5800
		
		
		
保存退出后,重启PHP。 再用"netstat -anpo | grep php-fpm | wc -l",来查看php的线程数是否在高并发时上来了。

参数分析: pm= dynamic 表示php启用的动态模式 注: php有动态和静态(static)两种工作模式,默认是动态模式。 pm.max_children 表示静态下最大线程数 pm.start_servers 表示动态下启动时的线程数,该参数大于pm.min_spare_servers,小于pm.max_spare_servers pm.min_spare_servers 表示动态下最小空闲线程数 pm.max_spare_servers 表示动态下最大空闲线程数 工作模式: Static模式 当工作模式设置为静态后,就只有pm.max_children项有效,即表示php-fpm工作时一直保持的线程数。 Dynamic 模式 动态模式下,与他相关的参数有pm.start_servers、pm.min_spare_servers 、pm.max_spare_servers,分别表示开启的php进程数,最小的进程数、与最大的进程数。 模式比较: 静态模式的话,比较适合一些内存比较大一点的服务器,8G及以上的,因为对于比较大内存的服务器来说,设置为静态的话会提高效率。 动态模式适合小内存机器,灵活分配进程,省内存。可以让php自动增加和减少进程数,不过动态创建回收进程对服务器也是一种消耗。

参考:https://blog.51cto.com/xiaozhagn/2136667

好了,处理完PHP的报错调优后,本以为一切终于可以正常了,没想到PHP是正常了,这下轮到了mysql的默认连接数问题了, 在PHP框架上产生了如下的报错:


		[ error ] [10501]SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382)
		
		
意思是说mysql中的max_prepared_stmt_count参数默认为:16382,已经超过。所以如果数据库的服务器硬盘跟得上的话,就增大max_prepared_stmt_count参数的值。 一般默认值应该是足够用的,因为并发没有那么大。也可能是应用端(PHP的代码有错误,没能正常关闭,然后越积赵多)那边没有关闭prepared的语句。

查看mysql如下3个参数值: Com_stmt_close prepare语句关闭的次数 Com_stmt_execute prepare语句执行的次数 Com_stmt_prepare prepare语句创建的次数 通过以下命令修改max_prepared_stmt_count的值(该值可动态修改,也可在配置文件中指定后重启服务生效)


		[[email protected] ~]# mysql -hrm-wzsafaeeasdf.mysql.rds.aliyuncs.com -uroot -p
		Enter password: 
		Welcome to the MySQL monitor.  Commands end with ; or \g.
		Your MySQL connection id is 13412261
		Server version: 5.7.14-AliSQL-X-Cluster-1.5.1.7-20200918-log Source distribution
		
		Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
		
		Oracle is a registered trademark of Oracle Corporation and/or its
		affiliates. Other names may be trademarks of their respective
		owners.
		
		Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
		
		mysql> SHOW GLOBAL STATUS LIKE 'com_stmt%';
		+-------------------------+------------+
		| Variable_name           | Value      |
		+-------------------------+------------+
		| Com_stmt_execute        | 1611653381 |
		| Com_stmt_close          | 1611634907 |
		| Com_stmt_fetch          | 0          |
		| Com_stmt_prepare        | 1611663970 |
		| Com_stmt_reset          | 0          |
		| Com_stmt_send_long_data | 0          |
		| Com_stmt_reprepare      | 0          |
		+-------------------------+------------+
		7 rows in set (0.02 sec)
		
		mysql> set global max_prepared_stmt_count=1048575;
		ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
		mysql> GRANT SYSTEM_USER ON *.* TO 'root'
		    -> ;
		ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSTEM_USER ON *.* TO 'root'' at line 1
		mysql> GRANT SYSTEM_USER ON *.* TO root
		    -> ;
		ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SYSTEM_USER ON *.* TO root' at line 1
		mysql> exit;
		Bye
		
		

数据库参考:https://zhuanlan.zhihu.com/p/67188414

修改完后,以为全部问题解决了,没想到,又报了这“ERROR 1040 (HY000): Too many connections”mysql的链接数满了。

按MySQL sleep连接过多 解决办法,把超时等待时间设置短

		elapse@luck:/var/www$ /usr/local/mysql/bin/mysql -uroot -p
		Enter password: 
		Welcome to the MariaDB monitor.  Commands end with ; or \g.
		Your MariaDB connection id is 31
		Server version: 10.5.8-MariaDB MariaDB Server
		
		Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
		
		Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
		
		MariaDB [(none)]> create user 'test'@'localhost' identified by 'test';
		ERROR 1396 (HY000): Operation CREATE USER failed for 'test'@'localhost'
		MariaDB [(none)]> set password for 'test'@'localhost' = 'test1';
		ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
		MariaDB [(none)]> grant all on *.*  to 'test'@'localhost';
		Query OK, 0 rows affected (0.001 sec)
		
		MariaDB [(none)]> set password for 'test'@'localhost' = 'test1';
		ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
		MariaDB [(none)]> grant system_user on *.* to 'root';
		ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to 'root'' at line 1
		MariaDB [(none)]> show global variables like 'wait_timeout';
		+---------------+-------+
		| Variable_name | Value |
		+---------------+-------+
		| wait_timeout  | 28800 |
		+---------------+-------+
		1 row in set (0.001 sec)
		
		MariaDB [(none)]> set global wait_timeout=30;
		Query OK, 0 rows affected (0.000 sec)
		
		MariaDB [(none)]> show global variables like 'wait_timeout';
		+---------------+-------+
		| Variable_name | Value |
		+---------------+-------+
		| wait_timeout  | 30    |
		+---------------+-------+
		1 row in set (0.001 sec)
		
		MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
		+---------------------+-------+
		| Variable_name       | Value |
		+---------------------+-------+
		| interactive_timeout | 28800 |
		+---------------------+-------+
		1 row in set (0.001 sec)
		
		MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
		+---------------------+-------+
		| Variable_name       | Value |
		+---------------------+-------+
		| interactive_timeout | 28800 |
		+---------------------+-------+
		1 row in set (0.001 sec)
		
		MariaDB [(none)]> set global interactive_timeout=30;
		Query OK, 0 rows affected (0.000 sec)
		
		MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
		+---------------------+-------+
		| Variable_name       | Value |
		+---------------------+-------+
		| interactive_timeout | 30    |
		+---------------------+-------+
		1 row in set (0.001 sec)
		
		MariaDB [(none)]>