From: | Cory Tucker <cory(dot)tucker(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Blocked updates and background writer performance |
Date: | 2016-01-12 18:25:13 |
Message-ID: | CAG_=8kCvTdZKUq8EpyLsywV4Mmz2gjj=nzQH-cUYc5pfnLJ5Xw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
PG 9.4.4 (RDS)
I'm experiencing an issue when trying to update many rows in a single table
(one row at a time, but parallelized across ~12 connections). The issue we
see is that the writes will periodically be blocked for a duration of
several minutes and then pick back up. After digging through our
monitoring stack, I was able to uncover these stats which seem to allude to
it being a background writer performance problem:
[image: bg_writer.png]
(apologies for the image)
Our settings for the background writer are pretty standard OOB (I threw in
some others that I thought might be helpful, too):
name | setting | unit
-------------------------+---------+------
bgwriter_delay | 200 | ms
bgwriter_lru_maxpages | 100 |
bgwriter_lru_multiplier | 2 |
maintenance_work_mem | 65536 | kB
max_worker_processes | 8 |
work_mem | 32768 | kB
The table that is being written to contains a jsonb column with a GIN index:
Table "public.ced"
Column | Type | Modifiers
---------------+--------------------------+-----------
id | bigint | not null
created_at | timestamp with time zone |
modified_at | timestamp with time zone |
bean_version | bigint | default 0
account_id | bigint | not null
data | jsonb | not null
Indexes:
"ced_pkey" PRIMARY KEY, btree (id)
"ced_data" gin (data jsonb_path_ops)
"partition_key_idx" btree (account_id, id)
It seems to me that the background writer just can't keep up with the
amount of writes that I am trying to do and freezes all the updates. What
are my options to improve the background writer performance here?
thanks
--Cory
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Mlodgenski | 2016-01-12 19:37:30 | Re: WIP: CoC V4 |
Previous Message | Joe45678 | 2016-01-12 17:50:34 | Setting privileges for users |