三个重要配置work_mem, maintenance_work_mem, effective_cachesize----数据架构师的PostgreSQL修炼

1.work_mem

[plain] view plain copy

1. bash-3.2# grep work_mem postgresql.conf  
2. #work_mem = 4MB             # min 64kB  

这些内存大小被用来完成内部排序与哈希表操作。

如果未分配足够内存，会导致物理I/O。

work_mem这个值是针对每个session的，所以不能设置的过大。

创建表myt，插入1000000行数值：

[plain] view plain copy

1. postgres=# create table myt (id serial);  
2. CREATE TABLE  
3. postgres=# insert into myt select generate_series(1,1000000);  
4. INSERT 0 1000000  

修改当前session的work_mem值为64kB:

[plain] view plain copy

1. postgres=# show work_mem;  
2.  work_mem  
3. ----------  
4.  4MB  
5. (1 row)  
6.   
7. postgres=# set work_mem = '64kB';  
8. SET  
9. postgres=# show work_mem;  
10.  work_mem  
11. ----------  
12.  64kB  
13. (1 row)  

查询当前临时文件占用情况：

[plain] view plain copy

1. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
2.  temp_files | temp_bytes  
3. ------------+------------  
4.           0 |          0  
5. (1 row)  

执行一个order by操作，再查询当前临时文件占用情况：

[plain] view plain copy

1. postgres=# select * from (select * from myt order by id) t limit 1000;  

[plain] view plain copy

1. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
2.  temp_files | temp_bytes  
3. ------------+------------  
4.           1 |   14016512  
5. (1 row)  

说明order by 是需要创建临时文件的。

用pg_stat_reset()来清空统计信息：

[plain] view plain copy

1. postgres=# select pg_stat_reset();  
2.  pg_stat_reset  
3. ---------------  
4.   
5. (1 row)  
6.   
7. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
8.  temp_files | temp_bytes  
9. ------------+------------  
10.           0 |          0  
11. (1 row)  

设置当前session的work_mem为16MB:

[plain] view plain copy

1. postgres=# set work_mem = '16MB';  
2. SET  
3. postgres=# show work_mem;  
4.  work_mem  
5. ----------  
6.  16MB  
7. (1 row)  

执行同样的order by操作，并查询临时文件占用情况：

[plain] view plain copy

1. postgres=# select * from (select * from myt order by id) t limit 1000;  

[plain] view plain copy

1. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
2.  temp_files | temp_bytes  
3. ------------+------------  
4.           0 |          0  
5. (1 row)  

发现，没有临时文件的占用，说明对于此次order by，mem_work足够大了。

2. maintainance_work_mem

该参数分配的存储空间主要用于analyzing，vacuum，create index, reindex等。

首先，清空统计信息:

[plain] view plain copy

1. postgres=# select pg_stat_reset();  
2.  pg_stat_reset  
3. ---------------  
4.   
5. (1 row)  
6.   
7. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
8.  temp_files | temp_bytes  
9. ------------+------------  
10.           0 |          0  
11. (1 row)  

再向myt中插入2000000行数据，查看myt所占磁盘大小：

[plain] view plain copy

1. postgres=# insert into myt select generate_series(1000001,3000000);  
2. INSERT 0 2000000  
3. postgres=#  
4. postgres=# select pg_size_pretty(pg_table_size('myt'));  
5.  pg_size_pretty  
6. ----------------  
7.  104 MB  
8. (1 row)  

查看当前maintenance_work_mem大小：

[plain] view plain copy

1. postgres=# show maintenance_work_mem ;  
2.  maintenance_work_mem  
3. ----------------------  
4.  64MB  
5. (1 row)  

查看当前临时文件占用情况，给myt创建index，再次查看临时文件占用情况：

[plain] view plain copy

1. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
2.  temp_files | temp_bytes  
3. ------------+------------  
4.           0 |          0  
5. (1 row)  
6.   
7. postgres=# create index myindex on myt(id);  
8. CREATE INDEX  
9. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
10.  temp_files | temp_bytes  
11. ------------+------------  
12.           1 |   60039168  
13. (1 row)  

调整maintenance_work_mem到1GB:

[plain] view plain copy

1. postgres=# set maintenance_work_mem = '1GB';  
2. SET  
3. postgres=# show maintenance_work_mem ;  
4.  maintenance_work_mem  
5. ----------------------  
6.  1GB  
7. (1 row)  

drop index:

[plain] view plain copy

1. postgres=# drop index myindex ;  
2. DROP INDEX  

再次给myt创建index，并查看临时文件占用情况：

[plain] view plain copy

1. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
2.  temp_files | temp_bytes  
3. ------------+------------  
4.           0 |          0  
5. (1 row)  
6.   
7. postgres=# create index myindex on myt(id);  
8. CREATE INDEX  
9. postgres=# select temp_files, temp_bytes from pg_stat_database where datname='postgres';  
10.  temp_files | temp_bytes  
11. ------------+------------  
12.           0 |          0  
13. (1 row)  

3. effective_cache_size

这个参数并不会导致具体的内存分配，而仅仅是告诉PostgreSQL对于单个SQL查询，有多少disk cache可以使用。

这片内存区域不是为PostgreSQL保留的，而是优化器来用于完成query excution plan的。

4.   PostgreSQL里的关键缓冲区

除了CLOG buffer（Commit Log buffer），别的应该都已经涉及过。

CLOG buffer主要是来存储每个事务的执行状态。