I’m building a website in WordPress, and during this week it started crashing with the Error establishing a database connection
message. I created a test archive that only connects to the DB and it tells me the error is “too many connections”
I’m pretty sure that I’m the only one that visits the site (I’m still developing it, and the apache logs don’t show more visits), the WordPress Backup to Dropbox plugin is the only one I think could be using the database a lot, but still I don’t think I should see that error.
The output of “show processlist” only shows me the “show processlist” query.
And the output of “show status like ‘%con’” is this:
+----------------------------------------+--------+
| Variable_name | Value |
+----------------------------------------+--------+
| Aborted_connects | 41002 |
| Com_show_contributors | 0 |
| Connections | 337541 |
| Max_used_connections | 152 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 151 |
+----------------------------------------+--------+
threads connected seems to be always at maximum
What can I do? Is this a server issue?
Edit: As @Giacomo1968 answer suggests I ran a the MySQL Tuning Primer Script, but it died with the too many connections error, the output was as follows:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.36-cll x86_64
Uptime = 0 days 0 hrs 56 min 33 sec
Avg. qps = 59
Total Questions = 200437
Threads Connected = 151
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 4127 out of 200644 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 4
Historic threads_per_sec = 2
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 150
Historic max_used_connections = 152
The number of used connections is 100% of the configured maximum.
You should raise max_connections
INNODB STATUS
Current InnoDB index space = 448 K
Current InnoDB data space = 3 M
Current InnoDB buffer pool free = 82 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
ERROR 1040 (08004): Too many connections
I had this happen in the past. It is possible there are many sites sharing the same server/database using pconnect. Especially on shared hosting solutions it is better to use standard connect. So instead of
mysql_pconnect()
change to usingmysql_connect()
.Is this a dedicated server? Or a shared host? Or even a shared DB pool?
While my gut says this is not a simple performance tuning issue, I would still recommend using the MySQL Tuning Primer Script located here to see if it reveals anything odd about your setup. Very easy to use & the recommendations are pretty spot on.
Depending on your setup you might need to learn how to performance tune by handâmeaning you learn to interpret MySQL output yourself and act on itâbut this script works quite well for 95% of the setups I have ever used them on. The other 5% are database idiosyncratic setups that required more custom care. I highly recommend tutorials like this one on the MySQL performance blog.
Also, this site has a nice compilation of things you can do to remedy issues when connecting to a database in WordPress specifically.