Re: BUG #6763: Severe memory leak with arrays and hstore - Mailing list pgsql-bugs
From | Craig Ringer |
---|---|
Subject | Re: BUG #6763: Severe memory leak with arrays and hstore |
Date | |
Msg-id | 5011E834.4020805@ringerc.id.au Whole thread Raw |
In response to | Re: BUG #6763: Severe memory leak with arrays and hstore (karavelov@mail.bg) |
List | pgsql-bugs |
Woah. Your email client did something insane, and I cannot read your message. See below: On 07/26/2012 09:37 PM, karavelov@mail.bg wrote: > ----- Craig Ringer (ringerc@ringerc.id.au), на 26.07.2012 в 11:17 ----- >> On 07/26/2012 09:32 AM, karavelov@mail.bg wrote: >> Finally I have > managed to migrate it in batches of 100-200k user ids and >> disconnecting > after each query in order to free the backend and leaked >> memory. > If > you do it in batches, but you do NOT disconnect and reconnect, does > the > backend continue to grow? > > What's the output of: > > SELECT > count(sub.user_id), to_char(AVG(sub.n_prefs), '99999.99') FROM ( > SELECT > user_id, count(name) AS n_prefs FROM old_prefs GROUP BY user_id) > AS sub; >>> and > > SELECT pg_size_pretty(pg_total_relation_size('old_prefs')); > > > ? > > -- > Craig Ringer > Ok, I will do the procedure again with taking > notes on each step. First, here are the results of the queries you asked: > pg=> SELECT count(sub.user_id), to_char(AVG(sub.n_prefs), '99999.99') FROM > ( SELECT user_id, count(name) AS n_prefs FROM old_prefs GROUP BY user_id) > AS sub; count | to_char ---------+----------- 1257262 | 2.26 (1 row) pg=> > SELECT pg_size_pretty(pg_total_relation_size('old_prefs')); pg_size_pretty > ---------------- 264 MB (1 row) pg=> d old_prefs Table "public.old_prefs" > Column | Type | Modifiers ---------+-------------------+----------- user_id > | integer | not null name | character varying | not null value | character > varying | not null Indexes: "old_prefs_user_id_ids" btree (user_id) Also > there are max of 34 rows per user_id in old_prefs here is the new table I > just created: pg=> d new_preferences Table "public.new_preferences" Column > | Type | Modifiers ---------+---------+----------- user_id | integer | not > null prefs | hstore | Indexes: "new_preferences_pkey" PRIMARY KEY, btree > (user_id) Foreign-key constraints: "new_preferences_user_id_fkey" FOREIGN > KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE Here is a newly > connected the backend: root@pg:/var/log# ps axu | egrep '10.0.2.71|USER' | > grep -v grep USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND > postgres 19121 0.0 0.0 2266944 3448 ? Ss 15:23 0:00 postgres: pg pg > 10.0.2.71(51734) idle Migrating the first 200k of the users to the new > scheme: pg=> select count(*) from old_prefs where user_id INSERT INTO > new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) > FROM old_prefs WHERE user_id commit; COMMIT Here is the backend: USER PID > %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 19121 0.8 7.1 > 3081772 582712 ? Ss 15:23 0:02 postgres: pg pg 10.0.2.71(51734) idle > Migrating another batch of users: pg => select count(*) from old_prefs > where user_id>=200000 and user_id INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=200000 AND user_id commit; COMMIT USER PID %CPU %MEM VSZ RSS TTY > STAT START TIME COMMAND postgres 19121 1.1 8.5 3176164 697444 ? Ss 15:23 > 0:05 postgres: pg pg 10.0.2.71(51734) idle Another batch: pg=> select > count(*) from old_prefs where user_id>=600000 and user_id INSERT INTO > new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) > FROM old_prefs WHERE user_id>=600000 AND user_id commit; COMMIT USER PID > %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 19121 0.7 9.6 > 3210224 791404 ? Ss 15:23 0:08 postgres: pg pg 10.0.2.71(51734) idle > Another batch: pg=> select count(*) from old_prefs where user_id>=1100000 > and user_id INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=1100000 AND user_id commit; COMMIT USER PID %CPU %MEM VSZ RSS TTY > STAT START TIME COMMAND postgres 19121 0.9 10.8 3277412 889860 ? Ss 15:23 > 0:11 postgres: pg pg 10.0.2.71(51734) idle So Pg backeng keep growing with > 100M per 200k row from old table that became 50-60k rows in the new table > Proceeding with another batch: pg=> select count(*) from old_prefs where > user_id>=1600000 and user_id INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=1600000 AND user_id commit; COMMIT USER PID %CPU %MEM VSZ RSS TTY > STAT START TIME COMMAND postgres 19121 0.9 11.5 3277412 945560 ? Ss 15:23 > 0:15 postgres: pg pg 10.0.2.71(51734) idle Another batch: pg=> select > count(*) from old_prefs where user_id>=2400000 and user_id INSERT INTO > new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) > FROM old_prefs WHERE user_id>=2400000 AND user_id commit; COMMIT USER PID > %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 19121 1.2 16.2 > 3736968 1331796 ? Ss 15:23 0:20 postgres: pg pg 10.0.2.71(51734) idle > Another batch: pg => select count(*) from old_prefs where user_id>=3400000 > and user_id INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=3400000 AND user_id rollback; ROLLBACK Ops.. have to cleanup the > old_prefs, some users were deleted in the meantime: pg=> delete from > old_prefs where user_id not in (select user_id from users); DELETE 7 pg=> > commit; COMMIT USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND > postgres 19121 1.4 26.4 4469520 2157588 ? Ss 15:23 0:29 postgres: pg pg > 10.0.2.71(51734) idle Near 1G grow on rolled back transaction.... pg=> > INSERT INTO new_preferences SELECT user_id,hstore(array_agg(name), > array_agg(value)) FROM old_prefs WHERE user_id>=3400000 AND user_id commit; > COMMIT USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres > 19121 1.7 26.6 4479944 2180536 ? Ss 15:23 0:35 postgres: pg pg > 10.0.2.71(51734) idle Another batch, bigger this time: pg=> select count(*) > from old_prefs where user_id>=3800000 and user_id INSERT INTO > new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) > FROM old_prefs WHERE user_id>=3800000 AND user_id commit; COMMIT USER PID > %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 19121 1.9 33.1 > 5238968 2710756 ? Ss 15:23 0:45 postgres: pg pg 10.0.2.71(51734) idle > Another big batch: pg=> select count(*) from old_prefs where > user_id>=4200000 and user_id INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=4200000 AND user_id commit; COMMIT USER PID %CPU %MEM VSZ RSS TTY > STAT START TIME COMMAND postgres 19121 2.2 35.7 5438412 2918720 ? Ss 15:23 > 0:55 postgres: pg pg 10.0.2.71(51734) idle Now a smaller batch: pg=> select > count(*) from old_prefs where user_id>=4400000 and user_id INSERT INTO > new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) > FROM old_prefs WHERE user_id>=4400000 AND user_id commit; COMMIT RSS keeps > growing: USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres > 19121 2.2 36.0 5438412 2943784 ? Ss 15:23 1:00 postgres: pg pg > 10.0.2.71(51734) idle Lets see if a bigger batch will pass: pg=> select > count(*) from old_prefs where user_id>=4500000; count -------- 631911 (1 > row) pg=> INSERT INTO new_preferences SELECT > user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE > user_id>=4500000 GROUP BY user_id; INSERT 0 296541 pg=> commit; COMMIT Ok, > this time it passed, but the backend is over 4G USER PID %CPU %MEM VSZ RSS > TTY STAT START TIME COMMAND postgres 19121 2.2 50.0 7227968 4088928 ? Ss > 15:23 1:17 postgres: pg pg 10.0.2.71(51734) idle Some observations: 1. > Backend does not free allocated memory between transactions. 2. Rolled back > transactions also leak memory. 3. Leaked memory is not linear to work done > - 2 transactions with 200k keys will leak less than 1 transaction with 400k > keys Regarding Tom's question: The old_prefs does not fit in work_mem but > is quite small regarding the total RAM. Isn't the "work_mem" a limit of the > memory each backend could allocate for sorting, grouping and aggregation? > My understanding is that bigger allocation will overflow to disk and will > not kill the server. I could be wrong though. Thanks in advance and best > regards -- Luben Karavelov >
pgsql-bugs by date: