Deep in a calculation, we’ve all been there trying to find the missing left or right parenthesis, among other things. After racking our brains, we sometimes just need a little relief and some humor to break up the day. Check out other comic relief at xkcd.com.
RAID 10 is recommended for FileMaker Server, a combination of redundancy and mirroring and gives the best performance.
If you have RAID 1, mirrored, and separate RAID 5 or 10, the OS should be on the RAID 1 and FileMaker and the Live databases on RAID 5 or 10. The backups should be on a separate disk and channel to take advantage of full async read/writes. You might have better performance making your backups to the RAID 1 drive with the OS. If anything, just use the RAID 1 for backups and leave the OS, FileMaker and the live files on the RAID 5 or 10 drives.
Having the FMS application, backups and logs on a separate partition on a RAID drive does nothing for performance. A partition does not mean separate disks.
UPDATE: Recently, I did a lot of work with Temp Files, RAM Drives, RAIDs and SSDs. I’ve changed my recommendation on what I wrote here regarding the OS on RAID 1. The ideal setup would be OS, FileMaker Server, and Backups on RAID 10 with the live databases and database temp files on an SSD RAID or SSD PCI Card. Actual FileMaker Server backups happen to a RAM Drive and then a script moves those files to the RAID 10. If you are using FileMaker 12, Progressive backups negate the need for backups to a RAM drive. However, the progressive backups should be located on the RAID 10 volume and not on the SSD where the live databases reside.
Fine tuning FileMaker Server cache can greatly improve performance in a large or heavily used solution. So, what is the optimal cache setting? I find that the general rule for setting cache is to set the RAM to the recommend setting that FileMaker Server suggests. I would leave the interval set at the default 1 minute, increasing the interval could leave you at a greater risk of loosing data.
Depending on what your solution is designed to do and how active the solution is, and whether you are writing or reading more data, you may be able to adjust the cache to tweak performance. Think of it this way, if you are writing more data by doing more edits or creating more records, then a smaller cache size may improve performance. Smaller chucks of data are written at a time. If you are reading more data, like running reports, then a larger cache size may improve performance since more data will be accessed from RAM instead of having to keep reading from disk.
In Windows Server 2003 or 2008, I recently confirmed that the memory usage on fmserver.exe is directly related to cache size. Whatever the cache is set to, that will be the minimum memory usage plus whatever extra ram is needed by FMS. The cache memory is reserved, regardless if it’s filled up or it’s empty. It kind of works like a RAM disk.
If you pop up Task Manager and click on the Processes tab and find fmserver.exe, watch the memory usage. Then go to the Admin Console, Configuration, Database Server, Databases and change the Cache size and click save. Watch the memory usage for fmserver.exe and you’ll see it change as you change the cache. There is need to restart FMS. Now you can tweak the cache setting until you find the optimum performance.
One of my clients is running FMS on Windows 2003 Server on a maxed out box that is extremely fined tuned. I rarely see the processors over 7%, although at times one of the 8 cores maxes out. The same goes for memory and networking. Although network usage does see more use. Disk usage is active, but I’m running on RAID 10 with 15K disks and backups are done on a RAM disk which an OS script then passes the backups off to an entirely separate hard drive system. In my opinion, FileMaker does not do a good job taking advantage of multiple processors.
It’s all about how FMS queues requests on specific files and one unindexed search, or something else that takes processing power like running a report with unindexed calculations, can bog down an entire group of users. If a particular file is extremely active and you run an unindexed search, that file is handed off to a single core to process one task at a time. You can just imagine what can happen here if you’re at the end of a long queue. I cannot remember if it’s one file or one table within a file and I have not done enough testing here to confirm. But, this is why in a system with multiple modules (and files for each), a solution may “seem” slow in one area, but not in others.
Even a table with hundreds of fields is much better split into smaller tables each with fewer fields since server passes all data in all fields from a single table over to the client when the client has to process certain requests. This can be another major bottleneck.
When the constrain find option first came out, I was able to drastically speed up some search routines. Finds with exact field matches (==) were being used and it was SLOOOW when running on a table with over a million + records. Removing the exact field matches in the first search pass to find a subset of records, then adding a Constrain Find using the exact field match (==) to run on the subset of the records, greatly improved the performance of the search.
Recently I’ve been working on exports from FileMaker that are being used to import into other systems. FileMaker is great at accepting and interpreting many formats, but not so true the other way around. I find it interesting how inflexible those other systems are.
When exporting data in CSV format, FileMaker puts quotes around the data in every field, including number fields. Excel seems to only quote data when there is a comma in the data. I was told by another developer of another database system that the CSV format I’m supplying is not in “standard CSV format.” But, there is NO industry standard CSV format and it’s acceptable to put quotes around the data of every field. In order to make the client happy and conform to their inflexible CSV import, I have to open and save the file in Excel before posting to their FTP site. An extra step that’s totally unnecessary.
I had similar experiences with tab delimited files exported from FileMaker. A client requested the files as pipe delimited, but tab would also be acceptable although not preferred. Most of the work I do is on a Mac, but I make sure its compatible across both platforms. I usually export in a Unicode format. However, I was told there were no carriage returns, so I exported as ASCII (dos) format. I was again told there are no carriage returns in the document and everything is in one long line. They also wanted the file saved with a txt extension instead of a tab extension stating tab was specific for Tableau. This is just not true. Although .tab may be an extension for Tableau, it’s not the only application associated with the extension.
The actual problem with the file is the issue with Carriage Returns (CR) and Line Feeds (LF). Older Macs use a Carriage Return and newer Macs use a Line Feed, like their Unix counterparts, including Linux. Windows requires both Carriage Return and Line Feed (CRLF). If you transfer the files via FTP in ASCII mode the conversion between platforms is supposed to be taken care automatically. This does not always work from my experience. I’m looking for a solution now and will post it once I have it. In the meantime, I’m exporting on a PC.
Although FileMaker is flexible with file formats it can import, many systems are not. Keep this in mind when exporting data for use in other systems.
If you have messy data, try Google Refine. There is nothing like dealing with messy data that is all over the place. We often have to normalize the data before importing and this is a great tool to help with data normalization.