pg_prewarm

pg_prewarm Overview

The pg_prewarm module provides a convenient way to load relational data into the operating system buffer cache or MogDB buffer cache.

Install pg_prewarm

Install Manually

1. Access Download page of the MogDB official website, download the pg_prewarm extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf pg_prewarm-1.1-3.1.0-01-CentOS-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd pg_prewarm/
   make install

Install By PTK

Please refer to Installing Extensions.

pg_prewarm loading mode

• mode: The loading mode, the options are 'prefetch', 'read', 'buffer', the default is 'buffer'

• prefetch: Asynchronously preload data into OS cache

• read: The end result is the same as prefetch, but it is synchronous and supports all platforms

• buffer: Preload data into database cache

Create and Use pg_prewarm

1. Create extension pg_prewarm.

   create extension pg_prewarm; 

2. Create a test table.

   create table test_pre (id int4,name character varying(64),creat_time timestamp(6) without time zone); 

3. Insert data into the test table.

   insert into test_pre select generate_series(1,100000),generate_series(1,100000)||  '_pre',clock_timestamp(); 

4. Check the table size.

   mogdb=# select pg_size_pretty(pg_relation_size('test_pre'));
   pg_size_pretty
   ----------------
   5136 kB
   (1 row)

5. Load data to the database cache. The result shows that pg_prewarm divides data into 642 data blocks.

   mogdb=# select pg_prewarm('test_pre','buffer');
    pg_prewarm
   ------------
        642
   (1 row)

6. Check the block size.

   mogdb=# select current_setting('block_size');
    current_setting
   -----------------
   8192
   (1 row)

   The size of each data block is 8 KB in MogDB by default.

   mogdb=# select 642*8; 
    ?column?
   ----------
     5136
   (1 row)