溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“分析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情況”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
