max_user_connections (30) exceeded messages

[Mr. Jinx]

Lately I'm getting these max_user_connections exceeded messages from MySQL.

I asked my hosting provider if they could raise the limit.
However, they told me that only in very rare situations you would need 30+ connections.
Even for extremely busy sites, 30 should be more than enough.
Most times, this error means something is going wrong.

They had a few suggestions:

• Make more use of Redis cache. Currently only 2 MB is used from the 256 MB cache available.
  Is 2 MB usage normal for XF? Using all of Xon's Redis optimization add-ons.
• Check for MyISAM tables and convert to InnoDB. I think this is already the case.
• Check for long queries. I'm not sure how to do this. Can I check this for a certain time, when the error happens?

Any other suggestions?

 

[Luehrsen]

Yes. The very rare case is that you seem to have a moderately successful forum.
I have the very same issue at the moment with my hoster and they are less than willing to do anything about it.
Thats why I am moving to another Hoster very soon.

 

[arn]

How many active users do you have at peak?

 

[Kirby]

Either you have bad/long-running queries, a badly configured MySQL server, a high amount of traffic or some configuration error on XenForo (broken resource URLs causing images that should be resources like images, CSS, JS, etc. to be handled by XF).

30 really should be enough for most forums.

 

[Luehrsen]

How many active users do you have at peak?

Around 100-150 active users at peak (Xenforo) on an image heavy board with a lot of proxied images.

(Edit: And yes, I have a CF page rule for proxy.php...)

Edit2: To elaborate a bit more, it is old, image heavy threads like these that trigger the 30 connections warning. And I have a lot of those in my archive and they tend to get some long tail google clicks from time to time. And of course CF can't have a cached asset when the hit in question is the first hit in weeks, if not months.

 

[arn]

I mean that seems plausible. Each image is going to be triggering a xenforo script and looks like over 60 proxy includes on that one page alone.

A work-around would be to use this lazy loading plugin: https://xenforo.com/community/resources/lazy-load-img.6057/ -- then all the images won't load at once -- which is a good idea anyway.

arn

 

[Luehrsen]

which is a good idea anyway

Definitely. I really happy to see that it will be a part of XF2.2 core.

I'm currently dipping my toes into the paid stuff CF is offering (still far away from the Business Plan), and I'm thinking about workers. Do you think it is reasonable to replace proxy.php with a cloudflare worker alternative?

 

[arn]

Definitely. I really happy to see that it will be a part of XF2.2 core.

I'm currently dipping my toes into the paid stuff CF is offering (still far away from the Business Plan), and I'm thinking about workers. Do you think it is reasonable to replace proxy.php with a cloudflare worker alternative?

I’m not sure they are mutually exclusive. What are you thinking of doing?

I proxy.php our images and use cloudflare to cache them.

 

[Luehrsen]

I proxy.php our images and use cloudflare to cache them.

Yeah, I'm also already piping proxy.php through cloudflare, but I'm thinking about totally replacing the proxy service with a cloudflare worker. Meaning that the proxy service is actually never touching XF, so it doesn't generate a database hit when uncached.

 

[arn]

we use workers for some minor other things -- but I'm not sure exactly how you could replace proxy.php with a worker.

You might be able to extend the cache-time for Cloudflare via workers beyond 30 days somehow with a worker.

arn

 

[Luehrsen]

how you could replace proxy.php with a worker

Replacing the functionality completely is certainly impossible or not worth it, especially the logging part. But for my jurisdiction the main purpose of the worker is to ensure a secure connection to the asset and to not implicitly leak data to a third party. (GDPR) So if only that is important to me I think it could actually live in a worker.

I'm just not sure if it is worth the effort (and cost) and if I'm missing some key aspect....

 

[Mr. Jinx]

In my case, the max_user_connections error seem to occur when a topic is visited with lots of images attached.
There is one topic with 54 images. I cannot reproduce the error when visiting this specific topic, but when the error happens it most times reports this topic as the problem.

 

[Mr. Jinx]

Still haven't found a solution for this. It seems to happen every now and then, espacalliy when someone visits a topic with lots of images attached.

Server error log

• XF\Db\Exception: User 'xxxxxxx' has exceeded the 'max_user_connections' resource (current value: 30)
• src/XF/Db/Mysqli/Adapter.php:165
• Generated by: Unknown account
• Sep 7, 2020 at 9:45 PM

Stack trace​

#0 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)
#1 src/XF/Db/AbstractAdapter.php(60): XF\Db\Mysqli\Adapter->getConnection()
#2 src/XF/Db/AbstractAdapter.php(83): XF\Db\AbstractAdapter->connect()
#3 src/XF/Db/AbstractAdapter.php(150): XF\Db\AbstractAdapter->query('
SELECT cach...', 1)
#4 src/XF/PermissionCache.php(32): XF\Db\AbstractAdapter->fetchOne('
SELECT cach...', 1)
#5 src/XF/PermissionSet.php(35): XF\PermissionCache->getGlobalPerms(1)
#6 src/XF/Entity/User.php(926): XF\PermissionSet->hasGlobalPermission('general', 'view')
#7 src/XF/Pub/Controller/AbstractController.php(241): XF\Entity\User->hasPermission('general', 'view')
#8 src/XF/Pub/Controller/AbstractController.php(20): XF\Pub\Controller\AbstractController->assertViewingPermissions('Index')
#9 src/XF/Mvc/Controller.php(123): XF\Pub\Controller\AbstractController->preDispatchType('Index', Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(349): XF\Mvc\Controller->preDispatch('Index', Object(XF\Mvc\ParameterBag))
#11 src/XF/Mvc/Dispatcher.php(257): XF\Mvc\Dispatcher->dispatchClass('XF:Attachment', 'Index', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Attachment), NULL)
#12 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Attachment), NULL)
#13 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#14 src/XF/App.php(2190): XF\Mvc\Dispatcher->run()
#15 src/XF.php(391): XF\App->run()
#16 index.php(20): XF::runApp('XF\\Pub\\App')
#17 {main}

 

[Masetrix]

You have to configure more connections on /etc/my.cnf config file on the server.
Always exercise caution because values that are too high can prevent the mysqlserver from starting.
(Value of 0 means unlimited connections)

max_user_connections = 60
save and
restart your MySql Server
restart your Apache Server

Or temporary on MySql-Commandline:
SET GLOBAL max_user_connections = 60;

then restart your Apache Server.

On newer LinuxSystems (systemd) check this:

How to change the max_user_connections value in MySQL/MariaDB on a Plesk server

Applicable to: Plesk for Linux Plesk for Windows Question How to adjust the max_user_connections value in MySQL/MariaDB on a Plesk server? Answer For Plesk on Linux Connect to a Plesk se...

support.plesk.com

MySQL values open_files_limit and max_connections are not applied

Applicable to: Plesk for Linux Symptoms The value of max_connections changes to 214 automatically after some time, although the value itself is defined in my.cnf: # grep -i 'max_connections' /...

support.plesk.com

 

[Overscan]

I doubt they have access to do this from the info in the post - presumably on shared hosting.

The "multiple external images in a topic each calling proxy.php" seems to be a likely cause of this error. I host all images locally in Xenforo attachments so it wouldn't apply to my site. Its possibly a kind of an architecture issue with the proxy.php solution. A reasonably busy website at work I manage rarely exceeds 20 connections to the MySQL database and usually sits around 5.

@Mr. Jinx Presume these are extenally hosted images?

 

[Mr. Jinx]

That's right, I can't raise the max_user_connections but as stated earlier, it should not be that high anyway for a regular site:

I asked my hosting provider if they could raise the limit.
However, they told me that only in very rare situations you would need 30+ connections.
Even for extremely busy sites, 30 should be more than enough.
Most times, this error means something is going wrong.

Probably some long running queries.

ps: I'm also hosting images locally.

 

[Masetrix]

That's right, I can't raise the max_user_connections but as stated earlier, it should not be that high anyway for a regular site:

As you yourself experience, this value is too low in some cases. For example, the value for us is 90.

 

[Walter]

Even for extremely busy sites, 30 should be more than enough.

That simply isn't true.

 

[Mr. Jinx]

Then again, my site isn't that busy that it would ever need so much connections.
It is like a bad written app that suddenly consumes 100% CPU. You can add more and more CPU's but it won't solve the underlying problem.
People that just advise to raise this value don't understand how things work, sorry.

 

[Masetrix]

Then again, my site isn't that busy that it would ever need so much connections.
It is like a bad written app that suddenly consumes 100% CPU. You can add more and more CPU's but it won't solve the underlying problem.
People that just advise to raise this value don't understand how things work, sorry.

But there was no mention of that in your request.

max_user_connections (30) exceeded messages

Lately I'm getting these max_user_connections exceeded messages from MySQL. I asked my hosting provider if they could raise the limit. However, they told me that only in very rare situations you would need 30+ connections. Even for extremely busy sites, 30 should be more than enough. Most...

xenforo.com

We are not clairvoyant and have to give help based on the information you provide here.
These lines
"XF\Db\Exception: User 'xxxxxxx' has exceeded the 'max_user_connections' resource (current value: 30"
and:
"#0 src/XF/Db/Mysqli/Adapter.php(28): XF\Db\Mysqli\Adapter->makeConnection(Array)"
show that more connections are requested here than permitted.

Furthermore, we cannot know that you are using faulty add-ons, you have not reported that, and therefore assume that only one value has to be increased here that is set too low. So please don't imply that someone doesn't know what they're talking about.