FileMaker 19.1 Performance Improvements Using Sharing Locks and Feature Flags

If you are using FileMaker Pro or Server 20.3 or later, please read this updated blog post for updated insights and instructions.

The FileMaker 19.1 release includes several significant performance improvements which are all made possible by a low-level change in how FileMaker databases are accessed by FileMaker clients and FileMaker Server.

Enabling Feature Flags

These performance enhancements can be toggled on and off. The toggles, referred to as feature flags, are controlled using a config file.

Some features operate on the client only, some on the server only, and some operate on both. Some behaviors are enabled – and others disabled – by default. To change the default behavior for a feature, you will need to add its feature flag name (for example, “ThreadedSorting”) to the config file of the client or of the server (or both).

Note that if you want to change a particular client-side behavior for all clients, you will need to add the feature flag in the config file on each client device – there is no one central place that governs client-side behavior for all clients.

The config file must be named ReleaseDebugOn.txt, and it must be located in the following place:

• FileMaker Pro: Application folder
• FileMaker Go: Documents folder
• FileMaker Server: “Database Server” or “Database Server/bin” folder

For FileMaker Server, for example, if it was installed in the default location, the folder would be:

• macOS: /Library/FileMaker Server/Database Server/bin/
• Windows: \Program Files\FileMaker\FileMaker Server\Database Server\
• Linux: /opt/FileMaker/FileMaker Server/Database Server/bin/

Multiple flags can be specified by placing each one on its own line. Make sure to use line feeds as separators for macOS and Linux. The config file should be set up to use UTF-8 file encoding.

After making a change to the config file, the application will need to be restarted for the change to take effect. If making this change on the server, the database server will need to be restarted.

Sharing Lock

Most of the performance improvements introduced in FileMaker 19.1 rely on a new feature that allows files hosted by FileMaker Server to be accessed by multiple clients concurrently, so long as the actions performed by those clients are read-only.

Prior to FileMaker 18, only one client was allowed to access a hosted database file at a time. FileMaker 18 introduced page-level locking, which made the lock obtained by a client more granular – instead of locking the whole file, only a page (a much smaller portion of the file) was locked. This allowed multiple clients to access the overall file concurrently, and as a result, many operations, like searching and evaluating relationships, performed much faster for files that are regularly accessed by more than just a few users at a time.

But the page-level locking change also had the effect of making FileMaker Server less stable. So, with FileMaker 18, Claris released an accompanying feature – Startup Restoration – which was intended to mitigate the increased instability. Unfortunately, Startup Restoration has not worked as reliably as it needed to, so with FileMaker 19.1, Claris has removed Startup Restoration (and with it, page-level locking). In its place, Claris has rolled out a different approach to achieving the kinds of performance gains that FileMaker 18 showed were possible.

Instead of locking segments (pages) of a file, FileMaker 19.1 locks the entire file – so far, that’s the same pre-18 behavior. But for read-only operations, the lock can now be shared by multiple clients – referred to as a sharing lock. Since most of the activity in a file consists of read operations, this change has the potential to significantly speed up FileMaker apps when they are accessed by multiple users.

The sharing lock will be enabled by default. To disable it, you can include the DisableSharingLockOnServer flag in the config file that is located on the server. Because this new feature involves a foundational, low-level change to FileMaker Server, a cautious approach would be to disable it for the first few months until others in the FileMaker community report that it’s working as expected.

Searching

By far, the most common FileMaker operation is a query. A query is what happens when a user (or a script) performs a find or when a relationship is evaluated (which happens whenever a value from a related field is accessed).

Since queries are read-only operations, multiple queries can now be performed in parallel – with one caveat: only indexed fields can be queried in parallel. (Typically most queries involve searches against indexed fields.)

Unlike the remaining performance improvements that we’ll cover below, this enhancement cannot be turned off by itself. Enabling (or disabling) the sharing lock feature will automatically enable (or disable) this behavior as well.

Sorting

Another very common FileMaker operation is sorting records. To date, FileMaker always transferred records to the client to be sorted, and the sort operation was always performed on a single thread. We now have greater control over both of these parameters.

With the 19.1 update, FileMaker will now sort records on the server when possible. Note the “when possible” caveat. The operation is not guaranteed to take place on the server, although it probably will in most cases. The server will use an algorithm to determine where the sort should take place. The decision will be based on a number of factors, including the availability of server resources at the time of the operation. To disable this new behavior for all clients, add the DisableServerSideSorting flag to the config file on the server. To disable it for just a specific client, add the flag to that client’s config file.

Once the server has decided where the sort will take place, the server (or client) can now perform the sort using multiple threads. This is controlled by two flags: ThreadedSorting tells the server to use multiple threads for sorts that happen on the server, and SharingLock4Pro tells the client to do the same. (Note that the ThreadedSorting flag will be ignored if the DisableServerSideSorting flag was also set.)

Multi-key Indexing

FileMaker evaluates relationships that are based on a multi-key field (a text field with a list of values instead of just a single value) differently from other relationships. FileMaker first decomposes the list of values in the multi-key field into separate values so that each one can be indexed. Only then can the relationship be evaluated. Prior to 19.1, FileMaker indexed these multi-key field values using a temporary file. With 19.1, the decomposed keys will be stored in memory, making the key comparison operation faster. To disable this new behavior, add the DisableMemoryKeyCmpIndexing flag to the config file.

Using a Temporary Cache File

FileMaker clients use a cache file for every hosted file they connect to. As the client interacts with the hosted file, record data (along with some other metadata) from that file is downloaded to the cache file. This way if a record is accessed a subsequent time, it doesn’t need to be retrieved from the server again (so long as it hasn’t been changed by someone else in the meantime).

FileMaker 15 changed how these cache files worked. Prior to 15, each cache file was treated as a temporary file and was deleted after the connection to the hosted file was closed. You could observe all this happening by going to the folder one level up from the Get(TemporaryPath) folder. Starting with 15, the cache files were made persistent, so that the next time the client connected to the same hosted file, the previous cache file could be reused. This worked well in most cases, since not having to redownload record data sped up the overall performance. It used to be a common refrain among FileMaker users that the solution is very slow when it’s first accessed, but then it speeds up over time. We no longer hear this complaint, and that’s due to the persistent cache file.

But there are some situations where having a persistent cache file causes problems. If the nature of your solution is such that users access large sets of records that are routinely modified by others, then those records will always have to be redownloaded to the cache file. To determine if this needs to happen, the client will compare what it has in its cache file to what the server has in the hosted file. This comparison operation can take a while. All this can be seen in the top call stats log as “Compare Modification Counts” and “Download Lists” operations. In this kind of scenario, starting with an empty cache file might be preferable.

Another situation where it can be useful to use a temporary cache file is when you need to measure the performance of certain actions. If you want to measure how long it takes a user to go through a certain workflow, that duration will be impacted by how extensively the client was able to rely on the cache file. For example, maybe some of the records were not yet in the cache file during the first test run, and so the average duration will be skewed as a result.

Prior to 15, a common approach was to remove the temp file – this was done by closing the file and, for good measure, restarting the client – and then connect to the hosted file and perform the task in the workflow. After a few runs like that, you could collect the average duration. This way, you could be assured that the temp file didn’t play a role in your measurement, since it was always empty at the start. But after 15, this technique wasn’t an option anymore.

19.1 makes it possible to once again use a temporary cache file. This can be done by adding the NormalTempFile flag to the client config file. Note that in most cases, you won’t want to do this. But it’s a good option to have for the aforementioned special cases.

Troubleshooting With Remote Calls

FileMaker 15 introduced the top call stats log which made it possible to see which remote calls had the biggest impact on performance. To learn more about remote calls and the top call stats log, see https://www.soliantconsulting.com/blog/filemaker-server-statistics-logging/.

The top call stats log was an important new troubleshooting tool, since it was the first time we had a view into how user actions were broken down into discrete operations, as well as how long each of those operations took to complete. 19.1 expands on this by making it possible to log all remote calls for a given client by adding the RemoteCalls flag to the config file used by that client. (As is the case with some of the other feature flags, the RemoteCalls flag can also be set for the server by adding it to the server’s config file. This way, server-side scripts can be examined to see what remote calls they generate.)

The remote calls will be logged to a xxxDebug.log file, which will be located in the same place as the feature flags config file (ReleaseDebugOn.txt). For FileMaker Pro and Go, the log file name will be DBDebug.log. For FileMaker Server, the remote calls will be logged to the log file that corresponds to the process used to execute the remote call; e.g. fmsDebug.log or fmsaseDebug.log.

Each remote call will be represented with two or three log entries. The first line will show the request that initiates the remote call. The second line will show the response along with how long it took for the response to be received. If a third line is present, it will show how long it took for the entire remote call to be processed and what the target of the operation was.

In the example below, the first line shows a request to perform a “Download With Lock” operation. The second line shows that the response was received in 0.8597 seconds. The third line shows that it took 0.8602 seconds to process the entire remote call and that the target of the remote call operation was a layout.

2020-09-29 09:16:56.706 -0500 [Main_0x11513b5c0] SendRC Download With Lock, op 90, for mislav.ets.fm, conn 686918, sess 1, tid 10, len 48
2020-09-29 09:16:56.792 -0500 [Main_0x11513b5c0] 0.85970(sec) RplyRC Download With Lock, op 90, for mislav.ets.fm, conn 686918, sess 1, tid 10, len 216, result 0
2020-09-29 09:16:56.792 -0500 [Main_0x11513b5c0] 0.86020(sec) DBFileUser::Download layout, key: 04.01

We can also see that the operation was executed on the main thread (“Main_0x11513b5c0”). That won’t always be the case. Here’s an example of an operation that was executed on a different thread and that was logged on two lines only:

2020-09-29 09:16:58.862 -0500 ExternalThread_0x700007627000 SendRC Download List, op 92, for mislav.ets.fm, conn 686918, sess 1, tid 20, len 54
2020-09-29 09:16:59.058 -0500 [ExternalThread_0x700007627000] 0.196422(sec) RplyRC Download List, op 92, for mislav.ets.fm, conn 686918, sess 1, tid 20, len 174, result 0

Turning on remote calls should be done judiciously, for short periods of time, and for one (or very few) users at a time, since this will likely have its own significant performance impact.

The general approach to diagnosing performance issues involves identifying the problem resource (CPU, RAM, disk, or network) and the problem client(s). Spikes in the Stats.log file will identify the problem resource, and the ClientStats.log file can be used to hone in on the problem client(s). The next step after that used to be to look at the top call stats log to identify the problem operation(s), and there may still be a benefit to doing that. But with 19.1, we can now go directly to the problem clients and turn on remote call logging specifically for them for a period of time. This will give us a list of all remote calls (not just the most expensive ones, as was the case with the top call stats log) generated by those clients during that time. This will give us a much more granular view into the activity performed by and on behalf of those clients.

This new capability has great potential, but using it (and the other tools) to troubleshoot will still be a bit of a chore. Some of the work that will need to be done each time:

• Enable the flag in the config file on the client
• Restart the FileMaker client – this could be done using the Exit Application step, but some coordination with the user will still be required to minimize the disruption to their work
• Wait while the remote calls are collected in the log file
• Remove the flag from the config file
• Restart the client once again
• Upload the log file
• Examine and interpret the log file to identify problem operation(s)

In spite of the work involved, having this new capability will make it possible to have a much more fine-grained view into performance bottlenecks, so we’re very happy to be able to add it to our toolbox.

11/2/20 update: It seems that the ForceOutput flag (see next section) has to be included in the config file along with the RemoteCalls flag in order for the remote calls to be logged. Thank you to Rhett Finch for pointing this out.

Force Output

The final feature flag to discuss is ForceOutput. It writes additional debug information to the xxxDebug.log file, such as confirmation of which flags have been enabled. For example:

Debug::DumpBitSettings(): feature enabled: AlwaysPrint
Debug::DumpBitSettings(): feature enabled: Assert Debug::DumpBitSettings(): feature enabled: ForceOutput
Debug::DumpBitSettings(): feature enabled: RemoteCalls
Debug::DumpBitSettings(): feature enabled: ThreadedSorting
Debug::DumpBitSettings(): feature enabled: NormalTempFile
Debug::DumpBitSettings(): feature enabled: SharingLock4Pro

Note that there are some flags listed above that are not set in the config file. Those seem to be features that are enabled by default and are not configurable.

The debug information will be written to the same xxxDebug.log file that is used for remote call logging (explained above).

Feature Flags Quick Reference

Here’s a summary of all of the feature flags:

We are including columns for where the feature flags can be set: client or server. This information is based on our testing and our understanding of how these features are intended to work. However, please keep in mind that as of the writing of this blog post, these features exist without formal documentation, so the information shown here might later prove to be inaccurate.

FM19.1 Performance Improvements

Demo File

Setting feature flags on clients cannot be done globally; for example, by toggling a setting in the admin console. Instead, this work has to be done separately on each client where you want the change to take effect. For this reason, it will be helpful to have a scripted way of making this change.

The accompanying demo file provides you with a script that creates the config file in the correct place (or deletes an existing config file, if you want to remove all of the feature flags). You can use this script in your own solution to make it easier to turn on or off feature flags to achieve better performance or facilitate troubleshooting. This script can be used to set the config file on clients, but it cannot be used to do the same on the server, because the server script engine does not have write access to the appropriate folder. When making a change to the config file on a client, keep in mind though that FileMaker Pro/Go will need to be restarted afterward in order for the change to take effect.

Note: The login for the demo file is demo/demo.