理解postgresql.conf参数-work_mem

做一些实际的例子来理解PostgreSQL的work_mem参数。
我们知道,work_mem用来限制进行排序或hash时的内存分配。我们先做一个排序,看看它是如何工作的。

准备测试环境

首先是测试前的准备工作,我们需要创建一个测试表:

CREATE TABLE tbl_test 
(   id serial PRIMARY KEY,
    random_text text,
    created_date TIMESTAMP WITH TIME zone DEFAULT CURRENT_TIMESTAMP
);

9be32795dcac330ccaf7887bd19f0b40

创建一些测试数据并写入到测试表中(1000万条)

\! perl -e '@c=("a".."z","A".."Z",0..9); print join("",map{$c[rand@c]}10..20+rand(80))."\n" for 1..10000000' > /tmp/random_strings

这个操作在文件中生成了1000万条随机的数据,下面使用COPY命令写入测试表中

COPY tbl_test (random_text) from '/tmp/random_strings';

表中数据如下图
bd364a07d4b521f75970e31e5ef554e6
因为id,我们将能轻松地限制我们是否想要10个,100、1000或更多的行进行排序,而random_text列没有索引,用来测试排序。

work_mem参数测试

测试环境准备好了,开始第一个测试:
先将当前的work_mem限制设置为1MB,在设置之前可以先进行一次RESET work_mem

testdb=# SET work_mem = ‘1MB’;
testdb=# SHOW work_mem;
 work_mem 
----------
 1MB
(1 row)

开始第一个测试,仅做简单查询和排序:

EXPLAIN ANALYZE SELECT * FROM tbl_test WHERE id <= 10 ORDER BY random_text ASC;

aff26c8da55ae510cddb8b7772853f46

我们看到当前的SortMethod为quicksort,对10行数据进行排序,PostgreSQL使用了26kb内存。
接下来,对更大的数据集进行排序的测试,work_mem仍然设置为1MB。下面的表格记录了分别对10、100、1000、10k、100k和1m行进行测试的结果:
ca51cc7576907d4c9a57818155ec68ba

观察测试结果,当排序数据达到10k行时,SortMethod由内存排序开始借助临时文件;有趣的是每行的排序时间并没有增加(或只在很小的、可以忽略的范围内波动),难道对10k行排序时,仅占用752kb的临时磁盘文件?显然不是这样的,实际上是目前我们设置的work_mem已经被占满,PostgreSQL需要另外借助752kb的临时磁盘文件对这10k行进行排序。如何在排序10k行时全部使用内存?我们可以通过加大work_mem进行测试。

02577806970b5cadc3d485bafcf50eaf

当我们设置work_mem为2MB时,可以看到10k行的排序会全部在内存中进行,使用了1543kb内存,同时执行时间也由原来的144.726ms缩短为35.878ms。

下面测试hash的排序

EXPLAIN ANALYZE SELECT * FROM tbl_test a JOIN tbl_test b USING (random_text) WHERE a.id < 10;

2638d4bb0ecdc2600db5226344c0d262

从上图中看到,就哈希而言不会使用到磁盘临时文件,而是增加了“Batches(批次)”的概念,或者说至少在查询计划中没有提到使用磁盘临时文件。
将数据量加到10W条之后
a4068d03a164ec1f8851768b5502e059

将数据量加到100W条
68bda46d9bf8801c8797a21fdf012377

增加数据量到100W条之后,可以看到Batches为64,每个Batches用到的内存数是1540kb,由此可以推出:希望通过一个批次执行完这个查询,需要的总的内存数的计算方法为:
需要的总内存数 = Batches*Memory Usage
对于100W条查询的这个例子就是:需要的总内存数=64*1540kb=98560kb,即96.25MB。那么我们测试将work_mem设置为100MB,是否能减少批次数。
SET work_mem=’100MB’;
11ee1e1c4f4e2d5a35331908e7462b7e

将work_mem的值设为100MB,执行的Batches确实降低到1,Memory Usage也使用到了96654kb,但是总的执行时间变化不大,性能方面并没有实质性的提升,甚至将work_mem设置到1GB,仍然看不到显著的性能提升,只能从其他方面着手,例如调整语句(以上用于测试的语句仅用于测试work_mem,等效写法:a.random_text , a.id ,a.created_date , b.id, b.created_date FROM tbl_test a,tbl_test b WHERE a.id < 10 AND a.id = b.id;)。 3143b3ad475d44164f23c87015bba66a

总结

综上,work_mem对复杂的查询的性能影响并不大,在实际生产中,work_mem的值也只需要设置到一个比较合理的值即可,遇到比较大结果集的简单查询时,可以在SESSION中进行临时设置,临时申请较大的work_mem来提升性能。

原文地址

UNDERSTANDING POSTGRESQL.CONF : WORK_MEM
http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/

参考

Estimating Needed Memory for a Sort
https://rjuju.github.io/postgresql/2015/08/18/estimating-needed-memory-for-a-sort.html

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 68 queries in 0.404 seconds