У меня 64-разрядная версия PostgreSQL 9.0 на 64-разрядной версии Windows Server 2003. Система имеет 8 процессоров с тактовой частотой 3 ГГц и 8 ГБ памяти.
Как я могу / должен настроить следующие параметры ?:
База данных используется для аналитики. Только два или три пользователя подключены в любой момент времени, выполняя запросы. Думаю, наборы данных могут насчитывать от 1 до 15 миллионов строк.
Базовое хранилище - это массив хранения EMC CX, подключенный по оптоволоконному каналу. Производительность здесь очень хорошая.
Правильные значения зависят от модели использования. Однако вот несколько рекомендаций
shared_buffers: 25% объема памяти для выделенного сервера postgresql. work_mem: используется для таких операций, как сортировка. Одно соединение может использовать это количество несколько раз, поэтому будьте осторожны с этим, если у вас много запросов, выполняемых одновременно. Это требует большого тестирования, чтобы увидеть, улучшает ли он производительность, но не заставляет систему использовать много памяти. Поэтому, если вы увеличите это значение, убедитесь, что ваша система не начинает использовать слишком много памяти. Лично я часто начинаю с 4 МБ.
maintenance_work_mem: это для определенных операций обслуживания, таких как очистка и индексация, установка довольно высокого уровня в целом экономия. 64 Мбайт или 128 Мбайт обычно достаточно.
Также установите эффективный размер кеша. Это подсказка для планировщика и должна быть установлена на объем памяти, используемой ОС в качестве дискового кеша + размер общего буфера.
Если вы хотите провести обширную настройку, я рекомендую прочитать об этом хорошую книгу, например: PostgreSQL 9.0 Высокая производительность.
Для очень больших наборов данных вы можете обнаружить, что SAN не оптимален. SAN очень быстро справляются со многими и множеством небольших iOS. Они часто просто подходят для последовательной пропускной способности, если у вас нет очень быстрого соединения с ними, и даже тогда они часто просто не оптимизированы для последовательной пропускной способности. Я протестировал производительность последовательного и произвольного чтения и записи на своих машинах с RAID-картами Areca и LSI с кэш-памятью с батарейным питанием, собственным интерфейсом SAS с программным RAID Linux и с SAN на бэкэнде. Самый быстрый для произвольного доступа был близок к совместимости с картами SAN и RAID, но для обеспечения последовательной пропускной способности программный RAID-массив Linux затопил их в землю. Если HW RAID может получить 350 Мбит / с, а SAN находится в диапазоне 100 Мбит / с (он подключен на гиге), собственный SAS с SW RAID получает около 1 Гбит / с чтения и около 80% этого при записи. Все по порядку, конечно. Не думайте, что ваша SAN очень быстрая для того, что вы делаете, это может быть, а может и нет. Протестируйте его с помощью bonnie ++ или dd или чего-то еще, чтобы получить представление о том, насколько он на самом деле быстр. Если вы получаете ~ 100 МБ / с последовательно, то это будет мучительно медленно по сравнению с более дешевой машиной с 4 или 8 дисками SATA 7200 об / мин с RAID-10 для аналитики.
Когда вы говорите о процессорах с тактовой частотой 8 ГГц, вы имеете в виду 8 разъемов с 4 или 8 ядрами в каждом? Или 8 ядер? Или 4 ядра с гиперпоточностью? для вашей работы все, что превышает 4 ядра, вероятно, будет пустой тратой. Все, что превышает 8 ядер, определенно бесполезно. С OLAP / Analytics вам понадобится меньше более быстрых процессоров, если вы можете их получить.
Переходим к настройкам. shared_mem не обязательно должен быть большим. В Windows реализация общей памяти не оптимальна для больших значений, и увеличение ее размера редко помогает повысить производительность. Тем не менее, я бы проверил различные значения, чтобы увидеть, но несколько сотен мегабайт, вероятно, будут настолько быстрыми, насколько это возможно. Объем работ по техническому обслуживанию может быть в пределах гигабайт, но большой выигрыш заключается в том, чтобы увеличить его более чем на 100M или около того. work_mem - это ножной пистолет postgresql. Если вы собираетесь запустить его, а я рекомендую использовать на вашем компьютере как минимум 16 или 32 Мбайт, убедитесь, что вы ограничиваете параметр postgresql max_connections максимум несколькими десятками соединений. Если каким-то образом кто-то запустит сразу несколько запросов, оперативная память может быстро закончиться. Не хорошо. OTOH, некоторые тесты, вероятно, покажут, что что-то больше сотни или около того не очень помогает.
Опасность слишком высокого значения work_mem заключается в том, что это приведет к выталкиванию данных, кэшированных ОС, из кеша только для того, чтобы их нужно было перезагрузить снова. Стоимость обращения к дискам, чтобы получить эти данные, обычно выше, чем выигрыш от реального запуска.
Хорошее практическое правило - держать work_mem * max_connections * 2 <1/4 памяти. Итак, на машине с оперативной памятью 64 ГБ и 100 подключениями вам потребуется work_mem * 200 <16 ГБ или около 80 мегабайт максимум. Это гарантирует, что любое патологическое поведение, при котором все соединения запускаются множеством типов, не слишком быстро убьет машину.
Если вы обнаружите, что work_mem из 1G работает намного лучше, чем 100M и т. Д., Вы можете пойти на компромисс, оставив обычный work_mem ниже для безопасности и установив для единственного потока, который выполняет большие запросы, свой собственный work_mem при подключении.
Я согласен с предыдущим постером о том, что окна неоптимальны для pgsql, с акцентом на то, что это намного хуже для OLAP, где возможность выделить больше shared_memory может быть преимуществом для pg / linux.
Вы найдете подробные ответы на эти три вопроса на Настройка сервера PostgreSQL, а также предложения по некоторым другим параметрам, которые вы, возможно, захотите настроить. Вы не сможете использовать большие настройки для shared_buffers в Windows, есть постоянный спад, когда он перестает помогать около 512 МБ. Включите log_temp_files и посмотрите, что появится, чтобы выяснить, действительно ли вам нужно поднять work_mem. Судя по тому, что вы говорите о своем наборе данных, что не похоже на то, что он выдает большие отдельные запросы, вам, возможно, даже не стоит об этом беспокоиться. Умеренное повышение до maintenance_work_mem может быть полезно для работы фоновой автоочистки, но если это не станет для вас проблемой, не обязательно устанавливать очень высокое значение.
ХОРОШО. Вы говорите: «Производительность ввода-вывода довольно хорошая». Это мало что значит, но я полагаю, что такое оборудование имеет хорошую производительность последовательного ввода-вывода ...
Ваши запросы выглядят в стиле «перебор большого количества данных для получения некоторых агрегированных результатов» с низким уровнем параллелизма.
Рекомендации по этому поводу будут зависеть от размера данных.
Если ваша база данных (или, по крайней мере, часто используемая часть) достаточно мала, чтобы ее можно было хорошо кэшировать в ОЗУ, то производительность ввода-вывода не будет иметь большого значения (кроме операций записи); однако, если ваша база данных огромна и вы хотите быстро ее обработать, то производительность последовательного ввода-вывода будет иметь значение.
В любом случае сначала самые простые:
work_mem
Когда вы выполняете запрос с несколькими сортировками и хешами (для объединений и агрегатов) или материализованными хранилищами кортежей, каждое из них может использовать до work_mem. Сортировки могут попадать на диск, но не хеши. Обратите внимание, что если ваш запрос имеет N сортировок, он будет использовать work_mem N раз. Это важно для большого количества пользователей. В вашем случае мало пользователей, вы можете установить довольно высокое, может быть, 128 МБ. Таким образом, хэши по-прежнему будут разрешены даже для больших наборов данных, которые могут быть намного быстрее, чем сортировка. Вы также можете изменить его перед выполнением запроса.
maintenance_work_mem
То же самое для создания индекса и тому подобного. Создание индекса btree - это сложная задача, поэтому установка параметра maintenance_work_mem на что-то большое, например 1-2 ГБ, потребует меньшего количества проходов сортировки (например, временных файлов), если вы создаете индекс для огромной таблицы. Просто помните, что при восстановлении этой резервной копии не следует запускать одновременно 10 создания индекса ...
Подробнее -> см. Документацию
Что касается shared_buffers, в Windows я не знаю. Вы должны спросить список рассылки.
Также помните, что pg может использовать только одно ядро для каждого запроса, поэтому отключите гиперпоточность. Однако несколько запросов, просматривающих одну и ту же таблицу параллельно, будут синхронизироваться, чтобы прочитать данные только один раз.
Кстати, есть ли причина, по которой вы не используете Linux на этом компьютере? PG более «родной» для Linux.