is it possible to limit somehow usage of resources for pg_dump?
The problem is, whole DBase is 775GB and i have two very large tables:
pg_largeobject table is – 390GB
pg_largeobject statistics:
Index scans 778313505
Index tuples fetched 1079812292
Tuples inserted 201022148
Table size 395 GB
Indexes size 6900 MB
hive.lob_messages – 265GB.
Index scans 194377937
Index tuples fetched 183894199
Tuples inserted 16902844
Table size 8127 MB
Toast table size 272573947904
Indexes size 3090 MB
In all cases I am executing this pg_dump command:
pg_dump -U postgres -d hive -Fc -v -f /nfs/hive_dump_10/hive_full_20191016_1945.dmp 2> /nfs/hive_dump_10/hive_full_20191016_1945.log
When i try to run pg_dump, to dump all the data it is killed by "out of memory".
My Postgre SQL server have had 20gb of RAMs and when I started a pg_dump, it kills after about ~30minutes. saying out of memory.
DB with 20GB of rams and pg_dump is killed by this *.log line:
pg_dump: reading large objects
Kern.log:
Oct 15 09:00:18 hive-psql kernel: [21504626.583951] Out of memory: Kill process 30663 (pg_dump) score 750 or sacrifice child
Oct 15 09:00:18 hive-psql kernel: [21504626.584000] Killed process 30663 (pg_dump) total-vm:19151692kB, anon-rss:16873728kB, file-rss:0kB, shmem-rss:0kB
Here is ZABBIX monitoring tool showing my three times tried a pg dump with 20GB rams:
So to cope with the problem i gave my all resources, additional +20 GB ram (40gb in total), and the it kills after ~3hours by this *.log line:
pg_dump: dumping contents of table "hive.lob_messages"
And kern.log says:
Oct 16 23:19:01 hive-psql kernel: [15614.408113] Out of memory: Kill process 2693 (pg_dump) score 840 or sacrifice child
Oct 16 23:19:01 hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB
ZABBIX window again, with 40GB in total:
I do not have any more resources for that.
So what are my options? My DB grows everyday, thank you.
I think that additional 20GB will be suffice, 60GB of rams it should be enough . But is this OK situation? What to do when my DB became like ~2TB in size and etc…
Other question, is my hive.lob_messages table is okay?
Table size and toast table size is different.
Best Answer
What the graph shows is pg_dump probably starting at 19:45 and having a normal memory consumption until 20:00. At 20:00, it appears that ~30 GB are suddenly allocated and not much is released until 23:15 when presumably the Out-Of-Memory condition occurs.
From the comments, the kernel says:
The
anon-rss
of pg_dump at about 36GB shows that it is indeed pg_dump that over-allocates. There is something wrong in pg_dump needing that much memory.Your database surely has many large objects, some of them may be very large (the theorical maximum per object is 2TB) but pg_dump processes large objects by:
opening a cursor on
SELECT oid FROM pg_largeobject_metadata ORDER BY 1
and reading it by chunks of 1000 entries: that should never blow up.for each large object, loop on lo_read to fetch the contents into a buffer of 16384 bytes: that should never blow up.
The fact that the table itself weighs 390 GB should not be a reason for pg_dump to do large allocations.
Assuming pg_dump displays this with the
-v
options (by the way, you did not mention what options were passed), it should display "reading large objects" instead.