MySQL max_user_connections exceeded?

[DZComposer]

Several times this week I've had to restart MySQL on my server because the user for IPS 4 maxes-out on connections. I did not have this problem with IPS 3, and it has only been happening since 4.0.2.

Is anyone else having this issue?

EDIT: Also, is there a recommended value for this setting in MySQL?

[AutoItScript]

How high you should set your connections is mostly memory related. There's no point raising it if you don't have the memory to support that number of connections. I've got a pretty busy board at 1.2 million posts and rarely needed >30 connections on IPB 3.

This script will tell you how much your "max" memory usage is so you can use it to tweak your max_connections value. http://mysqltuner.com

As for IPB 4. I just upgraded on my live site and I had to disable "Activity Stream" it was causing some really long running queries. Which in turn locked many tables. So that means that new connections are made because other connections are waiting for table locks. It may be that your max connections is fine - just try and disable that first. Myself and others have logged bugs about this, but no replies yet.

Your mySQL slow query log (/var/log/mysqld.slow.log on my server, check your my.cnf for the location) - will list long running queries which is how I found the Activity Stream query.

 

[DZComposer]

My max_user_connections is set to 300, server has 2GB RAM, but also runs the webserver. OS is Debian with no GUI. I can't run that script as I do not have root on MySQL.

Significant slow queries seem to be related to Permissions.

I think I'm going to be submitting a ticket. It shouldn't lock a table for 170+ seconds to run a permissions query.

[AutoItScript]

You've still got Activity Stream turned on. I didn't, but I imagine if someone clicks Activity Stream -> Posts that your site will grind to a halt. You can disable it from ACP -> System -> Applications -> System ->Activity Stream.  (Or you could just remove it from Guests in the same place).

Assuming you've got the lowest possible defaults on your mysql server (no doubt they are higher) then this is how you calculate how much total memory your mysql can use:

Total memory that could be used = key_buffer_size + tmp_table_size + ((per thread mem)*max_connections)
Per connection memory = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size

Defaults:
sort_buffer_size=2M
read_buffer_size=128K
read_rnd_buffer_size=256K
join_buffer_size=128K

And I'll assume super low values for:

key_buffer_size = 128M
tmp_table_size = 32M

So let's say you ever did have 300 connections, your server would try and use:

Per connection: 2048KB + 128 +256 + 128 = 2.5M
Total for 300 connections: 128M + 32M + (300 x 1536KB) = 910M

You can get your actual values from phpmyadmin if you have that installed and work out your exact memory usage. If you are running a webserver as well I'd probably not go above half your ram.

 

[RevengeFNF]

That value for Sort Buffer Size is too high and the Join Buffer Size is too low.

[AutoItScript]

I'm just saying, those are the defaults. I have no information on his actual configuration.