PostgreSQL一条SQL引发系统out of memory

2025-04-13 技术教程

错误描述

(1) Postgres执行的原SQL：

selectCOALESCE(m1.place_id,m2.place_id,m3.place_id)asplace_id,concat_ws('``',m1.rich_attributes,m2.rich_attributes,m3.rich_attributes)asrich_attributesfrom(SELECTplace_external_points.place_id,string_agg(concat(place_external_points.metadata_dictionary_id,'@-@',place_external_points.value),'``'::text)ASrich_attributesFROMplace_external_pointsbWHEREmetadata_dictionary_id=ANY(ARRAY[61,62])groupbyplace_external_points.place_id)m1fulljoin(SELECTplace_geocoded.place_id,string_agg(concat(place_geocoded.metadata_dictionary_id,'@-@',place_geocoded.value),'``'::text)ASrich_attributesFROMplace_geocodedgWHEREmetadata_dictionary_id=ANY(ARRAY[70,71,72,73,74,75,76,77,78])groupbyplace_geocoded.place_id)m2onm1.place_id=m2.place_idfulljoin(SELECTplace_attributes.place_id,string_agg(concat(place_attributes.metadata_dictionary_id,'@-@',place_attributes.value),'``'::text)ASrich_attributesFROMplace_attributesaWHEREplace_attributes.metadata_dictionary_id=ANY(ARRAY[5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59])groupbya.place_id)m3onm2.place_id=m3.place_id;

(2) 系统日志message:

Dec 27 10:39:13 shb-postgresql-01 kernel: Out of memory: Kill process 9116 (postgres) score 823 or sacrifice child

Dec 27 10:39:13 shb-postgresql-01 kernel: Killed process 9116, UID 501, (postgres) total-vm:40440476kB, anon-rss:28320224kB, file-rss:2154596kB

Dec 27 10:39:13 shb-postgresql-01 kernel: postgres: page allocation failure. order:0, mode:0x280da

Dec 27 10:39:13 shb-postgresql-01 kernel: Pid: 9116, comm: postgres Not tainted 2.6.32-431.el6.x86_64 #1

(3) 数据库日志：

less/usr/local/pgsql/data/pg_log/postgresql-2017-12-29_000000.logWARNING:terminatingconnectionbecauseofcrashofanotherserverprocessDETAIL:Thepostmasterhascommandedthisserverprocesstorollbackthecurrenttransactionandexit,becauseanotherserverprocessexitedabnormallyandpossiblycorruptedsharedmemory.HINT:Inamomentyoushouldbeabletoreconnecttothedatabaseandrepeatyourcommand.FATAL:thedatabasesystemisinrecoverymodeLOG:allserverprocessesterminated;reinitializingLOG:databasesystemwasinterrupted;lastknownupat2017-12-2909:20:36CSTFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeFATAL:thedatabasesystemisinrecoverymodeLOG:databasesystemwasnotproperlyshutdown;automaticrecoveryinprogressLOG:invalidrecordlengthat94A/478309C0LOG:redoisnotrequiredLOG:MultiXactmemberwraparoundprotectionsarenowenabledLOG:databasesystemisreadytoacceptconnectionsLOG:autovacuumlauncherstarted

(4) 脚本运行的时候内存变化：

# free -g

totalusedfreesharedbufferscached

Mem:31310000

-/+ buffers/cache:310

Swap:734

原因：通过以上信息可以发现是postgres的查询SQL把内存消耗殆尽，并且开始使用swap分区，导致系统发生oom-kill,数据库进程被干掉，然后重启恢复。

分析过程

(1) 表place_external_points和place_geocoded经查看都为空，表place_attributes有1亿多条数据,所以问题发生在最后一条select xxx from place_attributes语句上，这个表的列metadata_dictionary_id有对应的索引

(2) 通过执行计划explain发现使用了hash聚合：hashaggregate，SQL本身是group by ,没有使用group聚合，却使用了hash聚合，可初步判断数据库执行计划不是最优的：

#explainSELECTplace_id,string_agg(concat(metadata_dictionary_id,'@-@',value),'``'::text)ASrich_attributesFROMplace_attributesWHEREmetadata_dictionary_id=ANY(ARRAY[5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59])groupbyplace_attributes.place_id;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------HashAggregate(cost=7622781.80..7985216.03rows=323575width=62)GroupKey:place_id->Sort(cost=7622781.80..7712379.18rows=35838955width=62)SortKey:place_id->BitmapHeapScanonplace_attributes(cost=450351.86..2452151.90rows=35838955width=62)RecheckCond:(metadata_dictionary_id=ANY('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))->BitmapIndexScanonplace_attributes_metadata_dictionary_id_idx(cost=0.00..441392.12rows=35838955width=0)IndexCond:(metadata_dictionary_id=ANY('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))

解决方法

更新统计分析然后选择最优的执行计划

(Updates statistics used by the planner to determine the most efficient way to execute a query.)

postgres=# vacuum analyze place_attributes;

或者临时关闭enable_hashagg:

(Enables or disables the query planner's use of hashed aggregation plan types. The default is on)

# set enable_hashagg =0;

再次查看执行计划，发现执行计划不再走hashaggregate

yangzi=#explainSELECTplace_id,string_agg(concat(metadata_dictionary_id,'@-@',value),'``'::text)ASrich_attributesFROMplace_attributesWHEREmetadata_dictionary_id=ANY(ARRAY[5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59])groupbyplace_attributes.place_id;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------GroupAggregate(cost=7622781.80..7985216.03rows=323575width=62)GroupKey:place_id->Sort(cost=7622781.80..7712379.18rows=35838955width=62)SortKey:place_id->BitmapHeapScanonplace_attributes(cost=450351.86..2452151.90rows=35838955width=62)RecheckCond:(metadata_dictionary_id=ANY('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))->BitmapIndexScanonplace_attributes_metadata_dictionary_id_idx(cost=0.00..441392.12rows=35838955width=0)IndexCond:(metadata_dictionary_id=ANY('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))

最后再去执行原SQL,没有报错，并且很快完成。