Оптимизация postgresql centos 7
Обновлено: 04.07.2024
Дистрибутивы
Хочу отметить, что инсталляцию необходимо проводить на ветке 9.6, так как она официально заявлена рабочей. На 10 версии Postgres тестирование проводилось и результат отличный.
Установка
Если все дистрибутивы (качестве дистрибутива использованы пакеты с официального сайта 1С версии 9.6.7-1.1C) скачаны и заброшены на сервер, можно приступить к установке
Распаковка архивов
tar - xvf postgresql_9 . 6.7_1.1C_x86_64_addon_rpm.tar.bz2Установка PostgresSQL
Установка дополнений
Инициализация базы данных и запуск Postgres
Переключаемся на пользователя postgres
Запуск инициализации системной базы с указанием кодировки и рабочего каталога
/ usr / pgsql - 9.6 / bin / initdb -- locale = ru_RU . UTF - 8 - D / var / lib / pgsql / 9.6 / data / Success . You can now start the database server using : / usr / pgsql - 9.6 / bin / pg_ctl - D / var / lib / pgsql / 9.6 / data / - l logfile startЗапуск сервера Postgres
/ usr / pgsql - 9.6 / bin / pg_ctl - D / var / lib / pgsql / 9.6 / data / - l logfile startНастройка файла pg_hda.conf
Подключение в базе
Устанавливаем пароль пользователя postgres
Проверка локализации и работоспособности
Name | Owner | Encoding | Collate | Ctype | Access privileges postgres | postgres | UTF8 | ru_RU . UTF - 8 | ru_RU . UTF - 8 | template0 | postgres | UTF8 | ru_RU . UTF - 8 | ru_RU . UTF - 8 | = c / postgres + template1 | postgres | UTF8 | ru_RU . UTF - 8 | ru_RU . UTF - 8 | = c / postgres +Перезагрузка сервера
Настройка подключения
После перезагрузки запустим вручную и добавим в автозагрузку
Отроем порт Postgres на фаерволе для подключения или отключим совсем
firewall - cmd -- permanent -- zone = public -- add - port = 5432 / tcpОтключение фаервола
Отключим SELinux
Отредактируем файл /etc/sysconfig/selinux, присвоив параметру SELINUX флаг disabled:Настройка файла hosts
Для успешной работы кластера 1С Предприятия на Windows сервере, необходимо отредактировать файл hosts, как на Linux? так и Windows и привести к следующему виду. Тоже не обязательная процедура, но иногда возникают проблемы Для Windows необходимо отредактировать файл c:\Windows\System32\Drivers\etc\hostsПерезагрузка
Выполним перезагрузку для проверки работоспособности системыНастройка базы данных в кластере 1С Предприятие
Если подключение прошло успешно, ПОЗДРАВЛЯЮ. вы все настроили верно ))) Можно загрузить в базу нагрузочный тест Гилева и проверить быстродействие системы без оптимизацииОптимизация настроек Postgresql для сервера 1С Предприятие
Некоторые параметры разъяснены в этой статье. Сохраним резервную копию файла настроек
cp / var / lib / pgsql / 9.6 / data / postgresql . conf / var / lib / pgsql / 9.6 / data / postgresql . conf . bak Отредактируем файл /var/lib/pgsql/9.6/data/postgresql.conf и приведем его к виду shared_preload_libraries = 'online_analyze, plantuner, auto_explain'Конфигурация рассчитана на 32Gb оперативной памяти.
Описание параметров с сайта infostat
Значения могут указываться с суффиксами kB, MB, GB (значения в килобайта, мегабайтах или гигабайтах). После изменения значений требуется перезапустить службу PostgreSQL.
Размер кэша чтения и записи PostgreSQL, общего для всех подключений. Если данные отсутствуют в кэше, производится чтение с диска (возможно, будут кэшированы ОС)
Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности.
Но это не вся память, требуемая для работы, не следует указывать слишком большое значение, иначе не останется памяти как для собственно выполнения запросов клиентов (а чем их больше тем выше потребление памяти), так и для ОС и прочих приложений, например, процесса сервера 1С. Так же сервер полагается и на кэш ОС и старается не держать в своём буфере то что скорее всего закэшировано системой.
В тесте использовалось
shared_buffers = 512MB
Есть рекомендация при расчетах взять объем доступной памяти за вычетом shared_buffers, и поделить на количество одновременно исполняемых запросов. В случае сложных запросов делитель стоит увеличить, т.е. уменьшить результат. Для рассматриваемого случая из расчета 5 активных пользователей (2.5Гб-0.5Гб (shared_buffers))/5=400Мб. В случае если СУБД сочтет запросы достаточно сложными, или появятся дополнительные пользователи, потребуется значение уменьшить.
work_mem = 128MB
Рекомендуется устанавливать значение 50-75% от размера самой большой таблицы или индекса, но чтобы памяти хватило для работы системы и приложений. Рекомендуется устанавливать значения больше чем work_mem. В тесте использовал
maintenance_work_mem = 192MB
Можно установить порядка 16 МБ. В тесте использовал
temp_buffers = 32MB
Оптимизатор использует это значение при построении плана запроса, для оценки вероятности нахождения данных в кэше (с быстрым случайным доступом) или на медленном диске. В Windows текущий объем памяти, выделенной под кэш, можно посмотреть в диспетчере задач.
Так же до определенной версии сборка мусора была связана со сбором статистики (планировщик использует данные о количестве записей в таблицах и распределении значений индексированных полей для построения оптимального плана запроса). С одной стороны, сбор мусора делать необходимо, чтобы таблицы не разрастались и эффективно использовали дисковое пространство. С другой внезапно начавшаяся уборка мусора дает дополнительную нагрузку на диск и таблицы, что приводит к увеличению времени выполнения запросов. Аналогичный эффект создает автоматический сбор статистики (явно его можно запустить командой ANALYZE или совместно со сборкой мусора VACUUM ANALYZE). И хотя от версии к версии PostgreSQL совершенствует эти механизмы, чтобы минимизировать негативное влияние на производительность (например, в ранних версиях сборка мусора полностью блокировала доступ к таблице, с версии 9.0 работа VACUUM ускорена), тут есть что настроить.
Полностью отключить autovacuum можно параметром:
autovacuum = off
Так же для работы Autovacuum требуется параметр track_counts = on, в противном случае он работать не будет.
Отключать autovacuum крайне не рекомендуется, иначе имеет смысл самостоятельно запланировать регулярное выполнение команды VACUUM ANALYZE.
Замечание: VACUUM обычно не уменьшает размер файла таблицы, только помечает свободные, доступные для повторного использования области. Если же требуется физически высвободить лишнее место и максимально уменьшить занимаемое пространство на диске, потребуется команда VACUUM FULL. Этот вариант блокирует доступ к таблице на время работы, и обычно не требуется его использовать. Подробнее об использовании команды VACUUM можно прочитать в документации (на английском).
Если Autovacuum полностью не отключать, настроить его влияние на выполнение запросов можно следующими параметрами:
Если увеличить threshold и scale_factor, обслуживающие процессы будут выполняться реже, но небольшие таблицы могут существенно разрастаться. Если БД состоит преимущественно из небольших таблиц, общее увеличение занимаемого дискового пространства может быть существенным, таким образом увеличивать эти значения можно, но с умом.
Можно управлять производительностью AUTOVACUUM, делая его более длительным но менее нагружающим систему.
Настройка записи на диск. При завершении транзакции PostgreSQL начала пишет данные в специальный журнал транзакций WAL (Write-ahead log), а затем уже в базу после того, как данные журнала гарантированно записаны на диск. По умолчанию используется механизм fsync, когда PostgreSQL принудительно сбрасывает данные (журнала) из дискового кэша ОС на диск, и только после успешной записи (журнала) клиенту сообщается об успешном завершении транзакции. Использование журнала транзакций позволяет завершить транзакцию или восстановить базу если во время записи данных произойдет сбой.
В нагруженных системах с большими объемами записи может иметь смысл вынести журнал транзакций на отдельный физический диск (но не на другой раздел этого же диска!). Для этого нужно остановить СУБД, перенести каталог pg_xlog в другое место, а на старом месте создать символическую ссылку, например, утилитой junction. Так же ссылки умеет создавать Far Manager (Alt-F6). При этом надо убедиться что новое место имеет права доступа для пользователя, от которого запускается PostgreSQL (обычно postgres).
При большом количестве операций изменения данных может потребоваться увеличить значение checkpoint_segments, регулирующее объем данных, который может ожидать переноса из журнала в саму базу. По умолчанию используется значение 3. При этом следует учитывать что под журнал выделяется место, расчитываемое по формуле (checkpoint_segments * 2 + 1) * 16 МБ, что при значении 32 уже потребует более 1Гб места на диске.
PostgreSQL после каждого завершения пишущей транзакции сбрасывает данные из файлового кэша ОС на диск. С одной стороны, это гарантирует что данные на диске всегда в актуальном состоянии, с другой при большом количестве транзакций падает производительность. Полностью отключить fsync можно, указав
fsync = off
full_page_writes = off
Делать это можно только в случае если вы на 100% доверяете оборудованию и ИБП (источнику бесперебойного питания). Иначе в случае аварийного завершения системы есть риск получить разрушенную БД. И в любом варианте не помешает так же RAID-контроллер с батарейкой для питания памяти недозаписанных данных.
Определенной альтернативой может быть использование параметра
synchronous_commit = off
В этом случае после успешного ответа на завершение транзакции до безопасной записи на диск может пройти некоторое время. В случае внезапного отключения база не разрушится, но могут быть потеряны данные последних транзакций.
Если не отключать fsync совсем, можно указать метод синхронизации в параметре. Статья с диска ИТС ссылается на утилиту pg_test_fsync, но в моей сборке PostgreSQL её не оказалось. По утверждению 1С, в их случае в Windows оптимально себя показал метод open_datasync (судя по всему, именно этот метод и используется по умолчанию).
В случае если используется множество мелких пишущих транзакций (в случае 1С этом может быть массовое обновление справочника вне транзакции), может помочь сочетание параметров commit_delay (время задержки завершения транзакции в микросекундах, по умолчанию 0) и commit_siblings (по умолчанию 5). При включении опций завершение транзакции может быть отложено на время commit_delay, если в данный момент исполняется не менее commit_siblings транзакций. В этом случае результат всех завершившихся транзакций будет записан совместно для оптимизации записи на диск.
Прочие параметры, влияющие на производительность
В книге по ссылке есть некоторые другие параметры, которые можно настраивать. Так же настоятельно рекомендуется ознакомиться с документацией на PostgreSQL по назначению конкретных параметров.
Параметры из раздела QUERY TUNING, особенно касающиеся запрета планировщику использовать конкретные методы поиска, рекомендуется изменять только в том случае если есть полное понимание что делаете. Очень легко оптимизировать один вид запросов и обрушить производительность всех остальных. Эффективность изменения большинства параметров в этом разделе зависит от данных в БД, запросов к этим данным (т.е. от используемой версии 1С в т.ч.) и версии СУБД.
Оптимальная производительность PostgreSQL зависит от правильно определенных параметров операционной системы. Плохо настроенные параметры ядра ОС могут привести к снижению производительности сервера базы данных. Поэтому обязательно, чтобы эти параметры были настроены в соответствии с сервером базы данных и его рабочей нагрузкой. В этом посте мы обсудим некоторые важные параметры ядра Linux, которые могут повлиять на производительность сервера базы данных и способы их настройки.
SHMMAX / SHMALL
SHMMAX — это параметр ядра, используемый для определения максимального размера одного сегмента разделяемой памяти (shared memory), который может выделить процесс Linux. До версии 9.2 PostgreSQL использовал System V (SysV), для которой требуется настройка SHMMAX. После 9.2 PostgreSQL переключился на разделяемую память POSIX. Так что теперь требуется меньше байтов разделяемой памяти System V.
До версии 9.3 SHMMAX был наиболее важным параметром ядра. Значение SHMMAX задается в байтах.
Аналогично, SHMALL — это еще один параметр ядра, используемый для определения
общесистемного объема страниц разделяемой памяти (shared memory). Чтобы просмотреть текущие значения SHMMAX, SHMALL или SHMMIN, используйте команду ipcs.
SHM* Details — Linux
SHM* Details — MacOS X
Возможные ошибки неправильной конфигурации
Если SHMMAX настроен неправильно, вы можете получить ошибку при попытке инициализировать кластер PostgreSQL с помощью команды initdb.
initdb Failure
DETAIL: Failed system call was shmget(key=1, size=2072576, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 2072576 bytes),
reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1
Аналогично, вы можете получить ошибку при запуске сервера PostgreSQL используя команду pg_ctl.
pg_ctl Failure
DETAIL: Failed system call was shmget(key=5432001, size=14385152, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX.; To reduce the request size (currently 14385152 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.
Понимание различий в определениях
Определение параметров SHMMAX/SHMALL немного отличается в Linux и MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
Изменение параметров ядра на MacOS X
Изменение параметров ядра на Linux
Не забудьте: чтобы сделать изменения постоянными, добавьте эти значения в /etc/sysctl.conf
Большие страницы (Huge Pages)
PostgreSQL поддерживает большие страницы только в Linux. По умолчанию Linux использует 4 КБ страниц памяти, поэтому в случаях, когда операций с памятью слишком много, необходимо устанавливать страницы большего размера. Наблюдается прирост производительности при использовании больших страниц размером 2 МБ и до 1 ГБ. Размер большой страницы может быть установлен во время загрузки. Вы можете легко проверить параметры большой страницы и их использование на вашем Linux-компьютере, используя команду cat /proc/meminfo | grep -i huge.
Получение информации о больших страницах (только на Linux)
В этом примере, хотя размер большой страницы установлен в 2048 (2 МБ), общее количество больших страниц имеет значение 0. Это означает, что большие страницы отключены.
Скрипт определения количества больших страниц
Это простой скрипт возвращает необходимое количество больших страниц. Запустите скрипт на вашем сервере Linux, пока работает PostgreSQL. Убедитесь, что для переменной среды $PGDATA задан каталог данных PostgreSQL.
Получение цифры требуемых больших страниц
Вывод скрипта выглядит следующим образом:
Вывод скрипта
Рекомендуемое значение больших страниц — 88, поэтому вы должны установить значение 88.
Установка больших страниц
Проверьте большие страницы сейчас, вы увидите, что большие страницы не используются (HugePages_Free = HugePages_Total).
Снова информация о больших страницах (только на Linux)
Теперь задайте параметр huge_pages «on» в $PGDATA/postgresql.conf и перезапустите сервер.
И снова информация о больших страницах (только на Linux)
Теперь вы можете видеть, что используются очень мало больших страниц. Давайте теперь попробуем добавить некоторые данные в базу данных.
Некоторые операции с базой данных для утилизации больших страниц
Давайте посмотрим, используем ли мы сейчас больше больших страниц, чем раньше.
Еще раз информация о больших страницах (только на Linux)
Теперь вы можете видеть, что большинство больших страниц используется.
Примечание: примерное значение для HugePages, используемое здесь, очень низкое, что не является нормальным значением для машины на продуктовой среде. Пожалуйста, оцените необходимое количество страниц для вашей системы и установите их соответственно в зависимости от нагрузки и ресурсов.
vm.swappiness
vm.swappiness — это еще один параметр ядра, который может влиять на производительность базы данных. Этот параметр используется для управления поведением подкачки (swappiness) (подкачки страниц в память и из нее) в Linux. Значение варьируется от 0 до 100. Он определяет, сколько памяти будет выгружено или выгружено. Ноль означает отключение обмена, а 100 означает агрессивный обмен.
Вы можете получить хорошую производительность, установив более низкие значения.
Установка значения 0 в более новых ядрах может привести к тому, что OOM Killer (процесс очистки памяти в Linux) убьет процесс. Таким образом, можно безопасно установить значение 1, если хотите минимизировать подкачку. Значение по умолчанию в Linux — 60. Более высокое значение заставляет MMU (блок управления памятью) использовать больше пространства подкачки, чем ОЗУ, тогда как более низкое значение сохраняет больше данных/кода в памяти.
Меньшее значение — хорошая ставка на улучшение производительности в PostgreSQL.
Непосредственно перед установкой СУБД PostgreSQL необходимо выполнить следующие настройки:
Настройка параметров ядра CentOS 7
Настройка параметров ядра требуется для корректной установки и работы хостов на которых разворачиваются БД PostgreSQL 9.4. Перед тем как вносить изменения в файлы конфигурации, следует предварительно создать их резервные копии следующими командами:
Настройка параметров ядра ОС для инсталляции PostgreSQL 9.4
Отредактируйте файл /etc/sysctl.conf:
Рекомендуется закомментировать имеющиеся параметры kernel.shmmax и kernel.shmall.
Параметры виртуальной памяти
Добавьте в конец файла /etc/sysctl.conf следующие строки:
vm.dirty_expire_centisecs=500
vm.dirty_writeback_centisecs=100
vm.swappiness=0
vm.dirty_background_ratio=3
vm.dirty_ratio=15
Или выполните в терминале следующие команды(от root):
Параметры общей памяти(kernel.shmmni, kernel.shmmax и kernel.shmall)
Параметры kernel.shmmax и kernel.shmall зависят от количества RAM, установленной на сервере. Параметр kernel.shmmax указывается в байтах, а kernel. shmall в страницах. Для определения максимума общей памяти (параметр ядра shmall) в элементах размера страницы памяти, необходимо получить размер страницы в операционной системе (выполняем в терминале команду):
Максимальный размер общего сегмента shmmax должен равняться, по меньшей мере, половине общего объема памяти.SHMMAX не может превышать размер RAM минус один байт, но должен быть больше чем сумма всех SGA на данном хосте. Минимальное значение 536870912. Рекомендуется установить максимальное количество сегментов общей памяти shmmni равным 4096. Текущее значение этих параметров можно узнать, выполнив команды:
Семафоры
Linux предоставляет семафоры для передачи небольшого количества информации между процессами. Семафоры — это числа-счётчики или on/off значения, которые организуются в группы. СУБД PostgreSQL необходимо больше групп, больше комнат в каждой группе и больше счётчиков, чем предоставляется операционной системой по умолчанию. Измените параметр kernel.sem, определяющий необходимое количество семафоров, выполнив следующую команду:
Сетевые порты
Измените параметр net.ipv4.ip_local_port_range в /etc/sysctl.conf для модификации диапазона ip портов для автоматического назначения клиентам исходящих портов, выполнив в терминале команду:
Оптимизация сетевых настроек(TCP/IP)
Рекомендуется установить постоянные размеры для буфера - максимальный и по умолчанию для сокетов приложений. Их рекомендованные значения – разумная точка для начала. Выполните в терминале следующие команды:
Различная системная конфигурация
- Вам может понадобиться увеличить следующие конфигурационные параметры, если они слишком малы:
Максимальное количество конкурентных асинхронных запросов ввода/вывода:
Ограничения оболочки для пользователя postgres
Во время обычной работы запускается много процессов, открывается большое количество файлов и требуется больше ресурсов, чем обычно. В то же время, пределы (hard limits) на аккаунте предотвратит в некоторых случаях зависание процессов (resource starvation). Конфигурация PAM по умолчанию (в /etc/pam.d/system-auth) загружает модули (session required pam_limits.so) которые устанавливают и приводят в действие эти пределы.
Как администратор root добавьте следующие строчки в /etc/security/limits.conf:
Отредактируйте файл /etc/pam.d/login, добавив в него строку session required pam_limits.so Отредактируйте файл /etc/profile добавив перед:
unset i unset pathmunge
Текст со следующим содержанием:
if [ $USER = "postgres" ]; then ulimit -u 16384 -n 65536 fi
Настройка ОС для серверов БД PostgreSQL выполнена. Далее будет описан процесс установку СУБД PostgreSQL 9.4 на ОС CentOS 7.2.
Установка СУБД PostgreSQL 9.4
1) Для установки пакетов PostgreSQL в командной строке выполнить команду от root:
2) Инициализируем базу данных PostgreSQL, добавляем сервис в автозагрузку, стартуем сервис БД, проверяем статус, выполнив команды:
3) В /var/lib/pgsql ($HOME postgres) создаём файл .pgsql_profile следующего содержания: export PATH=/usr/pgsql-9.4/bin:$PATH
4) Создадим резервную копию и отредактируем файл /var/lib/pgsql/9.4/data/postgresql.conf:
Раскомментируем строку listen_addresses = 'localhost' и заменим localhost на '*'. Это говорит PostgreSQL принимать запросы со всех интерфейсов. Должно получиться следующее: listen_addresses = '*' На сервере 32 Gb RAM, выделим для PostgreSQL 16 Gb. Здесь же раскомментируем и изменим нижеследующие параметры исходя из 16 ГБ RAM доступных PostgreSQL:
5) Далее редактируем файл /var/lib/pgsql/9.4/data/pg_hba.conf. В конце файла добавляем строчку, прописав нужный адрес сети, вместо <адрес сети>:
Добавляем в конец pg_hba.conf,прописав нужный адрес сети, вместо <адрес сети>:
6) Перезапускаем службу PostgreSQL:
7) Меняем пароль для пользователя postgres (в ОС и в БД), после перезагружаем сервер и заходим под этим пользователем (postgres):
Вводим пароль, подтверждаем пароль, перезагружаем систему. После перезагрузки, заходим под postgres, в терминале набираем:
$ psql
и меняем пароль для пользователя БД postgres:
8) Установим PostgreSQL Adminpack:
9) Создаём базу данных и пользователя, где <user> - имя пользователя, <password> - пароль пользователя <user>, <dbname> - имя базы:
Выходим из psql:
10) Проверяем подключение к базе, выполнив следующую команду:
$ psql -h 127.0.0.1 <dbname> <user>
Если всё нормально, отключаемся.
11) Для того, что бы параметры вступили с силу, необходимо перегрузить PostgreSQL:
P.S. Некоторые параметры из postgresql.conf можно применять без перезагрузки PostgreSQL. Для этого служат следующие команды:
- su - postgresl; /usr/bin/pg_ctl reload — используя командный интерпретатор(bash)
- SELECT pg_reload_conf(); — используя SQL-код
Установка СУБД PostgreSQL 9.4 на ОС Windows
В данном разделе рассматривается инсталляция СУБД PostgreSQL 9.4 на ОС Windows без использования штатного инсталлятора.
Установка СУБД PostgreSQL
Установка СУБД PostgreSQL включает в себя выполнение следующих шагов(выполняются из-под административной учётной записи):
1) Скачиваем инсталляционный пакет rktools.exe. Инсталлируем его в систему. 2) Создаём директорию PostgreSQL на диске C:\, используя терминал командной строки(>mkdir "C:\PostgreSQL") или контекстное меню, вызываемое правой клавишей мыши(Создать—>Папку). 3) Скачиваем архив c бинарными файлами postgresql-9.4.xx-x-windows-x64-binaries.zip c https://www.enterprisedb.com/download-postgresql-binaries. Распаковываем архив в директорию C:\PostgreSQL. 4) Создаём каталог C:\PostgreSQL\data(>mkdir "C:\PostgreSQL\data"), в нём будут лежать базы. 5) Создаём пользователя postgres с паролем postgres. В командной строке(запущена из-под Администратора) вводим: net user postgres postgres /add 6) Устанавливаем ему неограниченный срок действия пароля: WMIC UserAccount WHERE Name="postgres" Set PasswordExpires=FALSE 7) Даём право входа в качестве службы утилитой ntrights из Windows Resource Kit Tools(rktools): ntrights +r SeServiceLogonRight -u postgres 8) Даём все права на каталог C:\PostgreSQL: cacls C:\PostgreSQL /E /G postgres:F 9) Создаём в C:\PostgreSQL\bin текстовый файл с именем pf, содержащий пароль — postgres: echo postgres> C:\PostgreSQL\bin\pf 10) Переходим в терминале CMD в директорию C:\PostgreSQL\bin и выполняем команду по инициализации базы: cd C:\PostgreSQL\bin initdb -U postgres --pwfile=pf -A md5 -E UTF8 --locale=Russian_Russia -D C:\PostgreSQL\data 11) Регистрируем сервис: pg_ctl register -N PostgreSQL -U postgres -P postgres -D C:\PostgreSQL\data -S auto 12) Запускаем службу: sc start PostgreSQL 13) Для удобства прописываем путь к бинарным файлам PostgreSQL в PATH(переменные окружения): pathman /as c:\PostgreSQL\bin 14) Вносим изменения в системный сервис PostgreSQL(В cmd ввести services.msc, выбрать службу PostgreSQL, вкладка Восстановление): Первый сбой: Перезапуск службы Второй сбой: Перезапуск службы 15) Добавляем в системные переменные окружения ключ PGDATA со значением C:\PostgreSQL\data: setx PGDATA "C:\PostgreSQL\data" 16) Для настройки кодовой страницы с корректным выводом кириллицы в терминале выполняем следующие команды: psql -d postgres -U postgres \! chcp 1251
Создание БД в PostgreSQL в Windows
Для создания БД в PostgreSQL необходимо выполнить следующие шаги(выполняются из-под административной учётной записи):
1) Запускаем интерпретатор командной строки(CMD) 2) Подключаемся к инстансу БД: psql -d postgres -U postgres 3) Устанавливаем PostgreSQL Adminpack: CREATE EXTENSION adminpack; 4) Создаём базу данных test и пользователя БД test с паролем test: CREATE DATABASE test; CREATE USER test WITH password 'test'; GRANT ALL privileges ON DATABASE test TO test; 5) Проверяем соединение с созданной базой данных: psql -h 127.0.0.1 -p 5432 -U test -d test
Некоторое время назад я настраивал работу 1С предприятия с базой данных postgresql. Во время тестирования столкнулся с проблемой медленной работы некоторых запросов. Хочу поделиться полезной информацией, которая позволит разобраться в таких ситуациях и попытаться ускорить работу и избавиться от узких мест в базе.
Если у вас есть желание научиться строить и поддерживать высокодоступные и надежные системы, рекомендую познакомиться с онлайн-курсом «DevOps практики и инструменты» в OTUS. Курс не для новичков, для поступления нужно пройти .Данная статья является частью единого цикла статьей про сервер Debian.
Введение
Сервер postgresql настроен по предыдущей статье - Установка и настройка postgresql на debian 8 для работы с 1С. Основные моменты по ускорению работы базы там приведены. Они существенно увеличивают производительность по сравнению с настройками по-умолчанию. В большинстве случаев этого бывает достаточно. Если нет - то у вас уже не типичный случай и надо разбираться более детально.
Проблема, с которой столкнулся я, кроется в особенности работы postgresql и отсутствии оптимизации 1С для работы с этой бд. База данных postgresql, в отличие от mssql, не умеет распараллеливать выполнение одного запроса не несколько ядер процессора. Даже если у вас очень высокопроизводительный сервер с большим числом ядер, вы можете попасть в ситуацию, когда какой-то тяжелый запрос будет очень сильно тормозить, нагружая только одно ядро. Остальные мощности процессора будут простаивать при этом. Увеличение ресурсов сервера никак не поможет вам ускорить работу базы. Она будет всегда спотыкаться на этом запросе.
Параллельное выполнение запросов на нескольких ядрах в postgresql
Есть несколько параметров, которые как раз отвечают за параллельную обработку запросов:
Их необходимо подбирать под свое количество ядер. В данном случае настройки представлены для 16-ти ядерной системы. Далее необходимо применить скрипт на базе 1С, который позволит оптимизатору постгреса использовать параллельную обработку тех запросов 1С где участвуют текстовые поля (большинство запросов), путём изменения определений функций. Текст скрипта очень длинный, поэтому не привожу его здесь, чтобы не нагружать статью. Качаем его с сайта - postgre.sql.
Запрос необходимо выполнить в базе, которую использует 1С. Для этого можно воспользоваться либо программой pgAdmin, либо напрямую подключиться к базе, через консоль сервера. Опишу второй вариант в подробностях.
Подключаемся к серверу с postgresql по ssh. Заходим под юзером postgres:
Переходим в домашний каталог пользователя:
Создаем файл с запросом, который будем выполнять. В данном случае можете сразу скопировать файл, который скачали ранее, либо создайте вручную и скопируйте в него текст запроса.
Если будете копировать готовый файл, убедитесь, что у пользователя postgres есть доступ к этому файлу.
Подключаемся к серверу бд:
Подключаемся к нужной базе данных:
Выполняем sql запрос из файла:
Все, можно идти проверять. Мы должны были увеличить быстродействие 1С запросов в базе postgresql, разрешив использовать параллельную обработку некоторых запросов. В моем случае это не дало никакого прироста по проблемным запросам. Сама база в целом работала нормально, но спотыкалась на определенных запросах. Разбираемся дальше.
Логирование sql запросов в postgresql
Для того, чтобы разобраться, что же конкретно у нас тормозит, надо посмотреть на сами запросы. Для этого нам нужно включить логирование запросов к базе данных. Запросов будет очень много, нам не нужны все подряд. Сделаем ограничение на логирование только тех запросов, которые выполняются дольше, чем 3 секунды. Для этого рисуем следующие параметры в конфиге БД:
И добавляем описание канала для логов LOCAL0 в конфиг rsyslog в файле /etc/rsyslog.conf, в самый конец:
Если оставить настройки rsyslog в таком виде, то лог запросов будет писаться не только в файл /var/log/postgresql/sql.log, но и в messages, и в syslog. Я не люблю спамить в системные логи, поэтому отключим запись sql логов туда. Добавляем в описание этих лог файлов значение LOCAL0.none. Должно получиться примерно так:
Перезапускаем postgresql и rsyslog:
Идем в базу 1С и вызываем свой запрос, который тормозит. Если его выполнение занимает больше, чем 3 секунды, вы увидите текст запроса в лог файле. Можете подольше попользоваться базой, чтобы собрать список запросов для анализа. Запросы 1С настолько громоздкие, что даже просто скопировать их из лога и обработать непростая задача. Воспользуемся для этого специальной программой.
Включение логирования запросов замедляет работу системы. Я рекомендую заниматься диагностикой либо в нерабочее время, либо на тестовой базе и сервере, если есть такая возможность. Для применения настроек базы данных, необходимо перезапускать ее. Это может доставить проблем, если с другими базами сервера кто-то работает. Примите это к сведению.Анализ запросов postgresql с помощью pgFouine
Устанавливаем pgFouine в debian:
Это старая программа, но для наших целей сойдет. Пользоваться ей очень просто. Я не вдавался в подробности настройки и не смотрел возможные параметры. Мне было достаточно сделать вот так:
Забираем файл report.html к себе на компьютер и открываем в браузере. У меня получилось примерно так:
Дальше вы можете разбираться со своими запросами, в зависимости от ваших знаний и возможностей. Я не знал, что делать дальше, для решения своей проблемы. Попытался построить карту запроса с помощью EXPLAIN ANALYZE, но не получилось. Запрос использует какие-то временные таблицы, так что просто скопировать и повторить его не получалось. Выходила ошибка, что какой-то таблицы не существует.
В настоящий момент я получил совет на профильном форуме по моей проблеме. Мне сказали, что ситуация известная и достаточно типичная для 1С. Исправлять ее нужно на стороне самой 1С, изменяя код запроса выборки из виртуальных таблиц на запросы из временных таблиц, соединяя их потом с основной. Это уже задача для программиста. Я в самой 1С не разбираюсь вообще.
Заключение
На текущий момент моя проблема не решена, но стало понятно, в каком направлении двигаться и что делать. В принципе, я изначально, когда стал заниматься этой задачей, предполагал, что проблема именно на стороне 1С из-за сложного запроса и отсутствии оптимизации работы 1С именно с postgresql. Я это понял, потому что с mssql таких тормозов никогда наблюдал на базах такого размера. В данном случае объем базы всего 10 гб, она не очень большая. 15 секунд лопатить запрос на такой базе можно только, если этот запрос ужасен. На деле все так и оказалось.
В процессе разбора ситуации приобрел определенный опыт, который постарался зафиксировать в этой статье. Думаю, он пригодится в будущем, как мне, так и другим пользователям. В интернете не нашел хороших статей по анализу производительности постгрес. Пришлось все собирать по крохам в разных статьях, но больше на форумах. С учетом стоимости лицензии mssql, замена ее на postgresql выглядит весьма обоснованной, так что тема актуальна.
Буду рад любым замечаниям и советам в комментариях. Тема для меня новая, но полезная. Хотелось бы разобраться в работе постгрес.
Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.
Читайте также: