A new year has come and we all hope it will push us forward to better tomorrows. New year also deserves new performance test results, especially since Claris released FileMaker 19.2 just a few days ahead of Christmas. So I used some of the calm holiday time to prepare new sets of tests and discover some new interesting things...
Sorting on Host with FileMaker 19.2
There was no change announced for the sorting on host algorithm, so I did not expect any difference from 19.1, and my tests have confirmed that. As I revealed in my first results of testing FileMaker 19.1, even with 19.2 there can still be cases when the decision to sort records on host is not ideal, especially when working on slow network.
There is actually one change related to performance, however, which I have not been able to test yet, and that is the added support of HTTP/2 for WebDirect, Data API and XML publishing. There are still known issues on macOS, so it is disabled by default on Mac, but it’s supposed to significantly improve performance of web apps, especially those needing to process many subsequent HTTP queries to render a page, which is the case of WebDirect for sure...
New optimized test set
In my previous testing I usually worked with the same number of records for all tests in a set. Therefore I either had to test with small number of records, or wait huge amount of time (often days) for the whole test set to finish. To make it easier to perform the same set of tests on many more configurations, I have created a new optimized set of tests.
For all test, after testing imports, exports and deletes, I now load the main test table with 100,000 records to have some reasonable mass, and each of the following test rounds operates on a subset of these records. Size of each subset is optimized so that the test round typically takes between one and a few minutes.
Every test round is repeated in 5 iterations to make sure I get consistent results or notice when one result is affected by some unexpected temporary condition change. To make the test set take about similar time when executing in multiple concurrent scripts, I made setups for 5, 10 and 20 concurrent server-side scrips operate on smaller data sets where appropriate.
This way most of the tests are supposed to perform about the same amount of work, so they can even finish faster when multithreading allows for concurrent scripts to take better advantage of the hardware power. The whole test set typically takes between about 1 and 4 hours to execute, depending on the hardware and network setup.
CPU and disk reference speeds
I have also added a consistent CPU and disk operations speed check to be collected with every test result being recorded. These “standard” tests are designed to easily indicate when and how the test results are directly affected simply by running the test within more or less powerful environment.
The CPU speed check is something I actually added back in fall 2019 and it helped me to reveal how CPU credits consumption affects performance of FileMaker Cloud.
The disk speed check is based on the data files feature introduced in FileMaker 18, reading or writing 1 KB (1024 bytes) of data 20 thousand times in a loop. I was quite surprised revealing that writing to data files is actually faster than reading from them using this feature. Anyway, based on my testing (the chart above is actually based on 1721 measurements), these checks seem to be providing consistent results, proportional to the hardware speed.
Concurrent server-side scripts
Running multiple concurrent servers-side scripts, triggered by the Perform Script On Server script step is the easiest and most common way to test how FileMaker Server can handle simultaneous requests from multiple clients.
With a task that does not need anything but processing power, such as math operations, FileMaker Server seems to handle that as simply as assigning one processor core to each script session. In my test on Core i7 with 6 cores, 5 scripts done about same amount of work in 1/5 of time, while 10 scripts needed about the same time as 5 scripts. This confirms that the single script session was only using one core.
Read-only operations are supposed to be using shared locks in 19.1 and newer and pure Perform Find against an unindexed field seems to confirm that. The bars representing execution times in the following chart are primarily sorted by iteration number, then by session number.
Retrieving the actual data, however, using the List function in this instance, seems to be a slightly different story. Again, with values sorted by iteration and session number, the following chart seems to show that initial fetching of the data does not take advantage of a shared lock, until the records are copied to the specific script session’s local cache. Subsequent requests, reading data from each session's own cache, are already as fast as the hardware allows.
With or without blobs
The most interesting addition in the new optimized test set, however, is data set including blobs. I have borrowed the term from the SQL database world where BLOB actually stands for Binary Large OBject, which is typically an equivalent of a container field in FileMaker terminology. In my test solution, however, I have (a bit incorrectly) used it for “a text field containing a lot of data”.
To be exact, I am using a generated random “Lorem ipsum” text exactly 297840 characters long, out of which I am extracting a random substring between 1 and 297840 characters long into each record. To make the test results consistent, I am using pre-generated random substring positions and lengths.
As soon as I start including the “with blobs” versions in my tests, the results I get quickly demonstrate an important aspect of how FileMaker works with data. The fact that it always fetches or commits the whole record, including all fields, except for containers and unstored calculations.
This test was simply replacing value in a single field, with a constant, using the Replace Field Contents script step. The difference you need to notice is that the first two bars represent time needed to update 10 thousand records, while the other two bars represent time for updating only 2 thousand records.
So, when dealing with data in FileMaker, what often matters a lot, if not most, is the average size of a record. Do you have too many fields in your table? Do you store too much data in a field? Do you really need all those fields and/or data for every single operation you do with the table? Just asking yourself these simple questions can be your ticket to a huge performance optimization.
Meeting and community testing ahead
The year 2021 has just started, and so my performance testing has. As I am writing this article, further instances of the new optimized test set are running on various versions of FileMaker, as well as different server configurations.
I am getting my testing database ready to share with you to run the same test set on your own setup as well and compare your results with the others. I am also preparing one surprise, a test that will be closer to real use than any of my previous tests. And even this one will be available for you to try.
It seems to be a good time for another online meeting to share and discuss my fresh discoveries with all of you interested in FileMaker performance tuning. So mark your calendar for Tuesday, the 19th of January, and see you there…
