Mysql Connection Lost

103 Posts

scitekmike Reply #1, 18 years ago

We’ve recently have been getting mysql lost connection errors while trying to save documents. The hosting platform is Mediatemple’s Grid Service. We just recently started to see this happen - never before in the last 1.5yrs of hosting with them and with about 20 modx driven sites.

Any idea what is causing this? Support seems to think that modx is opening up too many connections to mysql dbs and exceeding the limit on the Grid Service. I don’t really buy that but figured I might post here and see if anyone else is experiencing this and/or if anyone has a solution.

MODX Staff
10,725 Posts

opengeek Reply #2, 18 years ago

What kind of connection limit it is applied? i.e. How many connections to the db can you have?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

103 Posts

scitekmike Reply #3, 18 years ago

Quote from: OpenGeek at Oct 13, 2008, 07:19 PM

What kind of connection limit it is applied? i.e. How many connections to the db can you have?

I’m not certain - I will have to ask the support staff what their limit is.

btw here is the error trying to save:

error replacing into active users! SQL: REPLACE INTO `db42365_bstakModx`.`modx_active_users` (internalKey, username, lasthit, action, id, ip) VALUES (1, 'admin', '1223904798', '5', '11', '64.81.97.2') Lost connection to MySQL server during query

103 Posts

scitekmike Reply #4, 18 years ago

Quote from: OpenGeek at Oct 13, 2008, 07:19 PM

What kind of connection limit it is applied? i.e. How many connections to the db can you have?

"The max concurrent connection limit is set to 60, per (gs) Grid Service. I double checked with our System Administrator for confirmation. "

MODX Staff
10,725 Posts

opengeek Reply #5, 18 years ago

Alright, so if you have multiple sites on the service and you have 50 users hitting a front-end page on one of those sites, and 1 or 2 users hitting a manager page at the same time, you are going to run out of simultaneous connections. Now, what kind of traffic level are you getting? Is that kind of load possible?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

103 Posts

scitekmike Reply #6, 18 years ago

Quote from: OpenGeek at Oct 14, 2008, 09:26 AM

Alright, so if you have multiple sites on the service and you have 50 users hitting a front-end page on one of those sites, and 1 or 2 users hitting a manager page at the same time, you are going to run out of simultaneous connections. Now, what kind of traffic level are you getting? Is that kind of load possible?

We’re getting that type of load easily if the 60 connections is for all sites hosted which amounts to 15-20 sites most of which are modx installs. If the 60 connections are per site or db - then I don’t think it is likely. Now, that is with one of the Grid Service accounts. With another account, there is only one site and 2 db’s hosted under it and we’re seeing this connection issue.

MODX Staff
10,725 Posts

opengeek Reply #7, 18 years ago

Quote from: scitekmike at Oct 14, 2008, 10:30 AM

We’re getting that type of load easily if the 60 connections is for all sites hosted which amounts to 15-20 sites most of which are modx installs. If the 60 connections are per site or db - then I don’t think it is likely. Now, that is with one of the Grid Service accounts.

Those limits are almost definitely per account. If you have 15-20 sites, I can easily see that becoming a problem.

Quote from: scitekmike at Oct 14, 2008, 10:30 AM

With another account, there is only one site and 2 db’s hosted under it and we’re seeing this connection issue.

2 db’s? I assume one is MODx and the other is custom?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

103 Posts

scitekmike Reply #8, 18 years ago

Quote from: OpenGeek at Oct 14, 2008, 11:26 AM

Quote from: scitekmike at Oct 14, 2008, 10:30 AM

We’re getting that type of load easily if the 60 connections is for all sites hosted which amounts to 15-20 sites most of which are modx installs. If the 60 connections are per site or db - then I don’t think it is likely. Now, that is with one of the Grid Service accounts.

Those limits are almost definitely per account. If you have 15-20 sites, I can easily see that becoming a problem.

Quote from: scitekmike at Oct 14, 2008, 10:30 AM

With another account, there is only one site and 2 db’s hosted under it and we’re seeing this connection issue.

2 db’s? I assume one is MODx and the other is custom?

Yes, I’ve setup on for the site - MODx and then there is another for an ecommerce system, Cube Cart. However, there is maybe at most 5 people looking at this dev site right now as it’s not live and even if it was live - they don’t have nearly enough traffic to cause this problem.

How many connections does MODx open up say when saving documents?

103 Posts

scitekmike Reply #9, 18 years ago

Latest comment back from the support folks:

"As Ian noted, the connection limit is just one variable and may not be the cause of your issue. The query using the modx_active_users table may be improperly indexed or possibly uses multiple joins to execute the statement. This could also cause the connection error. I would certainly advise posting the error to the developers of Modx to find out what your options are. You can also get some better troubleshooting tools with a MySQL container as well."

Any ideas?

This site has the latest version installed 0.9.6.2

103 Posts

scitekmike Reply #10, 18 years ago

Well here is the latest from support:

"As Ian noted, the connection limit is just one variable and may not be the cause of your issue. The query using the modx_active_users table may be improperly indexed or possibly uses multiple joins to execute the statement. This could also cause the connection error. I would certainly advise posting the error to the developers of Modx to find out what your options are. You can also get some better troubleshooting tools with a MySQL container as well."

Now this is in relation to a site that isn’t even live yet and is the only site hosted under this account. Again, this is also with the latest version of MODx 0.9.6.2. Has anything changed between 0.9.6.p2 and the latest that would’ve increased sql connections, indexing changed etc. etc.?