Autonomic Buffer Pool Configuration in PostgreSQL

disturbedoctopusData Management

Nov 27, 2012 (4 years and 8 months ago)


Autonomic Buffer Pool Configuration in PostgreSQL

Wendy Powley, Pat Martin, Nailah Ogeer and Wenhu Tian
School of Computing
Queen’s University
Kingston, ON, Canada
{wendy, martin, ogeer, tian}

Abstract - As database management systems (DBMSs)
continue to expand into new application areas, the
complexity of the systems and the diversity of database
workloads are increasing. Managing the performance of
DBMSs via manual adjustment of resource allocations in
this new environment has become impractical. Autonomic
DBMSs shift the responsibility for performance
management onto the systems themselves. This paper
serves as a proof of concept, illustrating how autonomic
principles can be applied to a DBMS to provide automatic
sizing of buffer pools, a key resource in a DBMS. We
describe an implementation of our autonomic system in
PostgreSQL, an open source database management system,
and provide a set of experiments that verify our approach.
Keywords: Autonomic Computing, Database Management
Systems, buffer pool configuration, performance, resource
1 Introduction
As consumers demand more functionality and greater
sophistication from Database Management Systems
(DBMSs), vendors have been quick to deliver. However,
the desire to manage complex data types, the ability to store
very large objects, and the emergence of diverse and
varying workloads are factors that have led to
unmanageable complexity. It is no longer feasible for
database administrators (DBAs) to manually configure and
tune these systems.
One approach to this management problem is an
autonomic DBMS that is capable of automatically
managing its resources to maintain acceptable performance
in the face of changing conditions [3][6]. An autonomic
DBMS must be able to perform typical configuration and
tuning tasks including determining appropriate allocations
for main memory areas such as the buffer pools and the sort
heap, mapping database objects to buffer pools and
adjusting the many DBMS configuration parameters to
maintain acceptable performance.
Effective use of the buffer area can greatly influence
the performance of a DBMS by reducing the number of
disk accesses performed by a transaction. Many DBMSs
divide the buffer area into a number of independent buffer
pools and database objects are allocated among the pools.
Figure 1 illustrates this model where the indices, the stock
table and the warehouse table are all assigned to separate,
individual buffer pools, while the customer and item table
share a buffer pool. To make effective use of the multiple
buffer pools a DBA must choose an appropriate number of
buffer pools, map database objects to buffer pools (we term
this clustering), and accurately allocate the available
memory among the buffer pools. These critical choices
depend upon workload and system properties that may vary
over time, perhaps requiring reconfiguration of the buffer
pool settings.

B uf f er
P ool s
w ar eh ou s e
c us tom er
s t oc k
it em
i nd i ces

Figure 1: Multiple Buffer Pool Model
In past research we have examined the issues related
to buffer pool configuration and have proposed and
implemented solutions for multiple buffer pool sizing
[9][12] and for the clustering problem [14]. Our solutions,
however, have been implemented as stand-alone tools,
operating external to the DBMS code. These tools may
assist a DBA in the decision making process, but they
cannot, at this point, be considered part of a truly
autonomic solution. It is our goal to augment a DBMS
with our algorithms to provide fully autonomic buffer pool
configuration, thus relieving a DBA of this responsibility.
An autonomic DBMS system can be viewed as a
feedback control loop as shown in Figure 2 [8], controlled
by an Autonomic Manager. The autonomic manager
oversees the monitoring of the DBMS (the Managed
Element), and by analyzing the collected statistics in light
of known policies and/or goals, it determines whether or
not the performance is adequate. If necessary, a plan for
reconfiguration is generated and executed.
In this paper we present an implementation of
autonomic functionality for the buffer pool size
configuration problem using the open source database,
PostgreSQL. Although this paper focuses on a specific
task, the general framework presented is extensible to other
problems and systems. The main contribution of this paper
is a demonstration of the feasibility of our approach to
adding autonomic features to a DBMS.

Figure 2. Feedback Control Loop
The remainder of the paper is structured as follows.
Section 2 presents related work. Section 3 describes our
approach to automating the buffer pool size configuration
problem and we present some experiments to validate our
approach in Section 4. Section 5 provides a summary and
suggests some ideas for future research.

2 Related Work
Autonomic DBMSs, and autonomic systems in
general, have received a great deal of attention both in the
academic and the commercial worlds [3][6]. Self-tuning
concepts have been applied to problems such as index
selection [13], materialized view selection [1] and memory
management [4][5][7][9]. Chaudhuri and Weikum [6] cite
the need for self-tuning systems as an important reason to
rethink current DBMS architectures.
The buffer pool sizing problem has been tackled by
several researchers. Dynamic Tuning [4] groups all
database objects belonging to a transaction class into an
individual buffer pool and assumes there is no data sharing
among the transaction classes. To meet the specified
transaction time goals, Dynamic Tuning tunes the buffer
pool sizes according to the relationship the between buffer
pool miss ratio and the response time of the transaction
class. Data sharing was addressed by the Dynamic
Reconfiguration algorithm [9]. This approach tunes the
buffer pool sizes to satisfy the response time goals based on
the assumption that the average response time of a
transaction class is directly proportional to the average data
access time of the transaction instance of that class, while
the average data access time is a function of the buffer pool
size. A potential problem with the goal-oriented approach
is that it requires DBAs to pre-define reasonable class-
specific goals, which can be a very difficult task.

3 Approach
Our goal is to implement the buffer pool sizing
approach proposed by Tian [12] within the autonomic
framework shown in Figure 2. The sizing algorithm
developed by Tian was originally implemented as a stand
alone tool, external to the DBMS. The tool is used by a
DBA when he/she suspects that a performance decrease
may be due to incorrectly sized buffer pools. The DBA
collects statistics that are used as input to the buffer pool
sizing tool. The tool analyzes the statistics and suggests a
new buffer pool size configuration which the DBA can use
to manually reconfigure the buffer pools. Implementing
these ideas within the autonomic framework, the DBMS
must recognize that performance has degraded
(monitoring) and that the buffer pools are no longer
functioning efficiently (diagnosis/analysis). The system
must automatically initiate the sizing algorithm (plan
generation) and size the buffer pools accordingly (plan
execution). This autonomic functionality is implemented
within the DBMS itself.
PostgreSQL is an open source DBMS and, for this
reason, is an ideal candidate for demonstrating the
incorporation of autonomic features. PostgreSQL,
however, does not implement multiple buffer pools so our
first task was to add multiple buffer pool functionality to
the DBMS. Our modifications allow a DBA to specify the
number of buffer pools and their initial sizes in the start up
configuration file, and to assign database objects to buffer
pools via the command line. Many objects may share a
buffer pool. For the purpose of this paper we assume that
the correct clustering of objects is known, and that the
clustering solution remains stable throughout.
3.1 Monitoring
To support the monitoring required for the sizing
algorithm, additional code was added to the PostgreSQL
statistics collector to include statistics for each buffer pool
access including the number of logical reads, the number of
physical reads, and the average data access time (DAT)
incurred to fetch a data object. A logical read refers to any
data request made by an application. The data may already
be resident in the buffer pool or, if it is not, the request
results in an access to disk to retrieve the data, thus referred
to as a physical read.
System monitoring incurs a certain amount of
overhead, so it is important that monitoring is lightweight
and that the monitoring facilities are used sparingly. For
this reason, the statistics collector can be turned on and off
as necessary.
3.2 Analysis/Diagnosis
The analysis/diagnosis stage involves analyzing the
performance data collected by the monitor to determine
whether or not there has been a shift in performance and, if
so, determining the possible cause(s). This is a complex,
difficult task [11]. In the current approach, the system must
determine if there has been a change in the efficiency of the
buffer pool usage that may warrant a change to the buffer
pool sizes.
The standard metric for determining the efficiency of
the buffer pools is the buffer pool hit rate, that is, the
fraction of logical reads (all data accesses) satisfied by the
buffer pool cache without requiring a physical read (a disk
access). Maximizing the hit rate minimizes the number of
physical data accesses, which in turn maximizes
Managed Element
Autonomic Mana

throughput. We found, however, that hit rate is not
necessarily the best choice for a cost function because all
physical data accesses do not cost the same. The cost of a
physical data access is influenced by several factors
including the type of access (sequential or random), the
physical device involved, where the data is placed on the
device, and the load on the I/O system.
We found that a more suitable criteria to evaluate the
efficiency of the buffer pool is the average data access time
(DAT), that is, the average time to satisfy a logical read
request. This value is the average access time across all
buffer pools. The DAT is dependent upon the buffer pool
hit rate (the probability of finding the requested data in the
buffer pool) and the types of accesses made to each buffer
pool. An increase in the DAT indicates an increase in
physical accesses given that physical I/O requires more
time than accesses to RAM. To maximize buffer pool
usage it is desirable to minimize the number of physical
The buffer pool sizing algorithm is triggered once a
threshold of change in DAT is reached. The threshold in
our system is set to 5 percent. Therefore, if the average
DAT across buffer pools increases by 5 percent, the
resizing algorithm is triggered. This threshold can be
varied, but we found experimentally that a 5 percent
change is enough to make a significant difference in the
calculated optimal sizes [10].

3.3 Plan Generation
In the analysis/diagnosis stage the autonomic DBMS
determines the potential cause of a performance shift. This
may be a single resource, or a set of resources. In the plan
generation stage, the system determines how the
resource(s) should be tuned in order to correct the problem.
In the current work, this involves determining how to resize
the buffer pools to maximize performance.
The buffer pool sizing algorithm is implemented as an
internal routine in the DBMS. The task involves allocating
the M buffer pages among the K buffer pools such that
performance is maximized. This is, in general, a complex
constrained optimization problem that cannot be solved
exactly so heuristic methods must be used. We examined
several methods and found that a greedy method is most
effective for the problem [12].
The cost function used in our approach focuses on
minimizing the average time for a logical data access,
which takes into account both hit rate and physical data
access cost. System throughput is inversely related to
average logical access cost. We maintain data about buffer
pool performance at different allocations for the given
workload and use curve-fitting techniques to predict
performance under new allocations.
The overall system performance is maximized when
the weighted cost of all logical reads (WcostLR) is
minimized. The expected system cost of a logical read is
calculated by averaging the cost of logical reads across all
K buffer pools. WcostLR is expressed by:

)( (1)
where W
is the buffer access weight on BP
and costLR
the average cost of a logical read on BP
. The buffer access
weight (W
) indicates the percentage of the total number of
logical reads that are serviced by BP
. A lower WcostLR
indicates a lower data access time, thus yielding a faster
transaction response time.

The cost of memory access is obviously much faster
than the cost of physical access, thus it is reasonable to
assume that the data access time for a buffer pool is
primarily the cost of disk accesses. Therefore, we have
noPR costPR noLR costLR
where noLR
and noPR
represent the number of logical
reads and number of physical reads on BP
and costPR
indicates the cost of a physical read on BP
From this equation, we obtain:
costPR costLR ×=

Noting that
defines the buffer pool miss ratio,
which can also be represented by (1 –
) where
the hit ratio for BP
, we derive the following equation:
)HR - (1 costPR costLR

For a buffer pool that caches specific database
objects, we assume that the cost to perform a physical read
is fixed, namely,
is a constant for a given buffer
pool. Therefore, equation 4 theoretically represents a linear
relationship between the buffer pool hit ratio and the buffer
of the form:
c kx f(x) +=
where the slope k is -
the intercept, c, is

and x is

To complete the equation, we use a curve-fitting
technique to derive the parameters k and c. For a buffer
pool BP
, two samplings at two different buffer pool size
configurations (S
and S
) are taken. We then have:

)HR(S - )HR(S
)costLR(S - )costLR(S

)HR(S k - )costLR(S c

There are many proposed approaches to predicting
buffer hit rate in the literature. To simplify our
implementation, we chose to use Belady’s equation [2].
A greedy algorithm is then applied to search the
optimal sizes for the buffer pools. The goal of the greedy
algorithm is to minimize
. It starts from an initial
size allocation and then examines all adjacent allocations
that can be produced by shifting a fixed number of pages,
, between pairs of buffer pools. If the adjacent
allocation with the lowest estimated
has a lower
than the current allocation then the sizing
algorithm moves to the adjacent state with the lowest cost.
The algorithm halts when no further moves to new
allocations can be made.

3.4 Plan Execution/Reconfiguration.
The sizing algorithm described above produces a size
configuration <S
, S
.. S
>, where K is the number of
buffer pools, that minimizes the overall cost of a logical
read. The autonomic DBMS dynamically resizes the buffer
pools to the new size configuration.
Each buffer pool in our modified version of
PostgreSQL consists of a circular doubly linked list of
buffer pages called a
free list
. Any page on the free list is
a candidate for page replacement. If a buffer is in use by
the DBMS it is considered to be
and thus
unavailable for replacement. PostgreSQL uses a least
recently used (LRU) page replacement algorithm. The
buffer manager ensures that the LRU page is always found
at the head of the list. Dirty pages (that is, pages that have
been updated by the DBMS) are written to disk prior to

Figure 3: Contents of 2 Buffer Pools (S
, S
) = (8, 8)

Figure 3 shows the contents of 2 buffer pools with a
size configuration of (S
, S
) = (8, 8). Buffers 1, 2, 6,
13, 14 and 15 are currently in use by the DBMS. If the
buffer pool sizing algorithm suggests a new size
configuration of (S
, S
) = (6, 10) then buffers 6 and 7
are shifted to the second buffer pool as shown in Figure 4.
Reassigning buffer 7 to a new buffer pool simply involves
resetting the next and previous pointers in the lists. Buffer
6, however, is in use and cannot be shifted immediately.
This buffer remains associated with Buffer Pool 1 until
after the next access, at which point, the contents, if dirty
are flushed to disk and the buffer descriptor changed to
associate buffer 6 with Buffer Pool 2. Since the data has
been accessed, this buffer appears as a pinned buffer in
Buffer Pool 2.

Figure 4: Buffer Pools After Resizing (S
, S
) = (6, 10)

4 Validation
A set of experiments was performed with the
following objectives:

Verify that the PostgreSQL multiple buffer pool
implementation has a positive impact upon

Quantify the improvements associated with the
dynamic sizing algorithm.

Determine the additional overhead incurred by the
monitoring required for the sizing algorithm.

Determine the overhead involved in running the sizing
algorithm and resizing the buffer pools.

Illustrate our dynamic approach to autonomic buffer
pool sizing.

The experimental results are presented briefly here.
Full details of these experiments, as well as additional
experiments, can be found in [10].
Our experiments were run using a modified version of
PostgreSQL (7.3.2) (as well as the original version) on a
Sun Solaris machine configured with 15 9GB disks, 2GB
of RAM and 6 processors. We use a database with two
tables: TabA (15000 tuples), and TabB (7500 tuples).
Each table has unique id field (1 to N, with N being the
number of tuples in the table). Each tuple is 50 bytes.

4.1 Workload
The following two transactions make up our workload:

Transaction A
select * from TabA;
select id from TabA where id < 50;
select id from TabA where id < 100;

Transaction B
select * fromTabB;
select id from TabB where id < 50;
select id from TabB where id < 100;

Each transaction requires a table scan of a large table,
followed by a selection of a portion (or “hot set”) of the
data contained in the table. If the buffer pool is too small
to hold the entire table, many pages will be swapped in and
out of the buffer pool. If the two tables share a buffer pool
that is not large enough to accommodate both tables, the
execution of Transaction B will replace pages occupied by
TabA and the execution of Transaction A will replace
pages occupied by TabB, resulting in an excess of physical
The capacity of each buffer in PostgreSQL is 8
kilobytes. Therefore, the number of tuples that can fit in a
buffer pool with K pages is:

Number of tuples = floor[ (K * 8192) / 50 ] (7)

To hold the contents of TabA and TabB we require a total
of 138 buffers (92 for TabA and 46 for TabB). To
illustrate how our autonomic sizing system works we
restrict our entire buffer area to 124 buffers, split between 2
buffer pools, B1 and B2. TabA is assigned to B1 while
TabB is assigned to B2.
To illustrate the effectiveness of the multiple buffer
pool implementation, we use a buffer pool size
configuration of (S
, S
) = (64, 64), an equal distribution
of buffers between the two buffer pools. In this case we
expect to see a hit rate of near 0 for BP1 and a hit rate of
100 percent for BP2 (since BP2 is large enough to hold the
entire contents of TabB), which is what we observe. We
compare the throughput (transactions per second) using the
modified version of PostgreSQL to the original,
unmodified version of PostgreSQL. In both cases the
statistics monitor is turned on for the duration. We observe
an improvement in throughput of 7.6 percent with the
multiple buffer pool configuration, verifying that by
segregating the tables, overall system performance has
improved significantly.
Monitoring incurs a certain amount of overhead as
statistics must be collected for every buffer access for each
buffer pool. To examine the impact of the monitoring, we
compare the performance of our workload using the
modified version of PostgreSQL with the statistics collector
off, then with the statistics collector on. With 5 clients
issuing transactions simultaneously, we observe a 16
percent decrease in throughput when the statistics collector
is turned on. This significant overhead suggests that the
current statistics monitor must be invoked sparingly.
The main goal of this research is to integrate
autonomic buffer pool sizing to PostgreSQL to illustrate
that the feedback approach is feasible and beneficial. The
system must monitor the performance, recognize that the
buffer pools should be resized, run the sizing algorithm,
and dynamically resize the buffer pools to improve
performance. The following sequence of events illustrates
one scenario that we used to demonstrate our approach.
Note that all steps are dynamic, and that the workload
described above is running continuously with 5 clients
issuing transactions simultaneously.


An initial optimal size configuration was determined
by collecting statistics under 2 buffer pool size
configurations, (S
, S
) = (64, 64) and (S
, S
) =
(96, 32). These statistics were used by the buffer
pool sizing algorithm to determine the optimal size
configuration, (S
, S
) = (81, 47).


The workload was executed under the optimal buffer
pool size configuration, (S
, S
) = (81, 47) as
determined in Step 1. Under this configuration, after
800 transactions, the data access time (DAT) was
recorded as (DAT
) = (112.8, 0) where
DAT is measured in milliseconds.


The sizes of the tables TabA and TabB were
modified to simulate a change in the workload.
7500 tuples were removed from TabA and 7500
tuples were added to TabB. The DAT after the
database modifications, running the same workload,
was measured at (DAT
) = (0, 121).
Given that this average was more than 5 percent
greater than the average DAT measured earlier,
) = (112.8, 0), the resizing
algorithm was triggered. The algorithm used the
current statistics combined with the statistics from
previous buffer pool configuration to suggest a new
size configuration, (S
, S
) = (55, 73). The DAT
measured under this configuration was (DAT
) = (0, 71.5).

This scenario illustrates that our autonomic DBMS
can recognize a change in performance and respond
appropriately, and successfully, to this change.
The CPU overhead was measured during the
execution of the sizing algorithm and during the buffer pool
resizing. CPU usage increased approximately 1.5 percent
during this time, indicating that the sizing algorithm and
the buffer pool resizing incurs only negligible overhead.

5 Summary
We have presented an approach to introducing autonomic
features into a database management system to automate
the complex management issues associated with these
systems. We have demonstrated the feasibility of our
approach using the buffer pool sizing problem as an
example. The system is able to monitor itself, recognize
that the buffer pools are functioning less efficiently and
correct the problem dynamically.
The main downfall of the approach is the overhead of
monitoring. An autonomic system must be self-aware. For
a system to “know itself”, it must be able to monitor its own
performance and be able to compare current and past
performance to recognize changes. Future work will focus
on lightweight and less obtrusive monitoring techniques.
We have incorporated Xu’s buffer pool clustering
algorithm [12] into PostgreSQL and we are in the process
of testing this approach in conjunction with the sizing
algorithm. The main issue with this implementation is
determining when it is necessary to re-cluster the database
objects. This involves recognizing that the workload has
changed significantly and/or the database size has changed
significantly, thus affecting the way in which the objects are


S. Agrawal, S. Chaudhuri and V. Narasayya.
“Automated Selection of Materialized Views and Indexes,”
Proc. of 26
Int. Conf. on Very Large Databases
Egypt, September 2000.

L. A. Belady, “A Study of Replacement Algorithms
for Virtual Storage Computer”,
IBM System Journal
, Vol 5
No. 2, pp. 78-101, July 1966.

P. Bernstein, M. Brodie and S.Ceri, et al., “The
Asilomar Report on Database Research,”
Vol 27, No. 4, pp. 74-80
Dec. 1998.

K. Brown, M. Carey and M. Livny, “Managing
Memory to Meet Multiclass Workload Response Time
Goals,” Proc. Of 19
Int. Conf. on Very Large Databases,
Dublin, Ireland, pp. 328-341, Aug. 1993.

K. Brown, M. Carey and M. Livny, “Goal Oriented
Buffer Management Revisited,”
Vol 25 No. 2, pp.

353 – 364, June 1996.

S. Chaudhuri, G. Weikum, “Rethinking Database
System Architecture: Towards a Self-Tuning RISC-Style
Database System,” Proc. of 26
Int. Conf. on Very Large
Databases, Cairo, Egypt, pp 1-10, Sept. 2000.

J. Y. Chung, D. Ferguson and G. Wang, “Goal
Oriented Dynamic Buffer Pool Management for Database
Systems,” Proc. of Int. Conf. on Engineering of Complex
Systems (ICECCS’95), Ft. Lauderdale, Florida, Nov. 1995.

Kephart, J.O., Chess, D.M., “The Vision of
Autonomic Computing,”
, Vol 36 No. 1, pp. 41-
50, 2003.

P. Martin, H. Li, M. Zheng, K. Romanufa and W.
Powley, “Dynamic Reconfiguration Algorithm:
Dynamically Tuning Buffer Pools,” Proc. of 11
Int. Conf.
on Database and Expert Systems Applications, London,
UK, pp. 92-101, Sept. 2000.

N. Ogeer, "Buffer Management Strategies for
PostgreSQL," MSc Thesis, Queen’s University, April 2004.

S. Parekh, N. Gandhi, J. Hellerstein et al, “Using
Control Theory to Achieve Service Level Objectives In
Performance Management,”
Real-Time Systems,
Vol. 23,
No. 1-2, pp. 127-141, 2002.

W. Tian, W. Powley and P. Martin. “Techniques for
Automatically Sizing Multiple Buffer Pools in DB2,”
Proceedings of CASCON 2003, Toronto, Canada, pp. 237-
245, 2003.

G. Valentin, M. Zuliani, D. Zilio, G. Lohman and A.
Skelly. “DB2 Advisor: An Optimizer Smart Enough to
Recommend Its Own Indexes,” Proceedings of Int. Conf.
on Data Engineering, San Diego, California, pp. 101-110,
February 2000.

X. Xu, P. Martin and W. Powley, “Configuring Buffer
Pools in DB2/UDB,” Proceedings of CASCON 2002,
Toronto, Canada, pp. 171-182, Oct 2002.