node 轮询mysql ,报错,连接数过多

福尔摩斯对我笑 2019-03-11 8698 次 2066条

前言

在做一个项目时,使用到了连接池,项目中有2个轮询数据库的操作,开始时在本地测试没有问题,都很正常。

项目部署上线后,运行5分钟后发现出错,错误代码:MySQL: ERROR 1040: Too many connections,连接数过多。

查看连接数状态

mysql -u root -p

show full  processlist //查看连接数

发现有大量等待的连接,没有关闭。

看到这我比较奇怪,该项目中我其实轮询了多个数据库,为啥其它数据库没报错,唯有这个连接数过高呢?

检查代码

查看连接池代码,也没有问题,查询后确实已经回收了,有点郁闷,查了 mysql模块的手册,确实也没问题啊。mysql模块手册

 async await_query(sql,values){
        return new Promise((resolve, reject) => {
            this.connect().getConnection(function (err, connection) {
                if (err) {
                    reject(err)
                } else {
                    
                    connection.query(sql, values, (err, rows) => {
                       
                        if (err) {
                            connection.release(); //回收了
                            reject(err);
                        } else {
                            connection.release();
                            resolve(rows);
                           
                        }
                      
                    })
                }
            })

            
           
        })
       
    }
    

也尝试过,直接关闭链接,发现还是不行

connect().end(function (err) {
            console.log(err+"关闭数据库链接");
     });

查看mysql最大连接数,最大为214,仔细对比了下当前的连接数,发现确实超过214个连接,就开始报错

show variables like '%max_connections%';

于是,想了想,把连接数改大一点不就行了,经测试,这样做确实可以。但是看到一堆sleep的请求,强迫症犯了,这样虽能解决,但感觉不好,浪费资源,于是又开始查询资料。

set GLOBAL max_connections = 300;
  • 先来看看sleep的状态是什么意思?
  1. 客户端程序在退出之前没有关闭连接.[写程序的疏忽,或者数据库的db类库没有自动关闭每次的连接。
  2. 客户端sleep的时间在wait_timeout或interactive_timeout规定的秒内没有发出任何请求到服务器. [类似长连,类似于不完整的tcp ip协议构造,服务端一直认为客户端仍然存在(有可能客户端已经断掉了)]
  3. 客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了
  • 解决方法
  1. 修改mysql配置文件: wait_timeout=10 ,这样做需要重启服务器,不太好。
  2. 通过mysql命令修改

set global wait_timeout = 10;

使用第二种方法修改后,重新启动服务,发现确实解决了该问题,现在所有sleep的链接在10秒后都会自动关闭。

注意:使用以上方法时,也会造成其他问题,比如有些场景,连接就必须持久一点。设置成10秒很容易断开。

测试了一段时间,发现还是有问题,所以将回收换成了立即销毁

connection.destroy();

延伸问题

1、修改链接数后,查看当前的配置

show variables like '%timeout%';

发现,wait_timeout 还是 28800 , 默认的8小时。ok,继续搜寻吧 发现一篇博客: https://www.cnblogs.com/agostop/p/4835934.html,这里解释了这个问题

2、数据库查询后我确实已经回收了该链接,为啥还是存在sleep呢? 麻烦知道的大神,解答下,谢谢

今天又尝试了下,问题解决了,是我调用类方法分开进行了引用,导致无法准确获取到链接信息。

标签: 无

非特殊说明,本博所有文章均为博主原创。

头像也是LOGO
未来博客 博主大人
到了这个年纪,不应该再彷徨!

发表评论



 


已有 2066 条评论 1


  1. ┤솔레어카지노◑모 롱고 카지노☪《》↔gmvcs.com룰렛 필승 전략2019-02-21-22-05[]포커 이기는 법☒☺[]aRr마카오 개인 롤링말레이시아 카지노IoG카지노 룰렛 게임황금성 먹튀

    Spot on with this write-up, I actually feel this website needs far more attention. I’ll probably be returning to read through more, thanks for the information!

    回复 2020年2月26日
  2. 『인천콜걸』⇦예약금없는출장샵❤춘천 모텔 가격♥[카톡: wyk92]ト「qьx488.сом」[]┠☳인천[][]인천┩2019-04-20-12-24익산 모텔 추천ZJf[]╖[]Qs출장서비스인천태국 에스코트ヌ

    You are so interesting! I don’t think I’ve truly read through anything like that before. So great to find someone with a few unique thoughts on this issue. Seriously.. thank you for starting this up. This site is one thing that is needed on the internet, someone with some originality!

    回复 2020年2月26日
  3. Cheap towing

    I believe that is among the such a lot vital info
    for me. And i am satisfied studying your article. However
    want to statement on few common things, The web
    site taste is ideal, the articles is really excellent : D.

    Good process, cheers

    回复 2020年2月26日
  4. anus

    I’d like to thank you for the efforts you have put in penning this blog. I’m hoping to check out the same high-grade content from you in the future as well. In truth, your creative writing abilities has motivated me to get my own, personal site now 😉

    回复 2020年2月26日
  5. shit

    Hi, I do believe this is a great blog. I stumbledupon it 😉 I am going to return once again since i have saved as a favorite it. Money and freedom is the greatest way to change, may you be rich and continue to guide other people.

    回复 2020年2月26日
  6. douche

    Nice post. I learn something totally new and challenging on blogs I stumbleupon on a daily basis. It’s always useful to read through articles from other authors and use a little something from other websites.

    回复 2020年2月26日
  7. judi idn poker

    Terrific web-site you have here, i do agree on some points nevertheless, but not all.

    回复 2020年2月26日
  8. douche

    I really like it when people come together and share ideas. Great site, keep it up!

    回复 2020年2月26日
  9. 골목 게임

    I savor, lead to I discovered just what I used to be taking a look for.

    You have ended my four day lengthy hunt! God Bless
    you man. Have a nice day. Bye

    回复 2020年2月26日
  10. tienda etnica barata en madrid

    I truly appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thx again

    回复 2020年2月26日
  11. Folding Mountain Bikes

    Hey, you used to write magnificent, but the last several posts have been kinda boring… I miss your tremendous writings. Past several posts are just a bit out of track! come on!

    回复 2020年2月26日
  12. 우리카지노

    “I actually desire a copper tamagoyaki pan,” says Craig
    Koketsu, govt chef of Quality Branded, the restaurant
    group behind Quality Meats and Quality Italian. Chad Shaner, govt chef on the Wild Son, is hoping for a nice juicer this 12 months. https://uricasino114.com

    回复 2020年2月26日
  13. 우리카지노

    “I actually desire a copper tamagoyaki pan,” says Craig Koketsu, govt chef of Quality Branded, the restaurant group behind Quality Meats and Quality Italian. Chad Shaner, govt chef on the
    Wild Son, is hoping for a nice juicer this 12 months. https://uricasino114.com

    回复 2020年2月26日
  14. soccerstreams100

    Pretty nice post. I just stumbled upon your weblog and wished to say that I have truly enjoyed browsing your blog posts. After all I will be subscribing to your feed and I hope you write again soon!

    回复 2020年2月26日
  15. www.secrettantric.com

    Hurrah! After all I got a weblog from where I can genuinely obtain valuable
    facts concerning my study and knowledge.

    回复 2020年2月26日
  16. 포커 족보

    I know this if off topic but I’m looking into starting
    my own blog and was wondering what all is needed to get set up?
    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.

    Kudos

    回复 2020年2月26日
  17. 바카라 스토리

    Hey there this is somewhat 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 experience so I wanted to get guidance
    from someone with experience. Any help would be greatly appreciated!

    回复 2020年2月26日
  18. 카지노

    After looking over a handful of the blog articles
    on your site, I really like your way of writing a
    blog. I bookmarked it to my bookmark webpage list and
    will be checking back soon. Take a look at my website too and
    tell me what you think.

    回复 2020年2月26日
  19. 온라인 카지노

    Helpful info. Lucky me I discovered your website by
    chance, and I am shocked why this coincidence didn’t happened in advance!
    I bookmarked it.

    回复 2020年2月26日
  20. 카지노 게임 사이트

    It’s a shame you don’t have a donate button! I’d without a
    doubt donate to this fantastic blog! I guess for now i’ll settle for bookmarking and adding your RSS feed to my Google account.
    I look forward to fresh updates and will talk about this blog with my Facebook group.
    Chat soon!

    回复 2020年2月26日
  21. SM 카지노

    Wow, wonderful blog layout! How long have you been blogging
    for? you make blogging look easy. The overall look of your site is wonderful, as well as the content!

    回复 2020年2月26日
  22. 바카라 룰

    Hi would you mind letting me know which webhost you’re using?
    I’ve loaded your blog in 3 different internet browsers and I must say this blog loads a lot quicker then most.
    Can you recommend a good internet hosting provider
    at a honest price? Thank you, I appreciate it!

    回复 2020年2月26日
  23. bloody hell

    Having read this I thought it was really informative. I appreciate you spending some time and effort to put this content together. I once again find myself spending way too much time both reading and leaving comments. But so what, it was still worth it!

    回复 2020年2月26日
  24. jasa pengurusan siujk

    I’m extremely pleased to discover this web site. I need to to thank you for ones time for this particularly fantastic read!! I definitely loved every bit of it and I have you saved to fav to see new information on your web site.

    回复 2020年2月26日
  25. dummy

    Next time I read a blog, Hopefully it won’t fail me as much as this one. I mean, I know it was my choice to read through, however I genuinely believed you’d have something useful to talk about. All I hear is a bunch of crying about something that you could fix if you weren’t too busy seeking attention.

    回复 2020年2月26日
  26. sadist

    This site was… how do you say it? Relevant!! Finally I’ve found something that helped me. Thanks a lot!

    回复 2020年2月26日
  27. cheap cialis online

    tadalafil 20mg x 30 tabletten [url=http://buyscialisrx.com/]online cialis[/url] uso de tadalafil en mujeres online cialis predám tadalafil difference entre tadalafil et
    cialis http://buyscialisrx.com/ first intake of
    vardenafil and tadalafil

    回复 2020年2月26日
  28. George

    You can definitely see your expertise in the work you write.
    The world hopes for even more passionate writers such as you who aren’t afraid to say
    how they believe. At all times follow your heart.

    回复 2020年2月26日
  29. asshole

    Good day! I could have sworn I’ve been to this blog before but after browsing through many of the articles I realized it’s new to me. Nonetheless, I’m certainly delighted I discovered it and I’ll be bookmarking it and checking back regularly!

    回复 2020年2月26日
  30. 슬롯 머신

    This is really interesting, You’re a very skilled blogger.
    I have joined your feed and look forward to seeking more of your fantastic post.
    Also, I’ve shared your site in my social networks!

    回复 2020年2月26日
  31. desain rumah terbaru

    Hi colleagues, fastidious paragraph and good urging commented here, I am really enjoying by
    these.

    回复 2020年2月26日
  32. tits

    Hello there, I think your web site could possibly be having browser compatibility issues. When I look at your blog in Safari, it looks fine however, if opening in I.E., it has some overlapping issues. I merely wanted to provide you with a quick heads up! Other than that, great site!

    回复 2020年2月26日
  33. 로투스 홀짝 게임

    Greetings! I know this is kind of off topic but I was wondering which
    blog platform are you using for this site?
    I’m getting tired of WordPress because I’ve had problems with hackers and I’m
    looking at alternatives for another platform.
    I would be great if you could point me in the
    direction of a good platform.

    回复 2020年2月26日
  34. 더나인 카지노

    You actually make it seem so easy together with your presentation but I in finding this matter
    to be actually one thing that I feel I would never understand.
    It sort of feels too complicated and extremely vast for me.
    I am having a look ahead in your subsequent post, I will try to get the dangle of it!

    回复 2020年2月26日
  35. 바카라 게임

    It is the best time to make some plans for the longer term and it’s time to be
    happy. I’ve learn this put up and if I may just I desire to
    counsel you few interesting things or suggestions. Perhaps you could write next articles referring to this article.

    I wish to learn even more things about it!

    回复 2020年2月26日
  36. eudoraspender.wordpress.com

    Heya! I’m at work surfing around your blog from my new iphone 3gs!
    Just wanted to say I love reading through your blog and look forward to all your posts!
    Keep up the outstanding work!

    回复 2020年2月26日
  37. 우리 카지노

    Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same
    niche. Your blog provided us valuable information to work on. You have done a marvellous job!

    回复 2020年2月26日
  38. penis

    The next time I read a blog, Hopefully it won’t disappoint me as much as this one. I mean, Yes, it was my choice to read through, nonetheless I truly thought you’d have something helpful to talk about. All I hear is a bunch of complaining about something you could possibly fix if you were not too busy looking for attention.

    回复 2020年2月26日
  39. cockfucker

    I blog often and I genuinely appreciate your content. This article has truly peaked my interest. I’m going to book mark your blog and keep checking for new details about once per week. I subscribed to your RSS feed as well.

    回复 2020年2月26日
  40. w88

    Its such as you read my mind! You appear to grasp a lot approximately this, like you
    wrote the e book in it or something. I think that you can do with some percent to
    power the message home a bit, however instead of that, that is magnificent blog.

    An excellent read. I’ll definitely be back.

    回复 2020年2月26日
  41. 우리 카지노

    you are really a good webmaster. The site loading velocity is incredible.
    It sort of feels that you’re doing any unique trick. Furthermore,
    The contents are masterpiece. you’ve done a great process in this matter!

    回复 2020年2月26日
  42. แทงหวยออนไลน์

    No matter if some one searches for his essential thing, thus
    he/she desires to be available that in detail, thus that thing is maintained over
    here.

    回复 2020年2月26日
  43. hydrange.design.blog

    I visited many sites however the audio quality for audio songs existing at this site is genuinely marvelous.

    回复 2020年2月26日
  44. 더킹 카지노

    Write more, thats all I have to say. Literally, it seems
    as though you relied on the video to make your point. You
    clearly know what youre talking about, why waste your intelligence on just posting videos to your
    weblog when you could be giving us something informative to read?

    回复 2020年2月26日
  45. modern construction materials

    Hi there! I know this is kinda off topic but I was wondering which blog platform are you using for this website? I’m getting sick and tired of WordPress because I’ve had issues with hackers and I’m looking at alternatives for another platform. I would be fantastic if you could point me in the direction of a good platform.

    回复 2020年2月26日
  46. tension control bolts

    Do you mind if I quote a couple of your posts as long as I provide credit and sources back to your webpage? My blog is in the very same niche as yours and my users would really benefit from some of the information you present here. Please let me know if this ok with you. Thank you!

    回复 2020年2月26日
  47. 인터넷 카지노

    Really when someone doesn’t know then its up
    to other visitors that they will assist, so here it
    occurs.

    回复 2020年2月25日
  48. 바카라 사이트

    Highly energetic blog, I enjoyed that a lot. Will there be a part 2?

    回复 2020年2月25日