Server 7 performance

[chris martoglio]

We have developed an application using fm7 for a client. The client wants us to host the application on our server and they will access it from their offices. 17 users will access the application remotely, and each have FMP7v2 installed on their machines. Currently, the database is not that big, with any script step having to deal with at most 200 records. However, speed is horrendous over a WAN, but is good over a LAN. I've spoken with filemaker support, and they were not much help, other than to say, it should not be running this slowly.

We have established a series of selections from the app to test performance. One such selection takes 4-5 seconds on the LAN. The same selection takes and average of 48 seconds over the WAN. The other benchmarks work out about the same with an approximately 10x increase in time required to perform the scripts comparing LAN to WAN.

The scripts don't do anything spectacular, finds, sorts, deletes of multiple records from transaction tables, exports, etc. The application is contained primarily in one file (a second file has one table and no scripts). The primary file has 22 tables, 49 relationships. 91 layouts, 16 value lists, and 100 scripts.

The current configuration of the server is as follows: FM Server 7 running on a dual processor (1.25) G5 with 1gb memory and OS X Server 10.3. I've maxed-out the cache setting in FM Server 7 and set cache flush to one minute. I've just rebuilt the server, so the only thing running on it is FM Server 7, the OS X 10.3 firewall and Open Directory service (but it is set to "stand-alone computer"). Broadband connection is a business DSL (from BellSouth) with guaranteed upstream speed of 512Kbps, downstream speed of 768Kbps, and a static IP address. All application security is handled at the application (no usage of Open Directory, LDAP, etc.).

Since it runs well on the LAN, I've ruled out hardware or FM Server settings as the bottleneck and have focused on the connection. I took the router out of the loop, direct connected the G5 to the DSL modem and set the modem to IP Passthrough, so the G5 pics up the static IP address directly. I figured this was about as straight a pipe as I could create to test the application.

I've measured the throughput on the connection, and it is greater than the speeds listed above, so I've ruled out a slow DSL connection (slower than the guaranteed minimum speeds stated above). Unless FM Server 7 requires more bandwidth, say a T1, which is about 3x the bandwidth I currently have on the upstream and about 2x on the downstream.

I've also tried it with the firewall turned off (briefly). The firewall is the one provided with OS X Server 10.3. But turning off the firewall had no affect on performance.

Any help in getting this app to run faster over a WAN is greatly appreciated. Thanks.

[Kurt Knippel]

Well something to remember is that a T1 (1.5MBs) connection is about 100x SLOWER than a LAN (100MBs) connection and you are running 1/2 of a T1, so you are looking at about 200x slower performance than you would see on a LAN.

As luck would have it your tests are only showing something like 10x slowdown which is pretty good.

You really need to consider structuring your app to be as lean as possible to run over the WAN. Smaller screens, less graphics, conditional buttons, etc. You might also consider running via IWP and have them connect through a browser.

[chris martoglio]

Kurt,

Thanks for the reply, but a 10x slowdown from LAN to WAN makes the WAN functionality of FM good for only the lightest of apps. When I showed the FM tech support person the actual peformance (letting him logon and try the app), he said pretty emphatically that there is no way we should be seeing this type of slowdown.

For example, one of the scripts goes to a layout, enters two criteria for a find, performs the find, then deletes those records. It finds about 180 records out of no more than 500. Both fields involved in the find are indexed. The records contain three fields in total, none longer than 10 characters. To perform these steps takes about 15 seconds over the WAN.

As I see it, these are a pretty straight forward and light set of steps. If FM can't perform it with minimal delay (certainly much less than 15 seconds), then I don't really see much practical application for the remote access functionality.

Thanks again for your thoughts.

[Ender]

Indexed finds shouldn't take more than a few seconds over a WAN, even with thousands of records. I performed some speed tests a while back comparing FM6 and FM7 over a LAN and over a WAN. The speeds you describe sound abnormally slow.

At this point, I'm afraid I don't have a solution for you, but I'll try to chime in if something comes to mind.

[Ender]

Chris,

When you're performing these finds, are both fields defined in the table that the layout is based on or are they from a related table? My speed tests showed that finds on fields from the base table are within a couple seconds, but if the fields are from a related table, this is equivelant to an unstored find and those can take longer (38 seconds with 18,000 records the first time, 11 seconds the second time.)

[chris martoglio]

Thanks for the thought. The searches are taking place from a layout where the fields being searched are in the table associated with the layout.

Best,

Chris.

[LiveOak]

The promise of FM 7 & FM 7 Server was decreased communications between client and server with more operation being performed on the Server side. Some advertised benchmarks claim better performance of FM 7 / FMS 7 over the WAN than FM6/ FMS 5.5 over a LAN. One I heard has an operation that takes 12 seconds over a LAN in 6/5.5 taking 1 second over a WAN in 7/7.

The ultimate in remote performace will probably still continue to be Timbuktu / PC Anywhere / Citrix access where only KVM information travels over the WAN.

The actual performance of a solution over a WAN will probably continue to be dependent upon the exact nature of the solution. The only additional diagnostic might be trying to quantify how much communication is taking place. If it is a whole bunch, the speed difference from LAN to WAN might make sense. The speed difference is networks is something like 200:1 (100basedT vs. 0.5basedT).

-bd

[chris martoglio]

Good suggestion. Here's what we found. For the script referenced above, (it takes 4-5 seconds on the LAN, and averaged 48 seconds on the WAN) an average of 0.28MB of data was transferred in total: 0.17MB sent from client to server, 0.11MB sent from server to client. If I'm calculating it correctly, it should only take 4.38 seconds to transmit 0.28MB, not taking into account any latency and assuming an average transfer rate of 512kbps.

Best,

Chris.

[bruceR]

Did you build these files from scratch in FM7 or are they converted? How complex are they? Do you understand file reference issues?

[chris martoglio]

Bruce,

We built this app originally in FMP7 - there was no conversion. I don't think there is anything particularly complex. Most of the scripts manage transaction files, either adding groups of records or taking away groups of records. To know what to add or take away, it searches other tables, and creates new records, or deletes records in the transaction files based on what it finds. I do use globals a fair amount to pass some data, and to establish some relationships. But most of the complexity, if there is any, could likely be found in the relationships.

Let me know what you mean by your reference to "file relationship issues".

One thing to note, is that there is only one file reference (all other relationships are table references). The one file reference is to a simple file with one table, and that table is not included in any of these slow scripts. All other tables are contained in the primary file. Thanks.

Best,

Chris.

[Himitsu]

Chris,

If you would like, could you make me a login name and password on it and let me go in and see. I am running some very very very fast connections. That way you could rule out some more areas. If it still runs slow on my end, then we know it isn't the connection. I am running server 7 and thousands of records and 9 files (designed from Pro6 and converted) and mine runs nice and fast. Let me know and I will check it out.

[chris martoglio]

Himitsu,

Thanks for the offer. In about 2 hours from now, I'm taking the server to a colocation facility where they will let me connect and test just what you are suggesting. I've got two people stationed to run our benchmark test remotely. One is on a regular DSL line, the other is in a corporate environment, with huge bandwidth and fast ethernet LAN. Both are about 30 miles away. The one in the corporate environment is a network/infrastructure engineer, so he's seeing to it that he's got a nice connection. I'm assuming that I'll get a T1 connection at the colocation facility, so if my application is bandwidth constrained, I should see performance about 2x to 3x what I'm experiencing on my 512/762 dsl. I'll keep you posted. Thanks again.

Best,

Chris.

[Himitsu]

Ok... let me know how it goes. I had a goofy server once and the routers were very screwy. After we threw them out and got some new ones, our speed was nice and fast. And I am even running 8 websites with mail on the same server. Still running smooth. Good luck with it. I think it is amazing that there really isn't much high speed offers in the states. I am in Japan now, and the slowest connection is about 12 meg. I am currently running 100 meg. It is real fun when you connect to someone with the same connection and download in a blink of an eye!

[chris martoglio]

Well, the result of the T1 test: No difference. The benchmarked performance is the same as on the DSL. Makes us think that there is a bottleneck someplace else. I've spoken with the FMP support people, and they are working on it also. I'll post keep you posted. Thanks for the help.

Best,

Chris.

[Vaughan]

Sorry to go back so far, but you stated in the original post that "I've maxed-out the cache setting in FM Server 7 and set cache flush to one minute."

This does not sound optimal to me. FMS much be constantly flushing the cache. Change the setting back to the default, or to something like every 15 minutes or so.

[chris martoglio]

Vaughan,

To be more specific, we have tried a wide variety of cache settings changing cache size, and flush timing. No effect was noted even at the extremes of both. One thing we have found is that any portal sort is unworkable in a WAN configuration. Turn off all portal sorts, and sort via relationships. This is the most significant speed improvement we've found so far. However, performance is still very slow. Thanks.

Best,

Chris.

[chris martoglio]

Well, after much more testing, I think we've identified two culprits of the slow time: creating new records and deleting existing records. With broadband connections, the best new record creation rate we can achieve is 6.25 records per second, and a delete rate of 20 records per second. This may not seem too bad, but when I need to add 200 records to a transaction file, the delay is 32 seconds.

To achieve this in a test, I created a simple file with three stored fields and four global field. The script cycles through a loop 200 times. Within the loop, I add a new record and increment the counter. I populate the three stored fields using an auto-enter to copy one of the global fields into each one of the stored fields (one global put in one stored field). I found this to be a little faster than using the Set Field script step (32 seconds for 200 records vs. 38 seconds).

I also tried some other tricks I read about, like freezing the window. On our real app, this made some minor improvement, but not the dramatic improvement the author saw (author was able to create 88 blank records in under a second). This did not have any real affect on our simple test db, since there was no layout switching. I also tried starting the script from a blank layout, but found no noticable difference (32 seconds either way). Although, this could be more of a result of having just the seven fields shown on the layout (all with three digit numbers). I also tried creating the records through a portal: same result as using New Record script step.

As I mentioned earlier, and this is the most confounding part, we don't seem to be constrained anywhere. We've tried the same test on higher bandwidth connnections, with no change in the benchmark time. The server (a 1.25 G5 running OS X Server, with no other services running), never goes over 9% CPU utilization, the disk drive sees little demand, average bandwidth is minimal (10KB/sec), and as I just mentioned peak bandwidth registers higher with faster connections, but overall benchmark time remains essentially the same. Also, cache setting don't seem to matter with the small demand placed by a single user (me) on the server.

So, I'm wondering, is this as good as it gets? Any thoughts, suggestions, tips, greatly appreciated. Thanks.

Best,

Chris.