Jump to content
IPS Community Suite 简体中文
Sign in to follow this  
ipscn

MySQL max_connections

Recommended Posts

本贴记录对 mysql 的一些配置修改。

 

mysql -u root -p

show variables like 'max_c%';

MariaDB [(none)]> show variables like 'max_c%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 1000  |
+--------------------+-------+

 

然后更改

set global max_connections=1000;

确认下

MariaDB [(none)]> show variables like 'max_c%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 1000  |
+--------------------+-------+
2 rows in set (0.00 sec)

查看 MySQL 的状态

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu (i686) using readline 5.2

Connection id:		223
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			1 hour 51 min 39 sec

Threads: 2  Questions: 4489  Slow queries: 0  Opens: 51  Flush tables: 1  Open tables: 114  Queries per second avg: 0.670
--------------

 

Share this post


Link to post

同时打开的表的数量

MariaDB [(none)]> show variables like 'table_open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
1 row in set (0.00 sec)

这个暂时不用修改。

一个客户端可以打开的表的数量

MariaDB [(none)]> show variables like 'max_tmp%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| max_tmp_tables | 32    |
+----------------+-------+
1 row in set (0.00 sec)

也不做修改。

查看下文件打开数量限制

MariaDB [(none)]> show variables like '%open%file%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 400   |
| open_files_limit  | 1024  |
+-------------------+-------+
2 rows in set (0.00 sec)

也不做修改;

 

县城相关的变量

MariaDB [(none)]> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Innodb_master_thread_active_loops        | 65    |
| Innodb_master_thread_idle_loops          | 7358  |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threadpool_idle_threads                  | 0     |
| Threadpool_threads                       | 0     |
| Threads_cached                           | 2     |
| Threads_connected                        | 2     |
| Threads_created                          | 4     |
| Threads_running                          | 1     |
+------------------------------------------+-------+
12 rows in set (0.01 sec)

Threads_connected 和前面的 max_connections 做对比,可以大致确定系统的伏在,决定是否需要修改连接数限制。

查看每个县城的信息

MariaDB [(none)]> show processlist;
+-----+------+--------------+------+---------+------+-------+------------------+----------+
| Id  | User | Host         | db   | Command | Time | State | Info             | Progress |
+-----+------+--------------+------+---------+------+-------+------------------+----------+
|  82 | root | sf.lan:14656 | NULL | Sleep   | 6979 |       | NULL             |    0.000 |
| 223 | root | localhost    | NULL | Query   |    0 | init  | show processlist |    0.000 |
+-----+------+--------------+------+---------+------+-------+------------------+----------+
2 rows in set (0.01 sec)

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×