pc Custom Web Publishing Issues

[kfutter]

Hi folks,

 

I had a long explanation typed out for this one, but lost it with an errant mouse click, so I'll just cut to the chase, and back fill any info as required.

 

We're having major issues with our FMSA12 server bogging down under the load of too many CWP sessions (they vary from as few as 3 to as many as 127 from observations so far).

 

So, my two big questions are:

1. What's the 'sweet spot' for setting the number of concurrent sessions?
2. How much in the way of 'resource units' (memory, CPU) does each session consume on the server?

I no longer have the time right now to type out a long-winded background for this one, but please feel free to ask for any more information should you require it.

 

Kev

[kfutter]

No one?

 

Kev

[webko]

CWP sessions shouldn't persist - they should be opening and closing in millseconds.

 

Most likely there is a request that is taking a long time to resolve, and the other requests pile up behind it. I believe that FMSA12 is still single-threaded for web requests...

 

I'd check the logs to find the offending page.

[kfutter]

Thanks Tim. I've been over the logs many times (assuming you mean the FM server logs), and they're no help at all. I actually know the page and even the request that's causing the issue. I'm just trying to figure out how best to cope with it at the hardware level.

 

Kev

[webko]

I actually mean the web server logs in this particular instance...

 

However, if you already know which request / page is causing this issue, then that's what needs to be addressed.

As I  said, I believe the WPE is single-threaded - so if you have a long request, then it impacts on all subsequent requests until it has been performed.

 

Cheers

Webko

[kfutter]

Yep, I believe there's nothing I can do about the situation at this stage. I'm pulling a lot of data out of several databases, some with related data, to load into a web portal, and up to 150 people can potentially be doing it at once. It consistently craps out at about 70 concurrent CWP connections, but often gets overwhelmed well below that. There are no data points I can drop and nothing I can rationalise or streamline. It's actually applications like these that expose the limitations of FileMaker as a web back-end, but I can't do anything about that either!

 

Kev

[ggt667]

What you can do is to spllit your table into several tables. and avoid running the query on a table  that has many relationships.

 

What I ended up doing is to export FileMaker's edited data every 5 or 10 mins to postgreSQL and/or MySQL depending on project.

Using XML export, that way you will have recid available, and FX.php or phpFileMaker can update directly from the web, this reduced the load of WPE.

[kfutter]

Well, this issue has not been resolve so far, and in my searches for any clue or answer at all, I came across the following thread here:

 

http://fmforums.com/forum/topic/46682-web-publishing-performance-issues/

 

It's a similar issue to the one we're experiencing, though the last post was 2 years ago. The data I'm accessing is already in multiple tables, and that's part of the problem - up to 4 db requests to gather the required data, with up to 120 or more concurrent connections making these requests. This is data required for marking the roll at a school, and so it happens at the start of every period, and the bottleneck can last for 15 minutes or more. At this stage my only hope for a solution is to proxy as much of the data as I can in MySQL.

 

Does anybody know if FileMaker 13 fixes the single-threaded nature of the WPE? We'll be upgrading to it in the coming weeks regardless, but I'd like to know whether it's likely to improve the situation. In any case, it'll likely be our last FM upgrade.

 

Kev

[kfutter]

Is anyone actually reading this thread? Oh well. I'm wondering though how the hell FMSA 12 can be hosting nearly twice as many Custom Web Publishing sessions as the purportedly hard limit of 200. I set it to 200 just for giggles, and just witnessed it peak at 394. How's that even possible?

 

Even so, the WPE was still at a standstill, taking forever to output any data. I trawled through the IIS logs on the FM server, and could see tremendous wait times between IIS and WPE - up to 107 seconds in my sample log data. I'm at my wit's end with this problem, and really don't know where to go from here.

 

Kev

[Justin Close]

Hey Kev,

   We are having some CWP/PHP performance issues as well, although perhaps not on the scale you are.  A few questions for you:

 

*  Are you using secure connections (SSL/certificate)?

* Are you using Active Directory to authenticate to?

 

Every CWP hit requires a login, which can be part of the bottle neck.  We haven't solved our issues yet, but we a number of pages that come up blank or partially loaded, reports of authentication errors, etc. 

 

Another idea as far as your structure.  Are you purely receiving data from these requests?  That is, the end user already has a list of students (you mentioned attendance at a school) and then they are simply marking them off to send that back to the server.  Or does the list have to be downloaded each time?

 

I wonder if you could do something of a cache table; the list of students (assuming it will get pushed to the end user) probably doesn't change very often.  Can you build a scratch/temp/cache table that gets updated once a day, via a scheduled script?  Then the end user only has to hit one table to get the list of students, no relationships.  And then receiving the information can go to that same cache table to save the data; and then another scheduled script on the server scans that table every so often (once an hour) and slurps in that attendance data and spreads it around to the other tables that need it.  Makes sense?  A lot depends on exactly what you are doing, of course, and this may be over-simplified.  But it's somewhere to start, perhaps.

 

--  J

[kfutter]

Hi Justin,

 

Thanks for your input! To answer your questions, FM client access to databases is mediated by AD accounts, but the account used by the WPE is local to each database. I've had issues before where a particular database would simply stop responding to requests using that account, until I moved it to the top of the list in the "Manage Security" window!

 

As for the data being downloaded, it's not even trying to download students, just a daily timetable (though this requires up to 4 requests to the database). The system is designed to give teachers quick, web-based access to their classes. They can click on any class, and this will open the attendance roll in FileMaker for that class. The second part works really well, but grabbing that class list from FileMaker via WPE is the problem!

 

In any case, I've mostly solved the problem now, though there was no magic bullet. The solution mainly involved reducing the number of FileMaker queries to as close to zero as possible, so that the portal home page would not hang waiting for them. This was partly dealt with by moving the timetable off the home page, but where possible, I also proxied other data in MySQL, which mirrors your suggestion; it's a no-brainer with data that doesn't change regularly. The last major change was upgrading the server to v13, but it's hard to tell what impact that actually had, given all the other changes. All I can say is that, after doing all that work, the problem has dramatically improved, to the point of going away altogether, and I've had almost no complaints since.

 

So, the short answer is, don't use the WPE unless you absolutely have to!

 

Kev

[Justin Close]

It appears that much of our problem was getting slow responses from our AD server.  This is exacerbated by CWP because it has to log in every time you request data from the DB, so any time your PHP uses a form of the "execute()" method. 

 

I think that we have finally found a workaround for our problem, and you actually touched on the solution in your response: 

 

Move ALL the local file's account above ANY externally authenticated accounts.

 

I know it sounds weird, but here's a FileMaker Knowledge base article on it:

http://help.filemaker.com/app/answers/detail/a_id/7544/~/poor-performance-and-authentication-failures-when-accessing-files-via-external

 

The problem has been around for a long time (this article applies to 10, 11, 12, 13), but apparently FileMaker hasn't bothered spending any time fixing this. 

 

Now, this won't solve ALL login troubles.  A login attempt that uses an account that doesn't exist will still hit the AD servers and still wait for them to return a failed result.  While doing this waiting (which could be 10-30 seconds in our experience) ALL OTHER CWP requests will be put on hold.  It appears that CWP is single threaded...

 

I need to come up with a new mental-model of how authentication works.  The implication here is that FileMaker takes whatever was entered into the login screen and then compares that to the first account listed in 'Security', on down the list.  And then when it hits an entry listed as 'external' it goes and hits the AD servers?  But it doesn't hit them for every group, does it?  AD returns a list of groups that the user belongs to, I would assume that it remembers that list that is returned and doesn't have to fetch it again for every group in the Security list.

 

And why would having ANY local accounts down below the External accounts cause ALL login attempts to sometimes get long delays with AD?  It didn't necessarily matter if the account that was actually trying to log in was above/below the external accounts, but that if there were ANY local accounts down below.

 

Now, I am sure that I have some details wrong with all of my sweeping "ALL..." statements.    But I am hoping that someone out there can help to clarify the details.

 

--  J

[Justin Close]

Well, the workaround doesn't appear to have solved too many of our authentication problems.  We still have lots of trouble with getting '22 - authentication failed...' errors from the server when connecting via CWP, XML, or even the client. 

[kfutter]

Sorry to hear you're still having trouble Justin. So am I as it happens. We experienced some temporary improvements by reducing the number of CWP requests to as few as possible, but the problem has come back. Not as bad as it was, but still bad enough to frustrate many users, and cause overall performance to be very slow. I can't reduce the load any further without re-architecting some of our solutions, which is a position I'm rapidly being forced into.

 

I might try your suggestion and move all web accounts above the AD ones, but I'm not holding out much hope...

 

Kev

[kfutter]

I've just noticed something else about our beleaguered FileMaker server that bears mentioning. On the opening Status page of the Admin console,  the status for Web Publishing Engine keeps greying out, with the 'switch' moving to the 'off' position. At the same time, under Web Server above it, "XML is enabled" changes to "XML is disabled". I can ostensibly revive the WPE by moving the 'switch' back to the 'on' position, but the XML status remains disabled for several minutes before coming back.

 

This seems to be happening about every 10-15 minutes, sometimes more, and the number of clients (FM) or concurrent connections (web) don't seem to matter (ie, it doesn't appear to be a load issue, directly anyway). I'm assuming that the WPE comes back by itself too, if I'm not monitoring things - otherwise I'd come in every morning to find it off.

 

This is all getting rather desperate now, so if anybody has any clues about this, I'm all ears!

 

Kev

[webko]

Check the web logs... As per original observations, I strongly suspect you will find that there is a query that takes a long time to finish, and everything backs up behind that until it is finished...

 

Having been through this exact scenario on multiple systems. The errant PHP query needs to be found and refactored

 

Cheers

Webko

[kfutter]

To some extent Tim it's the accrued weight of all the requests to the server that's causing the issues, but a few days ago a clue surfaced as to where I might find this one problematic query you speak of. This led me to a page that was averaging between 22 and 27 seconds to load, with me as the only logged CWP connection. By doing nothing more than removing all the non-required fields from the FileMaker layout this page was being fed from, I was able to reduce this average down to less than 2 seconds. Now, I didn't stop to look at any of the fields I removed, but I suspect some of them were calculations. Either way, a 12-fold improvement is amazing, and I can see how it would only take a couple of users (out of 1500) looking at this page to produce the results I've been seeing.

 

In the end I broke the page up into sub-pages via AJAX, to further lighten the load, and each query now loads its data set in under 1 second. I can't test this under load until February next year now, but I'm confident that this will represent a significant - and hopefully lasting - improvement in CWP responsiveness.

 

Some additional information: out of desperation, a week or so ago we contacted FileMaker support to get some direct assistance. While they couldn't help us directly, the information they passed on will lead to a server rebuild at our end. For starters, we are apparently using an unsupported OS to host our FileMaker server (FMSA 13), being Windows 2008 Server R2 DCE. Why the standard server is listed as compatible, and the Data Center Edition is not, no one could explain. I'm led to believe that the only difference is in the licensing, and the OSes are otherwise identical. In any case, we're obliged to change to a supported OS in order for FileMaker (the company) to render us any assistance.

 

The FileMaker support rep was also able to confirm that the hardware involved was under spec for high-volume custom web publishing. I'd seen the system requirements data for WebDirect on the FileMaker website, but the FM support rep confirmed that they're applicable to CWP as well. Since our current server rarely shows more than 50% utilisation for either RAM or CPU, even with CWP responsiveness at its worst, I had discounted the hardware aspect (though it had been given additional resources as part of this saga). The inference is that this ostensible anomaly with resource monitoring is part of the reason DCE is not supported. Again, we'll beef up the hardware anyway, since we can.

 

It would be nice to think that this then represents the end of this War and Peace epic, but as I mentioned earlier, I won't really know until school is back in full swing next Feb.

 

Kev

[webko]

The non-required fields is always a good move - in all my CWP systems, I generally build a layout for each function, with only exactly what is required.

 

I also never use portals (even if I need the related information) - because you can't predict how many rows might be in the portal...

 

And the XML returns *every* piece of available data from a layout. This killed one system, as the portal had about 1000 rows, all of which were returned from the query to a single record...

 

Anyway, hopefully what you have discovered and done will improve matters.

 

Cheers

Webko