Symphony.

Hi Guys;

Recently we have been having an issue with too many connections on server and we have dropped it down to something to do with a JavaScript bug with a certain IE8 configuration that was making request to an ajax page multiple times in a second assuming somewhere between 4-12(estimation on part of the log files)

My question is would this open up a new MySQL connection every time to crash the server by running out of connections. or does the singleton & Symphony design prevent this.

We are also running other websites using different configurations on the same server but our hosting guys pinpointed the ajax page as being the issue seeing the multitude of simultaneous requests.

Just asking to see if we have really resolved the problem by changing the JavaScript or else if the problem lies elsewhere. Also it would be cool to know if a DDOS attack would make the SQL server crash due to the plenty of db connections.

Note on this page we were running a live DS which gets data externally on every request so it could take around 0.4-1s to get this data depending on the external server.

Help much appreciated :)

My question is would this open up a new MySQL connection every time to crash the server by running out of connections. or does the singleton & Symphony design prevent this.

It really depends on how your site was structured. Assuming you haven't done any caching to the server or on Symphony's end and you're going with a default build, then every page request will hit your database with a new request. This will potentially cause your site to become unresponsive (ostensibly like a DDoS attack). The singleton paradigm doesn't prevent your site from being hit with overwhelming number of requests. What it does is to ensure that at the core level, the system won't be making multiple redundant calls to instantiate objects that is already created.

Just asking to see if we have really resolved the problem by changing the JavaScript or else if the problem lies elsewhere. Also it would be cool to know if a DDOS attack would make the SQL server crash due to the plenty of db connections.

This is really hard to tell without actually looking at your environment. I'd say that the JS was the main cause of your issue. A DDoS attack would indeed make the SQL server crash alongside any other web services that your server is hooked into, that's the whole point of a DDoS attack. A good portion of your web hosts' job is to have defences against DDoS attacks on the hardware and software level, before it reaches your particular server. That said, it's also a responsible thing and also for the sake of a good user experience to make sure your site runs smooth and fast. This is where things such as caching and general optimisation tasks takes place.

Note on this page we were running a live DS which gets data externally on every request so it could take around 0.4-1s to get this data depending on the external server.

I would suggest caching the external request data as much as possible to lower the retrieval time.

I would suggest caching the external request data as much as possible to lower the retrieval time.

Seconded. For a website I am working on now we are changing our datasources from "on demand" updating to scheduled updating. This means a cron job will update the data every now and again (every 30 minutes, say) so symphony can just pull the data from a textfile, which is rediculously fast.

Ofcourse, since this requires a cron job, this is not something that is easily implemented from the core, but I think a LOT of sites would actually benefit from this. Unfortunately, I am incredibly busy at this moment, but if somebody wants to create an extension for this, I am more than happy to help!

I'd like to excuse myself for failing to reply any sooner but been really caught up with this one trying to figure it out for good.

I agree that the issue is most likely to be the Javascript. Identified the problem as something to do with caching on IE. basically this script was being invalidated by a variable every 4hours. Its just on IE the return value was being cached keeping it invalid. And forcing a check to re-validate the value...

I also think that the datasources were not running as we have a variable as mentioned above to confirm validity and kills the process with a die('invalid') using the delegate FrontendParamsResolve which should be before any datasources run. (ps. would this have any repercussions on symphony with some resource handling due to unclean exit. - looking to replace this with something which makes more sense whilst staying efficient with resources as it is there to block unwanted bot access)

So Allen if the singleton does not protect against creating multiple concurrent database connections. Say if this buggy script was sending anything in the region of 12 requests per-second it could have lead to Too Many Connections?

So Allen if the singleton does not protect against creating multiple concurrent database connections. Say if this buggy script was sending anything in the region of 12 requests per-second it could have lead to Too Many Connections?

That really depends on a couple of factors:

First is the number of concurrent connections your server allows.

Second is the amount of time a typical request takes to process. The longer the processing time, the longer the database connection remains linked.

If your server allows 100 maximum connections (I think the default is now 151 connections for MySQL v5.5+) and you get 12 requests a second, but each of your requests take over 10 seconds to process, then you will hit the Too Many Connections error by the 9th second.

12 req/sec * 9 sec > 100 connections

If this is the case, you either need to increase the max connections setting or reduce the processing time for requests.

Hmm - seems to make sense though only problem is that when not under load the page takes around 200ms to load. Since I am stopping page execution on with a die() on ParamsResolve the destructor states a MySQL close so I shouldn't be leaving open connections hanging.

I don't know under massive load how much does this 200ms rise up to being honest... but considering there are no data-sources I doubt there are more then a second or two... But I assume the longer the issue was the slower the server will start becoming...

The issue is resolved as-such as it was a buggy script causing these requests thanks to IE but have to give a reasonable explanation to why what and how it happened. The thing is I can never estimate how long each request to this page was taking; additionally I was only given a part of logs relating to this website - so other websites running on the server could have also used up some connections. Essentially meaning that I would only need around 60connections to crash it (depending on the other websites).

Right, so you're running the site under a shared environment. This certainly complicates the matter some.

There are many other things to consider about a typical journey that a request takes that could inform your particular situation without bringing the complication of a shared hosting environment. For example, how much memory does a typical request take to process? (you can find out using ?profile).

As requests reach past the allocated memory threshold, a server starts to utilise the hard drive to compensate (swapping/paging), this is particularly bad as hard drive access is many folds slower than RAM access. This contributes to requests taking longer (along side the usual suspects such as CPU load, network bottleneck etc.) and thus cause an accumulation of in-progress scripts that take much longer than usual to complete or worse, be in a perpetual processing state (hanging) that doesn't ever complete.

Yep; its shared though they are only websites/services of the same company as such. Only that I am only given details of the part I take care of so pretty hard to figure out.

Normal symphony pages take around 2-3s to generate when not cached - once cached this drops to around 0.7-1s using profile. (cache is similar to Nick Dunn's cacheable extension) big bulk is to do with XSLT transformation - at points this is super complicated & includes plenty of datasources.

Thanks for the explanation would help add up the time frame. They were not too happy with answers like 'depends what was happening on other websites on the server' but am sure if I try to seem technical it will look like the problem has been identified and understood (as well as obviously eradicated).

big bulk is to do with XSLT transformation

XSLT should be the least intensive process of them all, so I'm quite surprised by this. Typically, data sources (in other words, DB access) takes up the bulk of the server's overhead.

It used to before I had everything cached totally dropping the time it takes to generate datasources into less then 0.01s

Anyway we seem to have found the issue - it was our external source we had caching enabled every 6 minutes. And the big problem was that this feed was taking 6s+ to generate. So if plenty of users were requesting this resource (available only through RSS) at the same time it was clogging up our DB with some very long super slow queries, which was potentially leading to maxing out our connections.

it was our external source we had caching enabled every 6 minutes. And the big problem was that this feed was taking 6s+ to generate.

You could separate delivering the cached content and creating the cached content by running the latter in a cron. That way you won't have 20 people requesting the feed while it's cache is being refreshed, each of them having to wait 6s and significantly increasing the load.

Don't know exactly what type of datasource/transformation was taking so long but I bet you can simply introduce a ?refresh parameter that'll flush the cache by overriding its settings.

In case your transformations take more than a few hundredths to tenths of a second I would also look into those. Maybe they can be simplified as well.

doing a manual ?refresh is not feasible as data changes constantly even when there is nobody in office. Its like a financial system that comes up with events / marks some special currency pricing, so have no control over the external source.

I decided to go another way to cron; very similar. Going to adopt a push functionality going to run some application on a separate server/machine to check this feed every few seconds/minutes and push the data to my server by hitting an update script. Should be far more efficient.

doing a manual ?refresh is not feasible

Not manually but

*/5 * * * * curl http://yoursite.com/feed/?refresh

hmm would do but I cannot get the guys at the hosting to accept setting up a cron that forces a refresh that often. the one that I have is every 2hours which is not quite good enough, and would not be easy to get them to run one every other minute as they rightly claim that it would probably clog up the server...

they rightly claim that it would probably clog up the server...

How would it clog up the server? So long as the result of the request overwrites the previous, you won't have a build up of files on the server. We regularly use cron tasks that execute every few minutes without problems.

Although this is an old extension, the Tasklock API was created to prevent cron requests from piling up.

We had a problem where the cron was being executed every minute, but as the site grew, the task actually took longer than a minute to complete. This caused multiple processes to spawn which ultimately brought down the server.

The Tasklock API was used to check if a task was currently running, and if it was, we'd drop that cron and continue.

Looking quickly at the Tasklock code, it has a dependency on the ASDC extension, which has been deprecated. This isn't a big deal, as Symphony::Database() will almost be a drop in replacement.