title: How to determine appropriate max_user_connections in MySQL?
body: <p>I've had a couple of <code>max_user_connections</code> over the last few weeks. My old value was set to <code>50</code> and I could get upwards of 100+ errors per day. 
  My new value is <code>100</code> and this has reduced to 0-5 per day. </p>
  
  <p>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?</p>
  
  <p>Here is an example error from this morning:</p>
  
  <pre><code>(HY000/1226): User 'y12345_yugipub' has exceeded the 'max_user_connections' resource (current value: 100)
  </code></pre>
  
  <p>This user account is used on multiple scripts across the site. It's used for my APIs along with my internal connection checks. </p>
  
  <p>Some site stats:</p>
  
  <ul>
  <li>35k users per day</li>
  <li>250k pageviews per day</li>
  <li>Server side PHP caching using PHPFastCache. This reduces the amount of DB requests. However when the cache is cleared it can cause spikes.</li>
  </ul>
  
  <p>MySQL Traffic/Connections:
  <a href="https://i.stack.imgur.com/xk0JZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xk0JZ.png" alt="enter image description here"></a></p>
  
  <p>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:</p>
  
  <ul>
  <li>Use separate MySQL accounts for API? From what I gather, the max_user_connections is account based?</li>
  <li>Simply increase the limit until errors stop?</li>
  <li>Revise cache clearing to try avoid spikes</li>
  </ul>
  
  <p>EDIT: According to <a href="https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/" rel="nofollow noreferrer">this</a> 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. </p>
  
  <p>From the article: </p>
  
  <blockquote>
    <p>There is a better solution: use different user accounts for different
    scripts and applications and implement resource limiting for them.</p>
  </blockquote>
  
  <p>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. </p>