Thread: Possible memory leak in PostgreSQL 12.2 on truncate, insert ordelete when type interval is involved
Possible memory leak in PostgreSQL 12.2 on truncate, insert ordelete when type interval is involved
From
Hazuki Amamiya
Date:
Hi Developers,
Recently, there is a memory leak in the game server I hosted. After checking the log, the leak seems appeared after I upgrade PostgreSQL from 11 to 12.1, and I have no luck even I upgraded the PostgreSQL to 12.2 [PostgreSQL 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit] in Debian 10.
I ran a CLI php script which stays forever like a service, and the script won't disconnect form PostgreSQL after connected.
As my original script is difficult to reproduce for you to debug, here I attached a test PHP CLI script which I guess it can simulate the problematic behaviour found in my script:
==================================
<?php
$conn=pg_connect("host=127.0.0.1 dbname=fgtracker user=fgtracker password=fgtracker11 connect_timeout=5",PGSQL_CONNECT_FORCE_NEW);
pg_query ("DROP TABLE IF EXISTS temp_bugreproduction;");
pg_query ("DROP SEQUENCE IF EXISTS temp_bugreproduction_seq;");
pg_query ("CREATE SEQUENCE temp_bugreproduction_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;");
pg_query ("CREATE TABLE temp_bugreproduction (callsign text,flighttime interval,effective_flight_time interval,rank integer DEFAULT nextval('temp_bugreproduction_seq') NOT NULL) WITH (oids = false);");
$j=0;$sql="";
for ($i=0;$i<50;$i++)
$sql.="Insert into temp_bugreproduction (callsign ,flighttime ,effective_flight_time) VALUES ('abc', interval '00:00:01', interval '00:00:02');";
while (1)
{ $j++;
pg_query ("select setval('temp_bugreproduction_seq',1);");
pg_query ("Truncate table temp_bugreproduction");
pg_query ($sql);
for ($i=0;$i<10;$i++)
{ pg_query ("Insert into temp_bugreproduction select callsign ,flighttime +'00:00:01' ,effective_flight_time +'00:00:01' from temp_bugreproduction");
pg_query ("delete from temp_bugreproduction where flighttime > effective_flight_time");
}
print "Iterate $j\n";
}
?>
$conn=pg_connect("host=127.0.0.1 dbname=fgtracker user=fgtracker password=fgtracker11 connect_timeout=5",PGSQL_CONNECT_FORCE_NEW);
pg_query ("DROP TABLE IF EXISTS temp_bugreproduction;");
pg_query ("DROP SEQUENCE IF EXISTS temp_bugreproduction_seq;");
pg_query ("CREATE SEQUENCE temp_bugreproduction_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;");
pg_query ("CREATE TABLE temp_bugreproduction (callsign text,flighttime interval,effective_flight_time interval,rank integer DEFAULT nextval('temp_bugreproduction_seq') NOT NULL) WITH (oids = false);");
$j=0;$sql="";
for ($i=0;$i<50;$i++)
$sql.="Insert into temp_bugreproduction (callsign ,flighttime ,effective_flight_time) VALUES ('abc', interval '00:00:01', interval '00:00:02');";
while (1)
{ $j++;
pg_query ("select setval('temp_bugreproduction_seq',1);");
pg_query ("Truncate table temp_bugreproduction");
pg_query ($sql);
for ($i=0;$i<10;$i++)
{ pg_query ("Insert into temp_bugreproduction select callsign ,flighttime +'00:00:01' ,effective_flight_time +'00:00:01' from temp_bugreproduction");
pg_query ("delete from temp_bugreproduction where flighttime > effective_flight_time");
}
print "Iterate $j\n";
}
?>
====================================
While the script continuing to run, the RES (shown in command “top”) will keep increasing forever.
As this is my first bug report here, please accept my apologies if anything not clear.
In avoidance of doubt, although I mentioned in title that type interval may be the culprit, this is merely my guess and the culprit may be something else.
Hazuki
Re: Possible memory leak in PostgreSQL 12.2 on truncate, insert or delete when type interval is involved
From
Tom Lane
Date:
Hazuki Amamiya <hatsuki_a-ie@yahoo.com.hk> writes:
> Recently, there is a memory leak in the game server I hosted. After checking the log, the leak seems appeared after I
upgradePostgreSQL from 11 to 12.1, and I have no luck even I upgraded the PostgreSQL to 12.2 [PostgreSQL 12.2 (Debian
12.2-1.pgdg100+1)on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit] in Debian 10.
FWIW, I see no sign of a memory leak with this example.
> While the script continuing to run, the RES (shown in command “top”) will keep increasing forever.
Does it really increase "forever"? A fairly common cause of confusion
is that top will report the process' space consumption as gradually
increasing until all of Postgres' shared buffers have been touched
(after which the number stops growing). That's not a leak, it's just
an artifact of how top chooses to count usage of shared memory.
Usually, if you don't see the total "VIRT" column increasing, there's
no real leak happening.
regards, tom lane
Re: Possible memory leak in PostgreSQL 12.2 on truncate, insert ordelete when type interval is involved
From
Hazuki Amamiya
Date:
Hi Tom,
Indeed my scripts seems not able to replicate what happened in my production server. With my production script in my production server, both RES and VIRT do increase. Just I am unable to replicate it with a simple script and simple database schema.
Since the workaround is rather easy by establishing a new connection session every certain time, I would like to close this bug report.
Thanks for your help.
hazuki
On Tuesday, 18 February 2020, 06:22:41 GMT+8, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hazuki Amamiya <hatsuki_a-ie@yahoo.com.hk> writes:
> Recently, there is a memory leak in the game server I hosted. After checking the log, the leak seems appeared after I upgrade PostgreSQL from 11 to 12.1, and I have no luck even I upgraded the PostgreSQL to 12.2 [PostgreSQL 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit] in Debian 10.
FWIW, I see no sign of a memory leak with this example.
> While the script continuing to run, the RES (shown in command “top”) will keep increasing forever.
Does it really increase "forever"? A fairly common cause of confusion
is that top will report the process' space consumption as gradually
increasing until all of Postgres' shared buffers have been touched
(after which the number stops growing). That's not a leak, it's just
an artifact of how top chooses to count usage of shared memory.
Usually, if you don't see the total "VIRT" column increasing, there's
no real leak happening.
regards, tom lane
> Recently, there is a memory leak in the game server I hosted. After checking the log, the leak seems appeared after I upgrade PostgreSQL from 11 to 12.1, and I have no luck even I upgraded the PostgreSQL to 12.2 [PostgreSQL 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit] in Debian 10.
FWIW, I see no sign of a memory leak with this example.
> While the script continuing to run, the RES (shown in command “top”) will keep increasing forever.
Does it really increase "forever"? A fairly common cause of confusion
is that top will report the process' space consumption as gradually
increasing until all of Postgres' shared buffers have been touched
(after which the number stops growing). That's not a leak, it's just
an artifact of how top chooses to count usage of shared memory.
Usually, if you don't see the total "VIRT" column increasing, there's
no real leak happening.
regards, tom lane