PostgreSQL一條SQL引發(fā)系統(tǒng)outofmemory

錯誤描述

創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、?？诰W(wǎng)絡(luò)推廣、小程序定制開發(fā)、?？诰W(wǎng)絡(luò)營銷、?？谄髽I(yè)策劃、?？谄放乒P(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供?？诮ㄕ?/a>搭建服務(wù)，24小時服務(wù)熱線：18980820575，官方網(wǎng)址：muchs.cn

(1) Postgres執(zhí)行的原SQL：

select COALESCE(m1.place_id, m2.place_id, m3.place_id) as place_id, concat_ws('``', m1.rich_attributes, m2.rich_attributes, m3.rich_attributes) as rich_attributes from
(SELECT place_external_points.place_id, string_agg(concat(place_external_points.metadata_dictionary_id, '@-@', place_external_points.value), '``'::text) AS rich_attributes
FROM place_external_points b WHERE metadata_dictionary_id = ANY (ARRAY[61, 62]) group by place_external_points.place_id) m1
full join
(SELECT place_geocoded.place_id, string_agg(concat(place_geocoded.metadata_dictionary_id, '@-@', place_geocoded.value), '``'::text) AS rich_attributes FROM place_geocoded g
WHERE metadata_dictionary_id = ANY (ARRAY[70, 71, 72, 73, 74, 75, 76, 77, 78]) group by place_geocoded.place_id) m2 on m1.place_id = m2.place_id
full join
(SELECT place_attributes.place_id, string_agg(concat(place_attributes.metadata_dictionary_id, '@-@', place_attributes.value), '``'::text) AS rich_attributes FROM
place_attributes a WHERE place_attributes.metadata_dictionary_id = ANY (ARRAY[5, 7, 9, 10, 11, 12, 13, 33, 35, 46, 51, 52, 53, 54, 55, 57, 58, 59]) group by a.place_id) m3 on m2.place_id = m3.place_id;

(2) 系統(tǒng)日志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) 數(shù)據(jù)庫日志：

less /usr/local/pgsql/data/pg_log/postgresql-2017-12-29_000000.log
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
FATAL:  the database system is in recovery mode
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted; last known up at 2017-12-29 09:20:36 CST
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
FATAL:  the database system is in recovery mode
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  invalid record length at 94A/478309C0
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


(4) 腳本運(yùn)行的時候內(nèi)存變化：

# free -g

total       used       free     shared    buffers     cached

Mem:            31         31          0          0          0          0

-/+ buffers/cache:         31          0

Swap:            7          3          4

原因：通過以上信息可以發(fā)現(xiàn)是postgres的查詢SQL把內(nèi)存消耗殆盡，并且開始使用swap分區(qū)，導(dǎo)致系統(tǒng)發(fā)生oom-kill,數(shù)據(jù)庫進(jìn)程被干掉，然后重啟恢復(fù)。



分析過程


(1) 表place_external_points和place_geocoded經(jīng)查看都為空，表place_attributes有1億多條數(shù)據(jù),所以問題發(fā)生在最后一條select xxx from  place_attributes語句上，這個表的列metadata_dictionary_id有對應(yīng)的索引

# \d place_attributes;
       Table "denali_search_cn_17q3_20171226_epl.place_attributes"
         Column         |              Type              |   Modifiers   
------------------------+--------------------------------+---------------
place_id               | integer                        |
metadata_dictionary_id | integer                        |
value                  | text                           |
lang                   | character varying(50)          |
source                 | character varying(50)          |
create_time            | timestamp(6) without time zone | default now()
update_time            | timestamp(6) without time zone |
Indexes:
    "place_attributes_metadata_dictionary_id_idx" btree (metadata_dictionary_id)
    "place_attributes_place_id_metadata_dictionary_id_idx" btree (place_id, metadata_dictionary_id)
    "place_attributes_value_idx" btree (value)

    

(2) 通過執(zhí)行計(jì)劃explain發(fā)現(xiàn)使用了hash聚合：hashaggregate，SQL本身是group by ,沒有使用group聚合，卻使用了hash聚合，可初步判斷數(shù)據(jù)庫執(zhí)行計(jì)劃不是最優(yōu)的：

# explain SELECT place_id, string_agg(concat(metadata_dictionary_id, '@-@', value), '``'::text) AS rich_attributes FROM place_attributes WHERE metadata_dictionary_id = ANY (ARRAY[5, 7, 9, 10, 11, 12, 13, 33, 35, 46, 51, 52, 53, 54, 55, 57, 58, 59]) group by place_attributes.place_id;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=7622781.80..7985216.03 rows=323575 width=62)
   Group Key: place_id
   ->  Sort  (cost=7622781.80..7712379.18 rows=35838955 width=62)
         Sort Key: place_id
         ->  Bitmap Heap Scan on place_attributes  (cost=450351.86..2452151.90 rows=35838955 width=62)
               Recheck Cond: (metadata_dictionary_id = ANY ('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))
               ->  Bitmap Index Scan on place_attributes_metadata_dictionary_id_idx  (cost=0.00..441392.12 rows=35838955 width=0)
                     Index Cond: (metadata_dictionary_id = ANY ('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))

解決方法


更新統(tǒng)計(jì)分析然后選擇最優(yōu)的執(zhí)行計(jì)劃

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

postgres=# vacuum analyze place_attributes;

或者臨時關(guān)閉enable_hashagg:

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

# set  enable_hashagg =0;

再次查看執(zhí)行計(jì)劃，發(fā)現(xiàn)執(zhí)行計(jì)劃不再走h(yuǎn)ashaggregate

yangzi=# explain SELECT place_id, string_agg(concat(metadata_dictionary_id, '@-@', value), '``'::text) AS rich_attributes FROM place_attributes WHERE metadata_dictionary_id = ANY (ARRAY[5, 7, 9, 10, 11, 12, 13, 33, 35, 46, 51, 52, 53, 54, 55, 57, 58, 59]) group by place_attributes.place_id;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=7622781.80..7985216.03 rows=323575 width=62)
   Group Key: place_id
   ->  Sort  (cost=7622781.80..7712379.18 rows=35838955 width=62)
         Sort Key: place_id
         ->  Bitmap Heap Scan on place_attributes  (cost=450351.86..2452151.90 rows=35838955 width=62)
               Recheck Cond: (metadata_dictionary_id = ANY ('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))
               ->  Bitmap Index Scan on place_attributes_metadata_dictionary_id_idx  (cost=0.00..441392.12 rows=35838955 width=0)
                     Index Cond: (metadata_dictionary_id = ANY ('{5,7,9,10,11,12,13,33,35,46,51,52,53,54,55,57,58,59}'::integer[]))


最后再去執(zhí)行原SQL,沒有報(bào)錯，并且很快完成。

本文標(biāo)題：PostgreSQL一條SQL引發(fā)系統(tǒng)outofmemory
分享地址：http://muchs.cn/article6/joodog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、App設(shè)計(jì)、移動網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)公司、外貿(mào)建站、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

商城網(wǎng)站建設(shè)