Comments 29
Вы правы, я намерено выбрал самый неудачный для моего случая UUID v4. Согласен, что лучше использовать стандартные решения, или прокачивать их.
Но задача была не перепробовать все UUID, а показать насколько может тормозить индекс и почему. Дать повод разработчикам задуматься о том что недостаточно просто построить индекс, надо понимать как он будет работать и как его обслуживать в дальнейшем. Поэтому для примера добавил известные решения, что бы дать волю экспериментам :)
Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?
Как мне кажется, наиболее частый вариант похож на случай, когда 100500 пользователей в рандомном порядке добавляют в таблицу свои данные. А потом требуется каждому пользователю показать список только его данных. Естественно его записи будут размазаны по всей таблице в зависимости от того когда он их создал, и сколько других пользователей создавали записи параллельно с ним. В таком контексте рекомендация из статьи выглядит как "хорошо делай — хорошо будет".
В других СУБД есть возможность Clustered indexes (MSSQL, MySQL, Oracle) со своими особенностями. В PostgreSQL такой важной возможности нет, есть CLUSTER и сторонний pg_repack, с чем живем то и обслуживаем.
А что могли бы порекомендовать, если команду cluster, ввиду того, что она лочит таблицу, применить нельзя?
-o COLUMNS [,...], --order-by=COLUMNS [,...]
Perform an online CLUSTER ordered by the specified columns.
А насколько это хорошая идея - периодически запускать pg_repack (например, раз в день во время минимальной нагрузки), учитывая, что между запусками новые данные, получается, все равно будут "в конце"? Кажется, что все равно можно получить профит от этого, т. к. большая часть данных будет распределена по индексу, но я не до конца уверен. Или предполагается другое использование pg_repack?
Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?
Посты в соц. сетях. За счёт timestamp'а последние, а значит актуальные, посты находятся с краю B-дерева, что лучше утилизирует страницы. Ну и пагинация по индексу + лимиту. Ну делают так люди https://www.youtube.com/watch?v=9uRj1txUBIg
Интересно, что это такая за задача, где надо сортировать строки в таблице по их UUID?
а где вы увидели сортировку? я вижу индекс для поиска по uuid и тест массового добавления записей.
Как мне кажется, наиболее частый вариант похож на случай, когда 100500 пользователей в рандомном порядке добавляют в таблицу свои данные.
если время у них синхронизировано, то ulid, думается, будет неплохо работать
а где вы увидели сортировку?
Видимо вы не внимательно прочитали статью. Речь не о поиске одной строки по её UUID. Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.
Для этого надо, что бы физически все строки, которые выдаёт индекс, находились как можно кучнее, рядом друг с другом. Я может ошибаюсь, но по моему только явная сортировка гарантирует, что мы будем читать строки в каком-то определённом порядке. Остаётся только сделать так, что бы физически строки на диске лежали в таком же прядке. И для этого предлагается добавить к "рандому" монотонно растущий префикс, который гарантирует нам, что индекс будет выдавать строки в том же порядке, в котором мы их создавали.
если время у них синхронизировано, то ulid, думается, будет неплохо работать
Не очень понял о чём вы. Я описывал кейс, в котором использование описанного в статье трюка не поможет ускориться. В этом кейсе в одну таблицу одновременно пишут много пользователей. В результате этого записи любого из них будут размазаны по всей таблице. Т.е. при чтении строк одного юзера придётся с диска вычитать гораздо больше страниц, чем если бы в таблице был только один "пользователь".
Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.
ну не совсем так, речь про эффективный кэш, стабильный buffer pool в котором лежат стабильные части "горячих" данных — последний день, месяц.
Видимо вы не внимательно прочитали статью. Речь не о поиске одной строки по её UUID. Речь о том как сделать так, что бы вычитка большого числа строк из таблицы работала ещё быстрее, при использовании индекса по полю с условно рандомным значением.
ИМХО самая интересная часть статьи — «Тестирование различных типов индексов на запись»
update: понял, вы про раздел «Неравномерный индекс на чтении»; да, при добавлении данных от разных пользователей в одну таблицу получим «спагетти», кластерный индекс в ms sql и многих других бд решает эту проблему ценой некоторого оверхеда на запись.
если время у них синхронизировано, то ulid, думается, будет неплохо работатьНе очень понял о чём вы. Я описывал кейс, в котором использование описанного в статье трюка не поможет ускориться. В этом кейсе в одну таблицу одновременно пишут много пользователей. В результате этого записи любого из них будут размазаны по всей таблице.
я рассматривал такой кейс: 100500 пользователей добавляют записи с uuid; по uuid, разумеется, построен индекс.
с точки зрения стоимости добавления новых записей, замена старших бит таймстампом должна положительно сказаться на производительности.
поиск по uuid, подозреваю, тоже ускорится. vadv, не хотите проверить?
А почему скажется положительно? Добавление нового значения в "конец" индекса работает быстрее, чем добавление куда-то в середину?
А почему скажется положительно?
так про это же и была статья.
Мы видим постепенную деградацию производительности неравномерного индекса. Происходит это из-за того, что с каждым новым значением в индексе растёт активный dataset (количество блоков, которые участвуют в операциях INSERT). В итоге нам начинает не хватать buffer pool для того, чтобы держать активный dataset, и у нас возрастает общий IOPS за счет чтения, пока всё не упрётся в физические ограничения.
своими словами: при добавлении «в конец» индекса задействуется небольшое число страниц, которые с большой вероятностью окажутся в кэше; при добавлении «в середину» (а точнее в случайное место) у нас активность «размазана» по всему индексу.
поиск по uuid, подозреваю, тоже ускорится. vadv, не хотите проверить?
наверное зависит от запросов, а подскажите что именно проверить?
1. параметры запуска pg_bench (особенно интересны --client=clients и --jobs=threads)
2. в чём, на ваш взгляд, причина активной генерации wal в бенчмарке с uuid v4?
спасибо
спасибо!
привет alexeiivanov !
- тестирование проводилось на 8GB Ram, shared_buffers 2GB, max_clients 100, 4 CPU, 7k IOPS:
number of clients: 20 number of threads: 2 duration: 21600 s - генерируется много 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И в том и в другом случае включен 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 - отлично.
наверное, можно было бы даже в саму статью добавить. познавательно!
Почему PostgreSQL тормозит: индексы и корреляция данных