System and method to improve performance of an application using smarter prefetching of data pages based on application statistics.
Publication Date: 2015-Sep-03
The IP.com Prior Art Database
Disclosed is a system and method for improving the performance of a database application using smarter prefetching of database pages based on the statistics collected via application behavior.
Page 01 of 4
System and method to improve performance of an application using smarter prefetching of data pages based on application statistics .
Database applications spend a significant amount of time waiting on data pages mainly due to cache misses. It is well known that cache misses contribute to significant fraction of CPU cycles in a database system. These cache misses would result in wasting CPU cycles while the required data page is being fetched from disk and then loaded on to the memory/buffer pool. This would delay in processing instructions in a database, which in turn would lead to delay in query processing. To avoid these problems database systems use pre-fetching techniques, where the data pages being accessed is already fetched and kept in memory/buffer pool even before it is requested.
The current pre-fetching techniques used in the databases employs methods of pre-fetching sequentially i.e., when a page is being accessed/requested, database systems will try to pre-fetch few more pages sequentially belonging to the same database object (like Table, Index etc) in the anticipation of those pages being requested for access. All these current pre-fetching techniques rely on current database page being accessed and then they try to fetch the pages of same object(sequentially) which is being accessed. Many a times, an application might need to access pages belonging to different tables/indexes etc., and this would lead to cache misses, which in turn would increase query processing time.
A proposal to derive the optimal list of data pages to perform pre-fetching is described, wherein these pages are in turn accessed by database application program during its execution cycle. Here the embodiment of the proposed solution provides a new option while binding an application program to database which would optimize the pre-fetching. Whenever such an application program connects to the database for the first time with this option, the prefetch agent of the proposed solution would start recording the data pages accessed by this applications, after certain number of such iterations (default is 3 iteration, which can be modified based on user preference), the prefetch agent derives the optimal list of data page-ids from all these iterations.
Next time if the same application connects to the database, then the database system would automatically pre-fetch the optimal list of data pages to the memory/bufferpool. Hence reducing the cache misses, in turn reducing the IO activity to read more pages. Thus the overall performance of the application program is drastically improved.
Advantages of using the proposed mechanism instead of the known solutions :
1) Will reduce the cache miss, in a way...