Out of Memory - Mailing list pgsql-general

From Enrico Bianchi
Subject Out of Memory
Date
Msg-id 54B84A83.9020003@ymail.com
Whole thread Raw
Responses Re: Out of Memory
Re: Out of Memory
List pgsql-general
I have this situation:

Machine:
VPS with CentOS 6.6 x86_64
64GB of RAM
2GB of swap (unused)

Ulimit settings:
postgres            soft    nproc   2047                                                               
postgres            hard    nproc   16384                                                              
postgres            soft    nofile  1024                                                               
postgres            hard    nofile  65536                                                              
postgres            hard    stack   10240

PostgreSQL 9.4.0 from official repositories. Postgresql.conf is:

listen_addresses = '*'                                                                                 
port = 5432
max_connections = 20                                                                                   
shared_buffers = 16GB                                                                                  
effective_cache_size = 48GB                                                                            
work_mem = 419430kB                                                                                    
maintenance_work_mem = 2GB                                                                             
checkpoint_segments = 128                                                                              
checkpoint_completion_target = 0.9                                                                     
wal_buffers = 16MB                                                                                     
default_statistics_target = 500

When I launch a query (the principal field is JSONb), the database return this:

ERROR:  out of memory
DETAIL:  Failed on request of size 110558.

This is the query:

SELECT CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN field_id ->'comment'->>'id'
          WHEN '[' THEN jsonb_array_elements(field_id ->'comment')->>'id'
          ELSE NULL
        END AS comment_id,
        CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN field_id ->'comment'->'from'->>'id'
          WHEN '[' THEN jsonb_array_elements(field_id ->'comment')->'from'->>'id'
          ELSE NULL
        END AS user_id,
        field_id ->> '_id' post_id,
        CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN (field_id ->'comment'->>'created_timestamp')::timestamp without time zone
          WHEN '[' THEN (jsonb_array_elements(field_id ->'comment')->>'created_time')::timestamp without time zone
          ELSE NULL
        END AS comment_create_date,
        CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN cast(to_char((field_id ->'comment'->>'created_time')::timestamp without time zone,'YYYYMMDD') as numeric)
          WHEN '[' THEN  cast(to_char((jsonb_array_elements(field_id ->'comment')->>'created_time')::timestamp without time zone,'YYYYMMDD') as numeric)
          ELSE NULL
        END AS comment_created_day,
        field_id ->> 'pageId' page_id,
        CASE substring(field_id->>'feedtype',1,1)
          WHEN 'f' THEN 2
          WHEN 'b' THEN 1
          ELSE 3
        END AS owner_type,
        'WALL' comment_type,
       CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN to_char((field_id ->'comment'->>'created_time')::timestamp without time zone,'HH24')::numeric
          WHEN '[' THEN  to_char((jsonb_array_elements(field_id ->'comment')->>'created_time')::timestamp without time zone,'HH24')::numeric
          ELSE NULL
        END AS comment_time_slot,
        CASE substring((field_id ->'comment')::text,1,1)
          WHEN '{' THEN (field_id ->'comment'->>'like_count')::numeric
          WHEN '[' THEN (jsonb_array_elements(field_id ->'comment')->>'like_count')::numeric
          ELSE NULL
        END AS like_count,
        1 as sn_id,
        17 AS group_id
 FROM stage.fbcomment
 WHERE field_id ->> 'pageId' in (SELECT stage.eng_page.identifier::text
                                    FROM  stage.eng_group_page,
                                         stage.eng_page
                                   where stage.eng_group_page.page_id=stage.eng_page._id
                                     AND stage.eng_group_page.group_id=17
                                 )
;

And this is the query plan:

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=49.52..57597.31 rows=6729600 width=989)
   ->  HashAggregate  (cost=41.38..42.02 rows=64 width=12)
         Group Key: (eng_page.identifier)::text
         ->  Hash Join  (cost=32.54..41.22 rows=64 width=12)
               Hash Cond: (eng_group_page.page_id = eng_page._id)
               ->  Bitmap Heap Scan on eng_group_page (cost=4.77..12.57 rows=64 width=5)
                     Recheck Cond: (group_id = 17::numeric)
                     ->  Bitmap Index Scan on pk_eng_group_page (cost=0.00..4.76 rows=64 width=0)
                           Index Cond: (group_id = 17::numeric)
               ->  Hash  (cost=17.34..17.34 rows=834 width=17)
                     ->  Seq Scan on eng_page  (cost=0.00..17.34 rows=834 width=17)
   ->  Bitmap Heap Scan on fbcomment  (cost=8.14..103.95 rows=673 width=989)
         Recheck Cond: ((field_id ->> 'pageId'::text) = (eng_page.identifier)::text)
         ->  Bitmap Index Scan on fbcomment_idx  (cost=0.00..7.97 rows=673 width=0)
               Index Cond: ((field_id ->> 'pageId'::text) = (eng_page.identifier)::text)
(15 rows)

The query goes wrong also a subset of data extracted from the subquery. With this subset (5 values with generate 336500 records), vmstat is this:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 64467148  16888 996464    0    0     5     1   75   40 5  1 94  0  0
 2  0      0 64365252  16888 996464    0    0     0     0  734  167 13  2 82  0  2
 1  0      0 63471488  16888 996464    0    0     0     1 1102  176 21  4 75  0  0
 1  0      0 62257732  16888 996464    0    0     0    12 1070  160 21  4 74  0  2
 1  0      0 60939172  16888 996464    0    0     0     0 1072  158 21  4 75  0  0
 1  0      0 59627188  16888 996464    0    0     0     6 1071  161 21  4 75  0  0
 1  0      0 58324692  16888 996464    0    0     0     0 1069  152 21  4 75  0  0
 1  0      0 57002732  16888 996464    0    0     0     0 1049  133 21  4 75  0  0
 1  0      0 55671200  16888 996464    0    0     0     1 1076  152 21  4 75  0  0
 1  0      0 54316064  16896 996460    0    0     0     4 1056  140 21  4 75  0  0
 1  0      0 52939020  16896 996464    0    0     0     0 1052  140 22  3 75  0  0
 1  0      0 51558644  16896 996464    0    0     0     5 1069  156 21  4 75  0  0
 1  0      0 50188544  16896 996464    0    0     0     0 1069  156 21  4 75  0  0
 0  0      0 64464804  16896 996476    0    0     0     0  557  269 6  7 88  0  0

Any hint for resolving the problem is appreciated

Cheers,
Enrico

pgsql-general by date:

Previous
From: Robert DiFalco
Date:
Subject: Re: Simple Atomic Relationship Insert
Next
From: Nathan Clayton
Date:
Subject: Re: Indexing large table of coordinates with GiST