12 Replies Latest reply on Dec 10, 2016 3:24 AM by gdurniak

    FMP Performance Issues = Crashing, Cache, SLOOOW?

    emirii

      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):

      ff69069c0cf2d0120e88fe0bdd93ed3d.png

       

      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.

      ff69069c0cf2d0120e88fe0bdd93ed3d.png

       

      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.

        • 1. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
          Vincent_L

          Not sure It will fix your issues (but it can't help a lot), but do yourself a favor : PUT A SSD* (and with you comment on disk usage, plus 152 MB/s  max throughput (which I guess is a backup)), it would definitely help)

          Hard drives are for your worst enemies, and that's cruel !

           

          Also, I think it's obvious, but you don't have backups with integrity checking I hope.

           

          * If you can put a PCIe NVMe SSD that would be even better. And please don't believe the non reliability SSD bulshit. You can write petabytes on nowadays like samsung 850 Pro.

          1 of 1 people found this helpful
          • 2. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
            Vincent_L

            and also don't forget clients, unfortunately Filemaker is also very dependent on client's speed.

             

            oh, and by the way, run Geekbench 4 and tell us your score, for you setup I wouldn't run it with less than 3000 score in single core test.

            1 of 1 people found this helpful
            • 3. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
              wimdecorte

              emirii wrote:

               

              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).

               

              The rule is to set the cache so that you can get a consistent 100% cache hit score.  You never need to set it so that all of the database fits in cache; that's not how it works.

               

              Plenty of deployments have files much bigger than yours so I'm not sure where you got the impression from that most of the posts have to do with files smaller than 1GB.

               

              The size of the files is not so relevant in and by itself.  The design of the solution and how the server is deployed need to be looked at at the same time.  For instance: if you have 20 users all doing searches on unstored calcs on machine with not enough processing power then it will fall over.

              Or if you have an aggressive backup schedule on a server with slow hard drives then you will suffer.

               

              Some of the stats that you have in your screenshot show both very high 'elapsed time per call' and very high 'i/o time per call'.  That seems to indicate that you have some very expensive design choices going on in your solution AND that the server is not fast enough when it comes to the hard drives.

              I very much doubt that cache and memory have any effect for your symptoms.

              1 of 1 people found this helpful
              • 4. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                emirii

                Thank you all for your responses. So very sorry for the late reply. The holidays and busy with a new release!

                 

                I have ran Geekbench 4 on the virtual environment running FM Server. Results.. not good.

                Xen HVM domU - Geekbench Browser

                 

                The odd part is we don't really see any spikes in CPU usage after adding more cores to the stack.

                 

                We also had a few "ghost" records, which we re-indexed the file and got rid of them.

                 

                We do have a lot of calculations and large layouts with a lot of fields and portals that are used by up to 5 people each at a time constantly thru the day. (around 6 departments, all using their own large layouts).

                 

                During considerable slowdowns we have been trying to pinpoint and track the issues, but can't really find any noticeable bottlenecks in the hardware (CPU is hovering around 20%, HDD usage is not maxing out very often (it spikes), memory is about 50% usage).

                 

                The only indication of the problem seems to be the Wait Time, I/O Time, Elapsed Time, etc. All of our computers on the client end are not much older than 3 years running on Windows 10, all with at minimum 4GB memory, some i3s, some i5's and a few i7's.

                • 5. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                  wimdecorte

                  emirii wrote:

                  Xen HVM domU - Geekbench Browser

                   

                  The odd part is we don't really see any spikes in CPU usage after adding more cores to the stack.

                   

                   

                  Why is that odd?  What is your expectation?

                  1 of 1 people found this helpful
                  • 6. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                    wimdecorte

                    emirii wrote:

                     

                     

                     

                    During considerable slowdowns we have been trying to pinpoint and track the issues, but can't really find any noticeable bottlenecks in the hardware (CPU is hovering around 20%,

                     

                    I think it was mentioned before on this thread (and certainly on other threads).  Measuring the CPU usage on the OS/hardware level is not a very relevant indicator of whether your are suffering from lack of processing power.   Especially not if you have many cores but the design of the solution is such that FMS can't really off-load the tasks to multiple threads/cores.

                     

                    The FMS stats log is the only true source of performance on the 4 potential bottlenecks.  Some pain-points you can solve by throwing more hardware at it, but sometimes you can't because of the nature of the solution design.

                    1 of 1 people found this helpful
                    • 7. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                      philmodjunk

                      Seems to me that upgrading to Server 15--not an upgrade to decide on lightly, would allow you to use top 25 logging and that might really help isolate the major causes of some of your performance "hits" on the server.

                       

                      We've also used a development server (before the release of FMS 15) with a back up copy of the files and ran suspect scripts or batch update type manual operations while watching the server stats in the admin console. Since we were the only user, running a suspect script or other process that produced an immediate "spike" in the server stats helped us identify and eliminate a few culprits.

                      1 of 1 people found this helpful
                      • 8. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                        emirii

                        Your 2nd post answered my first post! I would expect the CPU usage to be high if it was a CPU bottleneck, but since it is a virtual environment I'm not 100% sure it works like that.

                         

                        According to your 2nd post, then our problem is our solution it's self which seems more likely. We started out with a single flat database and it grew into a very large quirky system of many relationships and some tables even have millions of records in them!

                         

                        Thank you very much for your input.

                        • 9. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                          wimdecorte

                          emirii wrote:

                           

                          Your 2nd post answered my first post! I would expect the CPU usage to be high if it was a CPU bottleneck, but since it is a virtual environment I'm not 100% sure it works like that.

                           

                          Virtualization has little to do with it.  You'd see the same behaviour on a physical server.

                           

                          Since you mention high Wait Time and Elapsed times it shows that FMS is struggling to get through the tasks but the design of the solution is such that FMS can not take full advantage of the available cores to get through it.

                          You could consider using PSoS and server-side schedules for some of the heavy lifting.  And/Or using faster processors.

                           

                          There is a disk i/o impact too given that both Wait and I/O are high.  You mentioned 10K rpm drives but not sure about the RAID.  There is definitely some speed to be gotten there so do find out what the RAID config is (and the whole disk setup in general; is it a SAN and if so what is the connection to the physical host?) and what other disk intensive things are done by the host server that runs the FMS virtual instance.

                          1 of 1 people found this helpful
                          • 10. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                            taylorsharpe

                            philmodjunk's recommendation to upgrade to 15 is a pretty good one and I think 15 is a little faster than 14.  I do a fair amount of cleanup of other people's databases to improve performance.  A lot can be done to improve performance by minimizing unstored calculations, making sure only calcs necessary in a layout, improving schema relationships, making use of PSoS which tend to work faster on the server, avoid scripts jumping around layouts, getting certain big projects to perform on layouts with nothing in the layout (not fields, no objects), using form view to load only 1 record when possible (especially during scripting), using popovers to pull up related data only when needed, avoiding filtered and sorted portals, making good use of ExecuteSQL, using Virtual lists, etc.  (there are more, that is just off the top of my head for now).  I've had databases with a couple thousands records that became sluggish with poor development.  And I've worked with one with 83,000,000 records that was very lean that worked just fine. 

                             

                            While good hardware and latest software clearly will help (especially fast storage), it all can be for vain in that a poorly designed solution will bring the best hardware to its knees.  Yes, go for the best hardware and latest software, but focus on the solution to improve performance. 

                            1 of 1 people found this helpful
                            • 11. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                              emirii

                              I am in the process of using my Developer License to update my DEV server to FM15, to play around and benchmark using the new "expense" statistic.

                               

                              I will use this to see what we can do. The layouts and the scripts are most likely the main issue here along with some less-than-stellar relationships. PSoS is a very interesting feature that I'm not sure it was known by the people who created the scripts, but something I will suggest.

                               

                              I really appreciate everyone's advice, hopefully we can go somewhere with this. Thank you!!

                              • 12. Re: FMP Performance Issues = Crashing, Cache, SLOOOW?
                                gdurniak

                                Not sure this question was "Answered",  but you do have a lot of work to do

                                 

                                File size is not the issue.  It just amplifies the bottlenecks

                                 

                                I just had a layout "hang",  and found that it depended on 15 calculations to display,  many un-stored

                                 

                                Putting that Layout in it's own Window helped greatly,  since FileMaker did not have to re-draw completely each time the Layout was required

                                 

                                greg

                                 

                                 

                                > Layouts will "hang" the program, causing either waiting circle or white box