Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 31
Спасибо большое Алексей!
Спасибо большое, опробуем!
Наткнулся на вашу книгу, спасибо за ваш труд).
оптимально настроить базу данных возможно только учитывая все параметры системы и приложения (например учитывать, SSD диски и влезает ли база в память)
Может попробуете расширить форму и учитывать эти параметры?
нужно в первую очередь исходить от размеров базы…
например цифры из реальной жизни. моя база весит 1 терабайт, из них горячих данных около 100 гигабайт. на сервере 768 Гб памяти. ваш калькулятор предлагает shared_buffers = 8GB. с такими настройками это мёртвый сервер. а вот если shared_buffers = 600GB, то всё теперь будет зависеть от процессоров, которых кстати тоже вечно не хватает.
для новичков конечно и так сойдёт, но этот калькулятор на самом деле пустышка и будет вводить новичков заблуждение и развивает лень читать официальную документацию.
например цифры из реальной жизни. моя база весит 1 терабайт, из них горячих данных около 100 гигабайт. на сервере 768 Гб памяти. ваш калькулятор предлагает shared_buffers = 8GB. с такими настройками это мёртвый сервер. а вот если shared_buffers = 600GB, то всё теперь будет зависеть от процессоров, которых кстати тоже вечно не хватает.
для новичков конечно и так сойдёт, но этот калькулятор на самом деле пустышка и будет вводить новичков заблуждение и развивает лень читать официальную документацию.
Добрый день. Установка shared_buffers больше 8 ГБ не даёт никакого хорошего эффекта на Linux (тем более что на 32 битных более 2-2.25ГБ тоже ведут к ухудшению). Так что даже тут калькулятор уже Вам помог. Остальные параметры нельзя просто поставить по формуле, поскольку факторы не однозначны (тип SSD дисков, запросы, влазят ли индексы в кеш, и т.д.), и вот такие параметры точно могут принести вред при настройке PostgreSQL.
Установка shared_buffers больше 8 ГБ не даёт никакого хорошего эффекта на Linux
Интересно, а чем это обусловлено?
Ну для начала нужно понимать, что значит этот параметр.
shared_buffers устанавливает размер памяти, которая используется для контроля за клиентами, обслуживание активных операций, и прочего. Но следует знать, что параметры, которые отвечают за буфер базы данных, выделение памяти на сортировку и обслуживание базы задаются отдельно, поэтому shared_buffers не должен потреблять основной кусок памяти на машине.
Каждый процесс на 32 битной машине лимитирован в 4ГБ адресного пространства, где хотя бы 1ГБ (в Линуксе) зарезервирован ядром. Это означает, что не зависимо, сколько на машине памяти, каждый PostgreSQL инстанс сможет обратится максимум к 3ГБ памяти. И эта память включает в себя не только shared_buffers, но всю остальную память (сортировка, обслуживание, различные внутренние кеши, прочее). А учитывая, что shared_buffers рассчитывается как (RAM / 4), то 2-2.5ГБ — «упирание» в потолок, поскольку вы просто дальше будете забирать память от других не менее важных операций.
На 64 битных машинах нет такого лимита (более 1ТБ), но тут уже вступает факт в то, что разработчики указывают, что сам PostgreSQL может не справляется эффективно с обработкой большого (более 8Гб) shared_buffers. Хотя в некоторых случаях было указано, что shared_buffers более 8Гб дает прирост производительности (но только в том случае, если вся база влазит в shared_buffers). Но это не указание к действию, это нужно проверять только практически.
В любой случае тюнинг дело тонкое :)
shared_buffers устанавливает размер памяти, которая используется для контроля за клиентами, обслуживание активных операций, и прочего. Но следует знать, что параметры, которые отвечают за буфер базы данных, выделение памяти на сортировку и обслуживание базы задаются отдельно, поэтому shared_buffers не должен потреблять основной кусок памяти на машине.
Каждый процесс на 32 битной машине лимитирован в 4ГБ адресного пространства, где хотя бы 1ГБ (в Линуксе) зарезервирован ядром. Это означает, что не зависимо, сколько на машине памяти, каждый PostgreSQL инстанс сможет обратится максимум к 3ГБ памяти. И эта память включает в себя не только shared_buffers, но всю остальную память (сортировка, обслуживание, различные внутренние кеши, прочее). А учитывая, что shared_buffers рассчитывается как (RAM / 4), то 2-2.5ГБ — «упирание» в потолок, поскольку вы просто дальше будете забирать память от других не менее важных операций.
На 64 битных машинах нет такого лимита (более 1ТБ), но тут уже вступает факт в то, что разработчики указывают, что сам PostgreSQL может не справляется эффективно с обработкой большого (более 8Гб) shared_buffers. Хотя в некоторых случаях было указано, что shared_buffers более 8Гб дает прирост производительности (но только в том случае, если вся база влазит в shared_buffers). Но это не указание к действию, это нужно проверять только практически.
В любой случае тюнинг дело тонкое :)
Основное предназначение shared_buffers — кэш блоков таблиц и индексов. Чем бОльшая часть данных там лежит, тем лучше для операций чтения.
Про 32 бита всё понятно, а вот на на 64 битной архитектуру про 8GB заявлять без понимания суть вопроса это как-то странно.
rhaas.blogspot.ru/2012/03/tuning-sharedbuffers-and-walbuffers.html
разработчики указывают, что сам PostgreSQL может не справляется эффективно с обработкой большого (более 8Гб) shared_buffers.
Про 32 бита всё понятно, а вот на на 64 битной архитектуру про 8GB заявлять без понимания суть вопроса это как-то странно.
rhaas.blogspot.ru/2012/03/tuning-sharedbuffers-and-walbuffers.html
For shared_buffers, the quick answer is to allocate about 25% of system memory to shared_buffers, as recommended by the official documentation and by the wiki article on Tuning Your PostgreSQL server, but not more than about 8GB on Linux or 512MB on Windows, and sometimes less. However, I've recently become aware of a number of cases which suggest that higher values may perform much better. PostgreSQL uses the operating system's memory for caching, but, as recent performance results have shown, there can be a huge performance benefit to having the entire database in shared_buffers as opposed to merely having it in RAM. Several EnterpriseDB customers or potential customers have reported excellent results from cranking up shared_buffers to very large values, even giving PostgreSQL the lion's share of system memory for its own use. This flies in the face of some conventional wisdom that PostgreSQL doesn't handle large shared_buffers settings well, but enough people have reported good results that it seems worth taking this strategy seriously.
В данном куске из статьи написано тоже самое, что высказал и я в коментарии — в основном 8ГБ пишут что это максимально, но некоторые заявляют, что если ставить больше — увеличивается производительность.
Где и кто пишет что 8GB максимум? Почему 8 а не 9?
Долго искать не нужно — первая строка в приведенной цитате из блог поста в Вашем коментарии. Сейчас я пока вернул расчет на 1/4 RAM, поскольку не вижу лимитов в исходном коде базы на 8Гб.
Ну вот я погуглил и не нашёл никаких корней этих 8GB кроме заметок в блогах в стиле «всем известно что больше 8GB» плохо". Почему плохо и почему 8GB никто нигде не говорит. Это очень похоже на какой-то старый миф, который уже никто не помнит откуда и зачем взялся. Таких мифов на самом деле полно :)
поддерживаю Randll, это заявление про 8GB было сделано очень давно и сами разработчики постгреса затрудняются дать разумное объяснение.
Установка shared_buffers больше 8 ГБ не даёт никакого хорошего эффекта на Linux
и
Хотя в некоторых случаях было указано, что shared_buffers более 8Гб дает прирост производительности
Как говорится, две большие разницы :)
Спасибо за развернутый ответ!
Хотя в некоторых случаях было указано, что shared_buffers более 8Гб дает прирост производительности (но только в том случае, если вся база влазит в shared_buffers).
Это, кстати, тоже не правда.
Вопрос в том, чтобы добится как можно большего cache hit ratio. Если вся база 1ТБ, working set 100GB, но большая часть всех операций работает с 30GB, то имеет смысл сделать shared_buffers где-то примерно 30GB. Таким образом можно получить до 99% cache hit ratio. Повышение shared_buffers выше не даст никакого профита, только проблемы…
В общем о shared_buffers следует рассуждать именно в таких терминах, а не в терминах «есть мнение что 8GB это разумный предел»
в shared_buffers лежат страницы с данными. если горячих данных 100Гб и shared_buffer 8Гб, то эти 100Гб будут постоянно читаться с диска и записываться в эти 8Гб выкидывая другие данные которые уже наверняка при следующем запросе вновь нужны будут.
конечно с диска физически читаться не будет, будет браться с дискового кэша, но этот миксер создаст огромную нагрузку на процессоры. При 100Гб горячих данных и около 1000 запросов в сек вряд ли этот миксер справится даже на многопроцессорном серваке.
если дать 600Гб shared_buffers, то в памяти сразу будет эти 100Гб горячих и ещё 500Гб иногда используемых данных. не будет обращений в дисковую подсистему, не будет постоянных копирований данных из дискового кэша в shared_buffers
главное чтобы (shared_buffers + (максимальное кол-во postgres воркеров * work_mem) + запас) не превысили объём доступной оперативной памяти, иначе всё начнёт свопиться и будет segmentation fault… так же нужно учесть что на серваке кто-то ещё может использовать оперативную память, например php
включите логирование долгих запросов log_min_duration_statement. ставите как можно больше shared_buffers. затем берёте самые тяжёлые и смотрите план запросов… регулируйте work_mem так чтобы план для этих запросов становился наилучшим, pgadmin это покажет в графическом виде. учтите что слишком большие значения work_mem могут быть бесполезными, т. к. даже самому тяжелому запросу может много и не нужно. затем считаете сколько максимум shared_buffers можно поставить чтобы не засвопилось и ставите это значение.
конечно с диска физически читаться не будет, будет браться с дискового кэша, но этот миксер создаст огромную нагрузку на процессоры. При 100Гб горячих данных и около 1000 запросов в сек вряд ли этот миксер справится даже на многопроцессорном серваке.
если дать 600Гб shared_buffers, то в памяти сразу будет эти 100Гб горячих и ещё 500Гб иногда используемых данных. не будет обращений в дисковую подсистему, не будет постоянных копирований данных из дискового кэша в shared_buffers
главное чтобы (shared_buffers + (максимальное кол-во postgres воркеров * work_mem) + запас) не превысили объём доступной оперативной памяти, иначе всё начнёт свопиться и будет segmentation fault… так же нужно учесть что на серваке кто-то ещё может использовать оперативную память, например php
включите логирование долгих запросов log_min_duration_statement. ставите как можно больше shared_buffers. затем берёте самые тяжёлые и смотрите план запросов… регулируйте work_mem так чтобы план для этих запросов становился наилучшим, pgadmin это покажет в графическом виде. учтите что слишком большие значения work_mem могут быть бесполезными, т. к. даже самому тяжелому запросу может много и не нужно. затем считаете сколько максимум shared_buffers можно поставить чтобы не засвопилось и ставите это значение.
Тут проблема не только с процессорами. Checkpoint операция должна сбрасывать данные с shared_buffer на диск. Тоесть чем больше у Вас shared_buffer, тем больше он может содержатся обьем измененных данных в памяти в PostgreSQL (худший вариант — весь shared_buffer содержит измененные данные), который потребует их сбросить на диск. И чем дольше этот обьем будет записываться на диск, тем дольше у Вас будет находится база, что не отвечает не на один запрос (в худшем случае).
за это отвечает wal_buffers, а не shared_buffers. по умолчанию wal_buffers конечно зависит от shared_buffers, но он не может быть больше 16Мб, да и кто мешает от этой зависимости избавиться. поставите нужное значение и никаких проблем.
в любом случае всё нужно записать на диск, а запись зависит от кол-ва insert, update, delete и т.д., но ни как не от объёма shared_buffers. более того, чем больше shared_buffers тем меньше вероятность того что диск будет задействован на чтение, а значит меньше iowait для записи на диск.
а ещё в shared memory можно посмотреть какие данные там лежат, чего нельзя сделать в дисковом кэше. www.postgresql.org/docs/9.3/static/pgbuffercache.html. это позволяет разработчику оценить и написать запросы так чтобы более эффективно использовать оперативную память, сократить обращения к диску. а если имеется несколько слэйв серверов, то можно на одном слэйве в памяти уложить одни данные, а на другом другие для эффективного использования памяти.
в любом случае всё нужно записать на диск, а запись зависит от кол-ва insert, update, delete и т.д., но ни как не от объёма shared_buffers. более того, чем больше shared_buffers тем меньше вероятность того что диск будет задействован на чтение, а значит меньше iowait для записи на диск.
а ещё в shared memory можно посмотреть какие данные там лежат, чего нельзя сделать в дисковом кэше. www.postgresql.org/docs/9.3/static/pgbuffercache.html. это позволяет разработчику оценить и написать запросы так чтобы более эффективно использовать оперативную память, сократить обращения к диску. а если имеется несколько слэйв серверов, то можно на одном слэйве в памяти уложить одни данные, а на другом другие для эффективного использования памяти.
Что бы не выдумывать вот цитата из рассылки по поводу shared_buffers:
* Shared buffers must (currently) compete with OS inode caches. If this is shared buffers are too high, much of the cached data is already cached by the operating system, and you end up with wasted RAM.
* Checkpoints must commit dirty shared buffers to disk. The larger this is, the more risk you have when checkpoints come, up to and including an unresponsive database. Writing to disks isn't free, and sadly this is still on the slower side unless all of your storage is SSD-based. You don't want to set this too much higher than your disk write cache.
* Performance gains taper off quickly. Most DBAs don't see gains after 4GB, and fewer still see any gains above 8GB. We have ours set at 4GB after a lot of TPS and risk analysis.
* Since shared_buffers is the amount of memory that could potentially remain uncommitted to data files, the larger this is, the longer crash recovery can take. Having this too high could mean the difference between a five-minute outage, and a five-second outage. The checkpoint_* settings control how this is distributed and maintained, but the risk starts here.
Что касается дискового кэша. Если база 1 ТБ с большим объёмом горячих данных, врядли на серваке будет ещё что-то кроме postgres. используя пулер, например pgbouncer, мы можем точно контролировать кол-во воркеров postgres, а значит можем точно знать сколько максимум памяти это займёт. в этом случае нет смысла страховаться дисковым кэшем в ущерб shared_buffers. Данные в дисковом кэше неизбежно дублируются в shared_buffers, т.е. одни и теже данные хранятся в 2х местах одновременно что крайне не эффективно ввиду того что оперативной памяти итак мало.
Что касается checkpoint. простым языком checkpointer process непрерывно монотонно пишет изменённые страницы на диск пока не достигнет checkpoint_timeout или размера wal 16Мб. пишет на диск он те страницы которые не используются(вероятно не используются) чтобы не блокировать процесс выполнения запросов воркерами. все изменённые страницы обязательно должны быть записаны на диск, т.е. если воркеру нужна страница которой нет в разделяемой памяти(shared_buffers), то она берётся с диска и записывается в свободное место разделяемой памяти, а при отсутствии места вытесняет другую не изменённую(или уже записанную chekpointer process на диск). Если в shared_buffers все страницы изменены, то воркерам придётся подождать(для многих систем как правило ждать недопустимо) пока checkpointer process что-нибудь запишет на диск. Чем больше shared_buffers, тем меньше вероятность того что придётся ждать checkpointer process. Вообще если wal'ы пишутся на другие диске, то checkpointer process пишет почти в реальном времени на достаточно большой скорости без iowait. Объём данных которые пишет на диск checkpointer process зависит только от кол-ва данных которые были изменены запросами типа insert, update и т.д., но никак не от размера shared_buffers.
Цитата из рассылки это из области «Люди говорят». Но когда пытаешься найти таких людей чтобы с ними это обсудить лично, то никак их найти не получается. Как правило в рассылке обсуждается один конкретный случай, на конкретном железе и конкретном ПО(кроме postgres там ещё php, java, python и т. д.) на этой же машине. Во всех 4х пунктам в Вашей цитате есть доля правды, но это конкретный случай.
В документации нигде нет информации о 8 Гб. Не понятно почему вы привязали калькулятор PgTune к этому значению и навязываете людям. Калькулятор должен учесть все основные факторы и выдать приближенные значения для конкретного случая который описал человек заполняя поля в форме PgTune. А Вы во всех случаях банально выдаёте заведомо не правильное единственное значение 8 Гб. Чтобы правильно настроить postgres нужно абсолютно чётко понимать как он это делает. Объясните, пожалуйста, как postgres это делает и почему с 8 Гб он это сделает лучше чем с 600 Гб. Наверняка я в чём то заблуждаюсь, откройте мне глаза, объясните на понятном русском как есть на самом деле без ссылок на фразы где так же нет объяснения.
В калькуляторе нужно учитывать как минимум наличие пулера, максимальное кол-во воркеров, объём оперативной памяти, производительность дисков, объём памяти требуемое другому ПО.
Что касается checkpoint. простым языком checkpointer process непрерывно монотонно пишет изменённые страницы на диск пока не достигнет checkpoint_timeout или размера wal 16Мб. пишет на диск он те страницы которые не используются(вероятно не используются) чтобы не блокировать процесс выполнения запросов воркерами. все изменённые страницы обязательно должны быть записаны на диск, т.е. если воркеру нужна страница которой нет в разделяемой памяти(shared_buffers), то она берётся с диска и записывается в свободное место разделяемой памяти, а при отсутствии места вытесняет другую не изменённую(или уже записанную chekpointer process на диск). Если в shared_buffers все страницы изменены, то воркерам придётся подождать(для многих систем как правило ждать недопустимо) пока checkpointer process что-нибудь запишет на диск. Чем больше shared_buffers, тем меньше вероятность того что придётся ждать checkpointer process. Вообще если wal'ы пишутся на другие диске, то checkpointer process пишет почти в реальном времени на достаточно большой скорости без iowait. Объём данных которые пишет на диск checkpointer process зависит только от кол-ва данных которые были изменены запросами типа insert, update и т.д., но никак не от размера shared_buffers.
Цитата из рассылки это из области «Люди говорят». Но когда пытаешься найти таких людей чтобы с ними это обсудить лично, то никак их найти не получается. Как правило в рассылке обсуждается один конкретный случай, на конкретном железе и конкретном ПО(кроме postgres там ещё php, java, python и т. д.) на этой же машине. Во всех 4х пунктам в Вашей цитате есть доля правды, но это конкретный случай.
В документации нигде нет информации о 8 Гб. Не понятно почему вы привязали калькулятор PgTune к этому значению и навязываете людям. Калькулятор должен учесть все основные факторы и выдать приближенные значения для конкретного случая который описал человек заполняя поля в форме PgTune. А Вы во всех случаях банально выдаёте заведомо не правильное единственное значение 8 Гб. Чтобы правильно настроить postgres нужно абсолютно чётко понимать как он это делает. Объясните, пожалуйста, как postgres это делает и почему с 8 Гб он это сделает лучше чем с 600 Гб. Наверняка я в чём то заблуждаюсь, откройте мне глаза, объясните на понятном русском как есть на самом деле без ссылок на фразы где так же нет объяснения.
В калькуляторе нужно учитывать как минимум наличие пулера, максимальное кол-во воркеров, объём оперативной памяти, производительность дисков, объём памяти требуемое другому ПО.
По моему вы все перепутали относительно чекпоинтов…
1. checkpointer process фиксирует в датфайлах на диске только то что содержится в WAL журнале. Измененные страницы пишет bgwriter — отдельный процесс. Если он медленно работает (при дефолтных настройках) то за него грязные страницы будут скидывать бэкенды (обратите внимание на pg_stat_bgwriter.buffers_backend).
2. checkpoint_timeout это не то что вы написали, а время между выполнением двух чекпоинтов. если с момента выполнения последнего чекпоинта прошло время указанное в checkpoint_timeout, то запустится новый чекпоинт.
3. >> непрерывно монотонно
неправда, если он сделал свое дело, то он будет курить в сторонке (пока не наступит checkpoint_timeout или не запишется число сегментов указанное в checkpoint_segments). еще раз повторюсь, dirty pages сбрасываются bgwriter'ом и в крайнем случе бэкендами
4. >> Если в shared_buffers все страницы изменены, то воркерам придётся подождать
не будут они ждать, они сами начнут скидывать страницы на диск. а dba следует тюнить bgwriter_lru_multiplier
вобщем во втором абзаце у вас полное непонимание вопроса, как бы обидно это незвучало.
p.s. такие длинные коменты очень сложно читать, если вы хотите подискутировать напишите мне скайп.
1. checkpointer process фиксирует в датфайлах на диске только то что содержится в WAL журнале. Измененные страницы пишет bgwriter — отдельный процесс. Если он медленно работает (при дефолтных настройках) то за него грязные страницы будут скидывать бэкенды (обратите внимание на pg_stat_bgwriter.buffers_backend).
2. checkpoint_timeout это не то что вы написали, а время между выполнением двух чекпоинтов. если с момента выполнения последнего чекпоинта прошло время указанное в checkpoint_timeout, то запустится новый чекпоинт.
3. >> непрерывно монотонно
неправда, если он сделал свое дело, то он будет курить в сторонке (пока не наступит checkpoint_timeout или не запишется число сегментов указанное в checkpoint_segments). еще раз повторюсь, dirty pages сбрасываются bgwriter'ом и в крайнем случе бэкендами
4. >> Если в shared_buffers все страницы изменены, то воркерам придётся подождать
не будут они ждать, они сами начнут скидывать страницы на диск. а dba следует тюнить bgwriter_lru_multiplier
вобщем во втором абзаце у вас полное непонимание вопроса, как бы обидно это незвучало.
p.s. такие длинные коменты очень сложно читать, если вы хотите подискутировать напишите мне скайп.
habrahabr.ru/post/217073/#comment_7438801
Я ничего никому не навязываю. Право человека использовать данную утилиту или нет :)
Я ничего никому не навязываю. Право человека использовать данную утилиту или нет :)
не обессудьте, критика двигатель прогресса…
нужно иметь инструмент дающий максимально приближённые настройки, от которых уже отталкиваться, а не гадать на кофейной гуще. pgtune действительно тот инструмент который должен пригодиться даже бывалому.
комментарий действительно упустил. сейчас значения для текущего набора входных данных соответствуют. не хватает исходных данных, не плохо бы ещё оттолкнуться от тех параметров что я описал ранее.
нужно иметь инструмент дающий максимально приближённые настройки, от которых уже отталкиваться, а не гадать на кофейной гуще. pgtune действительно тот инструмент который должен пригодиться даже бывалому.
комментарий действительно упустил. сейчас значения для текущего набора входных данных соответствуют. не хватает исходных данных, не плохо бы ещё оттолкнуться от тех параметров что я описал ранее.
Я думал добавить поля, для указания SSD или нет, какой размер базы, прочее. Но у меня нет данных по разным таким случаям и какая настройка давала выигрыш для PostgreSQL. Потому что по таким данным нужно создать математическую модель (если её еще нет) и добавить в pgtune. Ведь все, что делает он — считает по формулам по указаным условиям :)
Не обновляется с 2009 года (поэтому расчеты немного уже не актуальны стали)Похоже, что проект переехал на гитхаб. Там есть изменения за 2013-й год, например.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
PgTune — настройка производительности PostgreSQL для заданной аппаратной конфигурации (онлайн версия)