How can I monitoring, when Threads_connected increase from which place(files) or when it decrease, because some of cases it dose not fall down automatically in our production server and we are getting to max connection error there, So i need to identify issues in which files has problem, My site in magento 1.9 and max_connections=151 so i want to know what the ideal count for that and how can i monitoring ?

  • Create event which gets this parameter and saves it into some table (with grnularity of 1 sec, for example). Then analyze this data with conjunction to general log and search for a correlation between issues and value increasing. – Akina Apr 17 '19 at 8:34
  • What is Threads_created, Threads_cached, thread_cache_size? – Akina Apr 17 '19 at 8:36
  • Threads_created = 4921 Threads_cached = 1 thread_cache_size = 9 – Darshan TecStub Apr 17 '19 at 10:32
  • thread_cache_size value is too low. Increase it with accuracy (with the step of not more than 10%), wait some hours, then check the values again. Repeat until Threads_created is slightly greater than Threads_cached. – Akina Apr 17 '19 at 10:38
  • I have checked various time in my production server but always getting same count there. – Darshan TecStub Apr 18 '19 at 14:10

It sounds like there are a few long queries that lock lots of things, then runs for a while. Meanwhile, other connections come and try to get at the locked things, but get stalled.

If you are using MyISAM, switch to InnoDB.

Set long_query_time to 1 and turn on the slowlog. After a while, it will catch the naughty query. Show us the query, plus SHOW CREATE TABLE for the table(s) that it touches.

| improve this answer | |
  • @DarshanTecStub - Screenshots are not appreciated on this forum. Having to sign up to see them makes them more of a turn-off. – Rick James Apr 27 '19 at 14:25
  • When I run SHOW CREATE TABLE sales_flat_order_item command it give long result there, here I can't able to post that data because of comment area validation. So how can I display that table ? – Darshan TecStub Apr 30 '19 at 13:49
  • @DarshanTecStub - Go through the tutorial so you get an extra 100 Reputation. Then you can (I think) edit your Question. – Rick James Apr 30 '19 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.