Re: BUG #15660: pg_dump memory leaks when dumping LOBs - Mailing list pgsql-bugs
| From | Serbin, Ilya |
|---|---|
| Subject | Re: BUG #15660: pg_dump memory leaks when dumping LOBs |
| Date | |
| Msg-id | CALTXVii7pu5rnnEtuuo9z7o3EZ+qpJe8sUkbL_ttjhsgx-NHqw@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #15660: pg_dump memory leaks when dumping LOBs (Haribabu Kommi <kommi.haribabu@gmail.com>) |
| List | pgsql-bugs |
Hello! Thanks for quick follow-up.
But I still do not understand why pg_dump consumes so much RAM when exporting LOBS. Here's my point.
I've dropped all the databases, so the only one left is testdbl, with the only table 'image' which I mentioned earlier:
All the LOBS inside the table were empty. As I already mentioned, I used following to fill the table:
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1 .. 5000000
LOOP
INSERT INTO image (name, raster)
VALUES ('emptyfile', lo_import('/etc/motd'));
END LOOP;
END $$;
where /etc/motd is empty file.
testdbl=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7003 kB | pg_default | default administrative connection database
| | | | | postgres=CTc/postgres+| | |
| | | | | pgpool_chk=c/postgres | | |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7841 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6707 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
testdbl | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 536 MB | pg_default |
(4 rows)
testdbl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+----------+--------+-------------
public | image | table | postgres | 211 MB |
(1 row)
testdbl=# select count(*) from image;
count
---------
5000000
testdbl=# select count(*) from pg_largeobject;
count
-------
0
(1 row)
testdbl=# select count(*) from pg_largeobject_metadata;
count
---------
5000000
(1 row)
Overall size of the whole instance's ./base directory was following:
[postgres@host data]$ du -sh base/
676M base/
So I assume that the top RAM to be consumed would be under 676MB. But here what I've got:
Before starting pg_dump:
[postgres@host2 ~]$ free -m
total used free shared buff/cache available
Mem: 9665 143 9380 8 140 9301
Swap: 955 0 955
When "pg_dump: reading large objects" phase finished:
[root@host2 ~]# free -m
total used free shared buff/cache available
Mem: 9665 2203 7320 8 141 7241
Swap: 955 0 955
During "executing BLOBs" phase:
[root@host2 ~]# free -m
total used free shared buff/cache available
Mem: 9665 3982 5327 8 355 5428
Swap: 955 0 955
Peak during "pg_dump: saving large objects" phase:
[postgres@host2 ~]$ free -m
total used free shared buff/cache available
Mem: 9665 4007 4257 8 1400 5375
Swap: 955 0 955
So peak RAM usage was ~4GB and the whole dump procedure took nearly 2 hours to complete for ~600mb sized database.
An usual database with one table (without lobs) 10mil rows, ~1.5GB sized takes 20 seconds be dumped and peak RAM usage for it ~20MB.
So I'm still concerned with pg_dump behaviour and it doesn't seem to be expected.
Best Regards,
Ilya
чт, 28 февр. 2019 г. в 11:00, Haribabu Kommi <kommi.haribabu@gmail.com>:
On Wed, Feb 27, 2019 at 10:58 PM PG Bug reporting form <noreply@postgresql.org> wrote:
Hello,
One of our customers faced an unexpected behaviour when using pg_dump to
export one of DBs - pg_dump consumed all the server memory and got
terminated with following error:
....
pg_dump: reading row security enabled for table "public.databasechangelog"
pg_dump: reading policies for table "public.databasechangelog"
pg_dump: reading row security enabled for table "public.OperationAudit"
pg_dump: reading policies for table "public.OperationAudit"
pg_dump: reading large objects
pg_dump: [archiver (db)] query failed: out of memory for query result
pg_dump: [archiver (db)] query was: SELECT oid, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = lomowner) AS rolname, lomacl FROM
pg_largeobject_metadata
....
Looking through the plan gave following:Thanks for reporting the problem.pg_dump process collects all the database objects first (tables, indexes and etc)and then it write them into the file/archive.In your scenario, there are many large objects that are present which leads toallocate memory for the each object before it gets dumped leads to out of memory.currently I don't see any alternative to this problem other than excluding the dumpand export them separately.Regards,Haribabu KommiFujitsu Australia
pgsql-bugs by date: