1、问题展现
应用端登录出现Too many connections报错

检查发现mysql数据库服务端已经达到了max_connections上限

mysql> show variables like ‘max_connections’;
+—————–+——-+
| Variable_name   | Value |
+—————–+——-+
| max_connections | 1900  |
+—————–+——-+
1 row in set (0.00 sec)

mysql> show processlist;
已经达到了1900会话数。

thread_pool设置并不能阻止会话数的上升。
mysql> show variables like ‘thread_pool%’;
+————————————–+——-+
| Variable_name                        | Value |
+————————————–+——-+
| thread_pool_algorithm                | 0     |
| thread_pool_high_priority_connection | 0     |
| thread_pool_max_unused_threads       | 0     |
| thread_pool_prio_kickup_timer        | 1000  |
| thread_pool_size                     | 16    |
| thread_pool_stall_limit              | 6     |
+————————————–+——-+
6 rows in set (0.00 sec)

2、问题处理
重启mysql的服务。重启完mysql服务后,的确mysql的session数下降了,但是很快会话数又上升到了1900。
判断并不是mysql的服务器端的会话没释放,而是application端的会话没释放。
重启application的两台服务器,mysql的会话数恢复正常。

3、结论
先来看看mysql服务器端的会话保持时间:
mysql> show variables like ‘%wait_timeout%’; 
+————————–+———-+ 
| Variable_name | Value | 
+————————–+———-+ 
| innodb_lock_wait_timeout | 50 | 
| lock_wait_timeout | 31536000 | 
| wait_timeout | 28800 | 
+————————–+———-+ 
3 rows in set (0.00 sec) 

mysql> show variables like ‘%interactive_timeout%’; 
+———————+——-+ 
| Variable_name | Value | 
+———————+——-+ 
| interactive_timeout | 28800 | 
+———————+——-+ 
1 row in set (0.00 sec) 

interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。又见wait_timeout 
wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义),又见interactive_timeout 
如此看来,两个变量是共同控制的,那么都必须对他们进行修改了。继续深入这两个变量wait_timeout的取值范围是1-2147483(Windows),1-31536000(linux),interactive_time取值随wait_timeout变动,它们的默认值都是28800。 
MySQL的系统变量由配置文件控制,当配置文件中不配置时,系统使用默认值,这个28800就是默认值。要修改就只能在配置文件里修改。Windows下在%MySQL HOME%/bin下有mysql.ini配置文件,打开后添加两个变量,赋值。 

要解决这个问题:
1、Use connection pooling at client side (in MySQL Connector) to reduce the number of active connections between the client and the server. 
是在客户端安装MySQL Connector
2、Improve the application design to reduce the number of active connections needed and to reduce the time the connection has to stay active. 
从应用端去降低并发数,减少每个会话的保持时间
3、Increase the number of connections handled by MySQL server by adjusting max_connections (keep in mind that this consumes additional RAM and is still limited)
在mysql服务器端增加最大连接数设置,不过会消耗大量内存

建议用第二种方法。因为当前应用会话保持时间是10分钟,建议降低这个数值。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2147815/,如需转载,请注明出处,否则将追究法律责任。

This article has 1,337 comments

  1. codes

    I know this if off topic but I’m looking into starting my
    own weblog and was wondering what all is required to get setup?
    I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very web savvy so I’m not 100% positive. Any tips or advice would be greatly
    appreciated. Thanks

  2. Annawrete

    [url=http://ivermectincv.com/]ivermectin 6 mg tablets[/url] [url=http://viagrarem.com/]buy 1000 viagra[/url] [url=http://sildenafil9.com/]viagra tablets online india[/url] [url=http://sildenafil240.com/]generic sildenafil 25 mg[/url] [url=http://flagyl911.com/]flagyl price[/url] [url=http://viagrasl.com/]viagra voucher[/url]

  3. Denwrete

    [url=http://backtrim.com/]bactrim 400mg 80mg[/url] [url=http://silagratab.com/]buy silagra 50 mg[/url] [url=http://finpecia911.com/]finpecia tablets online[/url] [url=http://atenolol25.com/]atenolol 50 mg pill[/url] [url=http://tetracyclinecaps.com/]order tetracycline online without a prescription[/url]

  4. Joewrete

    [url=https://indocinrx.com/]indocin in india[/url] [url=https://ventolin24.com/]ventolin australia buy[/url] [url=https://backtrim.com/]bactrim ds medication[/url]

  5. Miawrete

    [url=https://tetracyclinecaps.com/]tetracycline 500 mg brand name[/url] [url=https://dapoxetine911.com/]dapoxetine medicine online[/url] [url=https://clonidinenorx.com/]clonidine 0.2 mg tablets[/url] [url=https://advair2019.com/]advair cost in usa[/url] [url=https://anafranilmed.com/]order anafranil online[/url]

  6. Nickwrete

    [url=http://clomid150.com/]can you order clomid online[/url] [url=http://propecia8.com/]where to buy finasteride[/url] [url=http://atenolol25.com/]atenolol online without prescription[/url]

  7. Colby

    Hi this is kinda of off topic but I was wanting to know if blogs use WYSIWYG
    editors or if you have to manually code with HTML. I’m starting a blog soon but have
    no coding skills so I wanted to get advice from someone with experience.
    Any help would be enormously appreciated!

  8. Sheri

    Its like you read my mind! You appear to know
    so much about this, like you wrote the book in it or something.
    I think that you could do with a few pics to drive the message home a bit, but other than that, this is great
    blog. A great read. I will certainly be back.

  9. XE TAI ISUZU FVR34SE4

    My partner and I stumbled over here by a different
    website and thought I may as well check things out.
    I like what I see so i am just following you.
    Look forward to finding out about your web page for a second
    time.

  10. peinture decorative

    I am extremely impressed with your writing skills as well
    as with the layout on your weblog. Is this a paid theme
    or did you modify it yourself? Anyway keep up the excellent
    quality writing, it’s rare to see a nice blog like this one today.

  11. world news

    Hey I am so thrilled I found your site, I really found you by error, while I was searching on Bing for something else, Nonetheless I am here now and would just
    like to say thank you for a fantastic post and a all round enjoyable blog
    (I also love the theme/design), I don’t have time to read
    it all at the moment but I have book-marked it and also added in your RSS feeds,
    so when I have time I will be back to read much more, Please do keep up the awesome job.

  12. Jimwrete

    [url=https://valtrex.us.org/]valtrex 1000 mg price[/url] [url=https://amoxicillinz.com/]buy amoxicillin[/url] [url=https://silagra24.com/]silagra 100[/url]

  13. Denwrete

    [url=http://furosemide.us.org/]furosemide 20[/url] [url=http://erythromycin24.com/]buy erythromycin[/url] [url=http://proscar.us.org/]buy proscar[/url] [url=http://plavix.us.com/]medication plavix 75 mg[/url] [url=http://metforminglc.com/]metformin 500 mg cost[/url]

  14. Joewrete

    [url=https://baclofenp.com/]medication baclofen 10 mg[/url] [url=https://flagyl365.com/]flagyl online no prescription[/url] [url=https://inderal.us.com/]cheap inderal[/url] [url=https://amoxicillintz.com/]amoxicillin 875[/url] [url=https://furosemide.us.org/]furosemide 20 mg tablet[/url] [url=https://lisinoprilm.com/]lisinopril 5mg tabs[/url] [url=https://zofran365.com/]zofran where to buy[/url]

  15. Jimwrete

    [url=https://disulfiram.us.com/]disulfiram antabuse[/url] [url=https://lisinoprilm.com/]lisinopril 20mg[/url] [url=https://yasminmed.com/]yasmin generic[/url] [url=https://amoxiltabs.com/]amoxil best price[/url] [url=https://sumycin365.com/]buy sumycin online[/url] [url=https://flagyl365.com/]buy oral flagyl antibiotic[/url] [url=https://erythromycin24.com/]benzoyl peroxide erythromycin gel[/url]