In my second performance-related interview at FMTraining.tv, I talked with Johan Hedman from Square Moon, who has got a great experience getting the most out of FileMaker Server by balancing the use of all its engines at once.

 

During our discussion, Johan shared insights on how FileMaker handles user sessions, the challenges of dealing with too many user sessions trying to do the same thing, causing troubles to other parts of the whole solution. Our conversation provided invaluable insights into FileMaker optimization, offering a deep dive into the nuances of balancing FileMaker Server's various engines effectively.

As usually, recording of the interview is available on the FMTraning.tv YouTube channel and you can watch it below:

 

For the case you prefer reading over watching videos, here's a brieaf recap of what we discussed. If you want to dive deeper into the topic, Dimitris Kokoutsidis has published even more comprehensive summary with some of his own explanations and elaborations worth considering.

 

Understanding FileMaker user sessions

During our conversation, Johan explained the importance of understanding how FileMaker handles user sessions. Each user session operates independently, meaning that every user connected to FileMaker Server has a distinct environment with its own cache, context, and state. This isolation can be beneficial for security and data integrity but also requires efficient resource management to avoid unnecessary strain on the server.

It is worth noting that different APIs/engines utilize different channels to access FileMaker data so not all resources are shared and leveraging multiple channels simultaneously can help you to get more out of FileMaker Server. Fore example, different server processes can utilize different CPUs, but each one can only utilize a single CPU.

Clay Maeckel revealed some architectural details in his Claris Engange 2024 session, along with the info on how OData is currently going through the XML interface but getting its own direct access to the database engine in the future. 

 

FileMaker Server has different connection limits for different engines and exceeding them can cause unexpected disruptions. As an example, Johan mentioned that 10 concurrent ODBC clients could stall FileMaker Server completely. Johan recommended leveraging FileMaker Server’s built-in logging tools to track session activity, diagnose inefficiencies, and optimize resource allocation.

 

Workarounds for resource-leaking bugs

Server's performance can get also degraded (especially over time) by bugs, such as memory leaks or failing cleanup procedures. As an example, Johan talked about an issue that prevents FileMaker Server from getting rid of temp files created for CWP/Data API sessions. He discovered that after exceeding certain threshold of concurrent sessions, FileMaker Server starts losing track of which sessions are still active, and failing to disconnect some obsolete ones. Some of their temp files can be purged by regularly executing maintenance scripts introduced in FileMaker Server 19.1.2 but some still remain and need to be deleted manually.

 

Johan shared a striking example of how easily the Data API can crash if this is not properly handled: "If you're really pushing it hard, if you have one Data API user doing multiple calls at the same time, unfortunately, you can crash the Data API engine in like two or three seconds. If you instead ask for unique Data API tokens, then you can have between 10 to 25 calls per second running. Not a lot of solutions are built that way, but if you have more than 25 calls within a second, then the FileMaker Data API would go down as well even though they have unique tokens."

As a workaround, Johan's team created a connection manager that leverages FileMaker Server Admin API and forces old CWP/Data API sessions to be disconnected. Unfortunately, there is no way through the API to distinguish between idle and still-active sessions, so this mechanism can sometimes interrupt longer queries. So they also use Zabbix to monitor activity of each client. Johan mentioned he wishes there was a way to set an idle timeout not only for FileMaker Pro and WebDirect users, but for all different kinds of sessions, separately for each session type.

Square Moon also built their own proxy to dispatch all incoming requests and ensure there are no more than 25 requests per second sent to FileMaker Server to handle. In addition to avoiding the overload, the proxy can also dynamically dispatch requests between different engines, such as CWP (via RESTfm), Data API, OData, ODBC, JDBC or XML, as well as cache the responses for the cases that exactly the same request arrives from different clients.

 

Diagnosing and troubleshooting performance issues

In the final part of our interview, we discussed best practices for troubleshooting and diagnosing performance issues in FileMaker. Johan recommended using server-side logs, such as the Top Call Stats, monitoring scripts, and leveraging FileMaker’s built-in diagnostic tools to identify inefficiencies, paying extra attention to every occasion when the spinning wheel on Mac or coffee cup on Windows appears to the users and understanding what FileMaker Server is doint at that time.

He also highlighted the importance of proactive testing and refining system architecture as user demands grow. By continuously evaluating system performance and making necessary optimizations, developers can ensure their solutions remain scalable and efficient over time.

We both agreed on the importance of avoiding any database schema changes in production environment as committing even a seemingly simple change can immediately stall all users for a long time.

Overall, our conversation provided invaluable insights into FileMaker performance optimization, and Johan's expertise helped shed light on the many nuances of balancing FileMaker Server's various engines effectively.

 

Different ways to get more out of FileMaker

Just as Wim Decorte showed how to "punish FileMaker Server for having a scripting engine" (read as "test what the scripting engine can handle") in my previous interview and Johan showed us all that we get get more out of FileMaker Server as whole by connecting to it through its different engines, in my next interview Vince Menanno explained how you can leverage locally stored FileMaker file to even avoid asking FileMaker Server too much completly.

However, I cannot resist to notice that however these three interviews seem to cover completely different approaches to maximizing performance of FileMaker solutions, they do have one thing in common. They all still do rely, to certain level, on the FileMaker calculation engine. Wim's Punisher paradoxically needs it to be at least a bit slow as it's an easy way to make the scripting engine busy (but I am sure Wim would not complain for having to update it if the calc engine got much faster), Vince's local editing files and the update-handling server-side script rely on calculations heavily, and Johan's way to touch a lot more data records per second than usual triggers every single unavoidable calculation so often that even every millisecond matters.

If you agree then make sure to check Initiative '24 and support our endeavor to convince Claris to make the FileMaker calculation engine significantly faster for everyone.