I've had a couple of max_user_connections over the last few weeks. My old value was set to 50 and I could get upwards of 100+ errors per day.
My new value is 100 and this has reduced to 0-5 per day.
I'm trying to find the sweet spot but unable to determine what exactly it should be. Is there specific tests or checks we can run to determine the value needed?
Here is an example error from this morning:
(HY000/1226): User 'y12345_yugipub' has exceeded the 'max_user_connections' resource (current value: 100)
This user account is used on multiple scripts across the site. It's used for my APIs along with my internal connection checks.
Some site stats:
- 35k users per day
- 250k pageviews per day
- Server side PHP caching using PHPFastCache. This reduces the amount of DB requests. However when the cache is cleared it can cause spikes.
MySQL Traffic/Connections:
I'm trying to give as much info as possible here as I don't know exactly what is needed to determine a correct value. My theories:
- Use separate MySQL accounts for API? From what I gather, the max_user_connections is account based?
- Simply increase the limit until errors stop?
- Revise cache clearing to try avoid spikes
EDIT: According to this article, possibly using different accounts will solve the issue. I think I'll probably set my public API to use a different account than my internal APIs as currently most all my scripts are using the one account.
From the article:
There is a better solution: use different user accounts for different scripts and applications and implement resource limiting for them.
EDIT 5 Days Later: It seems separating the API into it's own MySQL DB account has resolved the issue as I have received 0 of these errors since. However I would still be open to anyone who has a good way of theory-crafting what the optimal value would be.
