FileMaker Server Performance Improvements in 19.1.2

Table of Contents

• Performance enhancements in all versions
• A word about Punisher
• Sharing Lock (on by default)
• Purge Temp File on Server
• Server-side Sorting
• Threaded sorting
• In-memory multi-line key resolution
• Normal Temp file
• Finding performance hogs
• Conclusion

The effect of this change really only comes out under load. Single-user operations (see the “1” columns) will not benefit much from this, but once you start increasing the workload on the server, it will be able to handle more of these types of calls at the same time.

For reference: the search unstored calc script in Punisher performs a find across 5,000 records on an unstored calc field, and the slow searches script does a search with 8 find requests across multi-predicate relationships.

This Sharing Lock feature does not affect operations that do a lot of writes to the database. The script shown below parses 1,000 elements out of a JSON object and creates a record for each element.

As you can see, on each different machine configuration and across the FileMaker Server versions, the results per server are nearly identical.

One thing that this particular test does show is the effect of more cores: the exact same test on a machine with eight cores and with 20 sessions hammering away at it gets through the script in one-third of the time that it takes a machine with two cores. FileMaker Server will always take advantage of more cores if they are available for these kinds of operations, but the Sharing Lock feature does not make it faster.

The Sharing Lock feature is on by default when you install FileMaker Server 19.1. If for some reason, you need to turn it off, then you need to create a text file named ReleaseDebugOn.txt and put in the Database Server folder. (For macOS and Linux, in the bin subfolder).

When you add DisableSharingLockOnServer to that text file and restart your FileMaker Server, the Sharing Lock feature will be turned off.

If you are uncertain whether or not the setting took hold, then also add the ForceOutput line to the text file and check the fmsDebug.log file.

In prior versions of FMS, when you create a System Script schedule, the dropdown of available scripts will show the OS-level scripts that you have in the FileMaker Server scripts folder. So you may be surprised that the scripts shown below appear there as new options. Pick Sys_Default_PurgeTempDB and set the frequency of the schedule to what you need it to be.

Using this feature will no doubt become a best practice, something that we will set up on all new FileMaker Server deployments.

While not related to performance directly, it’s worth mentioning the other new default System script. Sys_Default_VerifyAllDB allows us to run verification on all the hosted files, similar to what the existing fmsadmin verify command does. And just like that command, this closes all the files first and then runs the consistency check as it tries to re-open each file. By closing the files, it does interrupt any user sessions that may still be active at that time, so use caution when scheduling this script.

Using FileMaker Pro 19.1 and FileMaker Server 19.1, and making sure to test as the only user in the file with no other FileMaker Server activity (to ensure that FileMaker Server will take on the sorting), the sort takes around 7 seconds.

Running the same script using FileMaker Pro 19.0 takes about 15 seconds.

The difference is also noticeable in the sort dialog; 19.0 shows the progress as the client works its way through the records, whereas 19.1 just displays the total record count to be sorted.

When you look at the FileMaker Server stats.log, you will also see the difference in behavior. With 19.0, more data travels down to the client so that the client can perform the sort; with 19.1, there is less data but more processor activity on the server.

It is very clear then that we are trading off-network traffic for server-side processing. As with anything that involves asking the server to do more: make sure that your server is up to the task and has sufficient processing capacity to handle the extra load. If not, then the client will still end up doing the sort.

Note that there are quite a few pieces to the sorting puzzle, including how much data of the target table is already cached at the client, how busy the server is, etc.; the overall effect of server-side sorting may not be clear at all times, but it should be beneficial overall.

If, for some reason, you find that you need to turn this feature off, then follow the instructions above to create the ReleaseDebugOn.txt file and add this flag to it: DisableServerSideSorting.

If you do that at the server level, then it will disable all server-side sorting. If you disable it at the client (by putting the ReleaseDebugOn.txt where your FileMaker Pro application is), then it will disable the feature for just that client.

When you turn the feature off at the client level, look for the DBDebug.log file in the same location as your FileMaker Pro application to confirm that the feature was indeed turned off after relaunching FileMaker Pro.

When you disable server-side sorting at the server level, look for confirmation in the aforementioned fmsDebug.log file.

Multi-line key relationships have a primary key that consists of a list of values that result in more related records, each of which has a foreign key value that matches a value in the list of primary key values.

Resolving the found set of matching related records obviously takes longer with a multi-line key construction like this. FileMaker Pro 19.1.2 changes how this is done by shifting the load entirely to be done in memory instead of involving the disk i/o through temp files and caches.
If your solution uses multi-line keys, you should see the benefit of this.

This feature, too, is enabled by default. To disable it, add DisableMemoryKeyCmpIndexing to the client’s ReleaseDebugOn.txt file.

¹ – DBCache location:
macOS: [User Account] -> Library -> Caches -> FileMaker -> DBCache
Windows: C:\Users\AppData\Local\Temp\FileMaker\DBCache

Finding performance hogs

FileMaker Server has a few logging mechanisms that can help track down how your solution and the server it is hosted on is performing. The stats.log file provides a high-level overview of performance data across the four possible bottlenecks: processing power, disk i/o, network throughput, and memory. The topcallstats.log provides more detailed information across those by capturing the 25 most expensive operations that the server had to perform in the logging interval (typically 30 seconds). While that is very useful information, it is also very fragmented information, especially in a busy environment. The irony here, of course, is that busy environments are the ones most likely to exhibit performance problems, and those are the ones for which you need the most comprehensive troubleshooting data.

Furthermore, operations logged in the topcallstats.log do not necessarily tell a FileMaker developer what user or scripted action is causing them. In the log example below, taken from a prior version, it is impossible to determine if the Query is a user doing a manual search or is a scripted Go To Related. There is no indication of what script is running at the time.

Because the log only retains the 25 most expensive operations per logging interval, that collection is inherently disjointed.

In FileMaker Server 19.1, you can now tell the FileMaker Pro client and FileMaker Server to log all operations (named remote calls). When you add the line RemoteCalls to the ReleaseDebugOn.txt file on the client or the server, all remote calls that happen at that level will get logged.

On the client, you will find the data in the DBDebug.log file.

And on the server, the remote calls are written to the fmsDebug.log file.

It is still early days, but ultimately this should allow us to build ‘fingerprints’ of typical user or scripted actions and how those translate into collections of operations as logged by the client and by the server. As developers, we will be able to better understand what remote call operations our solutions generate.

A word of caution: whenever you enable the topcallstats.log, you get the warning below about the amount of logging, possibly affecting the server performance.

While I have argued in the past that a well-chosen server should be able to handle the extra load (both processing and disk i/o), the extra amount of logging involved when using the RemoteCalls feature on the server is massive, and this feature should not be enabled routinely nor continuously. In our testing, we have seen a performance impact of up to 10% across all operations. That said, it certainly is a feature that will be immensely useful during development, testing, and troubleshooting. And since you can enable that feature on the client side to log everything that this client generates, investigating, learning, and troubleshooting what types of FileMaker activity generate what remote call operations can now be done client-side without affecting the server as a whole.