Browse Prior Art Database

Managing OLTP Database Performance Using Row Access Time

IP.com Disclosure Number: IPCOM000241466D
Publication Date: 2015-May-04
Document File: 5 page(s) / 87K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is a method to manage online transaction processing (OLTP) database performance by maintaining row access time for each row returned to client applications, and then designate data as hot or cold based on row access time. The method establishes and utilizes two database servers, one for managing hot data, and one for managing cold data.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 37% of the total text.

Page 01 of 5

Managing OLTP Database Performance Using Row Access Time

In an online transaction processing (OLTP) environment, it is advantageous to minimize query execution time. One way to reduce query execution time is to cache data in memory, sometimes including previous query results. However, there are tradeoffs and limitations. As the amount of data grows, hardware must be improved to maintain good performance. This may include larger memory, faster Central Processing Units (CPUs), additional CPUs, better disks, etc., which may become very expensive. Database maintenance operations and statistics update operations incur longer processing times as database size grows. Scaling up is more expensive than scaling out. Even though most OLTP queries work with recent data, customers typically do not delete old data.

Customers may use data compression techniques to reduce disk input/output (IO) and memory usage. However, compression techniques may only reduce data size to a 3:1 ratio. (Compression techniques may still be used with the method disclosed herein.)

Another technique customers may use is a scale out approach by sharding data across multiple nodes, which may be hashing or expression based. Although scaling out often tends to be less expensive than scaling up, sharding may not work very well with applications requiring strong transactional support and Atomicity, Consistency, Isolation, and Durability (ACID) properties.

A method is herein disclosed for managing OLTP database performance using row access time. The method maintains a record of row access time for each row returned to client applications. Then, the method designates data as hot or cold based on row access time. The method configures two database servers, one for managing hot data, and one for managing cold data. From time to time, the method moves cold data from the hot to the cold server. By default, client applications work with data in the hot server. However, client applications may choose to work with both hot and cold data.

Accessing data in the cold server is transparent to the client applications. The disclosed method tends to work best in an environment in which 80% of the client applications work with recent data, and 20% of the applications work with historical data as well.

According to embodiments of the present method, a database administrator (DBA) decides what is hot data and what is cold data in terms of how recently data was accessed which is defined using maximum and minimum thresholds. For example, the DBA may define data accessed in the last 30 days as hot data, and everything else as cold data. In this example, 30 days is the maximum threshold to qualify data as hot. A minimum threshold is used to optimize updating row access time. The database server tracks row access time. The database server designated as hot periodically moves cold data (as defined by the DBA) to the cold server. After the data is moved, the hot data tables are be de-fragmented to...