7 Replies Latest reply on Mar 27, 2009 4:34 PM by etripoli

    Hardware/Server/Network Optimization



      Hardware/Server/Network Optimization

      Your post

      Our database is very sluggish. Admittedly it is complex, and sums related records that are summing related records, and the relationships sort on unstored variables (often involving related data so canNOT be stored). We require this level of complexity, and i've tried storing the data and referencing the stored fields, but then it is not realtime, so that's not great either.


      We are using 100Mbit ethernet and switches, MacPro dual 2.6 duo, 2GB ram 3Gb/s SATA drive FMS9.


      I have a small budget because of rough times and we are non profit. I am getting different advice to improve performance. One expert says to never use SATA and replace the hard drive with SAS (which means buying also a raid card). Apple says it's not the hard drive, that i should buy FMS10 (does that sort the unstored relationships faster?).


      1) FMS9 says i am using 480 MB cache. How much RAM would I have to put in the machine to be able to cache 1GB? Then hard drive speed doesn't matter.


      2) If I can't do all the options, would my money be better spent on A) SAS hard drive + raid card B) FMS10 C) Faster processor single duo IMac with 2Gb and external firewire drive? (that way I can buy the newest Imac in a year and just move the drive? I can't just keep buying the newest MacPro) C) Upgrade MacPro motherboard to faster processor. I assume this option would be the least bang for the buck. D) use windows or OSX-Server or multiple hard drives E) use ODBC or something to use FMP as a front-end for a SQL back-end


      I would be buying FMS10 eventually either way so please don't advise based on sales but I would like to improve performance right now because when someone goes to a record, they have to wait 20 seconds while there is "sort" screen because it is calculating and sorting through the relationships. Repeat, there is no sort on the found set, the sort must occurring through the relationships.


      Please advise because people are always complaining about how long it takes to load records. Especially over the internet. I kindof suspect the internet ones can't be helped that much. I tried IWP but it did not work on complex layouts so it didn't seem viable.


      Thanks in advance


        • 1. Re: Hardware/Server/Network Optimization
             The first thing I would upgrade is your internal network from (100Mbit ethernet and switches) to Gigabit ethernet. This will help alot and is not very expensive - for example the HP ProCurve 1800-24G Switch is under $400 (good switch).

          The MacPro Dual 2.6 is a good box so I would not switch it out for a iMac. You might want to look at RAID10 for it 

          The next area I would focus on would be optimizing your database itself - some simple changes to the data will improve thing alot. This is especially true for web users. You might want to create WAN only layouts that are simplified.

          • 2. Re: Hardware/Server/Network Optimization

            Before upgrading your network, put a network monitor on the server and see how much/fast it's really pushing. I know our solution is slow, but it's not from the drives or network - it's the database itself.

            Also, look at FM9s built in stats, you might find info there that might help. 

            • 3. Re: Hardware/Server/Network Optimization
                 One other area to look at is the speed of the client machines - is you have a bunch of old Macs this will slow things down as well since some type of processing takes place on the client side.
              • 4. Re: Hardware/Server/Network Optimization

                Something simple I have found it when I use check boxes and how I call 0 or 1 in the box and or the sizes...  I do not know what it is but when it has to process check boxes it take a lot of time.  Mostly the issue is on list pages but it really drags it down. 



                • 5. Re: Hardware/Server/Network Optimization

                  WD Raptor Drives 74GB 10K SATA drives, HighPoint RocketRaid card (to do mirroring), and FMS10, if you can.  If you have the MacPro with 2 drives stacked at the top rear of the case, it should be easier to use the RocketRaid card.  And, with only 74GB of usable space when mirrored, for OS, Apps, and databases, you will be limited.


                  The next option would be to go with larger SATA drives, like the 1TB 7.2K Samsung Spinpoint F1 RAID, mirror, and make a small 100-200 GB partition for OS, Apps, and databases, and then partition the rest for daily backups (which you'll then copy to a real backup location, right?).


                  While the faster drives and mirroring will help reads/writes, you might notice an improvement just by moving to FMS 10.  Honestly, if I had to pick 1 thing to spend money on, I'd go with the FMS 10 upgrade.  Lots of great new features, improved performance, even if all your clients are still running FMP9.

                  • 6. Re: Hardware/Server/Network Optimization

                    Ok thanks everyone especially etripoli.


                    My PowerPC 2x2ghz is my machine for now, especially with budget cuts and it does not appear to have gigabit to test that. However all our machines do not have gigabit, some are 1.4Ghz processors, and we can't upgrade them all $. The slowest ones are the new imacs via internet. I don't use IWP because it does not work with my moderately complicated layouts. So I am stuck running an imac over the internet, and fast and slow machines on the LAN. I don't believe the LAN is the biggest culprit because the database is not particularly spry even when run locally. I believe the biggest slow down is that i have many summarizations and sortings of related records, which themselves sum and sort related records. In other words, there is heavy processing that is considered essential. I believe SQL would handle this fine. I have fantasized about using FMP as the front-end for a SQL backend, but i get the impression that doesn't work - FMP can import and export SQL, but it can't for example do a query and display the results. Please correct me if I'm wrong.


                    I wish I could cache the entire database. I don't understand why FMP won't let me if i want to have the memory (2Gb). My server hasn't crashed in two years and is on UPS. Then the hard-drive doesn't matter.


                    Based on the responses, I believe I am going to go for the server upgrade, though I can't right now upgrade the clients, and that means I won't be able to upgrade the hardware for a while.


                    Thanks for the input!



                    • 7. Re: Hardware/Server/Network Optimization

                      I had the same fantasy about FMP and SQL, and while there are advantages to such a setup, it is more difficult to maintain and use.  And no, unless you define a view in your SQL server, or do some tricks with custom menus and scripts in FM, all the data in the SQL table will be displayed.


                      I'd suggest using scheduled scripts on the server to convert your calculated summaries into periodically refreshed fields, but I'm having my own issues on that one!