Да, неплохой вариант, я забыл его упомянуть. Единственный его недостаток, что приходит в голову, то что эта функциональность появилась относительно недавно, а с проблемами сталкивался и в более старых версиях, где этой функциональности нет. Там помогает создание индекса по вычисляемому значению типа: create index on test((j->>1));
А не надо от него отказываться. :) Он может быть полезен. И поскольку веб программисты очень часто используют json для своих собственных нужд удобно хранить данные тоже в json, чтобы не заморачиваться преобразованиями туда-сюда. Но если по каким-то полям json происходят частые запросы, тогда их имеет смысл оптимизировать. Например создавая специальные индексы для поиска по этим полям или, например, выносить их в таблицу в виде вычисляемого поля или еще как. Небольшие json работают не так уж плохо, никто не жаловался. :) Проблемные json обычно очень длинные.
Ну смотри, тут надо разбираться, почему так произошло. Первое что мне пришло бы в голову проверить, это при каких user_id такое происходит. Например, я сталкивался с ситуацией, когда планировщик отказывался от использования индекса, если значение находилось среди most_common_vals, но использовал индекс для поиска для всех остальных значений.
Отправная точка, если вся БД не влазит в ОЗУ, то чем больше ОЗУ будет приходится на кэш shared_buffers, тем лучше. Разумеется в разумных пределах, надо оставить системе на нужды системы. Потому что со своим кэшем БД работает лучше (в том числе применяет оптимизации), чем с кэшем файловой системы.
Что касается 25% то тут надо копнуть, в каком году эта рекомендация появилась. :) В те времена ОЗУ настолько не хватало, что сервера из свопа не вылазили. С тех пор многое изменилось.
Любые рекомендации о размере shared_buffers в качестве пропорций к ОЗУ не работают. :) Это как пальцем в небо. Оптимальный размер shared_buffers зависит от размера БД, размера активных данных в БД, размера индексов в БД и т.д. Но не от размера ОЗУ на сервере. :) ОЗУ на сервере разве что может либо хватать для оптимального размера shared_buffers, либо не хватать. Т.е. правильная последовательность проанализировав БД вычислить нужный размер shared_buffers, а потом докупить нужное количество ОЗУ на сервер, чтобы хватало с запасом и еще оставалось на системные нужды.
не только кеш, но и грязные страницы
Кэш на запись это и есть "грязные страинцы", нет?
Если worker-у не хватает страниц для чтения с диска, он начнёт выгружать грязные страницы прямо в процессе выполнения запроса.
Мне сложно представить себе БД в которой была бы настолько катастрофическая ситуация с записью. Там скорее я бы подозревал bad design, корявую оптимизацию или неуместное использования SQL сервера для задач, для которых он не предназначен. Более того, тут вы сами является автором проблемы, которую якобы решаете. Уменьшая shared_buffers вы серьезно увеличиваете шанс, что их будет не хватать настолько, что даже грязные страницы во время транзакции придется вытеснять. Идеальное решение — shared_buffers должно хватать для всех активно используемых данных на чтение, не говоря уже и про запись.
Забирая память у кэша записи ОС мы лишаем себя возможности отложенной записи большого объёма данных. Любой пик записи на диск (кроме checkpoint, разве что) может быть сглажен за счёт грязных страниц менеджером виртуальной памяти. Но как быть, если вы всю память отдали под shared buffers?
Ну смотрите. Как пишет БД. Сначала, до завершения транзакции, все изменения на запись находятся в shared_buffers. На этом этапе урезание shared_buffers в угоду кэшу файловой системы никак не поможет, скорее усугубит, потому что уменьшая их вы создаете проблему, что их придется скидывать прямо во время транзакции. Потом, при commit они уходят в журнал транзакций и синкаются. Тут кэш файловой системы тоже никак не поможет, потому что postgresql будет ожидать конца реальной записи на винчестер. Ну и потом, при checkpoint те данные завершенных транзакций, которые уже в журнале транзакций, но еще не были сброшены в файлы таблиц сбрасываются в файлы таблиц. Тут PostgreSQL тоже будет ожидать подтверждение полной записи. Так что и тут файловый кэш никак запись не ускорит.
if you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system.
Так что ваша цифра ни с чем из документации не согласуется. Не надо ссылаться на документацию, если на самом деле там этого нет. Верить вашим тестам тоже нельзя, если они подробно не расписаны, как тестировались. :) И не перепроверены. Именно поэтому я подробно расписал свои тесты и приложил все скрипты, чтобы их можно было перепроверить любому желающему.
Скажем так, можно сформулировать задачу для тестирования: нужно установить пропорции shared_buffers. и кэш файловой системы таким образом, чтобы оптимизировать запись большого объема данных. Я в этом смысла не вижу, потому что не вижу теоретических предпосылок для того, чтобы уменьшение shared_buffers хоть как-то сказалось на ускорение записи. Если только вы не отрубили ожидание полной записи на диск, но на реальных БД ни один здравомыслящий человек так не сделает. Но я допускаю, что я могу ошибаться. Так что напишите аналогичную моей статье статью на эту тему, подробно распишите скрипты, как вы тестировали, чтобы каждый, например я, мог бы перепроверить ваш результат.
shared_buffers это и есть кэш самого PostgreSQL. И? Каким образом уменьшив shared_buffers вы хоть как-то выиграете в производительности на запись? :)
Если речь идет об нехватке производительности на запись, то тут можно советовать либо об быстрых винчестерах типа SSD, либо, использовать рейд контроллер, у которого есть собственный кэш, он включен на запись и защищен батарейкой. Последний вариант череват тем, что если рейд всё же сам по себе накроется, причем постепенно, он еще и покрешить базу данных сможет вместе с журналом транзакций так, что потом уже не восстановить.
Я согласен с тем, что горячие данные это какая-то доля. Согласен с тем что вы правильно сделали, что проводили эксперименты на сервере с реальными данными и нашли оптимальный для вас размер кэша. Вот с чем я категорический несогласен, что этот оптимальный размер кэша вы каким-то образом сумели увязать с 50% от размера ОЗУ и вывести из этого какое-то универсальное правило. Оптимальный размер кэша зависит только от размера БД и структуры (распределения данных, доли горячих данных) в этой БД, но никак не от размера ОЗУ на сервере. ОЗУ на сервере может только либо хватать для оптимального размера кэша, либо не хватать.
Тут вообще несуразно делать глубокомысленные рассуждения об эффективности кэша в процентах к… ОЗУ на сервере. :) Эффективность кэша определяется исключительно размером БД ну или размером данных в БД которые находятся в активном использовании. Они, очень желательно, должны влазить в кэш целиком. А еще лучше, конечно, БД целиком, хотя это уже не критично. А будет это 10% от ОЗУ сервера, 25%, 50% или 75% совершенно не важно, тут даже рассуждать на эту тему глупо. Хотя, конечно, чем больше памяти на сервере, тем лучше. И нет смысла делать размер кэша для БД больше, чем размер самой БД. Может и есть, но сходу я такой смысл придумать не могу.
А про то что кэш на мастере вреден об этом можете написать по подробнее. :)
Не помню что было на том, да и не важно. Чтобы производительность винчестера не сказывалась на картинку специально прогревал БД, чтобы данные были по максимуму в ОЗУ. Производительность работы с ОЗУ и измерялась. Ну а винчестер это что-то заведомо более медленное, чтобы продемонстрировать что работа с БД сильно замедляется если в кэше затирается индекс.
Здесь только 8 цветов + бит яркости, получается 16 цветов. Для старых терминалов. Я использовал возможность выбирать из 256 цветов, что не все терминалы, быть может, поддерживают.
не придираюсь, но это все-таки важный момент. Таблица помещается в память или же нет? Можно было бы заодно проверить и вариант, когда таблица и индекс полностью помещаются в память.
Смотрите, основная идея была продемонстрировать, что кэш PostgreSQL работает лучше, чем кэш файловой системы. Поэтому я создал немного искусственную ситуацию, которая бы лучше всего служила целям демонстрации некоторых достоинств кэша PostgreSQL по сравнению с кэшем файловой системы. Поэтому для наглядности демонстрации я создал такие условия, что таблица примерно полностью занимает ОЗУ, плюс индекс, таким образом при последовательном чтении индекс должен был бы затираться в кэше в случае кэша файловой системы.
То находится ли таблица целиком в кэше PostgreSQL можно посмотреть с помощью расширения pg_buffercache. Два года назад это расширение работало нестабильно, если делать запросы к нему под нагрузкой на PostgreSQL иногда крэшило сервер. Но может сейчас это исправили, в этом году не замечал.
Случай когда и таблица и индекс полностью находится в ОЗУ, точнее полностью в одном из кэшей, не замерял. Можете это сделать самостоятельно, если вам это интересно. Все скрипты я приложил.
Меня интересует момент про SeqScan (поиск по таблице без индекса). Запрос select count(*)… вынуждает постгрес выгружать всю таблицу в память.
Нет, постргрес так не делает. Хотя иногда все же делает. :) Подробнее ниже.
Вы учитывали использование постгресом кольцевых буферов для таких запросов? К сожалению, я в постгресе не эксперт, знаю об этом крайне мало.
О внутренностях системного кеша знаю еще меньше, так что не буду даже загадывать, есть ли подобные вещи там.
Собственно основной смысл этой демонстрации был в том, чтобы показать, что PostgreSQL использует кольцевые буферы для sequence scan, а кэш файловой системы нет и поэтому он может быть предпочтительнее. Не только поэтому, там есть и другие оптимизации, эта одна из них. Подробнее об этом вы можете поглядеть в презентации, ссылка была в статье. Но по собственному опыту я хочу уточнить, что кольцевые буферы PostgreSQL использует не всегда, а только для больших, по сравнению с собственным кэшем, таблиц. Если он видит, что таблица занимает мало места по сравнению с размером с собственным кэшем, то тогда он кольцевыми буферами не заморачивается. Я могу с этим ошибаться, но у меня сложилось такое впечатление.
Уверен, что ответ будет положительным, но обязан спросить. Вы ведь убедились, что THB отключены?
TLB? Нет, не убеждался, это был linux CentOS по дефолту, как он есть. :)
На только что перезагруженной машине, где запущена только база (которая аллоцирует память кусками одного размера) — разницы с большими страницами почти не будет, так как не будет фрагментации. В ситуации, когда таблица почти целиком лежит в кешах, и во время запросов псевдо-большие страницы не аллоцируются, обслуживание TLB может не повлиять на время тестовых запросов.
Я думаю что PostgreSQL аллоцирует весь собственный кэш целиком при загрузке. По крайней мере в случае использования HugePages это очевидно. И если и есть выигрыш от использования HugePages, то связан он не с аллоцированием.
По поводу невозможности выделения больших страниц на давно работающей машине. Под большие страницы выделяется только свободная память. Память, уже занятая файловым кешем, не освобождается.
По поводу этого утверждения я прокомментировать не могу, но думаю фрагментирует память не только файловый кэш, но и любые другие процессы, которые тоже запрашивают память. Я же не в single user mode линкс запускал, там были еще какие-то системные демоны и т.д. Поэтому в крайнем случае, когда по моим прикидкам кэш PostgreSQL должен занимать почти всё (по максимуму, но в разумны пределах) ОЗУ, без перезагрузки это не всегда получалось. А с перезагрузкой работало как часы в течении почти месяца. :)
Графики показывают, что использование больших страниц не дает ускорения (а где дает, там ускорение незначительно). Что наводит меня на мысль
Графики показывают, что иногда использование больших страниц дает ускорение, а иногда (в случаях с sequence scan) даже замедляет. :) Большое это ускорение или нет (примерно в полтора раза) дело вкуса. Но важно то, что этот эффект значительно превышает точность с которой собрана статистика и поэтому он достоверен. А вот какие механизмы при этом работают, какая роль TLB и откуда получается такой эффект, тут я теоретизировать не могу.
Устройств, которые подключаются к thunderbolt мало. Но если подключать внешний винчестер или рейд массив, то уж лучше thunderbolt, чем usb. Других устройств для thunderbolt не видел. Но кому нужен внешний сторадж тот заметит.
Поставил лайк, за то что использовали "постгрес", а не "постгря". :) Фраза "обновляйте ваши постгрясы" даже в голове не укладывается. :)
Ага, выглядит похоже.
Да, неплохой вариант, я забыл его упомянуть. Единственный его недостаток, что приходит в голову, то что эта функциональность появилась относительно недавно, а с проблемами сталкивался и в более старых версиях, где этой функциональности нет. Там помогает создание индекса по вычисляемому значению типа:
create index on test((j->>1));
А не надо от него отказываться. :) Он может быть полезен. И поскольку веб программисты очень часто используют json для своих собственных нужд удобно хранить данные тоже в json, чтобы не заморачиваться преобразованиями туда-сюда. Но если по каким-то полям json происходят частые запросы, тогда их имеет смысл оптимизировать. Например создавая специальные индексы для поиска по этим полям или, например, выносить их в таблицу в виде вычисляемого поля или еще как. Небольшие json работают не так уж плохо, никто не жаловался. :) Проблемные json обычно очень длинные.
Ну смотри, тут надо разбираться, почему так произошло. Первое что мне пришло бы в голову проверить, это при каких user_id такое происходит. Например, я сталкивался с ситуацией, когда планировщик отказывался от использования индекса, если значение находилось среди most_common_vals, но использовал индекс для поиска для всех остальных значений.
Отправная точка, если вся БД не влазит в ОЗУ, то чем больше ОЗУ будет приходится на кэш shared_buffers, тем лучше. Разумеется в разумных пределах, надо оставить системе на нужды системы. Потому что со своим кэшем БД работает лучше (в том числе применяет оптимизации), чем с кэшем файловой системы.
Что касается 25% то тут надо копнуть, в каком году эта рекомендация появилась. :) В те времена ОЗУ настолько не хватало, что сервера из свопа не вылазили. С тех пор многое изменилось.
Любые рекомендации о размере shared_buffers в качестве пропорций к ОЗУ не работают. :) Это как пальцем в небо. Оптимальный размер shared_buffers зависит от размера БД, размера активных данных в БД, размера индексов в БД и т.д. Но не от размера ОЗУ на сервере. :) ОЗУ на сервере разве что может либо хватать для оптимального размера shared_buffers, либо не хватать. Т.е. правильная последовательность проанализировав БД вычислить нужный размер shared_buffers, а потом докупить нужное количество ОЗУ на сервер, чтобы хватало с запасом и еще оставалось на системные нужды.
Мне сложно представить себе БД в которой была бы настолько катастрофическая ситуация с записью. Там скорее я бы подозревал bad design, корявую оптимизацию или неуместное использования SQL сервера для задач, для которых он не предназначен. Более того, тут вы сами является автором проблемы, которую якобы решаете. Уменьшая shared_buffers вы серьезно увеличиваете шанс, что их будет не хватать настолько, что даже грязные страницы во время транзакции придется вытеснять. Идеальное решение — shared_buffers должно хватать для всех активно используемых данных на чтение, не говоря уже и про запись.
Ну смотрите. Как пишет БД. Сначала, до завершения транзакции, все изменения на запись находятся в shared_buffers. На этом этапе урезание shared_buffers в угоду кэшу файловой системы никак не поможет, скорее усугубит, потому что уменьшая их вы создаете проблему, что их придется скидывать прямо во время транзакции. Потом, при commit они уходят в журнал транзакций и синкаются. Тут кэш файловой системы тоже никак не поможет, потому что postgresql будет ожидать конца реальной записи на винчестер. Ну и потом, при checkpoint те данные завершенных транзакций, которые уже в журнале транзакций, но еще не были сброшены в файлы таблиц сбрасываются в файлы таблиц. Тут PostgreSQL тоже будет ожидать подтверждение полной записи. Так что и тут файловый кэш никак запись не ускорит.
40% в документации не видел, цитата
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY
Так что ваша цифра ни с чем из документации не согласуется. Не надо ссылаться на документацию, если на самом деле там этого нет. Верить вашим тестам тоже нельзя, если они подробно не расписаны, как тестировались. :) И не перепроверены. Именно поэтому я подробно расписал свои тесты и приложил все скрипты, чтобы их можно было перепроверить любому желающему.
Скажем так, можно сформулировать задачу для тестирования: нужно установить пропорции shared_buffers. и кэш файловой системы таким образом, чтобы оптимизировать запись большого объема данных. Я в этом смысла не вижу, потому что не вижу теоретических предпосылок для того, чтобы уменьшение shared_buffers хоть как-то сказалось на ускорение записи. Если только вы не отрубили ожидание полной записи на диск, но на реальных БД ни один здравомыслящий человек так не сделает. Но я допускаю, что я могу ошибаться. Так что напишите аналогичную моей статье статью на эту тему, подробно распишите скрипты, как вы тестировали, чтобы каждый, например я, мог бы перепроверить ваш результат.
Ага, спасибо
shared_buffers это и есть кэш самого PostgreSQL. И? Каким образом уменьшив shared_buffers вы хоть как-то выиграете в производительности на запись? :)
Если речь идет об нехватке производительности на запись, то тут можно советовать либо об быстрых винчестерах типа SSD, либо, использовать рейд контроллер, у которого есть собственный кэш, он включен на запись и защищен батарейкой. Последний вариант череват тем, что если рейд всё же сам по себе накроется, причем постепенно, он еще и покрешить базу данных сможет вместе с журналом транзакций так, что потом уже не восстановить.
Я согласен с тем, что горячие данные это какая-то доля. Согласен с тем что вы правильно сделали, что проводили эксперименты на сервере с реальными данными и нашли оптимальный для вас размер кэша. Вот с чем я категорический несогласен, что этот оптимальный размер кэша вы каким-то образом сумели увязать с 50% от размера ОЗУ и вывести из этого какое-то универсальное правило. Оптимальный размер кэша зависит только от размера БД и структуры (распределения данных, доли горячих данных) в этой БД, но никак не от размера ОЗУ на сервере. ОЗУ на сервере может только либо хватать для оптимального размера кэша, либо не хватать.
Тут вообще несуразно делать глубокомысленные рассуждения об эффективности кэша в процентах к… ОЗУ на сервере. :) Эффективность кэша определяется исключительно размером БД ну или размером данных в БД которые находятся в активном использовании. Они, очень желательно, должны влазить в кэш целиком. А еще лучше, конечно, БД целиком, хотя это уже не критично. А будет это 10% от ОЗУ сервера, 25%, 50% или 75% совершенно не важно, тут даже рассуждать на эту тему глупо. Хотя, конечно, чем больше памяти на сервере, тем лучше. И нет смысла делать размер кэша для БД больше, чем размер самой БД. Может и есть, но сходу я такой смысл придумать не могу.
А про то что кэш на мастере вреден об этом можете написать по подробнее. :)
Не помню что было на том, да и не важно. Чтобы производительность винчестера не сказывалась на картинку специально прогревал БД, чтобы данные были по максимуму в ОЗУ. Производительность работы с ОЗУ и измерялась. Ну а винчестер это что-то заведомо более медленное, чтобы продемонстрировать что работа с БД сильно замедляется если в кэше затирается индекс.
Нет, не проверял, дефолтный линукс. Понапридумывали всякого, я про такое даже не слышал.
Здесь только 8 цветов + бит яркости, получается 16 цветов. Для старых терминалов. Я использовал возможность выбирать из 256 цветов, что не все терминалы, быть может, поддерживают.
Смотрите, основная идея была продемонстрировать, что кэш PostgreSQL работает лучше, чем кэш файловой системы. Поэтому я создал немного искусственную ситуацию, которая бы лучше всего служила целям демонстрации некоторых достоинств кэша PostgreSQL по сравнению с кэшем файловой системы. Поэтому для наглядности демонстрации я создал такие условия, что таблица примерно полностью занимает ОЗУ, плюс индекс, таким образом при последовательном чтении индекс должен был бы затираться в кэше в случае кэша файловой системы.
То находится ли таблица целиком в кэше PostgreSQL можно посмотреть с помощью расширения
pg_buffercache. Два года назад это расширение работало нестабильно, если делать запросы к нему под нагрузкой на PostgreSQL иногда крэшило сервер. Но может сейчас это исправили, в этом году не замечал.Случай когда и таблица и индекс полностью находится в ОЗУ, точнее полностью в одном из кэшей, не замерял. Можете это сделать самостоятельно, если вам это интересно. Все скрипты я приложил.
Нет, постргрес так не делает. Хотя иногда все же делает. :) Подробнее ниже.
Собственно основной смысл этой демонстрации был в том, чтобы показать, что PostgreSQL использует кольцевые буферы для sequence scan, а кэш файловой системы нет и поэтому он может быть предпочтительнее. Не только поэтому, там есть и другие оптимизации, эта одна из них. Подробнее об этом вы можете поглядеть в презентации, ссылка была в статье. Но по собственному опыту я хочу уточнить, что кольцевые буферы PostgreSQL использует не всегда, а только для больших, по сравнению с собственным кэшем, таблиц. Если он видит, что таблица занимает мало места по сравнению с размером с собственным кэшем, то тогда он кольцевыми буферами не заморачивается. Я могу с этим ошибаться, но у меня сложилось такое впечатление.
TLB? Нет, не убеждался, это был linux CentOS по дефолту, как он есть. :)
Я думаю что PostgreSQL аллоцирует весь собственный кэш целиком при загрузке. По крайней мере в случае использования HugePages это очевидно. И если и есть выигрыш от использования HugePages, то связан он не с аллоцированием.
По поводу этого утверждения я прокомментировать не могу, но думаю фрагментирует память не только файловый кэш, но и любые другие процессы, которые тоже запрашивают память. Я же не в single user mode линкс запускал, там были еще какие-то системные демоны и т.д. Поэтому в крайнем случае, когда по моим прикидкам кэш PostgreSQL должен занимать почти всё (по максимуму, но в разумны пределах) ОЗУ, без перезагрузки это не всегда получалось. А с перезагрузкой работало как часы в течении почти месяца. :)
Графики показывают, что иногда использование больших страниц дает ускорение, а иногда (в случаях с sequence scan) даже замедляет. :) Большое это ускорение или нет (примерно в полтора раза) дело вкуса. Но важно то, что этот эффект значительно превышает точность с которой собрана статистика и поэтому он достоверен. А вот какие механизмы при этом работают, какая роль TLB и откуда получается такой эффект, тут я теоретизировать не могу.
Устройств, которые подключаются к thunderbolt мало. Но если подключать внешний винчестер или рейд массив, то уж лучше thunderbolt, чем usb. Других устройств для thunderbolt не видел. Но кому нужен внешний сторадж тот заметит.
Thunderbolt это в первую очередь ePCI. У меня так рейд массив подключён. Про нюансы с egpu, не слышал.
Эх, автор. Судя по маркировке в виде молнии там не USB-C, а Thunderbolt. Что гораздо большее.
Разг. говорит что это слово вовсе не устарело, как вам кажется. :) Это активно используемый термин.
:D Это общепринятый термин.
https://ru.wiktionary.org/wiki/извращенец