The explosion of big data has presented many challenges for today’s database administrators (DBAs), who are responsible for managing far more data than ever before. And with more programs being developed and tested, more tools are needed to help optimize data and efficiency efforts. Using techniques such as DB2’s Multi-Row Fetch (MRF), DBAs are able to cut down on CPU time - and improve application efficiency. This article will explore the benefits of MRF, including the ability for DBAs to improve their application performance and lighten their workloads.
State of the Industry
Everyone is talking about big data, but what is it really? Data is generated by a plethora of activities in today’s digital world and is typically stored for a period of time for any number of reasons. This can be virtually any kind of data - data from weather sensors or electrical grid sensors, pharmaceutical data, social media posts, purchases, cell phone GPS signals, customer information, insurance claims- the list goes on and on. But as new data continues to grow at faster and faster rates, IT professionals are seeing a mounting problem. How are they going to manage, store and use this information to create a competitive advantage?
Pressures DBAs Face
These days, you rarely hear someone talking about big data without mentioning analytics or warehousing. While big data clearly has its uses for business intelligence, we pay for this insight with ever-increasing workloads and throughput demands. Since DBAs need to ensure that they can meet these growing demands without compromising mission-critical applications, it is now more important than ever for DBAs to quickly improve application efficiency and keep CPU utilization growth from getting out of hand.
What is MRF?
MRF, or “Multi-Row Fetch,” was introduced in DB2 version 8 in 2004. Stated simply, MRF is the ability for DB2 to send multiple rows back to a requesting program at once, rather than one row at a time.
Fetching multiple rows is analogous to walking to the kitchen sink and gathering drinking water for a family dinner. You can take one glass at a time, or you can fill a pitcher one time and fill all the glasses when you return. By transporting a larger amount, you greatly reduce the number of trips required for the same job. Similarly, by enabling the database to send more information in each ‘trip’ to the memory, MRF allows the same job to be completed faster and with less processing.
Benefits of MRF
MRF provides a variety of benefits for database administrators, most importantly the ability to reduce CPU times. MRF testing has been shown to provide CPU savings on individual Fetch statements in the range of 50-60%. This is because each Fetch requires only tens of instructions, versus the hundreds or thousands of instructions that are required when issued to DB2. It is important to note that the percentage improvement will be lower the fewer rows fetched per call, and also the more columns fetched. Performance gains start with 10 rows and the “sweet spot” for MRF is at about 100 rows. MRF is a great investment when fetching between 100and 1,000 rows.
As FETCH statements are an integral part of batch processing, it is not unreasonable to see total batch CPU savings in the double digits. For some projects, MRF can save more than one million dollars in a five-year period.
In the mainframe world, CPU time saved effectively equals money saved. When applications complete a project in less time, it frees up valuable resources to run additional applications. If the savings are significant enough, organizations facing an expensive CPU upgrade can even potentially delay that upgrade. This leads to significant savings, as CPU upgrades are not only expensive by themselves, but most mainframe software is licensed based on the CPU size. By delaying a CPU upgrade, organizations can avoid paying upgrade fees and higher maintenance costs associated with third party software licenses.
By implementing MRF, organizations can also avoid API overhead in terms of local applications with fewer database accesses, and in terms of distributed applications with fewer network operations, leading to a significant improvement in performance.
A major concern in many organizations that use IBM mainframes is a reduction in the peak four-hour usage window. In many cases, staff is dedicated to monitoring this, as an increase in utilization of even a few percentage points can mean tens or hundreds of thousands of dollars in additional utilization fees. With MRF, organizations can reduce their four-hour peak window, saving CPU resources and, ultimately, money.
There are many real-life examples of organizations saving money and time by implementing MRF. One North Carolina organization with 500,000 member families saw application Fetch statements improve by 74%. That is, they complete four times faster when using multi-row Fetch verses single-row Fetch. Had the same organization decided to recode all of their applications by hand, the process could have easily taken months, if not years, and cost hundreds of thousands of dollars. Additionally, a large retail chain saw CPU time and elapsed time reduced by 67% each as a result of using MRF. And an energy provider was able to reduce CPU by 44% and elapsed time by 35% in MRF testing.
Benefits of Third Party Software for MRF
In light of these examples, it is clear that developers should code their new applications to take advantage of MRF. However, they still have to deal with the mountain of old applications that are still running. DBAs are faced with the decision of either improving performance by utilizing MRF, or looking elsewhere for performance improvement.
Modifying existing applications to use MRF is like giving your car a tune-up: It can be time consuming, but it is beneficial in the long run, helping the car run more efficiently, improving gas mileage and reducing the risk of costly repairs. Organizations have two options: They can tune their applications by hand, which involves recoding and testing; or, they can use third-party software to implement MRF automatically, saving development time and freeing resources for other projects.
Lighten the Load
As big data continues to get exponentially bigger and as IT budgets continue to tighten, DBAs will need to implement as many CPU-saving techniques as they reasonably can. Switching from single-row to multi-row Fetch provides DBAs with another way to meet the challenges of big data, allowing them to reduce CPU times and save money.