内存参数有哪些?
- shared_buffers
- work_mem
- maintenance_work_mem
内存参数的作用:
-
shared_buffers
数据库读表时会把表文件从磁盘加载到内存中(shared_buffers),shared_buffers会给表文件分配空间,PostgreSQL数据页大小为8k(即8192 bytes)。
什么时候会把这些数据页刷回磁盘?
1⃣️bgwriter会定时去检测这些数据页是不是脏了(被修改过)(页头会有一个标记,如果被修改过,标记会改变),如果脏了会把数据页刷回磁盘。
2⃣️wal区段满了,postgresql.conf文件中的默认wal区段数是3(checkpoint_segments)就是说有3个WAL区段,每个WAL区段是16M,当3个WAL区段都写满了,就会触发checkpoint,把数据文件刷回磁盘,从而使磁盘中和内存中的数据一致。 -
work_mem
排序操作(order by)会在work_mem中进行,当在work_mem中要进行sort的数据多的时候,数据库效率会降低,所以系统会把数据分成若干部分进行排序,其中的一部分排好序之后会被写进临时文件(此时数据还未落盘),再对其他的数据进行排序,写临时文件,排好序之后会对这些临时文件进行merge,真正的完成排序。这个过程中大量的临时文件写操作会造成i/o,所以如果将有大量排序工作进行 在会话层把work_mem参数调大,就会减少临时文件的写,提高数据库性能。 -
maintenance_work_mem
maintenance_work_mem参数是维持工作内容的参数,当进行分析,清理,建索引,再索引操作时会用到该参数。
一般在进行数据库优化时,会把该参数调大,以便auto_vacuum进程的快速完成。
创建索引时使临时文件增加,调大该参数可以避免临时文件增加的情况。
举个例子:截止目前,数据库中存在31个临时文件:
select datname, temp_files,temp_bytes from pg_stat_database where datname=‘postgres’;
datname | temp_files | temp_bytes
----------±-----------±-----------
postgres | 31 | 1928445585
(1 row)
postgres=# show maintenance_work_mem;
maintenance_work_mem
----------------------
2MB
(1 row)
create index myindex on myt(id);
CREATE INDEX
postgres=# select datname, temp_files,temp_bytes from pg_stat_database where datname='postgres';
datname | temp_files | temp_bytes
----------+------------+------------
postgres | 32 | 1948491409
(1 row)
postgres=# drop index myindex;
DROP INDEX
postgres=# create index myindex on myt(id);
CREATE INDEX
postgres=# select datname, temp_files,temp_bytes from pg_stat_database where datname='postgres';
datname | temp_files | temp_bytes
----------+------------+------------
postgres | 33 | 1968537233
(1 row)
postgres=# drop index myindex;
set maintenance_work_mem to '64MB';
SET
postgres=# create index myindex on myt(id);
CREATE INDEX
postgres=# select datname, temp_files,temp_bytes from pg_stat_database where datname='postgres';
datname | temp_files | temp_bytes
----------+------------+------------
postgres | 33 | 1968537233
(1 row)