PostgreSQL cache details

The cache buffer of PostgreSQL is very similar and complex to other databases. Because I have Oracle and mindset backgrounds, how can I use it? Why?

The cache/buffer of PostgreSQL is very similar and complex to other databases. Because I have Oracle and mindset backgrounds, how do I use it/when/Why?

Directory:

Pg_buffercache
Pgfincore
Pg_prewarm
Dstat
Linux ftools
Use pg_prewarm to pre-load the relationship/index:
Pgfincore output:
How to refresh the relationship/index in the operating system cache?
Use the range Function of pg_prewarm to pre-load random blocks.

PostgreSQL details: click here
PostgreSQL: click here

Recommended reading:

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Cache .... !!, It is difficult to explain clearly in an article. But I will try to share my knowledge from Heikki, Robert Haas, and Bruce Momjian. There are two layers in PostgreSQL: PG shared buffer and operating system page cache, and any read/write will be cached through the Operating System (no other way so far ). Postgres writes the data in the operating system page cache, and the user feels that the data is written back to the disk. Then, the operating system cache is written to the corresponding physical disk location. PG shared buffer cannot control the system page cache, and even the system cache does not know anything. Therefore, most of the suggestions provided by Postgres DBA or experts are faster disk read/write or better cache.

The cache/buffer of PostgreSQL is very similar and complex to other databases. Because I have a background in Oracle and mindset, how can I use, when, why, and so on? About database cache caching, fixed cache, and database cache refreshing ,, in terms of database pre-loading and so on, I have obtained the answer from this method, but this method is a bit different. Even though my questions are annoying, they are always patient and make me understand how to expand my knowledge. In turn, you can read this blog post... ..

In some learning aspects, I drew a piece of data transmitted between memory and disk in Postgres, as well as some important tools and the new patch (pg_prewarm) provided by Robert Hass ).

Pg_buffercache
It Is A contrib module that tells you what PostgreSQL cache is. Install it as follows:

S = # create extension pg_buffercache
Pgfincore
It has a function to display data in the cache of the operating system page. The Pgfincore and pg_buffercache Federation are very convenient. Now, it can obtain PG buffer and operating system page cache information at the same time. Thanks to Cerdic Villemain. The backbone of Pgfincore is fadvise and fincore, which are linux ftools. You can use the source code to install fincore/fadvise. You can use pgfincore contrib module or ftools to produce the same results. I tried both of them and they are very simple and excellent.

Installation:

Download the latest version:

Use root User:

Export PATH =/usr/local/pgsql91/bin: $ PATH // you can specify the PATH for executing pg_config.
Tar-xvf pgfincore-v1.1.1.tar.gz
Cd pgfincore-1.1.1
Make clean
Make
Make install now connects to PG and runs the following command:

Postgres = # create extension pgfincore;
Pg_prewarm
Is it possible to pre-load the relationship/index to the PG cache in PostgreSQL? Yes, of course. Thanks to Robert Hass, he submitted some patches to the community and expects them to work in PG 9.2 or PG 9.3. However, you can use this patch to perform some tests in PG 9.1.
Pg_prewarm
There are three modes:

PREFETCH: gets data blocks asynchronously to the operating system cache, instead of PG buffer (the prompt is only the operating system cache)
READ: READ all data blocks to the virtual cache and write them to the operating system cache (the prompt is only the operating system cache)
BUFFER: Read all or some data blocks to the database BUFFER.
Installation:

I add the pg_prewarm patch to the PG source code installation. You need to adjust your installation steps in each step.

Decompress PG source code path:/usr/local/src/postgresql-9.1.3
PG installation path:/usr/local/pgsql91
All download paths:/usr/local/src

Note: Install PG before applying the pg_prewarm patch

1. download the patch and place it in/usr/local/src.

Patch additional email address

+ TgmobRrRxCO + t6gcQrw_dJw + Uf9ZEdwf9beJnu + RB5TEBjEw@mail.gmail.com

2. After the download, go to the PG source code directory and then perform the following steps.

# Cd/usr/local/src/postgresql-9.1.3
# Patch-p1 <../pg_prewarm.bin (after the download, I renamed pg_prewarm)
# Make-C contrib/pg_prewarm
# Make-C contrib/pg_prewarm instal3. the above commands will create files in the $ PGPATH/contrib/extension directory. Now you are ready to add the contrib module.

S = # create EXTENSION pg_prewarm;
CREATE EXTENSION
Postgres = # \ dx
List of installed extensions
Name | Version | Schema | Description
---------------- + --------- + ------------ + ----------------------------------------
Pg_buffercache | 1.0 | public | examine the shared buffer cache
Pg_prewarm | 1.0 | public | prewarm relation data
Pgfincore | 1.1.1 | public | examine and manage the OS buffer cache
Plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows) Documentation:

/Usr/local/src/postgres-9.1.3/doc/src/sqml
[Root @ localhost sgml] # ll puple *
-Rw-r -- 1 root 2481 Apr 10 pgprewarm. sgml
Dstat
It is a combination of tools such as vmstat, netstat, and top to form a "dstat" linux Command. When the database performance is abnormal, we will open several terminals to display the process, memory, disk read/write, and network information from the operating system level, however, switching between these windows is very painful. Therefore, dstat has several options to help display all commands in an output window.

Installation:
Dstat download connection: (RHEL 6)

Wget or

Yum install dstat document: