7中MySQL连接数被限定为217个的缓和格局

最近在项目中遇到一个问题,最近在项目中遇到一个问题,connections +1

意识难点

CentOS 7中MySQL连接数被限制为215个的消除格局,centosmysql

发觉难题

近些日子在类型中遇到多个主题素材,由于连接数过多,指示 “Too many connections”
,须要充实连接数。

自己在 /etc/my.cnf中期维修改了:

max_connections = 2000

唯独, 实际连接数一向被限定在 214:

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in set

思考

假设本身设置连接小于214时,例如 200,那么实际上连接数就是200,约等于说,笔者的铺排文件是未有失水准的。

查 MySQL 官方文书档案,里面说了:

The maximum number of connections MySQL can support depends on the
quality of the thread library on a given platform, the amount of RAM
available, how much RAM is used for each connection, the workload from
each connection, and the desired response time. Linux or Solaris should
be able to support at 500 to 1000 simultaneous connections routinely and
as many as 10,000 connections if you have many gigabytes of RAM
available and the workload from each is low or the response time target
undemanding. Windows is limited to (open tables × 2 + open connections)
< 2048 due to the Posix compatibility layer used on that platform.
Increasing open-files-limit may be necessary. Also see Section 2.5,
“Installing MySQL on Linux”, for how to raise the operating system limit
on how many handles can be used by MySQL.

大概意思是 MySQL 能够支持的最洛桑接数量受限于操作系统,要求时方可增大
open-files-limit。换言之,连接数与公事张开数有关。

缓和形式

[[email protected] ~]# ulimit -n
1024

可见,操作系统最大文件陈述符限制为 1024。

更动 MySQL 在 Linux 的最大文件汇报符限制,编辑
/usr/lib/systemd/system/mysqld.service文本,在文书最终加多:

LimitNOFILE=65535
LimitNPROC=65535

封存后,试行上边发号施令,使配置生效

$ systemctl daemon-reload
$ systemctl restart mysqld.service

其实连接数到 3000 了,化解

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set

参考

总结

如上正是那篇小说的全部内容了,希望本文的从头到尾的经过对大家的就学恐怕职业能推动一定的援助,假若有疑问大家能够留言交换,多谢大家对帮客之家的支持。

7中MySQL连接数被限制为2拾几个的消除办法,centosmysql 发掘难题近日在品种中遇到四个难题,由于连接数过多,提醒 “Too many
connections”…

SHOW VARIABLES LIKE ‘%connection%’;

前不久在品种中遭逢二个标题,由于连接数过多,提示 “Too many connections”
,必要追加连接数。

character_set_connection utf8mb4
collation_connection utf8mb4_general_ci
extra_max_connections
max_connections 1250
max_user_connections 1250

自家在 /etc/my.cnf中期维修改了:

 

max_connections = 2000

 

可是, 实际连接数一向被限定在 214:

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in set

 

思考

 

要是本身设置连接小于214时,比方 200,那么实际上连接数正是200,相当于说,作者的陈设文件是平昔不难点的。

支撑的最重庆接数为 max_connections +1

查 MySQL 官方文书档案,里面说了:

B.5.2.6 Too many connections

If you get a Too many connections error when you try to connect to
the mysqld server,
this means that all available connections are in use by other clients.

The number of connections permitted is controlled by
the max_connections system
variable. The default value is 151 to improve performance when MySQL is
used with the Apache Web server. (Previously, the default was 100.) If
you need to support more connections, you should set a larger value for
this variable.

mysqld actually
permits max_connections+1 clients
to connect. The extra connection is reserved for use by accounts that
have
the SUPER privilege.
By granting
the SUPER privilege
to administrators and not to normal users (who should not need it), an
administrator who also has
the PROCESS privilege
can connect to the server and
useSHOW PROCESSLIST to
diagnose problems even if the maximum number of unprivileged clients are
connected. SeeSection 13.7.5.29, “SHOW PROCESSLIST
Syntax”.

The maximum number of connections MySQL supports depends on the quality
of the thread library on a given platform, the amount of RAM available,
how much RAM is used for each connection, the workload from each
connection, and the desired response time. Linux or Solaris should be
able to support at least 500 to 1000 simultaneous connections routinely
and as many as 10,000 connections if you have many gigabytes of RAM
available and the workload from each is low or the response time target
undemanding. Windows is limited to (open tables × 2 + open connections)
< 2048 due to the Posix compatibility layer used on that platform.

Increasing open-files-limit may
be necessary. Also see Section 2.5, “Installing MySQL on
Linux”,
for how to raise the operating system limit on how many handles can be
used by MySQL.

 

 

 

The maximum number of connections MySQL can support depends on the
quality of the thread library on a given platform, the amount of RAM
available, how much RAM is used for each connection, the workload from
each connection, and the desired response time. Linux or Solaris should
be able to support at 500 to 1000 simultaneous connections routinely and
as many as 10,000 connections if you have many gigabytes of RAM
available and the workload from each is low or the response time target
undemanding. Windows is limited to (open tables × 2 + open connections)
< 2048 due to the Posix compatibility layer used on that platform.
Increasing open-files-limit may be necessary. Also see Section 2.5,
“Installing MySQL on Linux”, for how to raise the operating system limit
on how many handles can be used by MySQL.

大抵意思是 MySQL 能够援救的最洛桑接数量受限于操作系统,要求时得以增大
open-files-limit。换言之,连接数与公事张开数有关。

化解方法

[root@sqzr ~]# ulimit -n
1024

能够,操作系统最大文件陈说符限制为 1024。

退换 MySQL 在 Linux 的最大文件陈说符限制,编辑
/usr/lib/systemd/system/mysqld.service文件,在文件最后加多:

LimitNOFILE=65535
LimitNPROC=65535

封存后,实行下边发号施令,使配置生效

$ systemctl daemon-reload
$ systemctl restart mysqld.service

实则连接数到 两千 了,消除

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set

参考

总结

如上就是那篇小说的全部内容了,希望本文的开始和结果对大家的学习可能职业能推动一定的支持,假若至极我们能够留言交换,多谢我们对台本之家的支撑。

你只怕感兴趣的稿子:

  • Linux下刚安装完mysql修改密码的简练方法
  • CentOS 6.6 源码编写翻译安装MySQL
    5.7.18科目详解
  • MySql Error
    1698(2九千)难点的化解方法
  • 透过案例剖析MySQL中令人感冒的Aborted告警
  • 杀鸡取卵MySQL中IN子查询会招致不可能运用索引难题
  • MySQL调换分区的实例详解