本篇内容介绍了“分析PostgreSQL DBA的pgAdmin情况”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
work_mem的内存从哪分配
work_mem是session(进程)的私有内存,与Oracle的PGA类似,由进程自行申请和管理.如果超出work_mem的限制,PG会把数据写入到临时文件中,如果OS的内存足够,写临时文件时会缓存到os的page cache中,相当于数据仍然在内存中.
work_mem对排序性能的影响
下面来看看work_mem大小对排序性能的影响.
测试表:
CREATE TABLE test (id serial PRIMARY KEY, random_text text );
\! perl -e '@c=("a".."z","A".."Z",0..9); print join("",map{$c[rand@c]}10..20+rand(40))."\n" for 1..1000000' > /tmp/random_strings
copy test (random_text) FROM '/tmp/random_strings';
analyze test;
[local:/data/run/pg12]:5120 pg12@testdb=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+----------------------------------
id | integer | | not null | nextval('test_id_seq'::regclass)
random_text | text | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
[local:/data/run/pg12]:5120 pg12@testdb=# select count(*) from test;
count
---------
1000000
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=# select * from test limit 5;
id | random_text
----+----------------------------------------------
1 | 82nXOCCqPYxsOCGf3sXHTi51hG720
2 | wsYU8uZhanrFoPwJneIvqJYcYDAnKrKVo
3 | mTD4bJr83asYTRCtgdn
4 | xqrw1QoGouIOa0vlxW9t
5 | VbWuf4p3jhrsAOoMKQrwrBBPZib7ZMAUA387EhSO1qsU
(5 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#test表有2个列,其中id为主键,random_text是随机字符串,100w行数据.
work_mem设置为1MB
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='1MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=#
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100 ORDER BY random_text ASC;
QUERY PLAN
----------------------------------------------------------------------------------------
Sort (cost=12.86..13.09 rows=89 width=35) (actual time=0.990..1.056 rows=100 loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 34kB
-> Index Scan using test_pkey on test (cost=0.42..9.98 rows=89 width=35) (actual time=0.051
..0.165 rows=100 loops=1)
Index Cond: (id <= 100)
Planning Time: 1.028 ms
Execution Time: 1.201 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000 ORDER BY random_text ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------
Sort (cost=82.38..84.60 rows=887 width=35) (actual time=10.224..10.560 rows=1000 loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 122kB
-> Index Scan using test_pkey on test (cost=0.42..38.95 rows=887 width=35) (actual time=0.0
97..2.090 rows=1000 loops=1)
Index Cond: (id <= 1000)
Planning Time: 0.924 ms
Execution Time: 11.027 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 10000 ORDER BY random_text ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=914.20..936.37 rows=8869 width=35) (actual time=40.895..44.648 rows=10000 loops=1)
Sort Key: random_text
Sort Method: external merge Disk: 448kB
-> Index Scan using test_pkey on test (cost=0.42..332.63 rows=8869 width=35) (actual time=0
.054..7.950 rows=10000 loops=1)
Index Cond: (id <= 10000)
Planning Time: 0.501 ms
Execution Time: 45.357 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100000 ORDER BY random_text ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=17731.80..17985.59 rows=101517 width=35) (actual time=274.599..344.113 rows=100000
loops=1)
Sort Key: random_text
Sort Method: external merge Disk: 4472kB
-> Index Scan using test_pkey on test (cost=0.42..3731.97 rows=101517 width=35) (actual tim
e=0.072..29.042 rows=100000 loops=1)
Index Cond: (id <= 100000)
Planning Time: 0.192 ms
Execution Time: 348.499 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000000 ORDER BY random_text ASC;
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Merge (cost=76126.17..173355.26 rows=833334 width=35) (actual time=1299.103..2370.246 r
ows=1000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=75126.15..76167.81 rows=416667 width=35) (actual time=1291.503..1559.785 rows
=333333 loops=3)
Sort Key: random_text
Sort Method: external merge Disk: 14960kB
Worker 0: Sort Method: external merge Disk: 14976kB
Worker 1: Sort Method: external merge Disk: 14648kB
-> Parallel Seq Scan on test (cost=0.00..13441.33 rows=416667 width=35) (actual time=
0.013..78.030 rows=333333 loops=3)
Filter: (id <= 1000000)
Planning Time: 0.205 ms
Execution Time: 2418.291 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#work_mem设置为100MB
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='100MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=#
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100 ORDER BY random_text ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=12.86..13.09 rows=89 width=35) (actual time=0.623..0.652 rows=100 loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 34kB
-> Index Scan using test_pkey on test (cost=0.42..9.98 rows=89 width=35) (actual time=0.050
..0.163 rows=100 loops=1)
Index Cond: (id <= 100)
Planning Time: 1.029 ms
Execution Time: 0.768 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000 ORDER BY random_text ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------
Sort (cost=82.38..84.60 rows=887 width=35) (actual time=8.226..8.516 rows=1000 loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 122kB
-> Index Scan using test_pkey on test (cost=0.42..38.95 rows=887 width=35) (actual time=0.0
97..1.322 rows=1000 loops=1)
Index Cond: (id <= 1000)
Planning Time: 0.997 ms
Execution Time: 8.885 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 10000 ORDER BY random_text ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=914.20..936.37 rows=8869 width=35) (actual time=52.552..53.942 rows=10000 loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 1343kB
-> Index Scan using test_pkey on test (cost=0.42..332.63 rows=8869 width=35) (actual time=0
.054..8.050 rows=10000 loops=1)
Index Cond: (id <= 10000)
Planning Time: 0.444 ms
Execution Time: 55.059 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100000 ORDER BY random_text ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Sort (cost=12173.80..12427.59 rows=101517 width=35) (actual time=307.212..318.567 rows=100000
loops=1)
Sort Key: random_text
Sort Method: quicksort Memory: 12680kB
-> Index Scan using test_pkey on test (cost=0.42..3731.97 rows=101517 width=35) (actual tim
e=0.040..28.441 rows=100000 loops=1)
Index Cond: (id <= 100000)
Planning Time: 0.184 ms
Execution Time: 326.030 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000000 ORDER BY random_text ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=120390.84..122890.84 rows=1000000 width=35) (actual time=4333.238..4862.205 rows=10
00000 loops=1)
Sort Key: random_text
Sort Method: external merge Disk: 44536kB
-> Seq Scan on test (cost=0.00..20733.00 rows=1000000 width=35) (actual time=0.014..191.083
rows=1000000 loops=1)
Filter: (id <= 1000000)
Planning Time: 0.215 ms
Execution Time: 4909.541 ms
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#可以看到,在work_mem设置为100MB时,PG会尽可能的在内存中执行排序(排序算法从字面上来看是快速排序算法),但性能比起1MB时并没有非常明显的改进,而且得益于并行算法,在最后一个场景中1MB的性能比起100MB的性能还要好.
work_mem对HashJoin性能的影响
下面来看看work_mem大小对HashJoin性能的影响.
测试表同上,测试脚本:
set work_mem='1MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000;
set work_mem='100MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000;
set work_mem='1MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 10000;
set work_mem='100MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 10000;
set work_mem='1MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;
set work_mem='100MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;
set work_mem='1MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;
set work_mem='100MB';
EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;1MB vs 100MB
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='1MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1050.01..15104.46 rows=886 width=39) (actual time=5.191..172.614 rows=999 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=50.00..14015.86 rows=369 width=39) (actual time=105.367..160.113 rows=333 loops=3)
Hash Cond: (b.random_text = a.random_text)
-> Parallel Seq Scan on test b (cost=0.00..12399.67 rows=416667 width=35) (actual time=0.046..60.472 rows=333333 loops=3)
-> Hash (cost=38.93..38.93 rows=886 width=35) (actual time=4.903..4.903 rows=999 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 76kB
-> Index Scan using test_pkey on test a (cost=0.42..38.93 rows=886 width=35) (actual time=0.315..2.816 rows=999 loops=3)
Index Cond: (id < 1000)
Planning Time: 1.737 ms
Execution Time: 173.096 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='100MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1050.01..15104.46 rows=886 width=39) (actual time=1.133..139.035 rows=999 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=50.00..14015.86 rows=369 width=39) (actual time=89.747..135.071 rows=333 loops=3)
Hash Cond: (b.random_text = a.random_text)
-> Parallel Seq Scan on test b (cost=0.00..12399.67 rows=416667 width=35) (actual time=0.010..52.889 rows=333333 loops=3)
-> Hash (cost=38.93..38.93 rows=886 width=35) (actual time=0.806..0.806 rows=999 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 76kB
-> Index Scan using test_pkey on test a (cost=0.42..38.93 rows=886 width=35) (actual time=0.032..0.452 rows=999 loops=3)
Index Cond: (id < 1000)
Planning Time: 0.368 ms
Execution Time: 139.139 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='1MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 10000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1443.47..16329.38 rows=8868 width=39) (actual time=18.109..188.837 rows=9999 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=443.47..14442.58 rows=3695 width=39) (actual time=22.259..182.177 rows=3333 loops=3)
Hash Cond: (b.random_text = a.random_text)
-> Parallel Seq Scan on test b (cost=0.00..12399.67 rows=416667 width=35) (actual time=0.021..61.790 rows=333333 loops=3)
-> Hash (cost=332.62..332.62 rows=8868 width=35) (actual time=21.900..21.900 rows=9999 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 801kB
-> Index Scan using test_pkey on test a (cost=0.42..332.62 rows=8868 width=35) (actual time=0.069..12.185 rows=9999 loops=3)
Index Cond: (id < 10000)
Planning Time: 0.786 ms
Execution Time: 189.854 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='100MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 10000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1443.47..16329.38 rows=8868 width=39) (actual time=7.854..157.510 rows=9999 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=443.47..14442.58 rows=3695 width=39) (actual time=8.019..152.570 rows=3333 loops=3)
Hash Cond: (b.random_text = a.random_text)
-> Parallel Seq Scan on test b (cost=0.00..12399.67 rows=416667 width=35) (actual time=0.010..55.844 rows=333333 loops=3)
-> Hash (cost=332.62..332.62 rows=8868 width=35) (actual time=7.869..7.869 rows=9999 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 801kB
-> Index Scan using test_pkey on test a (cost=0.42..332.62 rows=8868 width=35) (actual time=0.031..4.434 rows=9999 loops=3)
Index Cond: (id < 10000)
Planning Time: 0.410 ms
Execution Time: 158.160 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='1MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=4999.50..36132.51 rows=101516 width=39) (actual time=235.147..405.768 rows=99999 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Hash Join (cost=3999.50..24980.91 rows=42298 width=39) (actual time=222.076..293.543 rows=33333 loops=3)
Hash Cond: (b.random_text = a.random_text)
-> Parallel Seq Scan on test b (cost=0.00..12399.67 rows=416667 width=35) (actual time=0.013..52.181 rows=333333 loops=3)
-> Parallel Hash (cost=3139.78..3139.78 rows=42298 width=35) (actual time=57.009..57.010 rows=33333 loops=3)
Buckets: 16384 Batches: 16 Memory Usage: 608kB
-> Parallel Index Scan using test_pkey on test a (cost=0.42..3139.78 rows=42298 width=35) (actual time=0.139..29.482 rows=33333 loops=3)
Index Cond: (id < 100000)
Planning Time: 1.389 ms
Execution Time: 410.420 ms
(12 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='100MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 100000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=5000.90..27999.06 rows=101516 width=39) (actual time=77.269..509.484 rows=99999 loops=1)
Hash Cond: (b.random_text = a.random_text)
-> Seq Scan on test b (cost=0.00..18233.00 rows=1000000 width=35) (actual time=0.014..129.504 rows=1000000 loops=1)
-> Hash (cost=3731.95..3731.95 rows=101516 width=35) (actual time=77.152..77.152 rows=99999 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 7760kB
-> Index Scan using test_pkey on test a (cost=0.42..3731.95 rows=101516 width=35) (actual time=0.031..41.401 rows=99999 loops=1)
Index Cond: (id < 100000)
Planning Time: 0.311 ms
Execution Time: 513.957 ms
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='1MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=38546.00..96467.99 rows=999999 width=39) (actual time=483.527..1982.466 rows=999999 loops=1)
Hash Cond: (a.random_text = b.random_text)
-> Seq Scan on test a (cost=0.00..20733.00 rows=999999 width=35) (actual time=0.051..286.223 rows=999999 loops=1)
Filter: (id < 1000000)
Rows Removed by Filter: 1
-> Hash (cost=18233.00..18233.00 rows=1000000 width=35) (actual time=482.952..482.952 rows=1000000 loops=1)
Buckets: 16384 Batches: 128 Memory Usage: 644kB
-> Seq Scan on test b (cost=0.00..18233.00 rows=1000000 width=35) (actual time=0.042..136.794 rows=1000000 loops=1)
Planning Time: 1.413 ms
Execution Time: 2023.608 ms
(10 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# set work_mem='100MB';
SET
[local:/data/run/pg12]:5120 pg12@testdb=# EXPLAIN analyze SELECT * FROM test a JOIN test b USING (random_text) WHERE a.id < 1000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=30733.00..65215.99 rows=999999 width=39) (actual time=495.932..1368.250 rows=999999 loops=1)
Hash Cond: (a.random_text = b.random_text)
-> Seq Scan on test a (cost=0.00..20733.00 rows=999999 width=35) (actual time=0.023..204.935 rows=999999 loops=1)
Filter: (id < 1000000)
Rows Removed by Filter: 1
-> Hash (cost=18233.00..18233.00 rows=1000000 width=35) (actual time=495.148..495.149 rows=1000000 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 74114kB
-> Seq Scan on test b (cost=0.00..18233.00 rows=1000000 width=35) (actual time=0.011..130.569 rows=1000000 loops=1)
Planning Time: 0.295 ms
Execution Time: 1417.372 ms
(10 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#从日志输出来看,在100MB时Batches数明显比1MB时少很多,表示所有数据都可以放在内存中处理(1个批次即可),执行时间也相对于少20%-30%左右.
增加work_mem会存在边际递减效应,除非内存足够,否则建议逐个测试找到最佳值,同时需考虑连接数对内存的影响.
“分析PostgreSQL DBA的pgAdmin情况”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
