1 2 Previous Next 15 Replies Latest reply on May 10, 2017 4:43 AM by CICT

    VM Server Specification and FileMaker 16 Performance

    CICT

      We’re thrilled that FileMaker v16 has been announced today. This version has transformed the user experience on our RemoteApp cloud servers when accessing via Microsoft Remote Desktop for Macs due to the replacement of MDI with SDI. Also great to be receiving confirmation of previous bugs having been fixed.

       

      We’ve been waiting for the official release to publish some tests carried out to compare results of virtual servers of different specifications on the performance of our systems. The majority of tests were carried out using FileMaker Pro 16 and FileMaker Server 16, although we did do some comparative tests using FMP v15 connected to FMS v16 and FMP/FMS v15.

       

      The servers tested were AWS EC2 Windows 2012 R2 instances, the smaller server set to 4Gb RAM, 2vCPU and 80Gb SSD storage (t2.medium) and the larger set to 256Gb RAM, 64vCPU and 80Gb SSD storage (m4.16large).

       

      Our conclusions were:

       

      The configuration of a virtual server has virtually no impact on speed of FileMaker completing a single routine and in some cases the 4Gb/2vCPU server completed the test marginally quicker than the 256Gb/64vCPU. The same conclusion was reached when we carried out a few tests excluding ExecuteSQL.

       

      The sizing of a server should be based around the number of people accessing it rather than the complexity of the solution.

       

      There was no identifiable speed improvement between FileMaker v15 and FileMaker v16. Combinations between the different FileMaker Servers and FileMaker Pro produced almost identical results, with either  versions of FileMaker Pro occasionally outperforming each other.

       

      Running ExecuteSQL calculations for the first time after launch and then again during the same session produced results where the subsequent tests provided a variation in speeds of 230% to 3600% faster than the time taken for the first run (for example a test initially taking 00:01:48 would then take 00:00:03). We haven’t had a chance to compare the data sets since getting these results but assume this is down to the structure of the SQL used in each test. However, this did prove that ExecuteSQL will always run significantly slower on first run, regardless of server specification.

       

      For anyone using XenApp/RemoteApp or perhaps Remote Desktop, a disconnected user session will retain the faster ExecuteSQL speeds until the user session is logged out (or perhaps when the FMS server disconnects the user?). We’ll try to understand this better when we get some time to follow this up, we’re not sure whether this is FileMaker Pro or FileMaker Server dependent, possibly FMP caching?).

       

      Testing Methodoligy

      All FMP tests were from our streaming servers, therefore all processing took place within the cloud (rather than from a typical Internet connection). There would have been a small amount of latency between our infrastructure supplier (Hyve - UK) and AWS (Ireland). We know that similar tests within our own cloud infrastructure would have been faster (due to previous tests where we were comparing AWS, Rackspace and our supplier’s internal infrastructure). Running FileMaker locally will often negate any internal infrastructure speed advantage, as the slowest part of the connection is usually from the user to the Internet.

       

      The tests were not scientifically carried out, we had preset scripts to run routines on one of our insurance systems with a start and end time set to report the result. The majority of tests did include ExecuteSQL, so our results will not necessarily reflect all aspects of FileMaker’s performance. 5 separate tests were run twice: immediately after FileMaker Pro was launched and then again within the same session (our main focus was with ExecuteSQL). A total of 61 separate runs of these tests were carried out, with repeats to validate the results.

       

      The above has allowed us to be more confident when sizing servers for our clients and I hope it is of interest.

       

      Regards

       

      Andy

        • 1. Re: VM Server Specification and FileMaker 16 Performance
          jrenfrew

          Thanks Andy...

           

          john

          • 2. Re: VM Server Specification and FileMaker 16 Performance
            Vincent_L

            Running ExecuteSQL calculations for the first time after launch and then again during the same session produced results where the subsequent tests provided a variation in speeds of 230% to 3600% faster than the time taken for the first run (for example a test initially taking 00:01:48 would then take 00:00:03). We haven’t had a chance to compare the data sets since getting these results but assume this is down to the structure of the SQL used in each test. However, this did prove that ExecuteSQL will always run significantly slower on first run, regardless of server specification.

            Which is an absolutely terrible behavior, because most of the time you only need to run the query once, so 90% of real world usage is hampered by this first run tax.

            That could be fixed, support that idea ExecuteSQLonHost ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... })

            • 3. Re: VM Server Specification and FileMaker 16 Performance
              bigtom

              Vincent_L wrote:

               

              Which is an absolutely terrible behavior, because most of the time you only need to run the query once, so 90% of real world usage is hampered by this first run tax.

              That could be fixed, support that idea ExecuteSQLonHost ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... })

              This already exists with PSOS and it is one of the fastest ways to do one time queries.

               

              I also have many cases where the same query is repeated many times. I am not sure your 90% number is accurate.

              1 of 1 people found this helpful
              • 4. Re: VM Server Specification and FileMaker 16 Performance
                CarlSchwarz

                I have found that FileMaker Pro 16 connected to FileMaker Server 15 has dramatically increased the performance of one of my applications.

                This application has a VERY heavy layout with loads of conditional formatting and lots of data on it.

                 

                I can't wait to test this application with FMS 16 and FM Pro 16!  It won't be for a while though... I'm not a trail blazer, I prefer to wait for the v1 bug fix before I think about rolling out an update.

                • 5. Re: VM Server Specification and FileMaker 16 Performance
                  TimAnderson

                  Useful info, thanks Andy

                  • 6. Re: VM Server Specification and FileMaker 16 Performance
                    velistar

                    Very useful info, thank you Andy.

                     

                    I believe the configurations that you have used wouldn't make much impact on computations like ExecuteSQL. If I am not mistaken, FM16 still uses a single core anyway. Improved caching has been a thing from FM15 so no surprise there either.

                     

                    I would be very much interested to see testing on WebDirect with different user sizes on different configurations. I have no idea how this can be done scientifically. FM16 brings in up to 100users on a single machine which mind blowing compared to the previous version.

                    • 7. Re: VM Server Specification and FileMaker 16 Performance
                      CICT

                      Like you Bigtom we've procedures where our customers start the day and know their first run will take a bit of time (SQL to generate mail merge tags in healthcare insurance system) but they know that for the rest of the day subsequent reruns will happen almost instantly.

                       

                      We also carried out significant PSOS tests earlier this year, again using our streaming servers and subsequently found the bug where data could be lost and FileMaker have subsequently suggested using 'Open Record' post PSOS that gets around the problem until a fix is released. However, what was interesting about these tests was that with the bandwidth available within the cloud between FM Pro and FM Server (both v15) that running the script normally was often quicker than PSOS (we'd ensured startup/shutdown scripts were taken out of the equation).

                       

                      Needless to say subsequently running FileMaker locally on our computers the same tests went in PSOS's favour significantly, which is understandable, but for a system running on a LAN only, PSOS wouldn't necessarily provide any benefits.

                       

                      Regards

                       

                      Andy

                      1 of 1 people found this helpful
                      • 8. Re: VM Server Specification and FileMaker 16 Performance
                        Vincent_L

                        This already exists with PSOS and it is one of the fastest ways to do one time queries.

                         

                        This is untrue. PSOS has the same first query tax. All Filemaker's SQL has, unfortunately, even JDBC.

                        I've tested this many times.

                         

                        Thought PSOS, as living inside the server, has no network latency to deal with, and hence is a tad faster than a query from the client, it's first query has the same time tax.

                        Actually, PSOS can be slower for small queries because, PSOS has a big overhead, it has to open a client session, and hence open the database. PSOS is just a new client opening in the server, so suffers from all the overhead of the client session creation.

                         

                        The only cure would be to have SQL server always opened (with all files opened) in Filemaker Server, to which the query would be run against, that's what the idea proposes.

                        • 9. Re: VM Server Specification and FileMaker 16 Performance
                          CICT

                          Hi Carl

                           

                          Like you, we'll not be using this in production as yet, but the MDI to SDI improvements for our RemoteApp use is so good we'll definitely be using FMPA for development, currently writing scripts for some of the new features, which we ensure work adequately in v15 until we can upgrade.

                           

                          I've had one or two PMs indicating that there could be examples of improvement on certain operations and wish we had more time to do more tests, but our main focus was on SQL as we become more dependent on it. No doubt there will have been a focus on certain processes during FileMaker's development and this will become apparent in time.

                           

                          Regards

                           

                          Andy

                          • 10. Re: VM Server Specification and FileMaker 16 Performance
                            Vincent_L

                            Improved caching has been a thing from FM15 so no surprise there either.

                            No, corrupt caching, that returns false results, is really what FM 15 introduced instead FMS 15v3 huge issue : Client-Server Cache gets corrupt / out of sync, SQL Queries unreliable

                             

                            And, this caching, seems to have very little benefits for Desktop clients at least :

                            FM 15 new cache feature, does anybody find any speed gains from it ?

                            1 of 1 people found this helpful
                            • 11. Re: VM Server Specification and FileMaker 16 Performance
                              CICT

                              Thanks velistar

                               

                              Agreed. It would  be brilliant if we could coordinate tests at a certain time to analyse load and performance. I would think that there is no doubt better specified servers would improve WebDirect and with the ability to have multiple worker servers to a single master server there is good potential, albeit I still believe the WebDirect licensing is constraining the use of this.

                               

                              Perhaps a good use of this forum would be to agree a list of tests to be carried out, then someone creates a number of AWS instances of varying sizes and those involved run the agreed tests at pre-agreed times (or use messaging). The results would be invaluable to us all and all the server logs could be published for analysis. This wouldn't be too difficult for FileMaker Pro tests as each person involved would have their own licenses and, for instance, we currently have a spare v15/16 server license (although not volunteering to do this at this particular time - very busy) but obtaining the WebDirect licenses could be more of a challenge.

                               

                              Kind regards

                               

                              Andy

                              • 12. Re: VM Server Specification and FileMaker 16 Performance
                                LSNOVER

                                Andy:

                                 

                                Can you elaborate on this bug?

                                 

                                FM SQL features definitely need some attention.  Both ExecuteSQL and ESS features have been sorely ignored.  (Yes, I know these features are technically unrelated).  

                                • 13. Re: VM Server Specification and FileMaker 16 Performance
                                  CICT

                                  Sure, have a look at the original post at Lack of confidence in perform script on server which includes our original video demonstrating the problem.

                                   

                                  Regards

                                   

                                  Andy

                                  • 14. Re: VM Server Specification and FileMaker 16 Performance
                                    bigtom

                                    Vincent

                                     

                                    I think it really depends on the query but PSOS eSQL is excellent for mobile over WAN. It is almost always faster than sending an entire large table all the way to the client. If you design around the strengths of the feature it is good.

                                     

                                    Sending the query to the server takes only milliseconds. A decent server with SSD storage runs the query fast even opening a client. Only the small result is passed back to the client in milliseconds.

                                     

                                    I have run tests on LAN and WAN with all types of clients and even with the first run "tax" on all fronts PSOS is still faster in almost all real world uses.

                                    1 2 Previous Next