MySQL "Too many connections" + > 6000 queries / second

Question

We have quite a big mysql (innodb) database with a lot of insert queries and 6000 queries per second.

Settings are:
max_connections = 1000

and usually when i do show status like '%onn%';

Threads_connected = 3

Nevertheless sometimes (every few random minutes) we get a "Too many connections" Error.

I read here: http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html
that max_connections > 1000 eat a lot of RAM and might be unstable.

How to handle such error? Is there a query queue which makes sure to have a consistent query flow here?

Answer 1

Are you sure about the threads connected?

Disclaimer: I am a SqlServer database guy, so - my MySql knowledge is limited.

Still.

If you normally have only 3 threads connected - that is a BIG blow to go over 1000. If you use proper pooling this may be the result of some overloading that suddenyl delays transactions from finisheing, resulting a BIG overhead of new connections.

I Just look at my small server and I have 59 connections open (pooling, keeping them alive 2 minutes) for about 30 or so batches per second (we do HPC - so in this case it is a lot of agents pulling status every second if they need new work - plus some results up and download). That is a lot more than you have - for less batches. if my connections would suddently go above 200 I would really investigate this. Assuming you have really 6000 batches per second at the same time as 3 connections - that is a factor of 300 explosion (very roughly) in connections which is something to really investigate and find the reason for.

Also - define your RAM. I mean, what is quite big. With this amount of sql statements I would expect a real server (64+gb memory, 2xXeon or something). 1000 connections may not use a lot of memory for a real server - they do for a small vps though. But then most such small instances do not run 6000 selects per second.