MySQL Connection are to high?

[PixyPumpkin]

Last week I received two error messages on MySQL that there where to many SQL connections. I run CentOS 3 with Plesk 7.5.4. The logfiles only show the nomal MySQL traffic nothing uncommon. What are probable causes on this high number of connections that makes MySQL temporarily not available? How can I solve this? Raise the number of possible connections? Run more servers? Is there software to monitor or analyse MySQL and SQL Connections more closely?

Thx in advance!

 

[PixyPumpkin]

Where can I raise the MySQL GLOBAL max_connections in Plesk? It seems that the setting in the /etc/my.cnf is overruled by Plesk?

 

[breun]

Setting it in /etc/my.cnf should work just fine.

 

[PixyPumpkin]

It does not, plesk is overruling this setting somehow

 

[breun]

What did you do exactly? SWsoft also says to edit /etc/my.cnf in their knowledge base. Just add set-variable=max_connections=500 in the [mysqld] section and restart mysqld.

 

[PixyPumpkin]

The setting in ect/my.cnf is on 500 but still MySQL say's 100

 

[breun]

Did you literally set set-variable=max_connections=500 under [mysqld] in /etc/my.cnf and did you restart mysqld afterwards? Where do you see it's 100?

 

[PixyPumpkin]

If I login to MySQL and ask show variables it show me the GLOBAL max_connections and tells me it is 100, I can put it on 200 here but after a reboot it will be back on 100 I found some other topics on this, I have to analyse them still.

 

[breun]

When I change /etc/my.cnf like I described in my previous post, restart mysqld and then login and run show variables I see max_connections is set to 500. No problems here, works just fine.

 

[wagnerch]

I wouldn't just go in and bump max connections, increasing this value too high will cause your server to start swapping due to the memory requires *each* client requires.

I have worked on domains that are doing 50 qps where the person did the same thing and brought his entire domain to a halt, and if you properly tune all of your queries you can live with 128 connections without a single issue. After tuning it was doing 100~150 qps.

What I would recommend is setting thread_cache_size to 100, this will reduce a significant amount of system CPU time due to creation/destruction of threads -- which ultimately slows down how many connections can come & go because they have to wait for their thread to be created.

In general I would suspect your issue is related to the queries not having proper indexes, or simply the wrong queries (cartesian products, improper schema design, etc).

I have worked on quite a few servers and rarely found max_connections to be a solution, but more of a problem. As the end result is thrashing due to excessive swapping.

 

[PixyPumpkin]

Thx! wagnerch