WebFaction
Community site: login faq

Dozens of "too many connections" have recently been appearing daily. I've seen that some users are able to use functions on the website just fine, while some other users cannot - even when testing at the same time (I can perform an action, but my friend cannot and generates this error). Even if immediately after I ran 'FLUSH TABLES', my friend was still not able to perform the action and continued to generate the error.

I was hoping someone here could give it a look to see what I'm missing - any advice would be greatly appreciated!

  • Total tables: 20

my.cnf settings: (commented with # and the suggested formula I found)

  • performance schema = 0
  • max allowed packet = 500M
  • max connections = 300
  • thread cache size = 100 # < max used connections
  • table open cache = 2000 # total tables * threads connected * .5
  • open files limit = 4000 # table open cache * 2
  • key buffer size = 600M # 1/3 of available space

Few results from SHOW GLOBAL STATUS: (I last restarted MySQL (v5.6) about 2 days ago)

  • Aborted connects: 75
  • Connections: 804146
  • Created tmp disk tables: 1227
  • Created tmp files: 6
  • Created tmp tables: 1504
  • Key blocks unused: 466144
  • Key blocks used: 37489
  • Key read requests: 1641958665
  • Key reads: 37446
  • Key write requests: 3509
  • Key writes: 3509
  • Max used connections: 301
  • Not flushed delayed rows: 0
  • Open files: 137
  • Open streams: 0
  • Open table definitions: 161
  • Open tables: 270
  • Opened files: 5274
  • Opened table_definitions: 161
  • Opened tables: 277
  • Table open cache hits: 253113
  • Table open cache misses: 277
  • Table open cache overflows: 0
  • Threads cached: 99
  • Threads connected: 1
  • Threads created: 11363
  • Threads running: 1

asked 04 Nov, 21:34

skateproject's gravatar image

skateproject
133
accept rate: 0%


It sounds like a bug in your code more than an issue in configuration. I suggest enabling SQL logging and trying to isolate which queries are being called and make sure you code is closing the connection after it is done obtaining the data.

permanent link

answered 04 Nov, 23:23

johns's gravatar image

johns ♦♦
5.0k29
accept rate: 23%

@Johns Thank you for making that point. I've made some revisions in how queries were called and eliminated a number of redundancies. I haven't seen the 'too many connections' errors since.

(12 Nov, 19:18) skateproject skateproject's gravatar image

Glad to hear it helped. For posterity what lead me to that conclusion was the number of connections. It should be more in line with the number of concurrent HTTP connections, 300 concurrent HTTP connections/queries per request, would be a high number for most services we host. If you are hosting about that number of HTTP concurrent connections and you can't slim it down with caching or clever queries based on the least per request then you should increase the number.

(12 Nov, 20:45) johns ♦♦ johns's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×165
×84
×8

question asked: 04 Nov, 21:34

question was seen: 74 times

last updated: 12 Nov, 20:50

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2017 PARAGON INTERNET GROUP LIMITED
WEBFACTION IS A SERVICE OF PARAGON INTERNET GROUP LIMITED
REGISTERED IN ENGLAND AND WALES 7573953 - VAT REGISTRATION NUMBER 182147021
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM