Database – Buffer Pool – Clearing

Background

During Database performance stressing, data is ingested into memory and written to disk.

As one tries out different database table structures and Data Manipulation Languages ( DML ) queries, one often finds it useful to clean the slate in between.

 

Granularity

Like any other operation one can target processing against the server, database, table, column, or record.

Vendor

In this post, we will briefly see how each Database Vendor manages this need.

The vendors we will consider are :-

  1. Microsoft ( SQL Server )
  2. Oracle ( Oracle DB Engine & MySQL )
  3. PostgreSQL
  4. Db/2

 

Microsoft

SQL Server

Outline

  1. Instance
    • DBCC DROPCLEANBUFFERS
  2. Database
    • ALTER DATABASE SET OFFLINE/ONLINE ( Offline/Online )
  3. Table
    • Not Supported

Tasks

DBCC DROPCLEANBUFFERS
SQL
DBCC DROPCLEANBUFFERS
ALTER DATABASE / SET OFFLINE AND ONLINE
Outline

This is not quite freeing the buffer pool without impacting current sessions.

When a database is taking offline, it is unavailable to everyone.

If you choose to so, the steps are :

  1. Take Database Offline
  2. Bring Database Online
SQL – Actual – OFFLINE
ALTER DATABASE [hrdb] SET OFFLINE
SQL – Actual – ONLINE
ALTER DATABASE [hrdb] SET ONLINE
Table

????

 

Oracle

Outline

  1. Instance
    • Flush Buffer Pool
  2. Database
    • ???
  3. Table
    • ???

Tasks

Flush Buffer Pool

Version 9i
SQL
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Version 10
SQL
alter system flush buffer_cache;

MySQL

Outline

  1. Instance
    • ???
  2. Database
    • ???
  3. Table
    • ???

PostgreSQL

Outline

  1. Instance
    • Shutdown PostgreSQL Server
  2. Database
    • ???
  3. Table
    • ???

 

Db/2

Outline

  1. Instance
    • Shutdown and Restart Db/2 Server
  2. Database
    • ???
  3. Table
    • ???

Summary

SQL Server and Oracle

Unfortunately, only Microsoft SQL Server and Oracle namesake database supports freeing memory for a running instance.

 

MySQL

MySQL has something called Query Cache.  It is different from Buffer Pool as it primary relates to caching query results.

Even then it has been deprecated since MySQL 5.7.20.

As of MySQL 8.0, the Query Cache has been completely removed.

 

Db/2

To flush a DB/2 instance buffer pool, one likely has to restart the DB/2 instance.

One basically has to issue db2stop and db2start.

 

References

Oracle

  1. Burleson Consulting
    • Oracle: flush the data buffer cache
      Link
  2. Julian Dyke
    • Oracle Internals

IBM

IBM – Db/2 – Linux, Unix, and Windows

  1. IBM
    • Developer Works
      • Forum Directory >‎ Information Management >‎ Forum: IBM DB2 for Linux, Unix, and Windows Forum >‎ Topic: How to flush /clear down a Bufferpool contents
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s