189 8069 5689
本篇内容介绍了“分析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情况”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！