fmserver.exe process - abnormally high CPU load?

[tripswitch]

Hi there,

I'm hoping you bright sparks can help, and that this is the correct forum to post this to - I suspect our issue may be ODBC-related at core, but the symptom is primarily centered around the fmserver process. Sorry this is a long post, I want to provide as much background as possible as it may help someone to recognise the problem. However I'll try and keep my questions at the end to the point based on the research I've already done.

First a bit of background:

We are running FMS8.0v4 Advanced on a Windows Server 2003 (SP2) box. A number of FM clients (max 10) could be connected via FM clients at any one time. Additionally the database serves as a data source via ODBC for an ASP website hosted on the same server. Incidentally I am the web developer, not the developer of the FM DB.

We have historically had a lot of issues with stability, which we thought we'd resolved - until a couple of weeks ago when, inexplicably, the server became very unstable again. The first evidence was that pages on the website which are retrieving data via ODBC either hung completely or took several minutes to load. In the worst cases, Filemaker connections would also hang, forcing a series of restarts to get everything back on the rails.

I've been monitoring the server closely this morning after yet another crash. Looking at processes running (in the Task Manager), I've noticed that each time a page is called on the website, the fmserver.exe process shoots up to between 50% and 80-90% CPU usage, which seems like a particularly heavy hit. Periodically (presumably at times where the site is being used by several concurrent users), it bobs around between about 70% and 95%, and it's at these times that the site (and FM clients) become unresponsive. In the worst cases it seems to get locked in at this level and the only way to resolve it is to restart the server.

So, to my questions:

1) Is this behaviour (and in particular CPU usage) something that other admins have come across? Are those kind of CPU hits for a single SQL call 'normal', or something to be concerned about? And do they give any clues as to what is going on under the hood?

2) I've read numerous posts about the 'quirky' Datadirect Sequelink ODBC driver ... we're currently running v5.4. Since the direct FM client connections to the server don't appear to put a big hit on CPU, and all other IIS sites we're running which don't connect to the Filemaker DB via ODBC continue to run fine, it seems to me that our problem must be with the ODBC driver. Can we run a later version of the Sequelink driver with FMSA8? And if so where can I find it? It appears from the Datadirect site that they've released v5.5 and v6 drivers but Filemaker seem to have removed all info and links to supported drivers pre-FMS9A from their site.

3) Is an upgrade to FMS9A recommended, and is it likely to resolve our issues?

4) Any other advice about what logs etc. I could be looking at to glean any more info would be greatly appreciated.

Thanks in advance for any light you can shed on our problem!

Nick

[Steven H. Blackwell]

We are running FMS8.0v4 Advanced on a Windows Server 2003 (SP2) box. A number of FM clients (max 10) could be connected via FM clients at any one time. Additionally the database serves as a data source via ODBC for an ASP website hosted on the same server.

That's a likely cause of the problem. Try moving the web serving to another machine and see if that clears up this issue.

Steven

[tripswitch]

Thanks for your response Steven :

Forgive my ignorance if I'm missing something fundamental, but doesn't FM ODBC require a system DSN to be set up and therefore that the database and webserver reside on the same machine?

Nick

[Steven H. Blackwell]

but doesn't FM ODBC require a system DSN to be set up and therefore that the database and webserver reside on the same machine?

I don't think so, but this a little outside my area of expertise. I'll see if I can find the answer. I know that the web server for FMS web publishing can be on a separate machine. The ODBC business may be another matter.

Steven

[tripswitch]

Thanks Steven ...

Any advice gratefully received. I'm finding this really hard to troubleshoot as I'm no expert in Filemaker Server and am struggling to understand at what point the system is breaking down and what can be done to resolve it.

Since my post last week, the server has required several restarts every day to get the processes running properly again, so it seems the stability issue is just getting worse. Each time, the same symptoms... after a few hours of normal running the fmserver.exe process fails to release processor resources, bringing our ASP site to a standstill.

It seems to me that the ASP/ODBC solution to publishing FM data on the web is fundamentally flawed in that it's totally reliant on the Sequelink driver, which seems to be fundamentally flawed in itself. The driver has always been painfully slow and I've already had to script using a couple of third party ASP components (APGen and ASPCache) to save out static versions of some pages and cache the data in others) to get around the speed issues ...

I'm currently looking into the FX.php class in conjunction with the Web Publishing Engine in FMS8A as a possible solution. Failing that I suspect that an upgrade to FMS9A and using the PHP Custom Web Publishing API may be the way to go. However it's not a decision to take lightly as it would require the complete recoding of an extensive dynamic site to use a different server technology. A big investment in time for myself and my bosses

Thanks

Nick

[Wim Decorte]

You can set up a system DSN on any machine pointing to FMSA on port 2399. I'd would agree with Steven that splitting the web server and FMS server roles will help.

The key in your story however is this sentence: "until a couple of weeks ago when, inexplicably, the server became very unstable again"

Something obviously changed, either in the setup, or in the database structure, or in the code using ODBC that produces the additional slowdown. If you can backtrack to the change you've solved it. Hard for us to say what that was.

Upgrading to FMS9 should help, improvements in ODBC performance have been reported with 9.0v3 (using the sequelink 5.5 drivers).

Another alternative is to use fmDotNet which is a fx.php-like tool but for .NET instead of PHP. If your web pages are ASP.NET then that would fit right in.

[tripswitch]

Thanks VERY much Wim,

I'll definitely experiment with setting up the ODBC DSN on a different box ... that small piece of knowledge opens up a whole world of possibilities for me on this and other projects!

The key in your story however is this sentence: "until a couple of weeks ago when, inexplicably, the server became very unstable again"

Something obviously changed, either in the setup, or in the database structure, or in the code using ODBC that produces the additional slowdown. If you can backtrack to the change you've solved it. Hard for us to say what that was.

You're absolutely right on this ... clearly something must have changed. I know the db developer rebuilt the Filemaker database and made some optimizations a couple of weeks ago but these were in response to an initial slowing down of the server. So inconclusive but possible that this has had something to do with it ... I have also launched a couple of other sites on the same server recently, but these are pure HTML/XML and completely separate to the database, and have remained unaffected by these problems. There's nothing else that I know of, but I will investigate.

Will definitely check out fmDotNet, that could be the perfect solution! Wish I'd known of it's existence earlier ... must keep my ear closer to the ground in future.

Thanks for all your help

Nick