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.

Ian Jempson

Ian has an impressive resumé amongst FileMaker developers and is an internationally recognised authority on all things FileMaker and is frequently invited to speak at industry events. In his 15 years of experience he has developed award-winning custom database systems as well as software products that are in daily use by tens of thousands across industries as diverse as investment banking, recruitment, events management, advertising and education In his role as Development Manager, Ian handles our increasing project management requirements. Certified in FileMaker versions 8 through 12, Ian is also well versed in other database platforms which drives him to constantly push the boundaries of what is possible with FileMaker Pro.

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