A quick case study in FileMaker scaling

Recently we’ve been looking at how well FileMaker scales. Typically we need to consider this when faced with large volumes of data or high loads caused by user demands on the system.

One of the recent instances where we’ve have to closely examine performance was with a call centre client. The system has a large volume of records which are handed out to the sales people in accordance with a reasonably complex set of business rules. The level of complexity of the rules meant that the key relationship at the heart of the call dispatch functionality had seven predicates and to further complicate matters it was sorted by four criteria. This data was also dynamically being updated on a constant basis – every few seconds records are being added and updated. The system enabled very quick growth in the number of sales agents. As the number of users grew we had to deal with the system performance dropping off rapidly.

Investigating the speed issues led us to the conclusion that we were facing record locking problems. These were causing the system to slow dramatically as the server tried to resolve the deadlocks. We isolated the single script step that was causing the issues. When the system was busy we timed this single Go To Related Record step at 1.5 seconds! No wonder there were slowdowns. We found the server wait times were increasing as a function of the number of database users and the number of records in the Queue. The system would enter a vicious circle as the server struggled to keep up.

The following screenshot clearly shows the long wait times.

We re-architected the system to use MySQL as the data store for the call queues. This uses a combination of FileMaker External SQL Sources (ESS) and calls directly to MySQL stored procedures from FileMaker. Data viewed by users is coming through ESS. Direct calls to MySQL stored procedures are used for determining which record is the next in the queue and other processing. The Go to Related Record script step that took 1.5 seconds was replaced by a call to a stored procedure that found the correct record, flagged it as in use, and returned it in a matter of a few hundredths of a second.

Here’s the screen shot of the server stats under the new method:

That’s better! The impressive thing is that wait times are now usually very short. The server is not making people wait – it’s busy working.  Those spikes in elapsed time are when a couple of the users are doing big data imports. We’ve not yet optimised those and that’s next on the list.

Overall the change was relatively easy to make. There were no major problems or gotchas. One thing to keep an eye out for when making this kind of change is the use of multi-keys in the filemaker system. These need to be revised in MySQL as multi-keys are a FileMakerism that won’t translate well.

The most important thing in all of this is that the call centre is much more efficient than before and the number of calls they’re making is much higher than was possible previously.

Simon Ward

Simon has been developing FileMaker databases since the late 90s and joined Linear Blue in 2006. Over this time he has developed database systems for clients in many different industries from Order Processing to Book Publishing. Simon’s BSc in Chemistry from Thames Valley University comes to the fore in his analytical skills and he is certified in FileMaker versions 7 through 12.

More Posts - Website

2 thoughts on “A quick case study in FileMaker scaling”

  1. Thanks Ian,

    something I don’t understand “We re-architected the system to use MySQL ” you mean, that you used a real MySQL server, or that you used the internal Filemaker functions to fetch filemaker data ?

    1. Hi Vincent

      We’re now using a MySQL database as the data source for a couple of the tables – those having to do with managing the call queue. These are accessed through ESS and also through MySQL Stored Procedures.

      Additionally we are using FileMaker’s internal SQL for user dashboards, reporting and data imports. For reporting and dashboards it’s quicker and easier to develop using FileMaker’s SQL than it is to use traditional FileMaker methods. In the case of data imports we’re getting more flexibility through the use of internal SQL, though this does require the use of a plugin for the inserts.

Leave a Reply