Как стать автором
Поиск
Написать публикацию
Обновить

Комментарии 11

@pluzanov, Павел, а нет ли каких изменений в системе блокировок при работе с секционированными таблицами? Как я понимаю, при планировании запроса идет блокировка всех партиций, в том числе и не задействуемых фактически, и получаем

ERROR: out of shared memory
  Hint: You might need to increase max_locks_per_transaction.

Комментарий пока не оценивали0

@Kazzman,
Постгресу нужно гарантировать что секции не будут удалены/изменены во время выполнения запроса, поэтому на каждую из них накладывается AccessShareLock. Тут вряд ли что-то изменится.

Но можно подставлять значение ключа секционирования константой в запрос, тогда будут блокироваться только секции участвующие в запросе плюс родительская. Если ключ секционирования нельзя вычислить заранее - увеличивайте max_locks_per_transaction, как и советует сервер.

Я использовал такой пример:

create table t (a int) partition by list (a);

select format('create table %I partition of t for values in (%s)', 'p'||x, x)
from generate_series(0,2) x
\gexec

\echo Ключ секционирования вычисляется во время выполнения запроса.
begin;
explain (costs off, analyze) select * from t where a = (select 0);

select relation::regclass, mode
from   pg_locks 
where  pid = pg_backend_pid() and
       locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;
commit;


\echo Ключ секционирования задан константой.
begin;
explain (costs off, analyze) select * from t where a = 0;

select relation::regclass, mode
from   pg_locks 
where  pid = pg_backend_pid() and
       locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;

commit;

Вывод скрипта:

DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
Ключ секционирования вычисляется во время выполнения запроса.
BEGIN
                             QUERY PLAN                             
--------------------------------------------------------------------
 Append (actual time=0.013..0.014 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Result (actual time=0.001..0.001 rows=1 loops=1)
   ->  Seq Scan on p0 t_1 (actual time=0.006..0.007 rows=0 loops=1)
         Filter: (a = $0)
   ->  Seq Scan on p1 t_2 (never executed)
         Filter: (a = $0)
   ->  Seq Scan on p2 t_3 (never executed)
         Filter: (a = $0)
 Planning Time: 0.257 ms
 Execution Time: 0.039 ms
(11 rows)

 relation |      mode       
----------+-----------------
 p2       | AccessShareLock
 p1       | AccessShareLock
 p0       | AccessShareLock
 t        | AccessShareLock
(4 rows)

COMMIT
Ключ секционирования задан константой.
BEGIN
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on p0 t (actual time=0.003..0.003 rows=0 loops=1)
   Filter: (a = 0)
 Planning Time: 0.059 ms
 Execution Time: 0.013 ms
(4 rows)

 relation |      mode       
----------+-----------------
 p0       | AccessShareLock
 t        | AccessShareLock
(2 rows)

COMMIT
Всего голосов 1: ↑1 и ↓0+1

Спасибо огромное, пусть ваш пример и прост сам по себе. В проде под нагрузкой пришлось увеличивать max locks до тысяч, иначе валилось. И сейчас я вижу что под конец записи , которая осуществляется хранимой процедурой, у меня 13 локов. Я в клиенте могу и вычисляю партицию до обращения к бд.

Значит как исследовать и что - теперь вроде понятно. Тут как вариант еще спринг шалит. Я чаще раза в сутки тут писать не могу, но по результатам отпищусь или уточняющие вопросы задам.

Комментарий пока не оценивали0

Павел, оказалось немного интереснее чем выглядело на первый взгляд. Посмотрите лог ниже , это два вызова одного и того же запроса подряд:

2022-11-07 12:27:28.158 MSK [3344] [192.168.200.58] СООБЩЕНИЕ:  duration: 0.024 ms  plan:
  Query Text: SELECT exists(select 1 from model 
                      where p_key = elements.pKey and 
                              model_id = elements.id and
                              type_code = elements.vtc and
                              code = elements.vc and
                              unique_id != uniqueId)
  Result  (cost=8.18..8.19 rows=1 width=1) (actual time=0.023..0.023 rows=1 loops=1)
    Output: $0
    Buffers: shared hit=3 dirtied=1
    InitPlan 1 (returns $0)
      ->  Index Scan using model_su052_p_key_model_id_idx on ffp.model_su052  (cost=0.15..8.18 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
            Index Cond: ((model_su052.p_key = 'SU052 '::character(6)) AND ((model_su052.model_id)::text = '4'::text))
            Filter: (((model_su052.unique_id)::text <> '4'::text) AND ((model_su052.type_code)::text = 'AX'::text) AND ((model_su052.code)::text = 'SU'::text))
            Buffers: shared hit=3 dirtied=1
      
      
      
2022-11-07 12:27:29.831 MSK [3344] [192.168.200.58] СООБЩЕНИЕ:  duration: 0.035 ms  plan:
  Query Text: SELECT exists(select 1 from model 
                      where p_key = elements.pKey and 
                              model_id = elements.id and
                              type_code = elements.vtc and
                              code = elements.vc and
                              unique_id != uniqueId)
  Result  (cost=8.34..8.35 rows=1 width=1) (actual time=0.030..0.030 rows=1 loops=1)
    Output: $0
    Buffers: shared hit=3 dirtied=1
    InitPlan 1 (returns $0)
      ->  Append  (cost=0.15..8258.58 rows=1009 width=0) (actual time=0.029..0.029 rows=0 loops=1)
                                                                   
                                                                                                   
            Buffers: shared hit=3 dirtied=1
            Subplans Removed: 1008
            ->  Index Scan using model_su053_p_key_model_id_idx on ffp.model_su053  (cost=0.15..8.18 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=1)
                  Index Cond: ((model_su053.p_key = $14) AND ((model_su053.model_id)::text = ($15)::text))
                  Filter: (((model_su053.unique_id)::text <> ($2)::text) AND ((model_su053.type_code)::text = ($16)::text) AND ((model_su053.code)::text = ($17)::text))
                  Buffers: shared hit=3 dirtied=1

со временем, на 15..50 вызов в одном подключении, бд решает что нужно сделать generic план и мучается с ним, получая до 5000 локов на транзакцию. Лечится путем установки

plan_cache_mode = force_custom_plan

Что, кстати, вызывает пятикратное ускорение операций в бд.

Но тут, как мне кажется, на лицо недоработка оптимизатора. Он сначала блокирует все партиции, а потом уже делает partition pruning. А по хорошему сначала б делать блокировку основной таблицы, потом отфильтровать неподходящие партиции и доблокировать оставшиеся.

Комментарий пока не оценивали0

Идея о переходе на generic план любопытная.

Но мне что-то не удалось повторить на своем примере. Переделал вот так и всё равно блокируется только нужная секция:

load 'auto_explain';
set auto_explain.log_min_duration=0;
set auto_explain.log_analyze=on;
set auto_explain.log_nested_statements=on;
set auto_explain.log_level='NOTICE';
\echo Ключ секционирования передается параметром.
begin;
set local plan_cache_mode = force_generic_plan;
do $$begin execute 'select * from t where a = $1' using 0; end;$$;

set auto_explain.log_min_duration=-1;
select relation::regclass, mode
from   pg_locks 
where  pid = pg_backend_pid() and
       locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;

commit;

LOAD
SET
SET
SET
SET
Ключ секционирования передается параметром.
BEGIN
SET
psql:s2.sql:26: NOTICE:  duration: 0.005 ms  plan:
Query Text: select * from t where a = $1
Query Parameters: $1 = '0'
Seq Scan on p0 t  (cost=0.00..41.88 rows=13 width=4) (actual time=0.004..0.004 rows=0 loops=1)
  Filter: (a = 0)
DO
SET
 relation |      mode       
----------+-----------------
 p0       | AccessShareLock
 t        | AccessShareLock
(2 rows)

COMMIT
Комментарий пока не оценивали0

Оставлю для тех, кто дочитал до этого места. Действительно при переходе на generic план блокируются все секции секционированной таблицы. Вот пример с подготовленным запросом:

create table t (a int) partition by list (a);

select format('create table %I partition of t for values in (%s)', 'p'||x, x)
from generate_series(0,2) x
\gexec

\echo Ключ секционирования передается параметром.
prepare s (int) as select * from t where a = $1;

begin;
\echo Generic plan
set local plan_cache_mode = force_generic_plan;
explain (analyze) execute s(0);

select relation::regclass, mode
from   pg_locks 
where  pid = pg_backend_pid() and
       locktype = 'relation' and relation::regclass <> 'pg_locks'::regclass;

commit;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
Ключ секционирования передается параметром.
PREPARE
BEGIN
Generic plan
SET
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..125.82 rows=39 width=4) (actual time=0.008..0.009 rows=0 loops=1)
   Subplans Removed: 2
   ->  Seq Scan on p0 t_1  (cost=0.00..41.88 rows=13 width=4) (actual time=0.007..0.007 rows=0 loops=1)
         Filter: (a = $1)
 Planning Time: 0.357 ms
 Execution Time: 0.035 ms
(6 rows)

 relation |      mode       
----------+-----------------
 p2       | AccessShareLock
 p1       | AccessShareLock
 p0       | AccessShareLock
 t        | AccessShareLock
(4 rows)

COMMIT
Всего голосов 1: ↑1 и ↓0+1

Двунаправленная логическая репликация - ну наконец-то!

Можно будет производить обновление мажорных версий без даунтайма вообще.

Комментарий пока не оценивали0

Это что. Сейчас активно делают поддержку DDL команд в логической репликации!

Всего голосов 1: ↑1 и ↓0+1

А в чём концептуальная проблема двунаправленной репликации? Помнится, в начале 2000-х писал систему, в качестве СУБД использовался Sybase SQL Anywhere. Четыре узла, все работают как мастера, репликация раз в 10 минут - никаких проблем за почти 10 лет работы системы. Единственное что, пришлось реализовать функции создания гарантированно уникальных первичных ключей (обычные последовательности по понятным причинам использовать нельзя было). Это было 20 лет назад

Комментарий пока не оценивали0

Насколько я понимаю, никакой концептуальной проблемы и не было. Надо было сделать фильтрацию по источнику, и вот ее сделали. Раньше, видимо, просто руки не доходили.

Комментарий пока не оценивали0

Как Егор уже написал, концептуальной проблемы не было.

А Вы вероятно писали систему, основанную на табличных триггерах? Для PostgreSQL, кстати, триггерных систем репликации общего назначения было создано несколько: Slony, Londiste, Bucardo. Последняя, судя по описанию, также поддерживает двунаправленную репликацию.

Комментарий пока не оценивали0
Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2024, Habr