Pull to refresh

Comments 29

В статье приведён пример нестандартного типа (который, скорее всего, мало кто поддерживает), но при этом не упомянуто, что алгоритмов генерации UUID несколько и среди них есть в т.ч. те, которые основаны на времени. Почему бы не использовать эти версии генерации UUID-а?

Вы правы, я намерено выбрал самый неудачный для моего случая UUID v4. Согласен, что лучше использовать стандартные решения, или прокачивать их.
Но задача была не перепробовать все UUID, а показать насколько может тормозить индекс и почему. Дать повод разработчикам задуматься о том что недостаточно просто построить индекс, надо понимать как он будет работать и как его обслуживать в дальнейшем. Поэтому для примера добавил известные решения, что бы дать волю экспериментам :)

Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?


Как мне кажется, наиболее частый вариант похож на случай, когда 100500 пользователей в рандомном порядке добавляют в таблицу свои данные. А потом требуется каждому пользователю показать список только его данных. Естественно его записи будут размазаны по всей таблице в зависимости от того когда он их создал, и сколько других пользователей создавали записи параллельно с ним. В таком контексте рекомендация из статьи выглядит как "хорошо делай — хорошо будет".

В других СУБД есть возможность Clustered indexes (MSSQL, MySQL, Oracle) со своими особенностями. В PostgreSQL такой важной возможности нет, есть CLUSTER и сторонний pg_repack, с чем живем то и обслуживаем.

А что могли бы порекомендовать, если команду cluster, ввиду того, что она лочит таблицу, применить нельзя?

pg_repack умееть делать кластеризацию по индексу

-o COLUMNS [,...], --order-by=COLUMNS [,...]
Perform an online CLUSTER ordered by the specified columns.

А насколько это хорошая идея - периодически запускать pg_repack (например, раз в день во время минимальной нагрузки), учитывая, что между запусками новые данные, получается, все равно будут "в конце"? Кажется, что все равно можно получить профит от этого, т. к. большая часть данных будет распределена по индексу, но я не до конца уверен. Или предполагается другое использование pg_repack?

Эпизодический запуск pg_repack, даже раз в неделю, будет проносить профит: он сделает индексы компактнее и быстрее. Принесет ли профит кластеризация по индексу конкретно вам я не могу сказать, так как все зависит от ваших запросов.
Другого использования pg_repack не предусмотренно

Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?

Посты в соц. сетях. За счёт timestamp'а последние, а значит актуальные, посты находятся с краю B-дерева, что лучше утилизирует страницы. Ну и пагинация по индексу + лимиту. Ну делают так люди https://www.youtube.com/watch?v=9uRj1txUBIg

У вас речь видимо идёт не про UUID, а про что-то вроде ULID из статьи.
Согласен — это вполне реальный кейс для пагинации по "курсору".

Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?

а где вы увидели сортировку? я вижу индекс для поиска по uuid и тест массового добавления записей.


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

если время у них синхронизировано, то ulid, думается, будет неплохо работать

а где вы увидели сортировку?

Видимо вы не внимательно прочитали статью. Речь не о поиске одной строки по её UUID. Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.
Для этого надо, что бы физически все строки, которые выдаёт индекс, находились как можно кучнее, рядом друг с другом. Я может ошибаюсь, но по моему только явная сортировка гарантирует, что мы будем читать строки в каком-то определённом порядке. Остаётся только сделать так, что бы физически строки на диске лежали в таком же прядке. И для этого предлагается добавить к "рандому" монотонно растущий префикс, который гарантирует нам, что индекс будет выдавать строки в том же порядке, в котором мы их создавали.


если время у них синхронизировано, то ulid, думается, будет неплохо работать

Не очень понял о чём вы. Я описывал кейс, в котором использование описанного в статье трюка не поможет ускориться. В этом кейсе в одну таблицу одновременно пишут много пользователей. В результате этого записи любого из них будут размазаны по всей таблице. Т.е. при чтении строк одного юзера придётся с диска вычитать гораздо больше страниц, чем если бы в таблице был только один "пользователь".

Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.

ну не совсем так, речь про эффективный кэш, стабильный buffer pool в котором лежат стабильные части "горячих" данных — последний день, месяц.

PostgreSQL проверяет области видимости и ему приходится таскать страницы из истории в случае с неравномерным индексом, поэтому хотелось показать насколько это важно — индекс по равномерным данным. Если вы историю будете хранить на медленном IO с неравномерным индексом — это вас погубит.

Видимо вы не внимательно прочитали статью. Речь не о поиске одной строки по её UUID. Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.

ИМХО самая интересная часть статьи — «Тестирование различных типов индексов на запись»


update: понял, вы про раздел «Неравномерный индекс на чтении»; да, при добавлении данных от разных пользователей в одну таблицу получим «спагетти», кластерный индекс в ms sql и многих других бд решает эту проблему ценой некоторого оверхеда на запись.


если время у них синхронизировано, то ulid, думается, будет неплохо работать

Не очень понял о чём вы. Я описывал кейс, в котором использование описанного в статье трюка не поможет ускориться. В этом кейсе в одну таблицу одновременно пишут много пользователей. В результате этого записи любого из них будут размазаны по всей таблице.


я рассматривал такой кейс: 100500 пользователей добавляют записи с uuid; по uuid, разумеется, построен индекс.
с точки зрения стоимости добавления новых записей, замена старших бит таймстампом должна положительно сказаться на производительности.


поиск по uuid, подозреваю, тоже ускорится. vadv, не хотите проверить?

А почему скажется положительно? Добавление нового значения в "конец" индекса работает быстрее, чем добавление куда-то в середину?

А почему скажется положительно?

так про это же и была статья.
Мы видим постепенную деградацию производительности неравномерного индекса. Происходит это из-за того, что с каждым новым значением в индексе растёт активный dataset (количество блоков, которые участвуют в операциях INSERT). В итоге нам начинает не хватать buffer pool для того, чтобы держать активный dataset, и у нас возрастает общий IOPS за счет чтения, пока всё не упрётся в физические ограничения.


своими словами: при добавлении «в конец» индекса задействуется небольшое число страниц, которые с большой вероятностью окажутся в кэше; при добавлении «в середину» (а точнее в случайное место) у нас активность «размазана» по всему индексу.

поиск по uuid, подозреваю, тоже ускорится. vadv, не хотите проверить?
наверное зависит от запросов, а подскажите что именно проверить?
  1. вариант a: заполняем таблицу данными с «честным» uuid
    вариант b: заполняем таблицу данными с ulid (ну точнее эмулируем)
  2. делаем 100500 поисков по uuid рандомных записей в том и в другом случае.
vadv, подскажите, пожалуйста:

1. параметры запуска pg_bench (особенно интересны --client=clients и --jobs=threads)
2. в чём, на ваш взгляд, причина активной генерации wal в бенчмарке с uuid v4?

спасибо

спасибо!

привет alexeiivanov !


  1. тестирование проводилось на 8GB Ram, shared_buffers 2GB, max_clients 100, 4 CPU, 7k IOPS:
    number of clients: 20
    number of threads: 2
    duration: 21600 s
  2. генерируется много WAL потому что запросам (бакендам) приходилось вытеснять грязные страницы на диск, так как не хватало место в shared_buffers

большое спасибо за подробный ответ!

по п.2 есть некоторые сомнения, которые хочется развеять.
WAL ведь - транзакционный лог с LSN.
сброс буферов на диск через WAL не проходит и, кажется, не должен сказываться на его приросте.
возможно, под WAL в графике скрывается что-то другое, или причина WAL-логенерации иная?

можно предположить что с checkpoints что-то нездоровое происходит и WAL активно чистится, что учитывается а графике, но до конца физика мне пока не ясна.

сброс буферов на диск через WAL не проходит

хм, вы правы, а тут я не прав, каюсь :)
запустил тест еще раз и после этого натравлю pg_waldump

в случае с UUID v4


pg_waldump pg_wal/00000001000002E4000000B8 | awk '{print $14}' | sort | uniq -c
   2723 COMMIT
      5 DEDUP
   2703 INSERT
     18 INSERT+INIT
   5423 INSERT_LEAF
     18 INSERT_UPPER
      7 SPLIT_L
     11 SPLIT_R

в случае с bigserial:


pg_waldump pg_wal/00000001000002E4000000E9 | awk '{print $14}' | sort | uniq -c
  92288 COMMIT
  91804 INSERT
    489 INSERT+INIT
  92042 INSERT_LEAF
    252 INSERT_UPPER
   2797 LOG
    253 SPLIT_R
      3 ZEROPAGE

alexeiivanov


И в том и в другом случае включен FPW (full page write), но средний размер записи в wal у bigserial меньше.
А происходит это потому что FPW записывается только после первой модификации страницы.
В случае с UUID — переиспользование буферов в буферном пуле маленькое и там мы пачкаем "новые" страницы чаще и чаще вызываем FPW.


Так что я оказался прав, запись WAL связана с вытеснением страниц из буфера, прав но отчасти :)


UUID:
rmgr: Heap        len (rec/tot):     54/  1378, tx: 2255313124, lsn: 2E5/1A036540, prev 2E5/1A036500, desc: INSERT off 25 flags 0x00, blkref #0: rel 1663/14010/24831 blk 703953 FPW
rmgr: Btree       len (rec/tot):     53/  8157, tx: 2255313124, lsn: 2E5/1A036AA8, prev 2E5/1A036540, desc: INSERT_LEAF off 8, blkref #0: rel 1663/14010/24832 blk 287891 FPW

bigserial:
rmgr: Heap        len (rec/tot):     71/    71, tx: 2255587536, lsn: 2E5/33FFFAC0, prev 2E5/33FFFA98, desc: INSERT off 41 flags 0x00, blkref #0: rel 1663/14010/24838 blk 24892
rmgr: Btree       len (rec/tot):     64/    64, tx: 2255587536, lsn: 2E5/33FFFB08, prev 2E5/33FFFAC0, desc: INSERT_LEAF off 83, blkref #0: rel 1663/14010/24843 blk 12623

@vadv,большое спасибо за дополнительное исследование! крайне любопытно и полезно.
про FPW - отлично.
наверное, можно было бы даже в саму статью добавить. познавательно!

А почему не привести ULID к формату UUID?
UUID по размеру во внутреннем представлении будет гораздо компактнее чем текст (16 байт против 26)

Only those users with full accounts are able to leave comments. Log in, please.