Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Slave replication and have multiple database servers? –  Nathan C Jul 10 '13 at 18:35
    
just one server 8G RAM ;) –  Lukas Jul 11 '13 at 7:13
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
i guess that's it. We seem to have some MySQL settings wrong => > 800 disk i/o per second. –  Lukas Jul 11 '13 at 7:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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