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.