分析PostgreSQL DBA的pgAdmin情况

发布时间：2021-11-05 11:29:36 作者：iii
来源：亿速云阅读：136

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

分析PostgreSQL DBA的pgAdmin情况

相关阅读