Ok so I've been having issues with max_user_connections errors on my dedicated server recently. The setting is set to 100 so it should be high enough. When this occurs and I take a look at htop I see alot of requests just chilling there...
Also when I run mysqltuner I get:
>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.6.17
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 811M (Tables: 382)
[--] Data in InnoDB tables: 2G (Tables: 672)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 372K (Tables: 9)
[!!] Total fragmented tables: 489
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 23h 0m 13s (194M q [150.816 qps], 11M conn, TX: 2686B, RX: 46B)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 1.1G global + 1.1M per thread (175 max threads)
[OK] Maximum possible memory usage: 1.3G (34% of installed RAM)
[OK] Slow queries: 0% (62K/194M)
[!!] Highest connection usage: 100% (176/175)
[OK] Key buffer size / total MyISAM indexes: 32.0M/382.3M
[OK] Key buffer hit rate: 99.6% (3B cached / 16M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (76K temp sorts / 39M sorts)
[!!] Joins performed without indexes: 69591
[OK] Temporary tables created on disk: 7% (567K on disk / 7M total)
[OK] Thread cache hit rate: 97% (246K created / 11M connections)
[!!] Table cache hit rate: 0% (768 open / 192K opened)
[OK] Open file limit used: 2% (389/16K)
[OK] Table locks acquired immediately: 99% (183M immediate / 184M locks)
[!!] InnoDB buffer pool / data size: 512.0M/2.1G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
max_connections (> 175)
wait_timeout (< 10)
interactive_timeout (< 10)
query_cache_type (=1)
join_buffer_size (> 256.0K, or always use indexes with joins)
table_cache (> 768)
innodb_buffer_pool_size (>= 2G)
In specific it states to "Reduce or eliminate persistent connections to reduce connection usage" under "General Recommendations".
I also read a comment here:
http://dev.mysql.com/doc/refman/5.5/en/t...tions.html
about turning off persistent connections if your having too_many_connections errors.
So I looked up mysql connections and found out I can turn it off in php.ini. What exactly is mysql persistent connections and is it safe to turn off?
A persistent connection remains open in case the application need to request more work. What is probably happening is you have 100 connections remaining open that don't get closed fast enough, and you're hitting your limit. It's always advisable to not use persistent connections. I have never found them useful in any application. Alternatively, if you have the resources for it, you could leave the enabled and increase your max connections to something like 500.
It is perfectly safe, and recommended, to turn it off
Great thanks spork!
Also, in the too_many_connections thread:
http://dev.mysql.com/doc/refman/5.5/en/t...tions.html
They also mention lowering connect_timeout:
Quote:Another symptom for PHP users, the "max_connections" error being returned to the browsers and the "show processlist" filling up with sleeping threads: This can sometimes be alleviated by using the PHP .htaccess option to lower the connect timeout from the default 60 seconds.
php_value mysql.connect_timeout 20
is it recommended to do this aswell?
I wouldn't recommend using persistent connections at all.
(2014-08-07, 03:32 AM)spork985 Wrote: [ -> ]I wouldn't recommend using persistent connections at all.
Right, I've turned off mysql persistent connections, but in the too_many_connections thread:
http://dev.mysql.com/doc/refman/5.5/en/t...tions.html
It also mentions lowering connect_timeout:
Quote: Another symptom for PHP users, the "max_connections" error being returned to the browsers and the "show processlist" filling up with sleeping threads: This can sometimes be alleviated by using the PHP .htaccess option to lower the connect timeout from the default 60 seconds.
php_value mysql.connect_timeout 20
is it recommended to do this aswell?
I would recommend it. A process should not take 20 seconds in MySQL to finish.
(2014-08-07, 08:10 AM)dragonexpert Wrote: [ -> ]I would recommend it. A process should not take 20 seconds in MySQL to finish.
That isn't how persistent connections work. They remain open even after the script execution completes.
Quote:Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. If it does not exist, it creates the link. An 'identical' connection is a connection that was opened to the same host, with the same username and the same password (where applicable).
(2014-08-07, 01:50 PM)spork985 Wrote: [ -> ]That isn't how persistent connections work. They remain open even after the script execution completes.
The connect timeout operates regardless of whether persistent connections are enabled or not.
With persistence, you'll have fewer connections, but they still need to be done at some point.
If it's a localhost MySQL server, the connect timeout is unlikely to solve anything. It's hard to guess your problem as I don't know what applications you're running or what your traffic is like, but seeing as you've got a fair amount of RAM available, you could probably just simply increase your connection count.
But there could be other underlying issues here, such as slow queries or lock contention which may cause queries to backlog and hence your number of concurrent connections to spike.
(2014-08-08, 07:14 AM)Yumi Wrote: [ -> ] (2014-08-07, 01:50 PM)spork985 Wrote: [ -> ]That isn't how persistent connections work. They remain open even after the script execution completes.
The connect timeout operates regardless of whether persistent connections are enabled or not.
With persistence, you'll have fewer connections, but they still need to be done at some point.
I'm aware of that. They shouldn't be hitting the timeout period at all. When the PHP script completes, the connection is closed automatically. Instead of setting a 20 second timeout on his MySQL connections, he should be figuring out why the PHP script is taking 20 seconds to execute in the first place.
Is it at all possible this is due to recent myBB upgrade? I never had this issue... And there are only a couple of correlational changes... The main one being upgrading to 1.6.14. Now entry processes and CPU and RAM spiking and getting max connection error...
In the past i would have up to 400 users on at once after a newsletter sent out, and no problems at all. Usage so low as to be nearly nonexistent...Now 100 on and getting these spikes....
Sorry for hijacking man. I'm just trying to figure out why its happening in the first place... Talking to hosting is akin to talking to a brick wall... They are recommending caching dynamic content...
