mysql连接数的优化_如何优化MySQL的连接数？

1 前言

MySQL与Apache的Web服务器的默认连接数是151，如果日志报“Too many connections”的错误，则需要通过MySQL的参数调整。

2 最佳实践

2.1 Apache端的错误日志

tail -f /var/log/httpd/error_log

可见如下日志，

[Sun Apr 07 23:03:52.437620 2019] [php7:error] [pid 29352:tid 139822802876160] [client 10.10.3.169:39374] PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [1040] Too many connections in /var/www/owncloud/lib/private/DB/Connection.php:62\nStack trace:\n#0 /var/www/owncloud/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/owncloud/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/owncloud/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/owncloud/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/owncloud/lib/private/DB/Connection.php(144): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/owncloud/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driv in /var/www/owncloud/lib/private/DB/Connection.php on line 62

2.2 MySQL端的错误日志

tail -f /var/log/mysqld.log

可见如下日志，

2019-04-07 23:04:02 140176395249408 [Warning] Aborted connection 504165 to db: 'owncloud' user: 'owncloud' host: 'localhost' (Got an error reading communication packets)

2.3 查看当前连接的客户端

show processlist;

如果以下可见大量的owncloud连接，则此问题确诊，

+-------+-------------+-----------------+----------+---------+------+--------------------------+------------------+----------+

| Id | User | Host | db | Command | Time | State | Info | Progress |

+-------+-------------+-----------------+----------+---------+------+--------------------------+------------------+----------+

| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |

| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |

| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |

| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |

| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |

| 6897 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |

| 14151 | owncloud | localhost:33397 | owncloud | Sleep | 0 | | NULL | 0.000 |

| 14152 | owncloud | localhost:33398 | owncloud | Sleep | 0 | | NULL | 0.000 |

+-------+-------------+-----------------+----------+---------+------+--------------------------+------------------+----------+

8 rows in set (0.00 sec)

2.4 查看当前的最大连接数

show variables like '%max_connections%';

显示如下，

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| extra_max_connections | 1 |

| max_connections | 151 |

+-----------------------+-------+

2 rows in set (0.05 sec)

2.5 修改连接数

vim /etc/my.cnf

修改如下参数，

max_connections=1000

修改完成后，我们需要通过重启使配置生效，

/etc/init.d/mysqld restart