"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Create a FileMaker Calendar
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
|
Document Management Videos
|
Quick Tip:
|
Coffee Cup
Do you see the coffee cup cursor icon a lot? This icon shows up in place of the arrow pointer when the host is busy and cannot process a request from a guest. This is different than the squiggly line cursor, which indicates the guest is sending information to the host. Using FileMaker Server as a host will probably eliminate the coffee cup icon, since it is multithreaded and can handle multiple jobs at once. This is different than multitasking. FileMaker Server actually does a little of each job that is queued. For instance, if a guest sorts a million records to preview a report, this may take a while to process. If another guest follows this long process with a simple find, they may get the coffee cup with regular FileMaker Pro as the host, since it can only process one guest at a time. What FileMaker Pro Server does is work on each task for a short time. Since the find takes a lot less time to process, FileMaker Pro Server will take care of it in one cycle and the user will never know another guest is processing a much more complex request. |
|
|
Create a Complete Contact Manager
|
Fun Stuff:
|
Dull Boy
FileMaker files were stored in clear text prior to FileMaker 7 so, if you opened a FileMaker file in a text editor, you could see the phrase "All work and no play makes Jack a dull boy" in the header repeated over and over. No, it's not a virus! My understanding is the developers just needed to take up some space in the header and that's what they came up with. Today, modern FileMaker files are stored in Unicode so I'm not sure if the phrase is still there. |
|
|
Create a Password Management solution!
|
RSS Feed
|
|
|
|
Server Optimization
|
Level: Intermediate
Version: FileMaker 19
Category: FileMaker Server
Tuesday, July 14, 2020 |
|
|
|
|
|
|
|
|
Just because you streamlined your solution during the design phase doesn't mean it's going to react efficiently across a LAN or WAN. You also need to optimize your hardware and network on your FileMaker Server machine. Hardware and networking are usually secondary to proper design but it can make a big difference in how your solution performs if you have sub par equipment. There are so many factors that play into how a solution performs that it's nearly impossible to provide the right answer for everyone. Instead, what I have here is a cheat sheet that will assist you in resolving speed issues.
Article Series
This is the second article in a series of two articles. Make sure to read the first article to fully optimize a FileMaker solution for WAN deployment:
Designing Efficiently
All the information in this article is available on the internet right now in articles, videos and other mediums so I'll try to specify where I digested the information whenever possible.
Internet Connection
The biggest bottleneck I find when deploying across a WAN is the throughput of the internet. In my experience, most small to medium size companies have whatever internet access is provided by the local cable company. This often translates to ten or twenty megabytes "down" which is very reasonable to pull data out at a good pace. More is usually better and how much of a pipeline to the internet you need depends on how much traffic your FileMaker databases have. However, what a lot of companies forget about is the "up".
When a client says they want to deploy the solution I'm designing to remote users, I usually have them conduct a speed test. More often than not, the "down" comes back fine but the "up" is a dismal one or two megabytes. This means data coming in is speedy but data being pushed out to users is unreasonably slow. Ten megabytes is a minimum these days. Even from my home office, I have 100 down and 100 up. Sometimes location limits the options but a lot of the time it's just the cable company providing a service that meets most of the customer's needs for surfing the internet with a web browser (which is mostly pulling data down).
Local Volume
Don't put your FileMaker file on DropBox or a file server. It should be on the local hard drive of the FileMaker Server (FMS). FileMaker is a hard disk based database system so you need to make sure it can get data quickly. In fact, placing your FileMaker solution on a remote volume could even crash your solution because of all the extra network activity. First, FileMaker Server has to go across the network and pull data to the local machine at speeds slower than a hard drive. Then, it has to push it back across the same network to save it.
Despite my constant warnings for the last twenty years in technical support at Claris and my professional career as a developer, people still deploy in this unsafe and inefficient manner to this day. Crazy, I know, but what can you do except keep fighting the good fight. I've known about this deployment issue for quite a while but it's also included in the FileMaker Knowledge Base in article titled "Optimizing network performance for shared databases":
Optimizing Network Performance for Shared Databases
It's a must-read article that I will be referring to quite a bit throughout this blog posting. I actually send it and other articles mentioned in this blog posting to any client where they are setting up their own server.
Other Software
Let's move on to the next item in the article that recommends running only FileMaker Server on your host machine. Running your FMS with other software on the same computer is definitely possible, it's just not advisable. Not only is it faster when FileMaker Server doesn't have to compete for limited resources like RAM, hard disk space and CPU, it's also less likely to crash your server at some point and possibly damage your FileMaker files.
I ran my own OSX pizza box for ten years alongside an email server, FTP server and a variety of other widgets and never crashed once. So, it can be done. But, I also locked down any updates from occurring. If just one update to a piece of software or the operating system disagrees with FileMaker in any way, you could literally crash your server hundreds of times before you notice. This can happen when the server and FMS automatically reboot after a crash, which is the most common setup. I prefer to avoid this scenario and run a lean, mean serving machine whenever possible.
FMS
Another bullet point in this article recommends using FileMaker Server instead of the peer-to-peer sharing found in FileMaker Pro. It seems like a great cost savings since FileMaker is a fraction of the retail price of FileMaker Server but in reality it's like that Macintosh and PC comparison. Even though a Macintosh costs more in the short term, it's actually less expensive in the long run because it's easier to use, costing you less in learning materials, troubleshooting and expert assistance. The same is true with FileMaker Server and it's cost savings over the long term.
FYI: Peer-to-peer sharing with FileMaker Pro as the host was deprecated in FileMaker 18 and will eventually be removed from the product line. No more development will be done on peer-to-peer sharing so it's just a matter of time before it stops working with modern operating systems and is removed permanently.
FileMaker Server is so much more efficient than FileMaker Pro because it's been focused on the task of serving up information to guests. Not only is it more efficient but it can handle a lot more of the tasks locally because it was built to serve data. It's also multi-threaded! Multi-threading isn't the same as multi-tasking. Multi-tasking is the ability to process multiple tasks at the same time. Multi-threading allows FileMaker Server to perform a little bit of each task placed in front of it. That means quick things like find requests are completed by FileMaker Server in a single pass over so slower requests like sorting don't monopolize all the processing power of FileMaker Server. Can anyone say coffee cup cursor?
The coffee cup cursor appears when FMS can't process your request at the moment and indicates this bottleneck with a cursor change to a steaming cup of coffee. It doesn't happen much these days but if it does, it usually means a redesign of the database or the hardware setup.
There are many more advantages with FileMaker Server like live backups, WebDirect, Custom Web Publishing, progressive backups and much more, but this is an article about efficiency so refer to the following article for advantages of FileMaker Server:
Optimizing Network Performance for Shared Databases
This is the same article as listed above but I repeated it here for ease of access.
Hardware Optimization
The last two items in the aforementioned article cover hardware optimization of the host and guest machines. The great thing is, it isn't rocket science. It's straightforward and easy to understand.
General Hardware Considerations for FileMaker Server
The article divides up the information into four basic areas:- Disk Subsystems
- Processor(s)
- Memory
- Network
Some of the points overlap from the first Knowledge Base article I mentioned, like using a dedicated server machine, but it quickly gets into great discussions regarding the type of hard drives you should be using. They recommend SCSI since they traditionally have faster RPMs and built-in controllers to help with asynchronous I/O. SCSI hard drives are hard to find these days so the most popular choice is Solid State Drives due to low read latency and consistent read performance.
TIP: Steven Blackwell edited this article and notes that SSD drives are fast at reading but not as fast at writing. If performance is crucial, he recommends a hardware controller rather than software in order to take some of the processing burden from the CPU.
RAID is also gaining popularity on FileMaker Servers because of reliability, performance and redundancy. But, don't just invest in any RAID system out there. Claris recommends RAID 5 or 1+0. RAID can be expensive and may be overkill for most deployments. If you are a small or medium business, consumer level hard drives may all you need as long as you have redundancy with a second hard drive for backups. That way, if one of your hard drives takes a dump, you'll have a backup.
BTW: FileMaker is a hard disk based application. It moves information back and forth between the hard drive and RAM. If you need the most efficient hardware for a complex deployment, make sure to get fast hard drives.
TIP: If you don't have lots of files, users and throughput, most small or medium businesses can get away with a consumer level machine as a server. Just make sure you have redundancy in the form of multiple hard drives. Place the operating system and live files on one hard drive and the backups on the second drive.
Processing power is next on the list. While it's second to hard drives, it's an important consideration for an efficient deployment. FileMaker Server even supports multiple processors and cores to distribute the workload. As with hard drives, how much power you need depends on the structural design as well as the number of FileMaker files, users and traffic. Most deployments don't need eight cores so don't overdo it unless it's needed. A common example of when to add power would be in the case of WebDirect since it requires a lot of processing power to render web pages.
Memory or RAM is also important. The article even goes so far as to say you can never have too much. While I don't recommend refinancing the family farm to buy a couple more gigabytes of RAM, the memory can quickly get filled up if a solution has container fields, complex formulas, detailed layouts, ODBC, JDBC, web publishing and other FileMaker elements that increase the amount of data being moved around. In other words, if it's just a contact manager with a thousand records and less than ten FileMaker clients, you can probably get by with the minimum RAM recommendation (8 gigabytes for FileMaker Server 18).
If you really want to get into the nitty gritty of RAM requirements then you need to study Cache Hits. I don't worry about RAM with most of my clients because they have standard requirements for the solutions they are hosting. And, with the popularity of the Cloud these days, it's likely out of most people's control. If you do have control over your server then your cache hits should be 90% of RAM, meaning most information comes from RAM. RAM is faster than the hard drive so speed goes hand-in-hand with RAM data access.
TIP: It is possible to assign too much RAM according to Steven Blackwell so make sure those cache hits are somewhere in the 90%.
FYI: If you are deploying on WebDirect in addition to your FileMaker clients, consider a two machine deployment. WebDirect renders all the HTML and CSS on the server so it's processor, RAM and hard drive intensive.
A network or multiple network cards with on-board processors are also important for an efficient deployment. Choosing NIC cards with an on-board processor offloads the central CPU, freeing it up to handle other requests. So, if you have that "big" deployment for a client, make sure to get multiple NIC cards or at least consider a second one if the solution seems sluggish. Again, lots of container data creates a lot of network traffic which equates to the need for faster networking.
TIP: Steven Blackwell recommends sticking with the default 128 megabyte default setting in FileMaker Pro in most situations.
And, don't forget to optimize the guest machines. While not as important as the server, slower or older machines can affect the speed of a FileMaker solution. Make sure guest computers connecting to the server have good processor, enough RAM and fast hard drives. If the computer is younger than three to five years old, you shouldn't have much of a problem. If you are using a portable device on a remote connection, a more modern device is recommended since they have smaller processors and less RAM already.
I'll leave you with one last article that is specifically aimed at WebDirect. It basically discusses the need for one or two machine deployments and concurrent users. However, the hardware needs are the same as a standard FileMaker deployment. This article just emphasizes when you will need more and better hardware:
General Hardware Considerations for FileMaker Server when using FileMaker WebDirect
General Performance Tips & Settings
The following information comes from two articles. One is designed for Macintosh servers and the other Windows servers. There's a lot of overlap but it's best to read the article for the operating system you prefer:
General Performance Tips for FileMaker Server for Windows
General Performance Tips for FileMaker Server for Macintosh
Some of the stuff is pretty basic like choose a supported operating system. But, you can't imagine how many times folks install and check later. It literally happens every week. And, it's not hard to check compatibility. Just Google "FileMaker 18 Requirements". I once had a client who installed FileMaker Server with no hitches and was able to see the sample database from guest machines. The problem was uploading their proprietary database since FileMaker Server was still in trial mode. I told them they just needed to install the license key but despite numerous attempts locally and remotely, it just wouldn't install. To make a long story short, it turns out the operating system wasn't supported. So, the moral of the story is do a quick check of the requirements for any software so you don't waste hours of your time. Worse yet, you might have frequent crashing and ultimately file corruption.
The article also goes on to recommend updating to the latest version of the operating system along with drivers, firmware and BIOS, if you are on Windows. If you are on the Macintosh, just update to the latest revision of the operating system. However, once everything is working, don't touch it. While the article doesn't say this, I say if it ain't broke, don't fix it. Any change on the server machine has the potential to bring the whole system to it's knees! Turn off all auto-updating and you can drive off into the sunset with your mind at ease.
Both Macintosh and Windows should also have unnecessary system components and services disabled. In other words, if FileMaker Server doesn't need it, turn it off. FileMaker Server only needs access to the hard drive and the network. Everything is included in FileMaker Server so don't run any daemons or other background processes unless absolutely necessary. This even includes the basics like screen savers, virus checkers and energy saving options. If you need to check your computer for viruses then close all your databases and run it. Just don't have it running in the background all the time.
Other processes include disk indexing, like Spotlight on Macintosh or Indexing Service on Windows, since it isn't necessary for FileMaker Server to run and will just slow things down and possibly crash the server if it conflicts with FileMaker processes. Add to this shadow copies or VSS and Time Machine. You'll be doing with backups with FileMaker Server and these applications can actually corrupt your data if they try to backup a live database. File sharing is also a big no-no! If you need to transfer a file to the server, you can do that with FileMaker Pro. If you need to grab a backup, you can turn it on temporarily but I'd restart your server just to be safe. Add to the list routine maintenance like defragmentation of the hard drive and you have an efficient system that will serve your users for years.
Additionally, auto-updates generally reboot the server automatically which is a disaster for open FileMaker file, says Steven Blackwell.
A Few Tips of My Own
Some stuff just isn't written down so here's my two cents worth. For starters, if you don't need the top call log file then turn it off since it's on by default. It taxes FileMaker Server quite a bit. The other logs are fine but the top ten log should only be turned on when needed. When do you need it? When you are having trouble and you want to see what are the top ten calls to FileMaker Server. It can often help you narrow down issues.
If you have FileMaker 18 Server, turn off startup restoration since it's on by default. The basic idea of startup restoration is to guard against corruption by logging all transactions (adding and editing records) so if a consistency issue is detected, it can automatically rollback. Startup restoration also speeds up many processes by allowing for parallel processing but at the expense of slower adding and editing of records (think of it as delayed gratification but the opposite). This all sounds great but most people don't need this feature because they don't have enterprise level deployments. And, there are bugs that need to be fixed according to the FileMaker community so I think it's best to keep it off for now even if you have a speedy server.
Information regarding the Startup Restoration feature came from a Knowledge Base article and the DBServices blog:
FileMaker Server Startup Restoration Feature
FileMaker 18 Database Engine Improvements
Make sure there is enough free disk space on the system drive of the master machine to store the temporary files. The Database Server creates temporary files in a temporary directory on the master machine to cache data for hosted files. The Database Server creates one temporary file for each open hosted file, and automatically closes and deletes the temporary file when the associated hosted file is closed. In most cases, the size of the temporary file is 10-20% of the size of the associated hosted file, but the actual percentage depends on the number of clients and server-side scripts and their activity level. With limited storage space, temporary file creation and modification could slowed or even prevented.
BTW: Guest machines also store temporary files so make sure they have adequate storage space or they could also slow down operations.
Performing scripts on the server with the Perform Script on Server can also slow things down. The basic idea behind this script step is to allow FileMaker Go users to activate a long running script without requiring them to stay in FileMaker go till it's complete. FileMaker Go doesn't work the same as FileMaker Pro because of the operating system. Apple's iOS won't let apps perform background processing for fear of slowing down the experience. It's even mentioned in the FileMaker 18 online Help:
FileMaker Server 18 Online Help
This troubleshooting section of the FileMaker Server 18 online help mentions some stuff I didn't find in the Knowledge Base articles referenced above. It recommends choosing another hard drive for progressive backups, since progressive backups write at the same time as changes to the live databases. It also mentions turning off startup restoration so I guess it is written down somewhere, lol. Someone should update that Knowledge Base article!
One thing I don't see mentioned anywhere which seems obvious to me is scheduling backups during off-peak times. In other words, don't schedule a backup to run right in the middle of the busiest time of the day. It's best to backup in the early morning or after hours. If you really need a backup in the day, try to schedule it during the lunch break when most people aren't using the system. In fact, any scheduled tasks should be run after hours whenever possible.
TIP: Steven Blackwell recommends using a separate drive for incremental backups, aka progressive backups, for the best performance. This allows the hard drive for the live databases to perform more efficiently.
Under the Hood
One of the most popular sessions at the FileMaker Developer Conference (now Claris Engage) is the Under the Hood sessions. Quite often, the now retired Jon Thatcher, presents a session on FileMaker Server for which he was a senior engineer. Plus, with 28 years at Claris and then FileMaker, he really know his stuff. Only the esteemed Clay Maeckel has been around longer! Anyhow, there are videos available with these sessions so I highly recommend watching them:
DevCon 2017 - Under The Hood: Server Performance
There are also some slides from a 2014 conference where he spoke. The information is still relevant and I offer the following link as a different way to digest the material:
FMK2014 FileMaker Performance Under the Hood by Jon Thatcher
Let me reflect on some of the major points he makes during this presentation. Jon starts off by talking about WebDirect with one and two machine deployments and how everything works on FileMaker Server. If you are using WebDirect, this is a very important five or six slides to pay attention. The more you understand about how FileMaker Server works, the better you can understand how to optimize it. There's a really interesting slide where he talks about how work is distributed and redirected in a multi-machine deployment.
I really enjoyed the discussion of how Perform Script on Server (PSOS) works. The most important thing I learned is that Perform Script on Server can produce the an exceeds host capacity error if there are too many virtual guests running scripts on the server. He does talk a bit about efficiency when he goes into how Perform Script on Server uses the temporary file like any other client. More practical information includes his discussion of how these virtual sessions still run like regular guest sessions.
That means OnFirstWindowOpen and OnLastWindowClose script trigger do have a bearing on how fast the virtual session opens. Layouts can also be an issue with Perform Script on Server if the layout(s) specified in the script are based on a huge table or have complicated layouts with portals and related fields. Best to use a better starting layout (refer back to File Options and the Switch to Layout option) and/or a blank layout if possible.
BTW: Since Perform Script on Server is essentially opens a virtual guest, it acts much like a regular guest. For example, you would always run a looping script on a blank layout so FileMaker doesn't have to process all the complexities on the layout each time the loop goes to the next record.
Perform Script on Server shares the same resources as the FileMaker Server so you may need more RAM or processing power if you will be using a lot of Perform Script on Server script steps. The issue is exacerbated with asynchronous processing when you turn off the option to wait for completion. This can allow one person to open multiple Perform Script on Server sessions and literally bring down the server. The moral of the story is, use Perform Script on Server but use it wisely. There are many tools that can often accomplish the same tasks as Perform Script on Server. One example would be using the FileMaker Server Script Schedule instead of Perform Script on Server to batch process records from many guests.
One of the most interesting things he says is about Cache Hits. He recommends 100% Cache Hits which is direct contradiction with the Knowledge Base articles we've already discussed which recommends 90%. I was always told 95%. I'm confused but Jon is the engineer for the product. Obviously, 100% Cache Hits is impossible since the data has to come from the hard drive at some point.
Jon also reiterates the necessity of having enough free disk space on the hard drive of a client. If there's isn't enough space, FileMaker guests may need to download data from the FileMaker Server over and over. This defeats the purpose of the temporary file which is designed prevent the guest from downloading information over the network when the hard drive is much closer and faster. Only when the data is updated on the FileMaker Server does it get downloaded again. And even this process is efficient, simply broadcasting a flag that this record has changed so next time you need it, make sure to download it to the temporary file again.
In the previous article in this series, I talk about designing with narrow tables. I got this information from this presentation because Jon mentions that FileMaker works at the record level. Even if a field is not displaying on a layout, it still gets downloaded with the record. Therefore, it may be prudent to split tables, using a one-to-one relationship to connect them. The example I used in the previous article was notes field. Since it contains a lot of data, it may be wise to place notes in their own table. If your notes field is often empty or contains very little data then there is no concern. Blank fields in records have a zero footprint in regards to downloading data. In other words, understand how FileMaker works and how your users will be using the solution so you can design it for efficiency.
But Wait, There's More!
For me, this was one of the most important design tips for remote access, especially from a portable device running FileMaker Go. Often, these devices are running on 3G or 4G networks. Therefore, it's important to optimize wherever possible. One way to speed things up up is to get the solution open so it can be used soon after entering credentials. If you allow the opening layout to show records from a table, it's going to need to load those records even though they are not necessarily displaying.
I say this because I like to use a main menu screen on portable devices since their use is very focused. No need to display all the features in the desktop version. Just give the users what they need out in the field. Therefore, I present users with a series of buttons like "Add Customer" or "Find Job". If you are the same as me then you need to make sure the main menu layout shows records from a table with only a single or no records.
The problem happens when FileMaker launches. The first thing it does is load the last layout that was showing before it is uploaded to the server. You may not see it but that's what FileMaker does. No script that runs OnFirstWindowOpen can bypass this behavior. Only an often forgotten option in File Options can rescue your FileMaker solution. It's called "Switch to Layout" and all you have to do is specify a layout as the starting point. Use a layout based on a one record table like splash screen and you're ready to go.
Final Thoughts
Don't take this optimization thing to far are you could be spending thousands of dollars more than you need on hardware and networking. First asses your needs and the determine the level of hardware that will be needed. Like I said before, if your needs are modest, a consumer level server with a couple of hard drives may be all you need to adequately serve the needs of your FileMaker users.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
