Multiple Buffer Pools and Dynamic Resizing of Buffer Pools

An autonomic database management system is a self tuning, self optimizing, self healing and self protecting database management system (DBMS). Since expert database administrators (DBAs) are scarce, introducing a DBMS that is self tuning will decrease the total cost of ownership of the system. In this thesis we present a first step to incorporating self tuning capabilities in the PostgreSQL DBMS. The buffer area is the main memory management area of the DBMS. Effective use of this area ensures efficiency of the DBMS. Some DBMSs split the buffer area into multiple buffer pools and this has led to performance increases in some cases. Once multiple buffer pools are supported it is up to the DBA to size them based on each of their needs. Optimal sizing leads to good performance, therefore, as the workload changes the DBA has to adjust the sizes of the buffer pools. We extend PostgreSQL (Version 7.3.2) to support multiple buffer pools. We remove the dependency on the DBA and automatically adjust the sizes of the buffer pools to changes in the environment. We present a number of experiments to verify our approach