Kim Burgestrand

Heartfelt software development. Sometimes other things.

How I reduced PostgreSQL memory usage from our Sidekiq workers

Like all good stories do, it started with a bunch of errors in my inbox:

ActiveRecord::StatementInvalid sidekiq#pro
  PG::ConnectionBad: PQconsumeInput() SSL connection has been closed unexpectedly : SELECT …
PG::ConnectionBad analytics:attribution
  could not fork new process for connection: Cannot allocate memory could not fork new process for connection: Cannot allocate memory could not fork new process for connection: Cannot allocate memory
ActionView::Template::Error admin#stats
  PG::OutOfMemory: ERROR: out of memory DETAIL: Failed on request of size 48. : SELECT … 

“How strange!”, I thought, “I wonder what the memory usage looks like.”

Well, saw patterns in graphs are rarely good. These errors cropped up after increasing the number of Sidekiq workers, and thus also the number of connections to the database.

After some intense googling, I found out that PostgreSQL stores PREPAREd statements per session. I know Rails uses prepared statements, and I know prepared statements need to be referenced by name, which means Rails keeps this name around somewhere. After looking through the source of Rails on GitHub, I found this:

# https://github.com/rails/rails/blob/v5.0.0/activerecord/lib/active_record/connection_adapters/statement_pool.rb#L6
module ActiveRecord
  module ConnectionAdapters
    class StatementPool # :nodoc:
      include Enumerable

      DEFAULT_STATEMENT_LIMIT = 1000

So, turns out Rails by default stores the 1 000 most recently used prepared statements. Quick math! With 30 workers, we can expect 30 000 prepared statements kept around in memory. All in all it’s not that much, but we’re sporting a humble 1GB of memory, so it turns out that this is enough to hit the roof!

So, what to do? More googling! Turns out that Rails has support for disabling prepared statements, and it’s had it since at least Rails v4.0! So, I sent a pull request with a configuration change to disable prepared statements for our Sidekiq workers:

default: &default
  adapter: postgresql
  ⋮
+  prepared_statements: <%= ! Sidekiq.server? %>

Once merged and deployed, memory usage for PostgreSQL dropped considerably, and has since been stable at just under 200 MB!

This probably comes with a cost of increased load, the good old Memory vs CPU tradeoff. We could possibly have lowered the statement limit (available since at least Rails 4.2) but the load on our servers is not too high, so we’re happy with just turning them off!

Kim Burgestrand

Heartfelt software development. Sometimes other things.

Tweet