如何优化MySQL的连接数?

MySQL & MariaDB

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

参阅文档:
===================
https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html

没有评论

发表评论

MySQL & MariaDB
如何开启Mariadb或MySQL Binlog?

1 前言 一个问题,一篇文章,一出故事。 笔者需要备份Mariadb的binlog,于是整理此文。 …

MySQL & MariaDB
如何迁移MariaDB或MySQL数据目录?

1 前言 一个问题,一篇文章,一出故事。 笔者之前的数据库使用默认部署在根目录下,现在想迁移至专用的 …

MySQL & MariaDB
如何基于CentOS 8.x安装MariaDB?

1 基础知识 – MariaDB数据库管理系统是MySQL的一个分支 – M …