max_user_connections error again and again. Does q2a close connections?

Question

I am still not rid of this error, see also http://www.question2answer.org/qa/18513/already-max_user_connections-active-connections-include

PHP Warning:  mysqli::mysqli(): (HY000/1203): User db348277_6 already has more than 'max_user_connections' active connections in /qa-include/qa-db.php on line 66

In the net I read:

"Your code is opening the connection to the database, but not closing it. Please see the following link for more help with properly opening and closing your database connections"

Could it be that q2a does not close the connections?

- - -

Note, I have no root access and cannot change the value of "max_user_connections". It is set to 35. This I found out by: "SHOW GLOBAL VARIABLES LIKE '%connections%'

- - -

My provider informed me that they will not increase this value on shared hosting, only on managed server which would cost me 100+ Euros :(

So I wonder if the "hack solution" on stackoverflow: http://stackoverflow.com/q/11183727/1066234 could be one way for us: Disable this error warning by @mysql_connect() ?

Comments

There are some cheaper VPS. Generally, the support is not very good and are not managed, but it might work for you.
I have a VPS in Contabo for my Q2A site.
Currently there are some problems on my site, but I've had far more access than currently and worked well.
https://contabo.com/?show=vps

Answer 1

MySQL connections are automatically closed as soon as the script is finished, so you don't need to close them explicitly. However Q2A does close it anyway, just to make sure it's done at the earliest opportunity. See the bottom of qa-page.php.

Check your plugins are not making too many SQL queries. The more you are making, the longer the connection stays open and is not available for others.

Make sure you have QA_PERSISTENT_CONN_DB set to false in your qa-config.php. See my answer here for a few more tips. As I mention there we have some efficiency improvements coming in v1.8 so maybe that will help.

Otherwise... if you have a lot of traffic then there's not really much that can be done on shared hosting. Perhaps try shopping around for cheaper VPS hosting. There are also hosts like Linode and Digital Ocean where you can get great servers for only $5-10/month, but they are unmanaged and you need to install everything yourself.

Comments

Scott, how do I know how many SQL queries each plugin has done or how many resources have consumed my server?

---

If you have the site set up on a test server (e.g. localhost) then set QA_DEBUG_PERFORMANCE to true in qa-config.php. Then at the bottom of the page it will list all the queries. It will also give you some timing stats so you can see what part is taking the most time.

---

Thank you, Scott!
Now I will be able to figure out which plugin is causing the problem on my server!

---

I can make this change in my live online site?

---

I wouldn't recommend it because then all your users can see the debug information and the SQL queries.

If you don't have a test server one option would be to upload a second copy of Q2A to another folder. Then in that second copy set debug to true. (Remember that if you're using the same MySQL details, any posts you make or options you change here will also change on the main site.)

---

Thanks for the info Scott. I fine-tuned all plugins, I would say. The mysql total query time is only 11 ms (21 queries).

Here is a screenshot: http://i.imgur.com/4FvPrvp.png

So I guess I am quite good with that :)

---

PS: It would be nice to have QA_DEBUG_PERFORMANCE enabled and display the debug information only for admin. So you can test it on the live site!

---

By the way @q2apro, I noticed in your notification plugin that you do some queries inside a loop: https://github.com/q2apro/q2apro-on-site-notifications/blob/master/q2apro-onsitenotifications-page.php#L182

That's usually a bad idea. Instead you should loop through and build an array of IDs, then use a "WHERE ... IN ..." clause with those IDs.

---

Of course, there is space for improvement :) Feel free to change the code as you like.