PostgreSQL Memory for Database Caching(2)

Inspecting the database cache

想让postgresQL 使用shared_buffers database cache ，需要使用pg_buffercache module，这是一个contrib里的可选模块。

Installing pg_buffercache into a database

In order to install this utility or to use it, you will need to be connected to the database as its superuser. Here's a sample that installs pg_buffercache into a new database, presuming the RedHat Linux standard directory installation tree; substitute your own PostgreSQL installation directory here:
$ createdb pgbench
$ psql -d pgbench -f /usr/share/postgresql/contrib/pg_buffercache.sql
注意：在8.x和9.x里都没有pg_buffercache.sql，需要从网上去下载。
安装此sql后，就可以在客户端上查看相关的share memory信息。SQL语句如下：
SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';
或者
select count(*) from pg_buffercache;

Database disk layout

In order to fully interpret the information returned by utilities such as pg_buffercahe,and therefore use that information to adjust memory sizing,
it's helpful to know a bit about how databases are stored on disk.
使用SQL命令：
show data_directory;
就可以知道数据存放的路径
如果想查看其他文件的存放路径，命令如图：


在data目录下面有一个base目录。
base目录下的内容如图：

 这个目录下的数字术语叫 object identifier or OID.这个标识是唯一的。

这些ID对应一个数据库，如果想知道映射关系，你也可以用SQL语句来查看，命令如图：

补充下，如果查询表的相关信息，是pg_class这张表，SQL就省略了。

你也可以在命令行里查看，命令如图：

Creating a new block in a database

Once you insert something into this table, a page is allocated in the buffer cache to hold that new information, and a standard 8K block is allocated on disk.