0

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?

2
  • Slave replication and have multiple database servers? Commented Jul 10, 2013 at 18:35
  • just one server 8G RAM ;) Commented Jul 11, 2013 at 7:13

1 Answer 1

2

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.

1
  • i guess that's it. We seem to have some MySQL settings wrong => > 800 disk i/o per second. Commented Jul 11, 2013 at 7:14

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.