Странности на реплике / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Странности на реплике

15 сообщений из 15, страница 1 из 1

Странности на реплике

#39790244

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

Привет.
На 9.6.5 обнаружил следующую странность. Есть очень простой запрос:

select count(*) from out_messages where proc_status = 'R';

Вот план на мастере:
explain (analyze,verbose,buffers) select count(*) from out_messages where proc_status = 'R';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.59..4.60 rows=1 width=8) (actual time=2.662..2.662 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=569
-> Index Only Scan using omg_proc_status_time_i on public.out_messages_tab (cost=0.57..4.59 rows=1 width=0) (actual time=0.039..2.640 rows=108 loops=1)
Output: out_messages_tab.proc_status, out_messages_tab.proc_time
Index Cond: (out_messages_tab.proc_status = 'R'::text)
Heap Fetches: 96
Buffers: shared hit=569
Planning time: 0.125 ms
Execution time: 2.707 ms
(10 rows)

Вот на реплике:
Aggregate (cost=4.59..4.60 rows=1 width=8) (actual time=132.853..132.853 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=99369
-> Index Only Scan using omg_proc_status_time_i on public.out_messages_tab (cost=0.57..4.59 rows=1 width=0) (actual time=0.049..132.834 rows=61 loops=1)
Output: out_messages_tab.proc_status, out_messages_tab.proc_time
Index Cond: (out_messages_tab.proc_status = 'R'::text)
Heap Fetches: 126822
Buffers: shared hit=99369
Planning time: 0.129 ms
Execution time: 132.910 ms

Видно, что кол-во строк полученных из индекса примерно одинаково, однако, кол-во буферов на порядки больше, и с каждым выполнением запроса на реплике - оно растет. При этом на мастере число прочитанных буфферов постоянно.
Таблица часто обновляется (insert,update), никаких hot_standby_feedback, idle_in_transaction и долгих сессий нет нигде.
Вакум "исправляет" ситуацию, и картина опять повторяется - реплика постоянно наращивает кол-во логических чтений.
Подозреваю, что связано с механизмом hint bits, но насколько я помню, оно должно работать и на мастере и на реплике?

...

Рейтинг:

0 / 0

22.03.2019, 17:41

| Ответить | Цитировать | Написать

Странности на реплике

#39790296

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

gav21,

а если wal_log_hints включить на мастере - ситуация меняется?

...

Рейтинг:

0 / 0

22.03.2019, 19:54

| Ответить | Цитировать | Написать

Странности на реплике

#39790297

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

Alexius,

правда для его включения рестарт базы понадобится(

>Подозреваю, что связано с механизмом hint bits, но насколько я помню, оно должно работать и на мастере и на реплике?
нет, от них эффект только на репликах может быть, т.к. по-умолчанию изменения hint bits в валы не пишутся.

...

Рейтинг:

0 / 0

22.03.2019, 20:01

| Ответить | Цитировать | Написать

Странности на реплике

#39790313

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

AlexiusAlexius,

правда для его включения рестарт базы понадобится(

>Подозреваю, что связано с механизмом hint bits, но насколько я помню, оно должно работать и на мастере и на реплике?
нет, от них эффект только на репликах может быть, т.к. по-умолчанию изменения hint bits в валы не пишутся.

Хорошая версия, спасибо. Но быстро проверить не получится, т.к. действительно нужен рестарт :(
Но разве реплика не должна свои хинт биты писать?

...

Рейтинг:

0 / 0

22.03.2019, 21:30

| Ответить | Цитировать | Написать

Странности на реплике

#39790388

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

gav21Но разве реплика не должна свои хинт биты писать?

да, она пишет их при чтении данных, но насколько я помню только при index scan'ах. попробуйте на реплике сделать

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

--пару раз
explain (analyze,verbose,buffers) select count(*) from out_messages where proc_status = 'R';

set enable_indexonlyscan = off;
--пару раз
explain (analyze,verbose,buffers) select count(*) from out_messages where proc_status = 'R';

set enable_indexonlyscan = on;
--пару раз
explain (analyze,verbose,buffers) select count(*) from out_messages where proc_status = 'R';

и покажите что получилось (строку с shared hit достаточно). при index only scan'ах мы скорей всего не выставляем hint bits(

...

Рейтинг:

0 / 0

23.03.2019, 10:55

| Ответить | Цитировать | Написать

Странности на реплике

#39790793

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

Alexiusgav21Но разве реплика не должна свои хинт биты писать?

и покажите что получилось (строку с shared hit достаточно). при index only scan'ах мы скорей всего не выставляем hint bits(

результат такой же, метод доступа не влияет (index scan, index only scan, bitmap index scan). Каждый следующее выполнение - увеличивает кол-во логических чтений. Даже Seq Scan сделал, подумал а вдруг, однако, не повлияло :(

...

Рейтинг:

0 / 0

25.03.2019, 10:09

| Ответить | Цитировать | Написать

Странности на реплике

#39790827

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

gav21,

out_messages это таблица или вью? покажите вывод:

Код: sql

1.
2.
3.
4.
5.
6.

\dt+ out_out_messages_tab
\di+ omg_proc_status_time_i 
\d+ omg_proc_status_time_i 
\d+ out_messages

select * from pg_stats where tablename = 'out_messages_tab' and attname = 'proc_status';

...

Рейтинг:

0 / 0

25.03.2019, 11:16

| Ответить | Цитировать | Написать

Странности на реплике

#39790909

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

out_messages это вью, которая просто селектит все поля из out_messages_tab

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

\dt+ out_messages_tab

                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size  | Description
--------+------------------+-------+----------+--------+-------------
 public | out_messages_tab | table | user | 171 GB |
(1 row)

\di+ omg_proc_status_time_i
                                      List of relations
 Schema |          Name          | Type  |  Owner   |      Table       | Size  | Description
--------+------------------------+-------+----------+------------------+-------+-------------
 public | omg_proc_status_time_i | index | user | out_messages_tab | 13 GB |



\d+ omg_proc_status_time_i
               Index "public.omg_proc_status_time_i"
   Column    |            Type             | Definition  | Storage
-------------+-----------------------------+-------------+----------
 proc_status | character varying(1)        | proc_status | extended
 proc_time   | timestamp without time zone | proc_time   | plain
btree, for table "public.out_messages_tab"

 select * from pg_stats where tablename = 'out_messages_tab' and attname = 'proc_status';
-[ RECORD 1 ]----------+-----------------
schemaname             | public
tablename              | out_messages_tab
attname                | proc_status
inherited              | f
null_frac              | 0
avg_width              | 2
n_distinct             | 2
most_common_vals       | {P,D}
most_common_freqs      | {0.9997,0.0003}
histogram_bounds       |
correlation            | 0.999576
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

Полное описание таблицы пока не могу показать.

...

Рейтинг:

0 / 0

25.03.2019, 13:19

| Ответить | Цитировать | Написать

Странности на реплике

#39790959

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

Заметил особенность,
индекс omg_proc_status_time_i - составной

Index "public.omg_proc_status_time_i"
Column | Type | Definition | Storage
-------------+-----------------------------+-------------+----------
proc_status | character varying(1) | proc_status | extended
proc_time | timestamp without time zone | proc_time | plain

и если в запрос добавить условие по второму полю из индекса (proc_time) то эффект с увеличением чтений не появляется..

...

Рейтинг:

0 / 0

25.03.2019, 14:28

| Ответить | Цитировать | Написать

Странности на реплике

#39790982

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

gav21Заметил особенность,
индекс omg_proc_status_time_i - составной

Index "public.omg_proc_status_time_i"
Column | Type | Definition | Storage
-------------+-----------------------------+-------------+----------
proc_status | character varying(1) | proc_status | extended
proc_time | timestamp without time zone | proc_time | plain

и если в запрос добавить условие по второму полю из индекса (proc_time) то эффект с увеличением чтений не появляется..

ошибся, все так же :(

...

Рейтинг:

0 / 0

25.03.2019, 14:55

| Ответить | Цитировать | Написать

Странности на реплике

#39791005

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

Кажется, что вот тут собака порылась.
Есть механизм hint bits для индексов:
https://www.cybertec-postgresql.com/en/killed-index-tuples/
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3f4d48802271126b1343289a9d2267ff1ed3788a

эффект работы который я наблюдаю на мастере (первое выполнение всегда логических чтений больше чем последующие)
Работает ли это на реплике?

...

Рейтинг:

0 / 0

25.03.2019, 15:34

| Ответить | Цитировать | Написать

Странности на реплике

#39791037

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

The main reason for slave DB not use the index hint bits because of
difference
in between xmin value of both master and standby, This is required for
proper
MVCC behavior for the queries that are running on the standby.

Following is the comment from the code that explains more.

/*
* During recovery we ignore killed tuples and don't bother to kill them
* either. We do this because the xmin on the primary node could easily be
* later than the xmin on the standby node, so that what the primary
* thinks is killed is supposed to be visible on standby. So for correct
* MVCC for queries during recovery we must ignore these hints and check
* all tuples. Do *not* set ignore_killed_tuples to true when running in a
* transaction that was started during recovery. xactStartedInRecovery
* should not be altered by index AMs.
*/

https://www.postgresql.org/message-id/flat/CAB3vJCnPMX2 Eg TPjA2Tu6ernL_4F6n7s7SOeuQh4QK1R-jXQ@mail.gmail.com#24fc7273a3088d8b2deb75037e55b975

вопрос закрыт, будем делать чаще вакум...

...

Рейтинг:

0 / 0

25.03.2019, 16:21

| Ответить | Цитировать | Написать

Странности на реплике

#39791053

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

gav21,

я hint bits в индексах и имел ввиду, вот тут подробнее написано про lp_dead, он в index сканах должен проставляться по идее.

возможно проблему с долгим выполнением запросов на реплике тут решит частичный индекс по proc_time, где proc_status != 'P'. по крайней мере, его точно есть смысл создать вместо полного.
но причина пока непонятна. на реплике точно не было долгих транзакций, когда вариант с index scan запускали?

можно еще вывод с мастера:

Код: sql

select * from pg_stat_user_tables where relname = 'out_messages_tab';

...

Рейтинг:

0 / 0

25.03.2019, 16:44

| Ответить | Цитировать | Написать

Странности на реплике

#39791066

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

Alexiusgav21,

я hint bits в индексах и имел ввиду, вот тут подробнее написано про lp_dead, он в index сканах должен проставляться по идее.

возможно проблему с долгим выполнением запросов на реплике тут решит частичный индекс по proc_time, где proc_status != 'P'. по крайней мере, его точно есть смысл создать вместо полного.
но причина пока непонятна. на реплике точно не было долгих транзакций, когда вариант с index scan запускали?

можно еще вывод с мастера:

Код: sql

select * from pg_stat_user_tables where relname = 'out_messages_tab';

?

спасибо за ссылку
Объяснение я привел выше. Недоделка в работе standby сделана в угоду работе MVCC.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

select * from pg_stat_user_tables where relname = 'out_messages_tab';
-[ RECORD 1 ]-------+------------------------------
relid               | 30608
schemaname          | public
relname             | out_messages_tab
seq_scan            | 733
seq_tup_read        | 83140381971
idx_scan            | 1115146656
idx_tup_fetch       | 24698476357
n_tup_ins           | 451872699
n_tup_upd           | 902253877
n_tup_del           | 0
n_tup_hot_upd       | 250704385
n_live_tup          | 447828707
n_dead_tup          | 1445108
n_mod_since_analyze | 4033544
last_vacuum         | 2019-03-25 09:18:38.782759+03
last_autovacuum     | 2019-03-12 08:22:01.656961+03
last_analyze        | 2019-03-22 15:01:04.598402+03
last_autoanalyze    | 2019-03-24 13:50:46.406934+03
vacuum_count        | 6
autovacuum_count    | 791
analyze_count       | 6
autoanalyze_count   | 2522

...

Рейтинг:

0 / 0

25.03.2019, 17:07

| Ответить | Цитировать | Написать

Странности на реплике

#39791415

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

gav21,

хм, действительно получается для индексов мы этот флаг игнорируем на репликах и включенный wal_log_hints тут не поможет. как удивительно.

...

Рейтинг:

0 / 0

26.03.2019, 12:23

| Ответить | Цитировать | Написать

15 сообщений из 15, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Странности на реплике

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=43&tid=1995278]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	41ms
get topic data:	11ms
get forum data:	2ms
get page messages:	49ms
get tp. blocked users:	2ms
others:	14ms

total:	150ms