Blocked updates and background writer performance

Поиск
Список
Период
Сортировка
От Cory Tucker
Тема Blocked updates and background writer performance
Дата
Msg-id CAG_=8kCvTdZKUq8EpyLsywV4Mmz2gjj=nzQH-cUYc5pfnLJ5Xw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Blocked updates and background writer performance  (Dorian Hoxha <dorian.hoxha@gmail.com>)
Re: Blocked updates and background writer performance  (Jeff Janes <jeff.janes@gmail.com>)
Список 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:

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


Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: WIP: CoC
Следующее
От: Jim Mlodgenski
Дата:
Сообщение: Re: WIP: CoC V4