Re: BUG #6763: Severe memory leak with arrays and hstore

Woah. Your email client did something insane, and I cannot read your
message. See below:

On 07/26/2012 09:37 PM, karavelov(at)mail(dot)bg wrote:
> ----- Craig Ringer (ringerc(at)ringerc(dot)id(dot)au), на 26.07.2012 в 11:17 -----
>> On 07/26/2012 09:32 AM, karavelov(at)mail(dot)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(at)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
>