We have a Postgresql 9.5 running on CentOS release 6.5 (Final) The server has 16GB of ram. We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides this transaction no query is running on the DB in this stage but it can be run during other queries so it must be transactioned.

It works fine until a point where the OOM killer decides it's enough and kills the postmaster process:

Out of memory: Kill process 1766 (postmaster) score 890 or sacrifice child
Killed process 1766, UID 26, (postmaster) total-vm:24384508kB, anon-rss:14376288kB, file-rss:138616kB

Here are the relevant postgres configurations:

#-------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#-------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB                  # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB                         # min 64kB
#maintenance_work_mem = 64MB            # min 1MB
#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
                                        # supported by the operating system:
                                        #   posix
                                        #   sysv
                                        #   windows
                                        #   mmap
                                        # use none to disable dynamic shared memory

# - Disk -

#temp_file_limit = -1                   # limits per-session temp file space
                                        # in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0                  # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1           # 1-1000; 0 disables prefetching
#max_worker_processes = 8

Also the vm.overcommit_memory is set to 2

One solution I found is to disable the OOM killer for the specific process which I'm trying to avoid since it feels to me like not the correct solution. I didn't find any way to force postgres to flush the memory to disk or to limit the total amount of memory used.

Any suggestions?

  • Convert your transaction into multiple smaller ones. Divide and conquer? – Gerard H. Pille May 11 at 6:59
  • @GerardH.Pille I thought about it but it cannot be done, it must be done as a single transaction. – Slava Shpitalny May 11 at 7:03
  • Can you give an example under which circumstances you could have to roll back these millions of updates? – Gerard H. Pille May 11 at 7:28
  • @GerardH.Pille The transaction performs an update to tables representing a map. While doing it other processes use that map. Meaning the map should change all at once or not change at all. If during the change the process dies or some other error occures we don't want to end up with partial map changes which may lead to corrupted state. – Slava Shpitalny May 11 at 7:36
  • I'd create a new version of the map, and only activate that version when done. – Gerard H. Pille May 11 at 11:11

Make sure you have plenty of swap space free, and disable the OOM killer. It is designed for a use case other than what you are doing. Linux can’t safely overcommit unless it has somewhere to swap to. Also check the kernel parameter vm.swappiness, what is it set to?

  • vm.swappiness is set to 60 – Slava Shpitalny May 11 at 7:23
  • The server has 8GB of swap space – Slava Shpitalny May 11 at 7:24
  • That is the default, I think you should add swap and encourage Linux to use it. – Gaius May 11 at 7:24
  • In the old days we would say a server should have 3-4x as much swap as physical memory. That is probably overkill now in most cases, but you should increase it to 16 or 32. – Gaius May 11 at 7:25
  • That is unless you can add more physical of course! That is why I love VMs or cloud :-) – Gaius May 11 at 7:27
up vote 0 down vote accepted

Ok, So after trying a lot of things, I found the solution.

The issue was hiding in our use of the JDBC library.

We had a long connection with auto commit set to false:

connection.setAutoCommit(false)

During that time we were doing a lot of small queries and a few queries with a cursor:

statement.setFetchSize(SOME_FETCH_SIZE)

In JDBC you create a connection object, and from that connection you create statements. When you execute the statments you get a result set.

Now, every one of these objects needs to be closed, but if you close statement, the entry set is closed, and if you close the connection all the statements are closed and their result sets.

We were used to short living queries with connections of their own so we never closed statements assuming the connection will handle the things once it is closed.

The problem was now with this long transaction (~24 hours) which never closed the connection. The statements were never closed. Apparently, the statement object holds resources both on the server that runs the code and on the PostgreSQL database.

My best guess to what resources are left in the DB is the things related to the cursor. The statements that used the cursor were never closed, so the result set they returned never closed as well. This meant the database didn't free the relevant cursor resources in the DB, and since it was over a huge table it took a lot of RAM.

The only thing I don't understand is why PostgreSQL didn't save some of the resources on the DISK (not swap) when it was failing to allocate memory.

Hope it will help some one in the future with similar problem.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.