Сорри за оффтоп-)

🇷🇺#бизнес #россия
Gucci зарегистрировала одноименный бренд в РФ — данные Роспатента

Праздничный квест.
В табл-е 5 млр строк, в GP 1000 сегментов.
Какой ключ распределения для X в запросе лучше, чем текущий ключ 'b' :

Holiday quest.
There are 5 billion rows in the table, 1000 segments in the GP.
Which distribution key for X in the query is better than the current 'b' :

select a, b, c , deleted_flg, dt_from, dt_to,
min(case when deleted_flg is true then null else dt_from end)
over(partition by a, b) as mn_dt_from
from X

С праздником, мужики!

Ответ на quest.
Еще вчера я бы поверил, что randomly - корректный ответ.
Вот мои доводы, запасайтесь поп корном!

Серия тестов на проде показала, что независимо от ключа, план запроса не меняется, который привожу в оригинале для точности ( cession_deal_rk, credit_deal_rk - это наши a и b в задаче )

Gather Motion 1056:1  (slice1; segments: 1056)  (cost=0.00..1231650.10 rows=5456054272 width=97) (actual time=25171.537..1101746.845 rows=5456054066 loops=1)
  ->  Result  (cost=0.00..65032.75 rows=5166719 width=97) (actual time=88416.959..108735.578 rows=8879058 loops=1)
        ->  WindowAgg  (cost=0.00..64531.58 rows=5166719 width=97) (actual time=88415.738..106450.656 rows=8879058 loops=1)
"              Partition By: cession_deal_rk, credit_deal_rk"
              ->  Sort  (cost=0.00..64531.58 rows=5166719 width=96) (actual time=88415.691..98580.905 rows=8879058 loops=1)
"                    Sort Key: cession_deal_rk, credit_deal_rk"
                    Sort Method:  external merge  Disk: 417165856kB
                    ->  Seq Scan on x  (cost=0.00..891.35 rows=5166719 width=96) (actual time=7.464..3897.447 rows=8879058 loops=1)
Planning time: 67.970 ms
  (slice0)    Executor memory: 2000K bytes.
"* (slice1)    Executor memory: 50717K bytes avg x 1056 workers, 67008K bytes max (seg240).  Work_mem: 48410K bytes max, 1595169K bytes wanted."
Memory used:  540672kB
Memory wanted:  4785705kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1346019.073 ms

Мой опорный вывод, что шаг Sort Method: external merge в плане выполняет сортировку слиянием отсортированных ранее частей табл-ы на сегментах,
а значит ровно рассыпаная по сегментам табл-а дает на этом шаге оптимальный результат.

В пользу этого говорит следующий факт.
Если выбрать ключом a, табл-а ( в нашем конкретном бизнес кейсе) станет сильно перекошена при максимуме 285 млн строк на сегменте, что
почти на 2 порядка выше среднего на сегмент ~5 млн строк (= 5 млрд сток в табл-е / 1056 сегментов),
В результате запрос валится по тайм-ауту после часа выполнения, тогда как при randomly выполняется меньше минуты.
Иными словами, такое поведение намекает, что сортировка происходит локально сначала именно на сегментах, и не все ее способны вынести.

В исходном коде проекта был обнаружен ключ b, который гораздо ровнее чем a, хотя и далек от равномерного.
Если в цифрах, для ключа по a,b skew составил 0.4, для randomly 0.001, т.е. данные почти без перекоса.
Считали skew как (max tuples(среди всех сегментов) - avg tuples(по всем сегментам)) / max tuples.

Таким образом, ключ оконки в partittion by не имеет значения, и подстраивать под него ключ дистрибуции для больших таблиц я бы не стал.
Конечно, если конкретный ключ дает равномерное распределение, то как вариант - можно оставить его, но универсальный способ - лучше раскидать таблицу по сегментам максимально равномерно.
Но в нашем квесте речь шла о решении конкретной узкой задачи.

Но не все так просто, ибо
я выявил баг с ALTER TABLE SET DISTRIBUTED BY.
Меня удивило, что данная операция смены ключа с a,b в randomly на табл-е в 5 млрд отработала мгновенно (0.2 с) и я решил проверить, все ли тут чисто.

После смены ключа с randomly на a,b ключ честно поменялся.
После отката к randomly (через ALTER TABLE SET x DISTRIBUTED RANDOMLY) ключ поменялся только в метаданных, т.е. pg_get_table_distributedby вернул RANDOMLY,
но по факту skew остался 0.4, вместо ожидаемого отсутствия перекоса.

И что хуже, эта фейковая смена ключа в RANDOMLY не отразилась на реальном плане, где при DISTRIBUTED RANDOMLY должен появиться Redistribute:

Gather Motion 1056:1  (slice2; segments: 1056)  (cost=0.00..1233203.18 rows=5456056832 width=97) (actual time=69988.544..1143226.306 rows=5456056682 loops=1)
  ->  Result  (cost=0.00..66585.28 rows=5166721 width=97) (actual time=70230.562..99398.590 rows=9020522 loops=1)
        ->  WindowAgg  (cost=0.00..66084.11 rows=5166721 width=97) (actual time=70229.410..94669.119 rows=9020522 loops=1)
"              Partition By: cession_deal_rk, credit_deal_rk"
              ->  Sort  (cost=0.00..66084.11 rows=5166721 width=96) (actual time=70229.304..76381.228 rows=9020522 loops=1)
"                    Sort Key: cession_deal_rk, credit_deal_rk"
                    Sort Method:  external merge  Disk: 417165728kB
                    ->  Redistribute Motion 1056:1056  (slice1; segments: 1056)  (cost=0.00..3366.42 rows=5166721 width=96) (actual time=173.046..30055.350 rows=9020522 loops=1)
"                          Hash Key: cession_deal_rk, credit_deal_rk"
                          ->  Seq Scan on x  (cost=0.00..891.35 rows=5166721 width=96) (actual time=23.282..11751.681 rows=5173811 loops=1)
Planning time: 55.041 ms
  (slice0)    Executor memory: 2044K bytes.
"  (slice1)    Executor memory: 1839K bytes avg x 1056 workers, 1839K bytes max (seg0)."
"* (slice2)    Executor memory: 77215K bytes avg x 1056 workers, 100128K bytes max (seg9).  Work_mem: 48410K bytes max, 1620584K bytes wanted."
Memory used:  540672kB
Memory wanted:  4862049kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1392286.244 ms

Соотв-но, randomly в итоге д.б. не самый эффективный согласно плану.

А теперь сухие цифры рез-тов тестов, 3 прогона CTAS исходного запроса для каждого ключа:
ab:
35 s 877 ms
30 s 125 ms
31 s 277 ms
среднее: 32.426 s

randomly:
35 s 682 ms
34 s 57 ms
27 s 515 ms
среднее: 32.418 s

b:
39 s 936 ms
34 s 546 ms
33 s 234 ms
среднее: 35.905 s

Т.е. ключ ab и randomly показали почти одинаковый рез-т
Ну и чтобы вас совсем добить, скажу , что на DR с большим отрывом победил исходный ключ b, дав по скорости 2.5 мин против 4 мин для ab и 5 5 мин у randomly в среднем на 2х прогонах.
Почему такая аномалия, теряюсь в догадках, но число сегментов на DR втрое меньше.

Итоговый вывод по тесту я бы озвучил так: хорош тот ключ, который входлит в набор полей оконной ф-ии и дает несильный перекос, чтобы сегменты
смогли выполнить локальную сортировку своих данных
Что интересно, для ключа b и ab план одинаков, т.е. для b GPORCA не счел нужным сделать Redistribute как для RANDOMLY.

В заключение, у меня только один вопрос, почему материализация данных запросов через CTAS работает менее 1 мин, а explain analyze исходного селекта висит более 20 мин.

Вопрос 1
Друзья, призываю в помощь!
Идентичный код прикладной PL/pgSQL ф-ии в 500 строк ( набор SQL запросов ) на одних и тех же данных и одном и том же железе в одной РГ дает сильно разный спилл при повторных запусках , то 20 TB(вчера), то <1(сегодня).
Число строк на выходе ф-ии отличается на 0.01%
Спилл появляется эпизодами, за последниий месяц всплеск спилла был в 4 из 27 запусков ф-ии.
Есть идеи, чем вызван такой разброс ?

Question 1
Friends, I need help!
An identical PL/pgSQL function code of 500 lines (a set of SQL queries) on the same data and the same hardware
in one Resource Group gives a very different spill upon repeated launches, sometimes 20 TB, sometimes less than 1TB.
The number of lines in the output of the function differs by 0.01%
Spill appears sporadically, in the last month it happened 4 times of 27 launches
Any ideas what caused this spread?

Друзья, 5 минут до начала!!!

Делюсь конфой, если кому будет интересно) за качество одного доклада точно ручаюсь, будет выступать мой близкий друг)

https://rshb.sk.jugru.org/

Ставьте + кто смотрит онлайн и узнал о митапе из этого канала

Люди ждут всегда 3 вещи - конца рабочего дня, Пятницу и Весну! Сегодня флэш-рояль! Всех с праздником!

Секрет 31 (Разбивай на счастье!)
Друзья, запасаемся попкорном. Будет лонгрид, т.к. было не так много времени, чтобы сократить матерьял, которым спешу поделиться.

Давно хотел познакомиться тет-а-тет с партишками в GP,
особенно на фоне слухов про них, зачастую нелицеприятных.

Тем более интерес подогревал 15 летний опыт с Oracle, где я безапеляционно убедился, что стратегия партицирования играет первостепенную роль
в производительности и обслуживании продукта.

Тут как раз рук-во спустило R&D задачку прощупать плюсы и минусы партицированных таблиц.

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

Т.к. в доке предел на число партиций на каждом уровне = 32 767, я решил пойти с самых простых вариантов и попытаться к нему приблизиться.

Я взял репрезентативную выборку в 7 млн строк из табл-ы документов на 74 млрд строк, по 1 млн для каждой ИС и
стал сохранять эти данные в партицированную таблицу той же структуры из 92 полей.

План теста был прост как валенок.
Я взял одноуровневое партицирование по дате.
В каждом тесте я создавал с нуля партицировнную табл-у вида, куда писал подготовленные 7 млн строк, потом читал небольшую выборку из полученной таблицы
с фильтром document_date between :1 and :2.
Единственное, в каждой строке document_date заполнялся случайно распределенной равномерной величиной DATE в диапазоне от 2000 до конца 25 г.
Начав с табл-ы в 50 партиций( далее N ), я накидывал каждый раз еще по 50 (Кстати, фильм "Еще по одной" - экстаз, смотрел несколько раз)
Т.е. в каждой итерации менялся только параметр EVERY (длина интервала нарезки парт-ий) от '190 days' до '1 days' для зверушки:

CREATE TABLE foo ( <columns> )
WITH (
   appendonly=true,
   orientation=column,
   compresstype=zstd,
   compresslevel=1
)
DISTRIBUTED BY (document_rk)
PARTITION BY RANGE(document_date)
          (
          START ('2000-01-01'::date) END ('2025-12-31'::date) INCLUSIVE EVERY ('190 days'::interval),
          DEFAULT PARTITION foo_prt_other
          );

Все скрипты я загнал в отдельную конфиг таблу( по 3 оператора для каждого теста: CREATE TABLE, INSERT, SELECT ), по которой прошелся вездесущим explain analyze,
который их и выполнит, и план в JSON сохранит ( по технологии, описанной в https://t.iss.one/mpp_secrets/39 )

Зарядив скрипт на ночь, я обнаружил утром, что тест абортнулся на insert-е в табл-у с размером партиции 9 дн. или с N=1055:
can't have more than 10000 different append-only tables open for writing data at the same time

Заглянув в параметры БД, я обнаружил параметр max_appendonly_tables, который задает то самое ограничение на число append-only таблиц,
в которое можно писать одновременно.
Я сначала растерялся, ведь я писал в рамках INSERT-а 1055 таблиц (партиция - это таблица), что никак не объясняло крэш!
Потом меня осенило, что PG не ORACLE и т.к. DDL не фиксирует транзакцию комитом, а все мои тесты выполнялись в цикле в одной транзакции, мог иметь
место накопительный эффект от предыдущих тестов.
Как бы там ни было, я решил сделать ход конем и проверить в отдельной сессии табл-у сразу с N = 9497 или с размером партиции 1 день.
Результат insert-а: опять шляпа, но уже с ошибкой high VMEM usage, что я могу объяснить тем, что у нас табл-а AOCO ( колоночная ) и файлов для такой табл-ы пишется одновременно
92(колонок) * 9497(партиций), что,видимо, БД не переварила.

Ну тогда пришлось ослабить структуру, и вместо AOCO взять AORO:
WITH (
appendonly=true,
orientation=row,
compresstype=zstd,
compresslevel=1
)
В такую табл-у с пограничным N = 9497 insert уже отработал корректно.
Это все хорошо скажете вы, а стоит ли игра свеч ?
Я по тому же принципу загнал в foo 1 млрд строк и тот же сет положил в плоскую foo_no_part ( т.е. без партов ) табл-у.
Замерил select ~1% строк в 3х тестах( через explain ), 1й тест дал небольшой выигрыш, но среднее оказалось меньше в итоге для плоской ( на фото тесты D2:F6 ).
Как же так ? Все зря ? Я подумал, что т.к. в БД 348 сегментов, то в среднем на сегменте по 3 млн строк для плоской таблы, которые фуллсканятся быстрее,
чем нужные партиции в foo. Чтобы сгладить накладные расходы на извлечение данных из партиций, я поднял ставки и загнал в обе по 10 млрд строк.
Снова выполнил по 3 теста на select ~1% строк ( 95 млн если точнее ) и справедливость восторжествовала:
На explain foo вырвалась вперед во всех 3х тестах, причем в одном из них двукратно ( тесты A1:C6 )
И чтобы исключить влияние пересылки строк на мастер, я провел еще 3 теста CTAS, сохранив рез-т select-ов предыдущих тестов из foo и foo_no_part в отдельные табл-ы c
randomly дистрибуцией(A9:C13). Рез-т на партициях дает 6-ти кратное ускорение.

Самые педанты спросят и будут правы - а что со временем вставки-то 10 млр строк в foo и foo_no_part.
Ответ: 42 мин 54 с и 15 мин 49 с соот-но, что ожидаемо, т.к. на вставку данных в табл-у самой простой структуры - это быстрее.
Правда стоит отметить, что в foo_no_part просто был CTAS из foo, а в foo вставлялся 1 млрд, растиражированный через generate_series(1,10)
и для каждой строчки ключ партиции document_date считался через '2000-01-01'::date + trunc(random()*9496))::int.

Вывод: Партиции могут давать заметный выигрыш бизнесу (Время-Деньги!) в зависимости от соотношения числа строк в табл-е и числа сегментов кластера.
Так, уже при ~30 млн строк табл-ы на каждом сегменте он составляет 6x!

Рез-ты тестов

Дорогие Леди, Мисс и Миссис!
Под звон весенней капели буду краток как тот парень.
Природа дала вам уникальную комбинацию повышенной отказоустойчивости через парную хромосому,
сопряженной с случайными перекосами сознания в сторону вашей безупречности.
За это вас и любим, скучать не даете!
С праздником, дорогие!❤️

Невероятно, но факт.
Если в той самой партицированной foo создать 110 млрд строк, сделать ее AOCO zstd1 (колоночной) с длиной секции 8 дн, или 1188 парт-ий,
то
select count(*) from foo виснет наглухо без возможности выполнения
select count(*) from foo where document_date between ('2000-01-01'::date) and ('2025-12-31'::date) отрабатывает за 4 мин.
Просто добавили фильтр, который покрывает все данные ( в дефолтной партиции 0 строк ).
Unbelievable, but true.
If in that same partitioned foo we create 110 billion rows, make it AOCO zstd1 (column) with a section length of 8 days, or 1188 partitions,
then
select count(*) from foo hangs completely without the possibility of execution
select count(*) from foo where document_date between ('2000-01-01'::date) and ('2025-12-31'::date) works in 4 minutes.
We simply added a filter that covers all data (there are 0 rows in the default partition).

Весенний бигдатник от sql-ninja.ru
22 марта в 10:20 на ст.м. Таганская,
Марксистская в БЦ Таганка Атриум

Как и просили:
Персональные данные, Архитектура, Cloudberry, Postgresql, Clickhouse, Spark.
Конечно кейтеринг, кофе, нетворкин и да, онлайн будет 😉

купить билет

erid: CQH36pWzJqDgK9RNVKVdELsKuxSzntDejEYYuuqwJG1fTU
Реклама, ИП "Ким Лестат Альбертович", ИНН 183403475084

Translation of last published secret:
Secret 31 (Break for happiness!)
Friends, stock up on popcorn. There will be a longread, because there was not much time to shorten the material that I am rushing to share.

I have long wanted to get to know the GP partitions face to face,
especially against the background of rumors about them, often unflattering.

Moreover, my interest was fueled by 15 years of experience with Oracle, where I was absolutely convinced that the partitioning strategy plays a primary role
in the performance and maintenance of the product.
Here, the management sent R&D the task of testing the pros and cons of partitioned tables.

I set the task, and for how long does it make sense to fragment the data, in other words, find the maximum number of partitions,
when the table can still be worked with.

Because in the doc the limit on the number of partitions at each level = 32,767, I decided to go with the simplest options and try to get closer to it.

I took a representative sample of 7 million rows from a document table with 74 billion rows, 1 million for each data source, and
began to save this data in a partitioned table of the same structure with 92 fields.

The test plan was as dumb as a felt boot.
I took a single-level partitioning by date.
In each test, I created a partitioned table of the type from scratch, where I wrote the prepared 7 million rows, then read a small sample from the resulting table
with the filter document_date between :1 and :2.
The only thing is that in each record document_date was filled with a randomly distributed uniform value DATE in the range from 2000 to the end of 2025 year.
Having started with a table of 50 partitions (hereinafter N), I added another 50 each time (By the way, the film "Another Round" is ecstasy, I watched it several times)
I.e. in each iteration, only the EVERY parameter (the length of the partition slicing interval) changed from '190 days' to '1 days' for the animal:

CREATE TABLE foo ( <columns> )
WITH (
   appendonly=true,
   orientation=column,
   compresstype=zstd,
   compresslevel=1
)
DISTRIBUTED BY (document_rk)
PARTITION BY RANGE(document_date)
          (
          START ('2000-01-01'::date) END ('2025-12-31'::date) INCLUSIVE EVERY ('190 days'::interval),
          DEFAULT PARTITION foo_prt_other
          );

I put all the scripts into a separate config table (3 operators for each test: CREATE TABLE, INSERT, SELECT), which I ran through the ubiquitous explain analyze,
which will execute them and save the plan in JSON (using the technology described in https://t.iss.one/mpp_secrets/39)

After loading the script overnight, I discovered in the morning that the test was aborted on insert into a table with a partition size of 9 days. or with N=1055:
can't have more than 10000 different append-only tables open for writing data at the same time.
Looking into the DB parameters, I found the max_appendonly_tables parameter, which sets the very limit on the number of append-only tables
that can be written to simultaneously( i.e. 10 000 ).
At first I was confused, because I was writing 1055 tables within the INSERT (a partition is a table), which did not explain the crash!
Then it dawned on me that PG is not ORACLE and since DDL does not commit a transaction, and all my tests were performed in a loop in one transaction, there could have been a cumulative effect from previous tests.
In any case, I decided to make a knight's move and check the table with N = 9497 or with a partition size of 1 day in a separate session.
Insert operator result: again crap, but with a high VMEM usage error, which I can explain by the fact that we have an AOCO table (column-based) and files for such a table are written simultaneously 92 (columns) * 9497 (partitions), which, apparently, the DB did not digest.
to be continued