Version 19.1.2 of FileMaker Server introduces a slew of new performance improvements. Some of these are turned on by default, and some need to be enabled explicitly. You will learn more about these in this blog post.
A little while ago, in our blog post about the new FileMaker Server for Linux, we also mentioned a prevailing myth: that FileMaker Server will run faster on Linux because…well, it’s Linux, isn’t it!? You will get some insights into our findings on that as well.
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
Performance enhancements in all version
The previous set of performance enhancements introduced by version 18 of FileMaker have been retired and can no longer be activated. Those features were already off by default since version 19.0 and had some known reliability issues. Those retired features were known under the moniker’s Page-Level Locking (PLL), Data Restoration, and sometimes Better Parallel Processing.
In its place come these features:
- Sharing Lock
- Purge temp file on Server
- Server-side sorting
- Sorting on parallel threads
- In-memory multi-line key resolution
The first one is by far the biggest change, but the other ones may have more or less impact on your solutions depending on how they are designed and how much sorting is done, whether or not you use multi-line keys for relationships, and so on.
A word about Punisher
Throughout this blog post, you will see a number of screenshots showing performance benchmarks of different server machines and how long it took them to perform certain tasks. Those metrics were collected using our own Punisher tool. You can read up on how it works here: https://github.com/soliantconsulting/fm-punisher.
Performance benchmarking across different servers, operating systems, and FileMaker Server versions only work if you let those servers perform the exact same tasks every single time. Punisher abstracts out the other variables that would have an unwanted impact on performance, such as client-side horsepower, network latency, and throughput, caching, etc. It focuses the testing on nothing but the server resources: processing power, disk i/o, and memory.
The results from Punisher are shown in a pivot table. Each row represents a particular test script running on a particular test server. In the example below, two scripts are shown that ran on an AWS t3.2xlarge instance type, which has eight cores.
Each column represents a test run with a number of concurrent Perform-Script-on-Server (PSoS) sessions, running on a specific FileMaker Server version on a given operating system. The Performance indicator (true/false) indicates whether FileMaker Server had the 19.1 performance enhancements features turned on (true) or off (false).
The cells then represent the number of seconds it took to complete each script execution. Underneath each number is a visual bar-chart representation of the number to more easily grasp the relative difference.
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.
Purge Temp File on Server
The temp files that FileMaker Server uses can grow over time to the point that they could become unwieldy and negatively impact your server’s performance. A server reboot will take care of that, but that obviously has a negative impact on your server’s uptime and your solution’s availability to your users.
In FileMaker Server 19.1, we now can – and should – create a schedule to let FileMaker Server purge its temp file. The frequency with which you do this will depend on the nature of your deployment and your solution. On most deployments, we will want to do this daily, during the time when the usage is at its lowest. The schedule does not interrupt any user connections, but given the nature of disk i/o, it may impact the performance of other running operations.
In the Admin Console, create a new schedule, pick System Script, and give your schedule a meaningful name.
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.
When you use FileMaker Pro / Go 19.1 and FileMaker Server 19.1, any sorting you do on big data sets may take significantly less time than before. The ‘may’ in the sentence is because the client and server will negotiate, and the server will take on the task if it can. That means that the exact same script or user action involving a sort may sometimes cause the sort to occur on the server and sometimes on the client.
Our Punisher tool has a table with 125,000 records. One of our test scripts sorts that data on three fields:
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.
In addition to sorting server-side, sorts can now also happen on multiple threads. In any version of FileMaker up to now, sorting always happened on one thread. This will still be the default behavior, but we can now enable an option to have server-side sorting utilize multiple threads by adding the ThreadedSorting flag to the config file. Similarly, we can add the SharingLock4Pro flag to the client config file for client-side sorting.
Unlike the DisableServerSideSorting flag, which disables the new server-side sorting feature, these two flags enable the multi-threaded sorting feature.
In-memory multi-line key resolution
A ‘normal’ relationship relates a single value in the primary key on the parent record to the same value in the foreign key field in child records.
Something like this:
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.
Normal Temp file
While not strictly a FileMaker Server feature, how the client manages its temp files and cache certainly does affect the user’s experience when working with a solution hosted on FileMaker Server.
Since FileMaker 15, the client keeps a persistent cache stored in the DBCache folder1. The aim of this cache is to speed up opening the solutions by avoiding rebuilding the temp file cache every time, something that is especially relevant for WAN connections and when using low-powered devices such as phones and tablets. Before version 15, the temp file with the cache was discarded every time your solution was closed.
In FileMaker Pro 19.1, you can now choose which of the two behaviors you want to use. The default remains to store a persistent DBCache, but by creating the DebugReleaseOn.txt file at the client and adding a line with NormalTempFile to it, you will go back to the pre-15 behavior; FileMaker Pro will work with a cache file for the duration of the session and discard it when the session ends.
There is no simple rule to determine which of these two choices will result in a more performant solution for you. This one depends to a very large extent on the design of the solution and, to a lesser extent, on the quality of the network connection between the client and the server.
If the TopCallStats.log on your FileMaker Server reveals that a lot of time is spent on comparing modification counts operations when a user logs in, then you can certainly experiment with this setting to see if it makes a difference while you work on adjusting the architecture to avoid running into these operations.
1 – DBCache location:
macOS: [User Account] -> Library -> Caches -> 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.
FileMaker Server 19.1 brings some massive under-the-hood changes that will take some time to fully understand and test so that developers can make the best possible use of them. These new features allow us to better utilize a server’s capacity and optimize FileMaker performance. If you have any questions, please feel free to reach out to our team.
7 thoughts on “FileMaker Server Performance Improvements in 19.1.2”
Great article. Thanks Wim.
Really appreciate the depth of insight and well articulated impact! With almost 100 active users on one of our databases, I’m very interested to compare our Zabbix time/call before and after upgrading to 19.1.2. And I hadn’t even noticed it in release notes, but the ability to view all remote calls could be a real game changer.
Thanks for this article.
I did not find this information in the Claris documentation
Where should the DebugReleaseOn.txt file be created on the client?
I would like to test the NormalTempFile function.
On the client you put the file in the same folder as the FMP application itself.
Better late than never, me! Thank you for this, Wim; very interesting…