|
lhammond
|
May 21, 2015 at 5:30 am
Apparently our hosting provider limits our mysql connections to 15 concurrent connections, and when we open up registration for our classes, we get more than 15 people at a time, and then nobody can access the site because our database connections are maxed out. It seems to me that if the plugin is pooling database connections properly, it should be able to make the request, release the connection, and let somebody else use it. Have you ever had troubles with EE3 not releasing database connections for use by others?
I am wondering if in my custom templates if I am not releasing the resources properly? We have a lot of angry customers now! Help!
|
|
Dean
|
May 21, 2015 at 5:54 am
Hi,
I haven’t heard of any issues with database connections before.
The number your host is quoting is fairly low, even Hostgator allow 25 connections on a shared server.
There should be nothing that keeps a database connection open. What normally happens is that the code will make a request to connect, connect and get the data and then close the connection. It will do this multiple time per visitor depending on what is requesting data and how often.
As Hostgator put it “A forum with 500 active members will rarely hit this 25 simultaneous connections.”
If you are expecting a lot of visitors, then you should consider upgrading your hosting to a plan with more concurrent database connections.
Reference: http://support.hostgator.com/articles/pre-sales-policies/rules-terms-of-service/mysql-connection-limits-processor-limits
P.S. I’m not recommending Hostgator, just using them as an example.
|
|
lhammond
|
May 21, 2015 at 8:41 am
Okay, so I suspect something is wrong in my code, then. We had to shut down our site and delay registration, because we hit the max database connections and then couldn’t even use WordPress admin. Now , while the site is in maintenance mode, if I run the mysqladmin command to view connected processes, I don’t see any (except my call to show the processes), but the open threads, tables, and slow queries are WAY high!
user@host:~$ mysqladmin -v -uroot -ppass proc stat
Uptime: 555498 Threads: 22 Questions: 741172472 Slow queries: 78632 Opens: 2359621 Flush tables: 1 Open tables: 4096 Queries per second avg: 1334.248
Any idea what that command returns on other EE sites? Or how I can improve it?
|
|
Josh
|
May 21, 2015 at 2:19 pm
If you suspect there’s something in your code that’s keeping connections open, I can suggest examining the code for native PHP mysql (or mysqli) statements. If you find any, you can replace them using methods from the $wpdb class. Anything using $wpdb should not be opening separate db connections.
Here’s a resource I found helpful with using the $wpdb class:
http://www.smashingmagazine.com/2011/09/21/interacting-with-the-wordpress-database/
|
|
lhammond
|
May 22, 2015 at 11:13 am
I don’t have any native mysql calls. The problem is definitely in the espresso_table.php page – it was doing my big nasty join at the top to get all the events, but then also calling a bunch of shortcode functions that each make separate dB calls. It was making 5+ calls per event, so we had 700+ queries on one page! So I changed it so that I’m not calling shortcodes and only using my big query, except for the add to cart link function. Query count down to about 120. That improved our number of simulataneous database connections dramatically. So I still think that somehow some of those other queries are/were getting separate dB connections.
Now we’re having other troubles… no confirmation emails going out. We just moved to AWS…
|
|
Lorenzo Orlando Caum
|
May 22, 2015 at 11:22 am
Hello, I’m not familiar with running sites on AWS but if its similar to a VPS, then it may not an email service running.
Do regular WP emails work? If not, then try Mandrill or Postmark app:
https://eventespresso.com/wiki/postmark-app-mandrill-transactional-email-handling-services/
—
Lorenzo
|
|
lhammond
|
May 22, 2015 at 12:52 pm
Now some of the emails got through, I think they’re queued up somewhere and having trouble because our mail accounts are still on hostmonster. Looking into it…
What do you make of the excessive database connections from making a lot of queries? Have you ever heard of that problem from other EE users?
|
|
Lorenzo Orlando Caum
|
May 22, 2015 at 12:58 pm
Hi, I’m not sure what is causing that. Does the same happen when the custom templates are deactivated?
—
Lorenzo
|
|
lhammond
|
May 26, 2015 at 7:15 am
I haven’t had a chance to try it with custom templates deactivated, but we did some load testing, and quickly overran our server because each refresh of the browser tab established a new database connection, which hangs around for some reason. I had 10 tabs open, refreshed each window every 5 seconds, and within a few minutes, our dB connections were up to 250! Those connections were still showing as active pipes into the database server even after I completely closed the browsers I was using. Since we have moved to our own virtual server, we have now configured mySQL to kill inactive connections after 3 minutes, instead of the 8 hour default(!). That seems to help with the database connection management, but I still don’t get why those connections weren’t getting closed. I am hoping it was just an unrealistic scenario, and that it can be explained by the browsers were getting hung up because we had so many refreshing so fast that they didn’t close properly. If real users get their page loaded and the script terminates correctly, it should release the dB connection properly.
|
|
Dean
|
May 27, 2015 at 3:27 am
Hi,
Have you run any tests with the default (none modified) EE running?
We haven’t come across this before with other users, and I suspect it would be an obvious problem as many of our users have high volume sites.
Please keep us informed if the issue re-occurs.
|
|
lhammond
|
May 30, 2015 at 7:26 am
Do you know what kind of settings the high-volume sites use? For things like Apache threads, PHP threads, wait time-outs, time to live on mySQL connections?
Unfortunately, I don’t have a dev site right now that I can use to test it without our customizations because we moved servers in a hurry, so I can’t get that answer until we can get that set up again.
|
|
Dean
|
June 1, 2015 at 5:00 am
Hi,
“Do you know what kind of settings the high-volume sites use? For things like Apache threads, PHP threads, wait time-outs, time to live on mySQL connections?”
Unfortunately not, each site is likely to be using a varying settings dependant upon host and server type and it’s also not something the majority of our customers would even know without specifically asking their hosts.
|
