February 7, 2018
FairCom DB: 7 Tips for a Fast Data Load
It is often said that time is money, and this is definitely true with database operations. One task, loading large amounts of data into a database, can consume significant chunks of time. … Time (and money) that administrators and developers could devote to other tasks.
FairCom DB (formerly known as c-treeACE) users, however, do not have worry about developing processes or programs to reduce data-load time. FairCom’s Vice President of Engineering Services, Randal Hoff, has provided tips on how to load large amounts of data into FairCom DB as easily and quickly as possible. Whether the data is being transferred from a legacy database or it is newly created data, users have the ability to more quickly load it into their database powered by Faircom DB if they follow one, all or any combination of these easy tips. The time-saving tips are below.
FairCom DB’s fast-load capability is just one of many reasons that make it the database trusted by organizations of all sizes across a wide-range of industries, including more than 40 percent of the Fortune 100. Database administrators and application developers who would like to learn for themselves how FairCom DB facilitates high-performance NoSQL and industry-standard SQL access within the same application (over the same data) can download a free trial at faircom.com/download-faircomdb. Click here for additional contact information.
Once you have FairCom DB integrated into your application, you will be faced with a real-world problem: loading massive amounts of data into your database. This can be a time-consuming task.
The first temptation might be to write a script that inserts your data into the database one record at a time. Fortunately, FairCom DB offers many features that streamline this process. Instead of records trickling in one at a time, your FairCom DB database can be gulping multiple records simultaneously.
In one customer case where we’ve used this process, the time to load billions of records–with several indices–went from approximately 2 weeks to less than 2 days.
The following tips can be used to speed up the process of inserting data into FairCom DB:
1. Turn off transaction processing
Transaction processing control can be turned off during these steps. Assuming you have the data preserved where you can start over in the event of a problem, you don’t need transaction processing control for this process.
If you desire to have transaction processing control down the road, then ensure you create the data and index files with TRNLOG file mode active. Once you create the file initially with TRNLOG enabled, you can speed up the operations by disabling TRNLOG programmatically as indicated in the FairCom DB Programmer Reference Guide topic titled Transaction Processing On/Off: http://docs.faircom.com/doc/ctreeplus/#29980.htm
Or you can call the cttrnmod program, explained in the topic titled cttrnmod – Change Transaction Mode Utility: http://docs.faircom.com/doc/ctreeplus/#49162.htm
Don’t forget to turn transaction processing back on after you have completed the data load. (The topics cited above explain how to do this.)
2. Use SHARED MEMORY protocol
If at all possible, run the data load program on the same machine hosting the FairCom DB data. This will allow the FairCom DB Server to use the shared memory communication protocol which is much faster than TCP/IP.
If you need to use TCP/IP, increase the number of threads loading data to multiple threads per CPU core to compensate for the network latency.
3. Use Direct I/O (V11 and later only)
When using FairCom DB V11 and later, please review Direct I/O support. This will provide some help when building and working with larger files: http://docs.faircom.com/doc/v11ace/#66369.htm
4. Multi-thread the inserts
The next way to boost performance is to use one of the non-relational c-tree APIs, such as the ISAM or c-treeDB API.
If you can break the data coming into the program into multiple chunks, these APIs allow you to take advantage of multi-threading to do the inserts. A good rule of thumb is to use one to two threads for each virtual CPU core.
5. Disable indices using CTOPEN_DATAONLY file mode
You can drop the index support when you are doing the data load. This will get the data into the data file in the fastest manner and will avoid the time it takes to update your indices on the fly.
See Opening a Table in the c-treeDB C API Developer’s Guide: http://docs.faircom.com/doc/ctreedb/#23603.htm
6. Insert in batches
With FairCom DB V10 and newer, you can use batch inserts. This is quicker than individual adds because we can maximize the OS packet size and get the maximum amount of data fed into the FairCom DB Server process with each batch call.
Review c-treeDB batches here: http://docs.faircom.com/doc/ctreedb/#15406.htm
7. “Rebuild” to create indices
Once you have all of the data loaded into the data files, do a rebuild to generate the indices. This is the fastest way to build the indices because you now have all the data in the c-tree data files, so the indices can be built from scratch with a known set of data. To generate your indices, use the function call ctdbRebuildTable discussed here: http://docs.faircom.com/doc/ctreedb/#48516.htm
Or you can call the ctrbldif program discussed here: http://docs.faircom.com/doc/ctreeplus/#31093.htm
To improve the performance of an index rebuild through the Server, increase these two settings in your ctsrvr.cfg file:
MAX_HANDLES
http://docs.faircom.com/doc/ctserver/#52143.htm
SORT_MEMORY
http://docs.faircom.com/doc/ctserver/#27977.htm
The tips given above should help you complete the data load process in much less time than a single-threaded program using ctdbWriteRecord() inserts.