2

I am having issues with a large query, that I expect to rely on wrong configs of my postgresql.config. My setup is PostgreSQL 9.6 on Ubuntu 17.10 with 32GB RAM and 3TB HDD. The query is running pgr_dijkstraCost to create an OD-Matrix of ~10.000 points in a network of 25.000 links. Resulting table is thus expected to be very big ( ~100'000'000 rows with columns from, to, costs). However, creating simple test as select x,1 as c2,2 as c3 from generate_series(1,90000000) succeeds.

The query plan:

                                     QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Function Scan on pgr_dijkstracost  (cost=393.90..403.90 rows=1000 width=24)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=196.82..196.83 rows=1 width=32)
           ->  Seq Scan on building_nodes b  (cost=0.00..166.85 rows=11985 width=4)
   InitPlan 2 (returns $1)
     ->  Aggregate  (cost=196.82..196.83 rows=1 width=32)
           ->  Seq Scan on building_nodes b_1  (cost=0.00..166.85 rows=11985 width=4)

This leads to a crash of PostgreSQL:

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited      
normally and possibly corrupted shared memory.

Running dmesg I could trace it down to be an Out of memory issue:

Out of memory: Kill process 5630 (postgres) score 949 or sacrifice child
[ 5322.821084] Killed process 5630 (postgres) total-vm:36365660kB,anon-rss:32344260kB, file-rss:0kB, shmem-rss:0kB
[ 5323.615761] oom_reaper: reaped process 5630 (postgres), now anon-rss:0kB,file-rss:0kB, shmem-rss:0kB
[11741.155949] postgres invoked oom-killer: gfp_mask=0x14201ca(GFP_HIGHUSER_MOVABLE|__GFP_COLD), nodemask=(null),  order=0, oom_score_adj=0
[11741.155953] postgres cpuset=/ mems_allowed=0

When running the query I also can observe with topthat my RAM is going down to 0 before the crash. The amount of committed memory just before the crash:

$grep Commit /proc/meminfo
CommitLimit:    18574304 kB
Committed_AS:   42114856 kB

I would expect the HDD is used to write/buffer temporary data, when RAM is not enough. But the available space on my hdd does not change during the processing. So I began to dig for missing configs (expecting issues due to my relocated data-directory) and following different sites:

https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT https://www.credativ.com/credativ-blog/2010/03/postgresql-and-linux-memory-management

My original settings of postgresql.conf are default except for changes in the data-directory:

data_directory = '/hdd_data/postgresql/9.6/main'
shared_buffers = 128MB          # min 128kB
#huge_pages = try           # on, off, or try
#temp_buffers = 8MB         # min 800kB
#max_prepared_transactions = 0      # zero disables the feature
#work_mem = 4MB             # min 64kB
#maintenance_work_mem = 64MB        # min 1MB
#replacement_sort_tuples = 150000   # limits use of replacement selection sort
#autovacuum_work_mem = -1       # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB          # min 100kB
dynamic_shared_memory_type = posix  # the default is the first option

I changed the config:

shared_buffers = 128MB
work_mem = 40MB             # min 64kB
maintenance_work_mem = 64MB

Relaunched with sudo service postgresql reload and tested the same query, but found no change in behavior. Does this simply mean, such a large query can not be done? Any help appreciated.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
PaDe
  • 51
  • 1
  • 3
  • possibly better suited for dba.stackexchange.com? – Joe Mar 08 '18 at 23:09
  • 1
    Show us the table definitions. Show us the data. Show us the query, Show us the tuning. NULL-hypothesis: Carthesian product. – wildplasser Mar 09 '18 at 00:47
  • 2
    This problem has nothing to do with Linux overcommit; if you change the configuration, you'll get OOM errors rather than a kill from the OOM reaper, but the problem remains: your function call consumes more memory than is available. Seems like the function allocates more and more memory until your available memory is exceeded. I've added relevant tags, hopefully somebody can say more. – Laurenz Albe Mar 09 '18 at 08:35
  • 1
    If you get OOM Killer and cannot configure postgres to use less memory, you either have to get more RAM or (if you're willing to wait for the process to complete) add swap until the kernel is happy. – Mikko Rantalainen Apr 08 '20 at 14:12

1 Answers1

0

I'm having similar trouble, but not with PostgreSQL (which is running happily): what is happening is simply that the kernel cannot allocate more RAM to the process, whichever process it is.

It would certainly help to add some swap to your configuration. To check how much RAM and swap you have, run: free -h On my machine, here is what it returns:

               total        used        free      shared  buff/cache   available
Mem:           7.7Gi       5.3Gi       928Mi       865Mi       1.5Gi       1.3Gi
Swap:          9.4Gi       7.1Gi       2.2Gi

You can clearly see that my machine is quite overloaded: about 8Gb of RAM, and 9Gb of swap, from which 7 are used.

When the RAM-hungry process got killed after Out of memory, I saw both RAM and swap being used at 100%.

So, allocating more swap may improve our problems.

Pierre
  • 530
  • 5
  • 13