MySQL服务器最大连接数怎么设置才合理-mysql教程-数据库-壹聚教程网MySQL服务器最大连接数怎么设置才合理如果mysql 连接数据设置不合理可能会导致很小的流量mysql就提示MySQL: ERROR 1040: Too many connections错误了，那么要如何才算是合理设置mysql最大连接数呢，下面我来给大家介绍介绍。
MySQL服务器的连接数并不是要达到最大的100%为好，还是要具体问题具体分析，下面就对MySQL服务器最大连接数的合理设置进行了详尽的分析，供您参考。
我们经常会遇见&MySQL: ERROR 1040: Too many connections&的情况，通常，mysql的最大连接数默认是100, 最大可以达到16384。
一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：
mysql& show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：
mysql& show global status like 'Max_used_connections';
MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是：
Max_used_connections / max_connections * 100% & 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接上线就设置得过高了
&在Windows下常用的有两种方式修改最大连接数。
第一种：命令行修改。
&&& &mysql -uuser -ppass(命令行登录MySQL)
&&& mysql&show variables like 'max_connections';(查可以看当前的最大连接数)
&&& msyql&set global max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)
&&& mysql&exit(推出)
&&& 这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。
&&& 第二种：修改配置文件。
&& 这 种方式说来很简单，只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能，一个是在安装目录下（这是比较理想的情况），另一种是在数据文件的目录下，安装的时候如果没有人为改变目录的话，一般就在C:/ProgramData/MySQL往下的目录下。
&与连接数相关的几个参数：
&&&& 在修改最大连接数的时候会有这样一个疑问&这个值是不是越大越好，或者设置为多大才合适？这个参数的大小要综合很多因素来考虑，比如使用的平台所支持的线程库数量（windows只能支持到2048）、服务器的配置（特别是内存大小）、每个连接占用资源（内存和负载）的多少、系统需要的响应时间等。可以在global或session范围内修改这个参数。连接数的增加会带来很多连锁反应，需要在实际中避免由此引发的负面影响。
&&& 首先看一下MySQL的状态：
--------------
mysql& Ver 14.14 Distrib 5.5.15, for Win32 (x86)
Connection id:&&&&&&&&& 1
Current database:
Current user:&&&&&&&&&& root@localhost
SSL:&&&&&&&&&&&&&&&&&&& Not in use
Using delimiter:&&&&&&& ;
Server version:&&&&&&&& 5.5.15 MySQL Community Server (GPL)
Protocol version:&&&&&& 10
Connection:&&&&&&&&&&&& localhost via TCP/IP
Server characterset:&&& utf8
Db&&&& characterset:&&& utf8
Client characterset:&&& gbk
Conn.& characterset:&&& gbk
TCP port:&&&&&&&&&&&&&& 3306
Uptime:&&&&&&&&&&&&&&&& 1 hour 3 min 27 sec
Threads: 12& Questions: 18& Slow queries: 10& Opens: 33& Flush tables: 5& Open tab
les: 34& Queries per second avg: 6.256
--------------
Open tables:34,即当前打开表的数量是34个，注意这个34并不是实际的34个表，因为MySQL是多线程的系统，几个不同的并发连接可能打开同一个表，这就需要为不同的连接session分配独立的内存空间来存储这些信息以避免冲突。因此连接数的增加会导致MySQL需要的文件描述符数目的增加。另外对于MyISAM表，还会建立一个共享的索引文件描述符。
&&& 在MySQL层面，有几个系统参数决定了可同时打开的表的数量和要使用的文件描述符，那就是table_open_cache、max_tmp_tables和open_files_limit。
mysql& show variables like 'table_open%';
+------------------+-------+
| Variable_name&&& | Value |
+------------------+-------+
| table_open_cache | 256&& |
+------------------+-------+
1 row in set (0.00 sec)
table_open_cache:256，这就是说所有的MySQL线程一共能同时打开256个表，我们可以搜集系统的打开表的数量的历史记录和这个参数来对比，决定是否要增加这个参数的大小。查看当前的打开表的数目(Open tables)可用上边提到过的status命令，另外可以直接查询这个系统变量的值：
mysql& show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables&& | 3&&&& |
+---------------+-------+
1 row in set (0.00 sec)
Open_tables就是当前打开表的数目，通过flush tables命令可以关闭当前打开的表。 这个值如果过大，并且如果没有经常的执行flush tables命令，可以考虑增加table_open_cache参数的大小。
接下来看max_tmp_tables:
mysql& show variables like 'max_tmp%';
+----------------+-------+
| Variable_name& | Value |
+----------------+-------+
| max_tmp_tables | 32&&& |
+----------------+-------+
1 row in set (0.00 sec)
max_tmp_tables:32即单个客户端连接能打开的临时表数目。查看当前已打开的临时表的信息：
mysql& show global status like '%tmp%table%';
+-------------------------+-------+
| Variable_name&&&&&&&&&& | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0&&&& |
| Created_tmp_tables&&&&& | 11&&& |
+-------------------------+-------+
2 rows in set (0.00 sec)
根据这两个值可以判断临时表的创建位置，一般选取BLOB和TEXT列、Group by 和 Distinct语句的数据量超过512 bytes，或者union的时候某列的数据超过512 bytes的时候，就直接在磁盘上创建临时表了，另外内存中的临时表变大的时候，也可能被MySQL自动转移到磁盘上（由tmp_table_size和max_heap_table_size参数决定）。
增加table_open_cache或max_tmp_tables 参数的大小后，从操作系统的角度看，mysqld进程需要使用的文件描述符的个数就要相应的增加，这个是由open_files_limit参数控制的。
mysql& show variables like 'open_files%';
+------------------+-------+
| Variable_name&&& | Value |
+------------------+-------+
| open_files_limit | 2670& |
+------------------+-------+
1 row in set (0.00 sec)
但是这个参数是OS限制的，所以我们设定的值并不一定总是生效。如果OS限制MySQL不能修改这个值，那么置为0。如果是专用的MySQL服务器上，这个值一般要设置的尽量大，就是设为没有报Too many open files错误的最大值，这样就能一劳永逸了。当操作系统无法分配足够的文件描述符的时候，mysqld进程会在错误日志里记录警告信息。
相应的，有两个状态变量记录了当前和历史的文件打开信息：
mysql& show global status like '%open%file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files&&& | 0&&&& |
| Opened_files& | 76&&& |
+---------------+-------+
2 rows in set (0.00 sec)
MySQL为每个连接分配线程来处理，可以通过threads_connected参数查看当前分配的线程数量：
mysql& show status like '%thread%';
+------------------------------------------+-------+
| Variable_name&&&&&&&&&&&&&&&&&&&&&&&&&&& | Value |
+------------------------------------------+-------+
| Delayed_insert_threads&&&&&&&&&&&&&&&&&& | 0&&&& |
| Performance_schema_thread_classes_lost&& | 0&&&& |
| Performance_schema_thread_instances_lost | 0&&&& |
| Slow_launch_threads&&&&&&&&&&&&&&&&&&&&& | 0&&&& |
| Threads_cached&&&&&&&&&&&&&&&&&&&&&&&&&& | 0&&&& |
| Threads_connected&&&&&&&&&&&&&&&&&&&&&&& | 1&&&& |
| Threads_created&&&&&&&&&&&&&&&&&&&&&&&&& | 1&&&& |
| Threads_running&&&&&&&&&&&&&&&&&&&&&&&&& | 1&&&& |
+------------------------------------------+-------+
8 rows in set (0.00 sec)
比较threads_connected参数和前面提到的max_connections参数，也可以作为目前的系统负载的参照，决定是否需要修改连接数。
查看每个线程的详细信息：mysql&对影响系统运行的线程：kill connection|query threadid的命令杀死。
上一页： &&&&&下一页：相关内容
阴阳神将喵将传编辑推荐
电脑壁纸相关专题当前位置:&&
mysql中如何查看最大连接数(max_connections)和修改最大连接数
&&&&发布时间:&&
以下的文章主要是向大家介绍的是MySQL最大连接数的修改，我们大家都知道MySQL最大连接数的默认值是100, 这个数值对于并发连接很多的数据库的应用是远不够用的，当连接请求大于默认连接数后，就会出现无...
&& & &以下的文章主要是向大家介绍的是MySQL最大连接数的修改，我们大家都知道MySQL最大连接数的默认值是100, 这个数值对于并发连接很多的数据库的应用是远不够用的，当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些。在使用MySQL数据库的时候，经常会遇到这么一个问题，就是“Can not connect to MySQL server. Too many connections”-mysql 1040错误，这是因为访问MySQL且还未释放的连接数目已经达到MySQL的上限。通常，mysql的最大连接数默认是100, 最大可以达到16384。(本文由搜集整理)& & 常用的修改最大连接数的最常用的两种方式如下:& & 第一种：命令行查看和修改最大连接数(max_connections)。&&&&&mysql&-uuser&-ppassword(命令行登录MySQL)
&&&&mysql&show&variables&like&'max_connections';(查可以看当前的最大连接数)
&&&&msyql&set&global&max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)
&&&&mysql&exit& & 这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。& & 第二种：通过修改配置文件来修改mysql最大连接数(max_connections)。& & 这种方式说来很简单，只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能，一个是在安装目录下，另一种是在数据文件的目录下，安装的时候如果没有人为改变目录的话，一般就在C:/ProgramData/MySQL往下的目录下,linux系统中一般在/etc目录下。& 其他需注意的：& &在编程时，由于用MySQL语句调用数据库时，在每次之执行语句前，会做一个临时的变量用来打开数据库，所以你在使用MySQL语句的时候，记得在每次调用完MySQL之后就关闭MySQL临时变量。& & 另外对于访问量大的，可以考虑直接写到文本中，根据预测的访问量，先定义假若是100个文件文件名,需要的时候，再对所有文本文件中的数据进行分析，再导入数据库。&
169IT站内文章除注明原创外，均为转载,整理或搜集自网络.欢迎任何形式的转载，转载请注明出处.转载请注明：文章转载自:[]本文标题:
本类别最新文章推荐:
特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！
(C)2012-,站长邮箱:www_169it_(请将#改为@)[root@linuxidc&mongodb-linux-i686-2.4.1]#&./bin/mongo&192.168.6.42MongoDB&shell&version:&2.4.1connecting&to:&192.168.6.42/test&&db.serverStatus().connections{&"current"&:&1,&"available"&:&818,&"totalCreated"&:&NumberLong(1)&}&&db.serverStatus().connections{&"current"&:&2,&"available"&:&817,&"totalCreated"&:&NumberLong(2)&}
819个连接数对于一般的站点我认为已经够用，并且都是现连现取现断。但这个连接数也可以修改，只要在启动的时候加入--maxConns即可服务器启动
[root@lee&mongodb-linux-x86_64-2.4.1]#&./bin/mongod&--dbpath=/root/db&--maxConns=2000Wed&Apr&3&11:06:21.905&[initandlisten]&MongoDB&starting&:&pid=2812&port=27017&dbpath=/root/db&64-bit&host=leeWed&Apr&3&11:06:21.957&[initandlisten]&db&version&v2.4.1Wed&Apr&3&11:06:21.957&[initandlisten]&git&version:&ce11a693be8b4d0d160d633eee75110Wed&Apr&3&11:06:21.957&[initandlisten]&build&info:&Linux&ip-10-2-29-40&2.6.21.7-2.ec2.v1.2.fc8xen&#1&SMP&Fri&Nov&20&17:48:28&EST&&BOOST_LIB_VERSION=1_49Wed&Apr&3&11:06:21.957&[initandlisten]&allocator:&tcmallocWed&Apr&3&11:06:21.957&[initandlisten]&options:&{&dbpath:&"/root/db",&maxConns:&2000&}Wed&Apr&3&11:06:21.982&[initandlisten]&journal&dir=/root/db/journalWed&Apr&3&11:06:21.982&[initandlisten]&recover&:&no&journal&files&present,&no&recovery&neededWed&Apr&3&11:06:22.297&[initandlisten]&preallocateIsFaster=true&2.62Wed&Apr&3&11:06:22.717&[initandlisten]&--maxConns&too&high,&can&only&handle&819Wed&Apr&3&11:06:22.724&[initandlisten]&waiting&for&connections&on&port&27017Wed&Apr&3&11:06:22.725&[websvr]&admin&web&console&waiting&for&connections&on&port&28017Wed&Apr&3&11:06:25.126&[initandlisten]&connection&accepted&from&192.168.4.86:53917&#1&(1&connection&now&open)
查询最大连接数
[root@linuxidc&mongodb-linux-i686-2.4.1]#&./bin/mongo&192.168.6.42MongoDB&shell&version:&2.4.1connecting&to:&192.168.6.42/test&&db.serverStatus().connections{&"current"&:&1,&"available"&:&818,&"totalCreated"&:&NumberLong(1)&}&&
发现还是819？其实是Linux默认进程能打开最大文件数有关，可以通过ulimit 解决
[root@lee&mongodb-linux-x86_64-2.4.1]#&ulimit&-n&2500[root@lee&mongodb-linux-x86_64-2.4.1]#&./bin/mongod&--dbpath=/root/db&--maxConns=2000Wed&Apr&3&11:11:07.013&[initandlisten]&MongoDB&starting&:&pid=2930&port=27017&dbpath=/root/db&64-bit&host=leeWed&Apr&3&11:11:07.013&[initandlisten]&db&version&v2.4.1Wed&Apr&3&11:11:07.013&[initandlisten]&git&version:&ce11a693be8b4d0d160d633eee75110Wed&Apr&3&11:11:07.013&[initandlisten]&build&info:&Linux&ip-10-2-29-40&2.6.21.7-2.ec2.v1.2.fc8xen&#1&SMP&Fri&Nov&20&17:48:28&EST&&BOOST_LIB_VERSION=1_49Wed&Apr&3&11:11:07.013&[initandlisten]&allocator:&tcmallocWed&Apr&3&11:11:07.013&[initandlisten]&options:&{&dbpath:&"/root/db",&maxConns:&2000&}Wed&Apr&3&11:11:07.031&[initandlisten]&journal&dir=/root/db/journalWed&Apr&3&11:11:07.031&[initandlisten]&recover&:&no&journal&files&present,&no&recovery&neededWed&Apr&3&11:11:07.170&[initandlisten]&waiting&for&connections&on&port&27017Wed&Apr&3&11:11:07.171&[websvr]&admin&web&console&waiting&for&connections&on&port&28017Wed&Apr&3&11:11:10.076&[initandlisten]&connection&accepted&from&192.168.4.86:53161&#1&(1&connection&now&open)
再查看最大连接数，搞定
[root@linuxidc&mongodb-linux-i686-2.4.1]#&./bin/mongo&192.168.6.42MongoDB&shell&version:&2.4.1connecting&to:&192.168.6.42/test&&db.serverStatus().connections{&"current"&:&1,&"available"&:&1999,&"totalCreated"&:&NumberLong(1)&}&&
关于ulimit的更多知识大家可以去网上检索检索客户端程序通常是通过DRIVER来??接，由于每次建立链接的成本都挺高，因此都用链接池来实现，SPRING DATA MONGODB中是如下配置
mongo.dbname=cms#线程池的大小mongo.connectionsPerHost=100#这个*mongo.connectionsPerHost则是如果链接数大于100的等待xttk数mongo.threadsAllowedToBlockForConnectionMultiplier=4#等待线程的等待时间mongo.maxWaitTime=1500mongo.socketTimeout=1500mongo.connectTimeout=1000mongo.autoConnectRetry=truemongo.socketKeepAlive=truemongo.slaveOk=true
autoConnectRetry&simply means the driver will automatically attempt to reconnect to the server(s) after unexpected disconnects. In production environments you usually want this set to true.
connectionsPerHost&are the amount of physical connections a single Mongo instance (it's singleton so you usually have one per application) can establish to a mongod/mongos process. At time of writing the java driver will establish this amount of connections eventually even if the actual query throughput is low (in order words you will see the "conn" statistic in mongostat rise until it hits this number per app server).
There is no need to set this higher than 100 in most cases but this setting is one of those "test it and see" things. Do note that you will have to make sure you set this low enough so that the total amount of connections to your server do not exceed
db.serverStatus().connections.available
In production we currently have this at 40.
connectTimeout. As the name suggest number of milliseconds the driver will wait before a connection attempt is aborted. Set timeout to something long (15-30 seconds) unless there's a realistic, expected chance this will be in the way of otherwise succesful connection attempts. Normally if a connection attempt takes longer than a couple of seconds your network infrastructure isn't capable of high throughput.
maxWaitTime. Number of ms a thread will wait for a connection to become available on the connection pool, and raises an exception if this does not happen in time. Keep default.
socketTimeout. Standard socket timeout value. Set to 60 seconds (60000).
threadsAllowedToBlockForConnectionMultiplier. Multiplier for connectionsPerHost that denotes the number of threads that are allowed to wait for connections to become available if the pool is currently exhausted. This is the setting that will cause the "com.mongodb.DBPortPool$SemaphoresOut: Out of semaphores to get db connection" exception. It will throw this exception once this thread queue exceeds the threadsAllowedToBlockForConnectionMultiplier value. For example, if the connectionsPerHost is 10 and this value is 5 up to 50 threads can block before the aforementioned exception is thrown.
If you expect big peaks in throughput that could cause large queues temporarily increase this value. We have it at 1500 at the moment for exactly that reason. If your query load consistently outpaces the server you should just improve your hardware/scaling situation accordingly.
readPreference.&(UPDATED, 2.8+)&Used to determine the default read preference and replaces "slaveOk". Set up a ReadPreference through one of the class factory method.&A full description of the most common settings can be found at the end of this post
w.&(UPDATED, 2.6+)&This value determines the "safety" of the write. When this value is -1 the write will not report any errors regardless of network or database errors. WriteConcern.NONE is the appropriate predefined WriteConcern for this. If w is 0 then network errors will make the write fail but mongo errors will not. This is typically referred to as "fire and forget" writes and should be used when performance is more important than consistency and durability. Use WriteConcern.NORMAL for this mode.
If you set w to 1 or higher the write is considered safe. Safe writes perform the write and follow it up by a request to the server to make sure the write succeeded or retrieve an error value if it did not (in other words, it sends a getLastError() command after you write). Note that until this getLastError() command is completed the connection is reserved. As a result of that and the additional command the throughput will be signficantly lower than writes with w &= 0. With a w value of exactly 1 MongoDB guarantees the write succeeded (or verifiably failed) on the instance you sent the write to.
In the case of replica sets you can use higher values for w whcih tell MongoDB to send the write to at least "w" members of the replica set before returning (or more accurately, wait for the replication of your write to "w" members). You can also set w to the string "majority" which tells MongoDB to perform the write to the majority of replica set members (WriteConcern.MAJORITY). Typicall you should set this to 1 unless you need raw performance (-1 or 0) or replicated writes (&1). Values higher than 1 have a considerable impact on write throughput.
fsync. Durability option that forces mongo to flush to disk after each write when enabled. I've never had any durability issues related to a write backlog so we have this on false (the default) in production.
j&*(NEW 2.7+)*. Boolean that when set to true forces MongoDB to wait for a successful journaling group commit before returning. If you have journaling enabled you can enable this for additional durability. Refer to&&to see what journaling gets you (and thus why you might want to enable this flag).
ReadPreference&The ReadPreference class allows you to configure to what mongod instances queries are routed if you are working with replica sets. The following options are available :
ReadPreference.primary()&: All reads go to the repset primary member only. Use this if you require all queries to return consistent (the most recently written) data. This is the default.
ReadPreference.primaryPreferred()&: All reads go to the repset primary member if possible but may query secondary members if the primary node is not available. As such if the primary becomes unavailable reads become eventually consistent, but only if the primary is unavailable.
ReadPreference.secondary()&: All reads go to secondary repset members and the primary member is used for writes only. Use this only if you can live with eventually consistent reads. Additional repset members can be used to scale up read performance although there are limits to the amount of (voting) members a repset can have.
ReadPreference.secondaryPreferred()&: All reads go to secondary repset members if any of them are available. The primary member is used exclusively for writes unless all secondary members become unavailable. Other than the fallback to the primary member for reads this is the same as ReadPreference.secondary().
ReadPreference.nearest()&: Reads go to the nearest repset member available to the database client. Use only if eventually consistent reads are acceptable. The nearest member is the member with the lowest latency between the client and the various repset members. Since busy members will eventually have higher latencies this&should&also automatically balance read load although in my experience secondary(Preferred) seems to do so better if member latencies are relatively consistent.
Note : All of the above have tag enabled versions of the same method which return TaggableReadPreference instances instead. A full description of replica set tags can be found here :
6 使用 yum 安装MongoDB及服务器端配置
13.04下安装MongoDB2.4.3
MongoDB入门必读(概念与实战并重)
Ubunu 14.04下MongoDB的安装指南
《MongoDB 权威指南》(MongoDB: The Definitive Guide)英文文字版[PDF]
Nagios监控MongoDB分片集群服务实战
基于CentOS 6.5操作系统搭建MongoDB服务
MongoDB 的详细介绍：MongoDB 的下载地址：
本文永久更新链接地址：
相关资讯 & & &
图片资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款