This Indiana FileMaker Developers Group presentation was by Jeremiah Hammond on scaling FileMaker to handle a very large set of data in one table.
Some of the lessons learned include:
- What common operations are disk-bound and which are processor bound
- Difference between 32-bit and 64-bit in regards to dealing with this amount of data
- There are still a lot of unanswered questions about what it does as the demo opens – the times vary greatly depending on the hardware setup
- Indexing records in the sample file (2 Billion records) would take 4-5 hours on a text field
- Indexing records on a date field would take 4 times longer
- Opening a file this large takes a long time
- Converting and importing fixed length files presented some interesting challenges, including using a program called Gawk to process the files into a csv format
- Indexing was turned off to speed up imports
- Calculation fields, particularly one using the Middle function, slowed the import process dramatically, and had to be deleted
- The original csv files were 1.2 TB in size
- The final FileMaker file without indices was 2.2 TB
- Temp files ate up a lot of hard drive space during import, requiring twice as much disk space
- Hammond used BaseElements to check the amount of hard drive space after each import to work around the temp file problem
- Once the database was finished, adding more queries turned out to be too much for FileMaker with that large a record count
- Problems with import file size (>4GB) led to splitting the files into smaller files
- FileMaker Pro 13 for Mac is not a 64 bit program
- It took about 60 days to get past all the problems they encountered
- Indexing a field on the 17 billion record file took about 3 days per field (longer for some field types)
- There are two types of index types in FileMaker: a work index and a value index. Clicking the “All” option sets up both.
- There is more to learn – watch the video for more tips
The final result: a 2.2 Terabyte file with a 17 billion records in one table.
A hundred years ago, if you wanted a cold drink in the summer or needed to ice an injured knee, you were largely out of luck. It took millions of years of cultural and technical evolution to get to the point where people had a freezer in their house.