BlogFileMaker

FileMaker Server Performance Improvements in 19.1.2

By November 3, 2020 6 Comments

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.

Performance enhancements in all version

The previous set of performance enhancements introduced by version 18 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.

Sharing Lock (on by default)

This is the feature that should have the biggest impact on the greatest number of solutions. While the details for how the Sharing Lock works under the hood are very technical and deal with low-level access to the database, the long and short of it is that this feature allows more tasks to be run at the same time. The effect is most noticeable with operations that involve Finds (sometimes also referred to as Queries). In prior versions (ignoring Startup Restoration for the moment), all operations utilized exclusive locks, meaning that they had to be queued and then processed one at a time. Writing to the database in 19.1 still requires an exclusive lock, but read operations can take advantage of the new shared lock feature.

The effect on performance is dramatic. The results from Punisher show that our two test scripts that most heavily use Finds perform orders of magnitude faster when the sharing lock is on.

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.

Photo of script in Punisher parsing 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).

Photo of the ReleaseDebugOn.text file created and added to the Database Server folder in Windows

ReleaseDebugOn.text file added in Windows

Photo of the ReleaseDebugOn.txt file added to the bin subfolder on macOS

ReleaseDebugOn.txt file added in macOS or Linux

ReleaseDebugOn.txt file

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

Photo of 'DisableSharingLockOnServer added to the text file

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.

Photo of the ForceOutput line added to the text 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.

Photo showing System Script selected as Schedule Type under Script Schedule under Configuration area of the admin console

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.

Photo of Sys_Default_PurgeTempDB selected on the dropdown for the script name for the System Script under Script Schedules

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.

Server-side Sorting

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:

Photo of the Sort Records window

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.

Sort dialog in 19.0

Sort dialog in 19.0

Sort dialog in 19.1

Sort dialog in 19.1

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.

Photo of stats log in 19.0

Stats log in 19.0

Photo of stats log in 19.1

Stats log in 19.1

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.

Photo of the DBBug.log file that highlights that the DisableServerSideSorting flag was added

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

Threaded sorting

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:

Photo showing an example of a single value in the primary key on the parent record to the same value in the foreign key field in child records

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.

Photo of multi-line key relationships

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
Windows: C:\Users\<user name>\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.

Photo of log example from a prior version of FileMaker Server

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.

Photo of the Top Call Statistics warning

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.

Conclusion

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.

Wim Decorte

Wim Decorte

Wim is a Senior Technical Solution Architect at Soliant. He is a FileMaker 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 and 18 Certified FileMaker Developer and the author of numerous Tech Briefs and articles on FileMaker Server. Wim is one of the very few multiple FileMaker Excellence Award winners and was most recently awarded the FileMaker Community Leader of the Year award at the 2015 FileMaker Developer Conference. He is also a frequent speaker at the FileMaker Developer Conference and at FileMaker Developer groups throughout the world. In addition to being a renowned expert on FileMaker Server, Wim also specializes in integrating FileMaker with other applications and systems. His pet project is the open source fmDotNet connector class that he created.

6 Comments

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant