1 2 Previous Next 17 Replies Latest reply on Sep 19, 2016 9:15 AM by TonyWhite

    Server Hardware / Solution Perfomance Problems - 100 users

    mattel

      TLDR; hardware problem or software problem.

       

      Currently we run filemaker server in 2 locations (windows 2012 r2), one fm server 14 and fm pro on the same machine.  The other has a filemaker server and 2 Remote desktop servers accessing it.  We are having some problems with some general sluggishness in the solution.  We are also experiencing random crashes from filemaker app itself (I attributed these just to filemaker having some bugs).  The solution experiences similar speed/performance when the client is run on a developer desktop with fm pro adv.  The solution was never intended to run over a WAN and has not been optimized for such.

       

      The solution is deployed using the split model, with one back end file for each sales group (20 total, each back end file typically has less than 10 users).  Front end files are then accessed over remote desktop, a script makes a copy of the "master" front end file for the user when they click the icon, so they are presented with a "fresh" version every time.  Other in house applications are also run on these remote desktop servers (involving office, sql server, etc).

       

      We have been advised to take our current setup and break up sales groups to about 40 users per machine.  Then create a server with FM Server15,  40 FLT connections, remote desktop, fm pro, sql express and the other services for our in house apps.   OR create 1 FM Server at each of the locations, and host the remote desktop/in house apps on separate servers.  Which would give us the FM Server workload of about 100 users at one site and 30 at the other.

       

      If we are re-arranging stuff then we might as well upgrade the servers.  So I'm looking for hardware suggestions for new / used servers.  I'm including a few shots of the relationship graph in case that could be part of the problem.  Our current hardware is below:

       

      FM Server 15:

      2x Quad Core X5560, 48 GB Ram, 4x 10k drives in raid 10 setup.  3 partitions (system, data, backup)

      Total FM file backend size is less than 10GB

       

      Remote Desktop Servers:

      server1: FM 15

      2x Quad Core X5550, 72GB Ram, 3x 10k drives in raid 5, (50 users)

       

      Server 2: FM15

      2x Quad Core X5560, 48 GB Ram, SM863 480GB SSD mirror, (40 users), (also runs in house apps, sql etc)

       

      Combo server

      FM 14 pro and 14 server, 2x Quad Core X5560, 48 GB Ram, 3x 10k drives in raid 5, (30 users), (also runs in house apps, sql, etc)

       

      Attached Relationship Graph: (some stuff marked out)

        • 1. Re: Server Hardware / Solution Perfomance Problems - 100 users
          Johan Hedman

          Do you have index on for the computer where you have your live databases? Disable that

           

          Do you have a anti virus software on your computer where you have your live databases? If so, make sure that it exclude all folders where FileMaker Server is working and store databases

           

          Do you have any backup software that runs backups on the computer where you store your live databases? If so, make sure it exclude all databases folders

           

          Do you have any other software running on the machine that might, try to move them to another machine. FMS should be a dedicated server machine.

           

          Regering your solution, you should think about making Table Occurrence Groups instead of having nested spider net where everything is related to each other. That is not going make your solution fast. That could look something like this

          Unknown.jpeg

          You have a few master Table Occurrences and then you only have that tables related tables related.

           

          Also it is very good if you where to use Graphical User Interface technique where you have data in one database file and all layouts and functions in another file

          Look at my presentation from DevCon this year where I talk about GUI-files

          WEB002 - Prepare Your Custom App for FileMaker WebDirect - Johan Hedman

          • 2. Re: Server Hardware / Solution Perfomance Problems - 100 users
            Mike_Mitchell

            "The solution was never intended to run over a WAN and has not been optimized for such."

             

            Well, I'd say start there. Regardless of whether you're running over a WAN or not, optimizing the solution is the first place you start. Narrow tables, avoid portal filters, avoid unstored calculations referencing related tables (or especially using ExecuteSQL), store your data wherever possible. After that, start looking at hardware, other processes running on the server, etc.

             

            More important than the Graph model is what those relationships are based on, whether they're sorted or not, and how many records are in each table. I'm working on a solution for a client right now that was experiencing severe performance issues, and it was directly attributable to those factors. Without changing the Graph model - in fact, I added TOs - I sped the solution up enormously just by eliminating sorted relationships and unstored calculations on which relationships (and portal filters) were based.

            2 of 2 people found this helpful
            • 3. Re: Server Hardware / Solution Perfomance Problems - 100 users
              mattel

              It's worth noting as well - that an almost identical graph exists in the back end data file.  It started out as being one file, and we cloned it to make the front end file.

               

              There are no relationship based sorts.  The unstored calc's are mainly the totals for orders, and purchase orders, which nothing is sorted on nor related by.  About 15 total.

               

              We have a list of all our fields that use ExecuteSQL to fill, most of them are ones that refer to something from the line item transaction table where we needed it to be based on a certain transaction type.  I think there are 12 or so total.

               

              Narrowing up some of the line item tables was something I had already figured should be looked at - it has about 150 fields, and most of them are used only in one layout or in one script.  Some of those fields contain a 20kb blob of xml as well.

              • 4. Re: Server Hardware / Solution Perfomance Problems - 100 users
                Mike_Mitchell

                Yeah, I'd start with things like portal filters (performance killers) and then look at stripping down the Graph in the data file. Every join on the Graph has to be cached when FileMaker opens the file, so the more joins you have, the longer it will take and the bigger the cache. But if the solution is slow on a developer desktop, then it's not network related (as I would have suspected with the RDP deployment strategy). Rather, it's the amount of work going on in the solution itself. (Which means the narrow / wide table issue is not as critical as it might seem, although it'd still be a good idea to look at it.)

                 

                Beyond that, I would look more at exactly what operations are slow. You mention ExecuteSQL in fields. This is generally not a good practice, as when ExecuteSQL fires, if there are any open records on the client, the server will send down all records associated with the query to the client - which could cause a really awful delay. Since you can't control the open state of a record when the calculation is in a field, I'd avoid this if at all possible. But is there a layout that's particularly slow? A script? What's going on when the performance is slow? Check the server logs and see what your performance stats look like.

                 

                You might want to look at using summary fields for totals instead of unstored calculations. Summary fields (in the child table) can sometimes be better performant than a calc field in the parent.

                 

                Performance issues are often difficult to track down because there are many factors, and often they're cumulative. Good luck! Check back if you have further questions.

                • 5. Re: Server Hardware / Solution Perfomance Problems - 100 users
                  wimdecorte

                  mattel wrote:

                   

                  Then create a server with FM Server15, 40 FLT connections, remote desktop, fm pro, sql express and the other services for our in house apps

                   

                  It wasn't clear from your description; do you intend to run all of the above on one box?

                   

                  If so: don't do it.  Don't run FMP client sessions on the same box as FMS.

                  • 6. Re: Server Hardware / Solution Perfomance Problems - 100 users
                    wimdecorte

                    Also: use the FMS stats log to see where the painpoints are and where you can spend money.  I suspect you'll get more bang for the buck in increasing the processing power (more & faster cores) and increasing the disk i/o

                    • 7. Re: Server Hardware / Solution Perfomance Problems - 100 users
                      CICT

                      Without actually dealing with your crashing problems, I'm intrigued why you are considering new hardware and not moving to hosted virtual machines?

                       

                      Although you have a system not designed for WAN usage, you're using Remote Desktop linking to the server, so if you were to put this whole configuration, using your existing RDP and FileMaker licenses within the same IAAS supplier then your RDP/FileMaker Pro servers would be communicating with your FileMaker Servers utilising the full bandwidth of the IAAS and you should get office performance speed over the WAN.

                       

                      We have many solutions that are similar in use, albeit we tend to use Citrix or more recently Microsoft Remote App and at times exceed speeds we obtain when using a local copy of FMP linked to an FMS server on a LAN.

                       

                      This also gives you dynamic expansion ability, snapshot backups, pretty instant restoration and cloud to cloud backup opportunities.

                       

                      Is there a particular reason you have to retain physical hardware, when you've done most of the hard work already in terms of WAN usage?

                       

                      Regards

                      Andy

                      • 8. Re: Server Hardware / Solution Perfomance Problems - 100 users
                        CarlSchwarz

                        I'd agree to look at the SQL first because it needs to pull down all the data.  If you can make it fill in a 'Native FileMaker' way then they will fill much more responsively.

                        Also look at the layouts.  If it's an old solution that was upgraded then the classic layouts can cause some issues.  I've found that rebuilding layouts using new themes can make layouts much more responsive.

                        • 9. Re: Server Hardware / Solution Perfomance Problems - 100 users
                          mattel

                          At first I thought yes, then I was told that we would be running FM Server on a box with 100 or so users and then 3 or 4 RDP servers to run the in house app and the FMP front end.

                           

                          As for the logs - i've looked but since they are all ID based and not based on anything in the english language - I haven't been able to correlate the "Filemaker Speak" to "english" to find out which scripts/tables/etc are actually a problem.

                          • 10. Re: Server Hardware / Solution Perfomance Problems - 100 users
                            mattel

                            As far as I know, the powers that be, have decided they would rather spend on a 3 year old server with new hard drives, than migrate to a similar beast in an IAAS service cloud.  Additionally due to integrations with existing programs, it's far easier if it's all on one network, instead of setup over VPN/VPC etc.

                             

                            CarlSchwarz

                            It's not an old solution - it was started in FMP13, in 2015.  while not using themes (to the fullest extent) it is all based on one of the filemaker 13 or 14 standard themes.

                             

                            !

                            • 11. Re: Server Hardware / Solution Perfomance Problems - 100 users
                              wimdecorte

                              mattel wrote:

                               

                               

                              As for the logs - i've looked but since they are all ID based and not based on anything in the english language - I haven't been able to correlate the "Filemaker Speak" to "english" to find out which scripts/tables/etc are actually a problem.

                               

                              Wrong logs - at least for determining whether your server is suffering from lack of resources on any of the 4 traditional bottlenecks, or getting close to any constraints.

                               

                              The regular FMS stats.log will give you that info.  The new FMS15 log for 'top calls' is the one that you seem to be alluding to and that one can help narrow down specific problems. 

                              • 12. Re: Server Hardware / Solution Perfomance Problems - 100 users
                                bigtom

                                Remote Desktop uses plenty of resources itself. Do not run FMP on the FMS. Unstored ExecuteSQL calc fields should be avoided whenever possible. If you need the data let FMS do the work with PSOS to run the ExecuteSQL and return the result only.

                                 

                                It has been a while since I worked with PSOS on multiple servers but I recall you can create an environment where you can distribute the scripts to separate servers.

                                 

                                You can have multiple servers in multiple locations and there are Server-Sync tools available now to help with that.

                                 

                                The two barriers most people hit with WAN are disk speed and actual connection speed.

                                 

                                SSD is worth the price, best money I ever spent on my FMS was for SSD RAID. How capable is the RAID connection? I have seen some people with slow controllers that slow things up. If you try to run SSDs on an old SATA PCI controller not the best use. 

                                 

                                Making sure the servers and clients have good connections is often overlooked. You need good upstream speed at the server. I have seen offices that have a "1Gb" service that is actually about 150-200Mb down and maybe 50Mb up. That might work for 5 users on an optimized solution, but if you get 100 users fighting over 50Mb of bandwidth it is not so great.

                                 

                                You may want to hire one of the more experienced FileMaker Consultants to take a good look at what you have going on and give you advice.

                                1 of 1 people found this helpful
                                • 13. Re: Server Hardware / Solution Perfomance Problems - 100 users
                                  gdurniak

                                  It appears you have a bit of a mess

                                   

                                  One word of advice:  If FileMaker has "hit a wall", and is "sluggish", new hardware makes little difference ( we've tried )

                                   

                                  You need to "fix" the solution ( especially if it runs slow locally )

                                   

                                  There are suggestions here ( and dozens more ), and it is mostly trial and error. FileMaker's tools are limited ( e.g. we can't log the slowest query )

                                   

                                  We also operate from two locations, and now encourage users to take advantage of Remote Desktop

                                   

                                  Also, I can see limiting a Remote Desktop box to 40 users,  but not FileMaker Server ( not sure what you mean )

                                   

                                  greg

                                   

                                  > We are having some problems with some general sluggishness in the solution.

                                  We are also experiencing random crashes from filemaker app itself

                                  The solution experiences similar speed/performance when the client is run on a developer desktop with fm pro adv

                                  The solution was never intended to run over a WAN and has not been optimized for such.

                                   

                                  Front end files are then accessed over remote desktop

                                  Other in house applications are also run on these remote desktop servers

                                  We have been advised to take our current setup and break up sales groups to about 40 users per machine

                                  • 14. Re: Server Hardware / Solution Perfomance Problems - 100 users
                                    bigtom

                                    gdurniak wrote:

                                     

                                    It appears you have a bit of a mess

                                     

                                    That is about how I see it.

                                    1 2 Previous Next