We are having a multiple point issue with our Filemaker database.
We have a large solution hosted on a server that is partitioned into 3 virtual server OS's running Windows Server 2012 R3. We have eliminated server and network bottlenecks by increasing CPU rations to our FMP Virtual Server and creating a LAG between our server and our network switch. We were seeing CPU queue times and memory spikes before, but those have since disappeared since solving those issues. However, even with the above fixes we are still experiencing many issues with our database.
FMP Server Specs (VM):
FMP Server 14
14 Databases Total
- Our largest Database is 6GB (thousands of fields, hundreds of tables, hundred or so layouts, around 8-10 years old)
- 2nd largest is 4GB (around 6 years old, hundreds of fields)
- 3rd largest is 2GB (it stores the external files for our largest database)
- a few under 1GB (misc logs/report dumps/etc)
6 Virtual CPU's (6 sockets with 1 core per socket)
12GB RAM (shared, but FMP VM currently uses around 40%-60%)
300GB Storage, 10,000 RPM HDD (unsure about RAID configuration)
FMP Server Settings & Statistics:
We have an upcoming PHP solution that is using minimal data and has it's own layout with fields it needs, not really seeing issues with these layouts or the PHP connection.
We've been playing around with the server cache settings, we have tried setting it to 1GB, 512MB, 756MB. We read that less is better as long as your cache hit % was 95-100, though we saw a large decrease in performance when we put our cache to 512MB, even though the cache hit stayed at 100%, with no unsaved 0%.
Statistics (I have attached this as well):
We have anywhere from 12-50 clients connected at any given time on their own FMP Desktop. Everyone is using Filemaker Pro 14. We often see "Elapsed Time" skyrocket for every clients, not certain ones. They seem to rotate on/off.
FMP Client Side:
Layouts will "hang" the program, causing either waiting circle or white box. Once the program resolves it's self, it seems to be Ok for a little while, then hangs again. Sometimes (rarely) it just crashes the program.
The coffee cup sometimes appears when loading layouts/lists
FMP Server Side:
Large disk usage when sorting large tables (expected)
Slow FMP Server performance overall, but not visibly using very many resources
Searching on these forums, a lot of the solutions to many of these problems seem to apply to small databases under 1GB, with less users than we have. I can't really find anything specifying what's best for a large database like ours. For example, one person said lowering the cache will help with stability/performance, while an official article said that maxing out the cache is better, and another said enough cache "to fit the whole database in it", which in our case would be around 12GB (more than the max cache size allowed/100% of the server memory).
What would be the best practices in this situation? We are losing a lot of work hours over slow FMP Server/FMP Pro instances and just upgraded to FMP 14 a little under a year ago.