本篇内容介绍了“分析PostgreSQL DBA的pgAdmin情况”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

work_mem的内存从哪分配
work_mem是session(进程)的私有内存,与Oracle的PGA类似,由进程自行申请和管理.如果超出work_mem的限制,PG会把数据写入到临时文件中,如果OS的内存足够,写临时文件时会缓存到os的page cache中,相当于数据仍然在内存中.

work_mem对排序性能的影响
下面来看看work_mem大小对排序性能的影响.
测试表:

CREATETABLEtest(idserialPRIMARYKEY,random_texttext);\!perl-e'@c=("a".."z","A".."Z",0..9);printjoin("",map{$c[rand@c]}10..20+rand(40))."\n"for1..1000000'>/tmp/random_stringscopytest(random_text)FROM'/tmp/random_strings';analyzetest;[local:/data/run/pg12]:5120pg12@testdb=#\dtestTable"public.test"Column|Type|Collation|Nullable|Default-------------+---------+-----------+----------+----------------------------------id|integer||notnull|nextval('test_id_seq'::regclass)random_text|text|||Indexes:"test_pkey"PRIMARYKEY,btree(id)[local:/data/run/pg12]:5120pg12@testdb=#selectcount(*)fromtest;count---------1000000(1row)[local:/data/run/pg12]:5120pg12@testdb=#select*fromtestlimit5;id|random_text----+----------------------------------------------1|82nXOCCqPYxsOCGf3sXHTi51hG7202|wsYU8uZhanrFoPwJneIvqJYcYDAnKrKVo3|mTD4bJr83asYTRCtgdn4|xqrw1QoGouIOa0vlxW9t5|VbWuf4p3jhrsAOoMKQrwrBBPZib7ZMAUA387EhSO1qsU(5rows)[local:/data/run/pg12]:5120pg12@testdb=#

test表有2个列,其中id为主键,random_text是随机字符串,100w行数据.
work_mem设置为1MB

[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='1MB';SET[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=100ORDERBYrandom_textASC;QUERYPLAN----------------------------------------------------------------------------------------Sort(cost=12.86..13.09rows=89width=35)(actualtime=0.990..1.056rows=100loops=1)SortKey:random_textSortMethod:quicksortMemory:34kB->IndexScanusingtest_pkeyontest(cost=0.42..9.98rows=89width=35)(actualtime=0.051..0.165rows=100loops=1)IndexCond:(id<=100)PlanningTime:1.028msExecutionTime:1.201ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=1000ORDERBYrandom_textASC;QUERYPLAN-----------------------------------------------------------------------------------------Sort(cost=82.38..84.60rows=887width=35)(actualtime=10.224..10.560rows=1000loops=1)SortKey:random_textSortMethod:quicksortMemory:122kB->IndexScanusingtest_pkeyontest(cost=0.42..38.95rows=887width=35)(actualtime=0.097..2.090rows=1000loops=1)IndexCond:(id<=1000)PlanningTime:0.924msExecutionTime:11.027ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=10000ORDERBYrandom_textASC;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=914.20..936.37rows=8869width=35)(actualtime=40.895..44.648rows=10000loops=1)SortKey:random_textSortMethod:externalmergeDisk:448kB->IndexScanusingtest_pkeyontest(cost=0.42..332.63rows=8869width=35)(actualtime=0.054..7.950rows=10000loops=1)IndexCond:(id<=10000)PlanningTime:0.501msExecutionTime:45.357ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=100000ORDERBYrandom_textASC;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=17731.80..17985.59rows=101517width=35)(actualtime=274.599..344.113rows=100000loops=1)SortKey:random_textSortMethod:externalmergeDisk:4472kB->IndexScanusingtest_pkeyontest(cost=0.42..3731.97rows=101517width=35)(actualtime=0.072..29.042rows=100000loops=1)IndexCond:(id<=100000)PlanningTime:0.192msExecutionTime:348.499ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=1000000ORDERBYrandom_textASC;QUERYPLAN-------------------------------------------------------------------------------------------GatherMerge(cost=76126.17..173355.26rows=833334width=35)(actualtime=1299.103..2370.246rows=1000000loops=1)WorkersPlanned:2WorkersLaunched:2->Sort(cost=75126.15..76167.81rows=416667width=35)(actualtime=1291.503..1559.785rows=333333loops=3)SortKey:random_textSortMethod:externalmergeDisk:14960kBWorker0:SortMethod:externalmergeDisk:14976kBWorker1:SortMethod:externalmergeDisk:14648kB->ParallelSeqScanontest(cost=0.00..13441.33rows=416667width=35)(actualtime=0.013..78.030rows=333333loops=3)Filter:(id<=1000000)PlanningTime:0.205msExecutionTime:2418.291ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#

work_mem设置为100MB

[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='100MB';SET[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=100ORDERBYrandom_textASC;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=12.86..13.09rows=89width=35)(actualtime=0.623..0.652rows=100loops=1)SortKey:random_textSortMethod:quicksortMemory:34kB->IndexScanusingtest_pkeyontest(cost=0.42..9.98rows=89width=35)(actualtime=0.050..0.163rows=100loops=1)IndexCond:(id<=100)PlanningTime:1.029msExecutionTime:0.768ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=1000ORDERBYrandom_textASC;QUERYPLAN-----------------------------------------------------------------------------------------Sort(cost=82.38..84.60rows=887width=35)(actualtime=8.226..8.516rows=1000loops=1)SortKey:random_textSortMethod:quicksortMemory:122kB->IndexScanusingtest_pkeyontest(cost=0.42..38.95rows=887width=35)(actualtime=0.097..1.322rows=1000loops=1)IndexCond:(id<=1000)PlanningTime:0.997msExecutionTime:8.885ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=10000ORDERBYrandom_textASC;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=914.20..936.37rows=8869width=35)(actualtime=52.552..53.942rows=10000loops=1)SortKey:random_textSortMethod:quicksortMemory:1343kB->IndexScanusingtest_pkeyontest(cost=0.42..332.63rows=8869width=35)(actualtime=0.054..8.050rows=10000loops=1)IndexCond:(id<=10000)PlanningTime:0.444msExecutionTime:55.059ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=100000ORDERBYrandom_textASC;QUERYPLAN-----------------------------------------------------------------------------------------------Sort(cost=12173.80..12427.59rows=101517width=35)(actualtime=307.212..318.567rows=100000loops=1)SortKey:random_textSortMethod:quicksortMemory:12680kB->IndexScanusingtest_pkeyontest(cost=0.42..3731.97rows=101517width=35)(actualtime=0.040..28.441rows=100000loops=1)IndexCond:(id<=100000)PlanningTime:0.184msExecutionTime:326.030ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestWHEREid<=1000000ORDERBYrandom_textASC;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=120390.84..122890.84rows=1000000width=35)(actualtime=4333.238..4862.205rows=1000000loops=1)SortKey:random_textSortMethod:externalmergeDisk:44536kB->SeqScanontest(cost=0.00..20733.00rows=1000000width=35)(actualtime=0.014..191.083rows=1000000loops=1)Filter:(id<=1000000)PlanningTime:0.215msExecutionTime:4909.541ms(7rows)[local:/data/run/pg12]:5120pg12@testdb=#

可以看到,在work_mem设置为100MB时,PG会尽可能的在内存中执行排序(排序算法从字面上来看是快速排序算法),但性能比起1MB时并没有非常明显的改进,而且得益于并行算法,在最后一个场景中1MB的性能比起100MB的性能还要好.

work_mem对HashJoin性能的影响
下面来看看work_mem大小对HashJoin性能的影响.
测试表同上,测试脚本:

setwork_mem='1MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000;setwork_mem='100MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000;setwork_mem='1MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<10000;setwork_mem='100MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<10000;setwork_mem='1MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;setwork_mem='100MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;setwork_mem='1MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;setwork_mem='100MB';EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;

1MB vs 100MB

[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='1MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------Gather(cost=1050.01..15104.46rows=886width=39)(actualtime=5.191..172.614rows=999loops=1)WorkersPlanned:2WorkersLaunched:2->HashJoin(cost=50.00..14015.86rows=369width=39)(actualtime=105.367..160.113rows=333loops=3)HashCond:(b.random_text=a.random_text)->ParallelSeqScanontestb(cost=0.00..12399.67rows=416667width=35)(actualtime=0.046..60.472rows=333333loops=3)->Hash(cost=38.93..38.93rows=886width=35)(actualtime=4.903..4.903rows=999loops=3)Buckets:1024Batches:1MemoryUsage:76kB->IndexScanusingtest_pkeyontesta(cost=0.42..38.93rows=886width=35)(actualtime=0.315..2.816rows=999loops=3)IndexCond:(id<1000)PlanningTime:1.737msExecutionTime:173.096ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='100MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------Gather(cost=1050.01..15104.46rows=886width=39)(actualtime=1.133..139.035rows=999loops=1)WorkersPlanned:2WorkersLaunched:2->HashJoin(cost=50.00..14015.86rows=369width=39)(actualtime=89.747..135.071rows=333loops=3)HashCond:(b.random_text=a.random_text)->ParallelSeqScanontestb(cost=0.00..12399.67rows=416667width=35)(actualtime=0.010..52.889rows=333333loops=3)->Hash(cost=38.93..38.93rows=886width=35)(actualtime=0.806..0.806rows=999loops=3)Buckets:1024Batches:1MemoryUsage:76kB->IndexScanusingtest_pkeyontesta(cost=0.42..38.93rows=886width=35)(actualtime=0.032..0.452rows=999loops=3)IndexCond:(id<1000)PlanningTime:0.368msExecutionTime:139.139ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='1MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<10000;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------Gather(cost=1443.47..16329.38rows=8868width=39)(actualtime=18.109..188.837rows=9999loops=1)WorkersPlanned:2WorkersLaunched:2->HashJoin(cost=443.47..14442.58rows=3695width=39)(actualtime=22.259..182.177rows=3333loops=3)HashCond:(b.random_text=a.random_text)->ParallelSeqScanontestb(cost=0.00..12399.67rows=416667width=35)(actualtime=0.021..61.790rows=333333loops=3)->Hash(cost=332.62..332.62rows=8868width=35)(actualtime=21.900..21.900rows=9999loops=3)Buckets:16384Batches:1MemoryUsage:801kB->IndexScanusingtest_pkeyontesta(cost=0.42..332.62rows=8868width=35)(actualtime=0.069..12.185rows=9999loops=3)IndexCond:(id<10000)PlanningTime:0.786msExecutionTime:189.854ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='100MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<10000;QUERYPLAN---------------------------------------------------------------------------------------------------------------------------------------------Gather(cost=1443.47..16329.38rows=8868width=39)(actualtime=7.854..157.510rows=9999loops=1)WorkersPlanned:2WorkersLaunched:2->HashJoin(cost=443.47..14442.58rows=3695width=39)(actualtime=8.019..152.570rows=3333loops=3)HashCond:(b.random_text=a.random_text)->ParallelSeqScanontestb(cost=0.00..12399.67rows=416667width=35)(actualtime=0.010..55.844rows=333333loops=3)->Hash(cost=332.62..332.62rows=8868width=35)(actualtime=7.869..7.869rows=9999loops=3)Buckets:16384Batches:1MemoryUsage:801kB->IndexScanusingtest_pkeyontesta(cost=0.42..332.62rows=8868width=35)(actualtime=0.031..4.434rows=9999loops=3)IndexCond:(id<10000)PlanningTime:0.410msExecutionTime:158.160ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='1MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------------------------Gather(cost=4999.50..36132.51rows=101516width=39)(actualtime=235.147..405.768rows=99999loops=1)WorkersPlanned:2WorkersLaunched:2->ParallelHashJoin(cost=3999.50..24980.91rows=42298width=39)(actualtime=222.076..293.543rows=33333loops=3)HashCond:(b.random_text=a.random_text)->ParallelSeqScanontestb(cost=0.00..12399.67rows=416667width=35)(actualtime=0.013..52.181rows=333333loops=3)->ParallelHash(cost=3139.78..3139.78rows=42298width=35)(actualtime=57.009..57.010rows=33333loops=3)Buckets:16384Batches:16MemoryUsage:608kB->ParallelIndexScanusingtest_pkeyontesta(cost=0.42..3139.78rows=42298width=35)(actualtime=0.139..29.482rows=33333loops=3)IndexCond:(id<100000)PlanningTime:1.389msExecutionTime:410.420ms(12rows)[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='100MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<100000;QUERYPLAN--------------------------------------------------------------------------------------------------------------------------------------------HashJoin(cost=5000.90..27999.06rows=101516width=39)(actualtime=77.269..509.484rows=99999loops=1)HashCond:(b.random_text=a.random_text)->SeqScanontestb(cost=0.00..18233.00rows=1000000width=35)(actualtime=0.014..129.504rows=1000000loops=1)->Hash(cost=3731.95..3731.95rows=101516width=35)(actualtime=77.152..77.152rows=99999loops=1)Buckets:131072Batches:1MemoryUsage:7760kB->IndexScanusingtest_pkeyontesta(cost=0.42..3731.95rows=101516width=35)(actualtime=0.031..41.401rows=99999loops=1)IndexCond:(id<100000)PlanningTime:0.311msExecutionTime:513.957ms(9rows)[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='1MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000000;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------HashJoin(cost=38546.00..96467.99rows=999999width=39)(actualtime=483.527..1982.466rows=999999loops=1)HashCond:(a.random_text=b.random_text)->SeqScanontesta(cost=0.00..20733.00rows=999999width=35)(actualtime=0.051..286.223rows=999999loops=1)Filter:(id<1000000)RowsRemovedbyFilter:1->Hash(cost=18233.00..18233.00rows=1000000width=35)(actualtime=482.952..482.952rows=1000000loops=1)Buckets:16384Batches:128MemoryUsage:644kB->SeqScanontestb(cost=0.00..18233.00rows=1000000width=35)(actualtime=0.042..136.794rows=1000000loops=1)PlanningTime:1.413msExecutionTime:2023.608ms(10rows)[local:/data/run/pg12]:5120pg12@testdb=#setwork_mem='100MB';SET[local:/data/run/pg12]:5120pg12@testdb=#EXPLAINanalyzeSELECT*FROMtestaJOINtestbUSING(random_text)WHEREa.id<1000000;QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------HashJoin(cost=30733.00..65215.99rows=999999width=39)(actualtime=495.932..1368.250rows=999999loops=1)HashCond:(a.random_text=b.random_text)->SeqScanontesta(cost=0.00..20733.00rows=999999width=35)(actualtime=0.023..204.935rows=999999loops=1)Filter:(id<1000000)RowsRemovedbyFilter:1->Hash(cost=18233.00..18233.00rows=1000000width=35)(actualtime=495.148..495.149rows=1000000loops=1)Buckets:1048576Batches:1MemoryUsage:74114kB->SeqScanontestb(cost=0.00..18233.00rows=1000000width=35)(actualtime=0.011..130.569rows=1000000loops=1)PlanningTime:0.295msExecutionTime:1417.372ms(10rows)[local:/data/run/pg12]:5120pg12@testdb=#

从日志输出来看,在100MB时Batches数明显比1MB时少很多,表示所有数据都可以放在内存中处理(1个批次即可),执行时间也相对于少20%-30%左右.

增加work_mem会存在边际递减效应,除非内存足够,否则建议逐个测试找到最佳值,同时需考虑连接数对内存的影响.

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