Популярные сообщения

среда, 4 августа 2021 г.

Типовая конфигурация Postgres 12.1 Разворачивание Postgres 12.1 под SuSe 12.3

 Данная инструкция также применима для Postgres 12.2 и SuSe 15 с соответствующими правками по путям.

Подготовка ОС.

Необходимые пакеты

В действительности тут прозапас. Взят список для Oracle. Но внятной инструкции для Postgres я не нашёл. Полагаю, ему вообще достаточно пакетов по умолчанию. Но, поскольку этот список может меняться в зависимости от дистрибутива, то пусть будет с запасом.

 

zypper install binutils \
gcc \
gcc48 \
glibc \
glibc-32bit \
glibc-devel \
glibc-devel-32bit \
libaio1 \
libaio-devel \
libcap1 \
libstdc++48-devel \
libstdc++48-devel-32bit \
libstdc++6 \
libstdc++6-32bit \
libstdc++-devel \
libstdc++-devel-32bit \
libgcc_s1 \
libgcc_s1-32bit \
mksh \
make \
sysstat \
readline-devel \
zlib-devel

Настройка параметров ядра

vi /etc/sysctl.conf

# Конфигурация для RAM = 4GB
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.hung_task_timeout_secs = 600
kernel.sem = 250 32000 100 128
kernel.shmall = 1011681
kernel.shmmax = 2071922688
kernel.shmmni = 4096
kernel.sysrq = 0
kernel.watchdog = 1
kernel.watchdog_thresh = 60
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_forward = 0
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.tcp_syncookies = 1
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.all.forwarding = 0
vm.dirty_expire_centisecs=500
vm.dirty_writeback_centisecs=100
vm.swappiness=1
vm.dirty_background_ratio=3
vm.dirty_ratio=15

Лимиты

Обычно устанавливаем лимиты для пользователя postgres в размер памяти минус 1 ГБ для работы ОС (4 - 1 = 3 в данном примере). Для систем с размером памяти более 16 ГБ можно оставить 2 ГБ под ОС, остальное под postgres.

Добавляем в /etc/security/limits.conf следующий блок параметров

# PostgreSQL
postgres soft nproc 2047
postgres hard nproc 16384
postgres soft nofile 4096
postgres hard nofile 65536
postgres soft stack 10240
postgres hard stack 32768
postgres soft  memlock 3145728
postgres hard  memlock 3145728

 

Параметры монтирования

В /etc/fstab добавляем следующую строк. Также с размером в размер памяти минус 1-2 ГБ.

none                    /dev/shm        tmpfs   defaults,size=3G       0 0

Перезагрузка

shutdown -r now

Создание пользователя и необходимых директорий

mkdir -p /u01/postgres/12.1/pgsql/data
useradd --base-dir=/u01 --user-group --shell=/bin/bash --home-dir=/u01/postgres --create-home postgres
chown -R postgres:postgres /u01
passwd postgres

Сборка Postgres 12.1 из исходников

На сервер в папку /u01/distr копируется дистрибутив postgresql-12.1.tar.gz

cd /u01/distr
tar xvzf postgresql-12.1.tar.gz
cd ./postgresql-12.1
./configure --prefix=/u01/postgres/12.1/pgsql
make -C /u01/distr/postgresql-12.1 world
make -C /u01/distr/postgresql-12.1 install-world
chown -R postgres:postgres /u01/postgres

Создание сервиса systemd

Создаём файл /etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
 
[Service]
User=postgres
ExecStart=/u01/postgres/12.1/pgsql/bin/postgres -D /u01/postgres/12.1/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
 
[Install]
WantedBy=multi-user.target

Права и автозагрузка.

chmod 755 postgresql.service
systemctl enable postgresql

Профиль пользователя postgres

В файл /u01/postgres/.profile добавить следующие переменные

export LD_LIBRARY_PATH=/u01/postgres/12.1/pgsql/lib
export PATH=/u01/postgres/12.1/pgsql/bin:$PATH
export MANPATH=/u01/postgres/12.1/pgsql/share/man:$MANPATH
export PGDATA=/u01/postgres/12.1/pgsql/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432

Инициализация и запуск кластера

Для больших систем можно сделать больший размер wal-лога. Для небольших оставить по умолчанию (16 МБ).

# Для больших баз
initdb --data-checksums --wal-segsize=256 --pgdata=/u01/postgres/12.1/pgsql/data --encoding=utf8 --username=postgres --pwprompt
 
# Для небольших баз
initdb --data-checksums  --pgdata=/u01/postgres/12.1/pgsql/data --encoding=utf8 --username=postgres --pwprompt

Конфиругирование параметров экземпляра.

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
# Other connections
host    all             all             0.0.0.0/0               md5
 

postgresql.conf

Ниже приводятся только изменённые параметры

# - Connection Settings -
 
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 1000                  # (change requires restart)
superuser_reserved_connections = 10     # (change requires restart)
 
authentication_timeout = 10s            # 1s-600s
 
# - Memory -
 
shared_buffers = 2GB                    # min 128kB
                                        # (change requires restart)
huge_pages = off                        # on, off, or try
                                        # (change requires restart)
temp_buffers = 32MB                     # min 800kB
 
work_mem = 64MB                                # min 64kB
maintenance_work_mem = 64MB             # min 1MB
 
# - Settings -
 
wal_level = replica                     # minimal, replica, or logical
                                        # (change requires restart)
fsync = on                              # flush data to disk for crash safety
                                        # (turning this off can cause
                                        # unrecoverable data corruption)
wal_sync_method = fsync
 
max_wal_size = 4GB
min_wal_size = 512MB
 
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/%f && cp %p /u01/postgres/12.1/pgsql/pg_wal_archive/%f'            # command to use to archive a logfile segment
 
restore_command = 'cp /u01/postgres/12.1/pgsql/pg_wal_archive/%f %p'            # command to use to restore an archived logfile segment
 
random_page_cost = 3.0                  # same scale as above
 
hot_standby = on
 
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
 
# - Where to Log -
 
log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
 
# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
 
# These are only used if logging_collector is on:
log_directory = 'log'                   # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 100MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
 
log_min_messages = info
log_min_error_statement = error
log_checkpoints = on
log_lock_waits = off                    # log lock waits >= deadlock_timeout
log_statement = 'ddl'                   # none, ddl, mod, all
log_replication_commands = on
log_timezone = 'Europe/Moscow'
 
# - Query and Index Statistics Collector -
 
track_activities = on
track_counts = on
track_io_timing = on
 
autovacuum = on                 # Enable autovacuum subprocess?  'on'
 
autovacuum_vacuum_scale_factor = 0.02   # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01  # fraction of table size before analyze
 
# - Locale and Formatting -
 
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'Europe/Moscow'
 
 
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'                     # locale for system error message
                                        # strings
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
 
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
 
# - Shared Library Preloading -
 
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
 
 
 

Настройка регулярных заданий (crontab)

Используются три основных скрипта.

# Postgres pg_dump
# Необходимо добавить в скрипт в PG_DATABASES список баз данных, для которых необходим дамп
0 23 * * * /u01/postgres/scripts/backup_pg_dump.sh
 
# Резервная копия средствами pg_basebackup
20 23 * * * /u01/postgres/scripts/backup_pg_basebackup.sh
 
# Удаление старых логов Posgres
0 * * * * /u01/postgres/scripts/remove_old_pg_logs.sh
 

Создание необходимых директорий

mkdir /u01/postgres/scripts
mkdir -p /u01/postgres/backup/pg_dump
mkdir -p /u01/postgres/backup/pg_basebackup

/u01/postgres/scripts/backup_pg_dump.sh

В PG_DATABASES необходимо добавлять через пробел список баз данных, для которых требуется создавать дамп

#!/bin/bash
 
source /u01/postgres/.profile
 
PG_DATABASES=(postgres mse)
BACKUP_PATH=/u01/postgres/backup/pg_dump
DUMP_FILE=$BACKUP_PATH/dump_`date +%Y%m%d`.dump
LOG_FILE=$BACKUP_PATH/dump_`date +%Y%m%d`.log
 
for PG_DB_NAME in ${PG_DATABASES[*]}
  do
    DUMP_FILE=$BACKUP_PATH/dump_${PG_DB_NAME}_`date +%Y%m%d`.dump
    LOG_FILE=$BACKUP_PATH/dump_${PG_DB_NAME}_`date +%Y%m%d`.log
    date &> ${LOG_FILE}
    echo "Dumping database ${PG_DB_NAME}" &>> ${LOG_FILE}
    echo &>> ${LOG_FILE}
    pg_dump -v -F c -f ${DUMP_FILE} ${PG_DB_NAME} &>> ${LOG_FILE}
  done;
 
 
find ${BACKUP_PATH} -name "*.dump"  -type f -mtime +3 -print
find ${BACKUP_PATH} -name "*.dump"  -type f -mtime +3 -delete
 

/u01/postgres/scripts/backup_pg_basebackup.sh

#!/bin/bash
 
source /u01/postgres/.profile
 
BACKUP_PATH=/u01/postgres/backup/pg_basebackup
BACKUP_SUBPATH=`date +%Y%m%d`
LOG_FILE=$BACKUP_PATH/base_backup_`date +%Y%m%d`.log
 
mkdir ${BACKUP_PATH}/${BACKUP_SUBPATH}
date &> ${LOG_FILE}
echo "Making base backup of ${PGDATA}" &>> ${LOG_FILE}
echo &>> ${LOG_FILE}
 
pg_basebackup -D ${BACKUP_PATH}/${BACKUP_SUBPATH} --username postgres --wal-method=fetch --gzip --format=tar --no-verify-checksums
 
find ${BACKUP_PATH}/* -name "*.tar.gz"  -type f -mtime +7 -print
find ${BACKUP_PATH}/* -name "*.tar.gz"  -type f -mtime +7 -delete
 

/u01/postgres/scripts/remove_old_pg_logs.sh

#!/bin/bash
 
source /u01/postgres/.profile
 
find $PGDATA/log -name "*.log"  -type f -mtime +3 -delete
 

Создание пользовательской базы данных

Всячески призываю всех создавать отдельные базы данных и отдельные схемы под приложения.

Практика размещения пользовательских данных в схеме public создаёт проблемы с безопасностью, т.к. в public имеют доступ все.

# В psql
postgres@postgres=# create user covidmob with encrypted password '******';
postgres@postgres=# CREATE DATABASE covidmob OWNER covidmob;
postgres@postgres=# \c covidmob postgres
You are now connected to database "covidmob" as user "postgres".
postgres@covidmob=# create extension pg_stat_statements;
postgres@covidmob=# create schema covidmob AUTHORIZATION covidmob;
postgres@covidmob=# GRANT CONNECT ON DATABASE covidmob to covidmob;
postgres@covidmob=# GRANT USAGE ON SCHEMA covidmob to covidmob;
Автор: на Комментариев нет:

Обновление Postgres 12.1

 

Подготовительные действия:
1. Выключаем экземпляр БД 12.1 предварительно посмотрев параметр

SHOW wal_segment_size;

2. Кладем в /u01/distr дистрибутив 13.2

3. Ставим по инструкции как написано в тут
Но не делаем шаги:
- Необходимые пакеты
- Настройка параметров ядра
- Лимиты
- Параметры монтирования

4. Что делаем но с правками: - Создание необходимых директорий

mkdir -p /u01/postgres/13.2/pgsql/data
chown -R postgres:postgres /u01/postgres/13.2/


5. Cборка Postgres 13.2 из исходников

cd /u01/distr
tar xvzf postgresql-13.2.tar.gz
cd ./postgresql-13.2
./configure --prefix=/u01/postgres/13.2/pgsql 
make -C /u01/distr/postgresql-13.2 world
make -C /u01/distr/postgresql-13.2 install-world
chown -R postgres:postgres /u01/postgres/13.2/


6. Правка сервиса systemd
vim /etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
 
[Service]
User=postgres
ExecStart=/u01/postgres/13.2/pgsql/bin/postgres -D /u01/postgres/13.2/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
 
[Install]
WantedBy=multi-user.target

7. Перечитаем конфигурацию systemd Обязательно

systemctl daemon-reload

8. Правка профиля пользователя postgres
Здесь важно не забыть поменять пути до bin постгресовых
иначе кластер будет иницилизироваться старой версии но по новому пути
вобщем меняем везде
vim  /u01/postgres/.profile

export LD_LIBRARY_PATH=/u01/postgres/13.2/pgsql/lib
export PATH=/u01/postgres/13.2/pgsql/bin:$PATH
export MANPATH=/u01/postgres/13.2/pgsql/share/man:$MANPATH
export PGDATA=/u01/postgres/13.2/pgsql/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432

9. Инициализация и запуск кластера  делаем из под su -l postgres

Проверяем версию initdb

initdb --version
если ответ 13.2 все ок можно инициализировать (ну или на какую версию вы обновляетесь)

Для того, что бы понять какой у нас размер wal сегмента мы ранее выполняли команду

Далее либо:

Для больших систем можно сделать больший размер wal-лога. Для небольших оставить по умолчанию (16 МБ).
# Для небольших баз
initdb --data-checksums  --pgdata=/u01/postgres/13.2/pgsql/data --encoding=utf8 --username=postgres --pwprompt
# Для больших баз
initdb --data-checksums --wal-segsize=256 --pgdata=/u01/postgres/13.2/pgsql/data --encoding=utf8 --username=postgres --pwprompt

С подготовлениями закончили, теперь само обновление.
Обновление на 13.2 с копированием содержимого data в новый экземляр *(дальше будет пояснение)
Оба экземпляра БД 12.1 и 13.2 должны быть выключены

В режиме check проверяем все ли у нас ок.
Везде ответы должны быть, что ок. Если что то не так, обязательно напишет.
(Отступы и кавычки исторически менялись пару раз от версии к версии, о том что актуально в настоящий момент можно почитать к версии на которую обновляемся в утилите pg_upgrade)

/u01/postgres/13.2/pgsql/bin/pg_upgrade \
 --old-datadir "/u01/postgres/12.1/pgsql/data" \
 --new-datadir "/u01/postgres/13.2/pgsql/data" \
 --old-bindir "/u01/postgres/12.1/pgsql/bin" \
 --new-bindir "/u01/postgres/13.2/pgsql/bin" \
 --old-options "-c config_file=/u01/postgres/12.1/pgsql/data/postgresql.conf" \
 --new-options "-c config_file=/u01/postgres/13.2/pgsql/data/postgresql.conf" \
 --check

Если все ок, то можем обновляться

(убираем check)

/u01/postgres/13.2/pgsql/bin/pg_upgrade \
 --old-datadir "/u01/postgres/12.1/pgsql/data" \
 --new-datadir "/u01/postgres/13.2/pgsql/data" \
 --old-bindir "/u01/postgres/12.1/pgsql/bin" \
 --new-bindir "/u01/postgres/13.2/pgsql/bin" \
 --old-options "-c config_file=/u01/postgres/12.1/pgsql/data/postgresql.conf" \
 --new-options "-c config_file=/u01/postgres/13.2/pgsql/data/postgresql.conf" \
 --check

Как только обновление завершилось, будет предложено провести analyze нового кластера и удаление старого.
Не удаляем старый.

Запускаем новый 13.2

systemctl start postgresql.service

чекаем в процессах или статусе systemd или в psql, что работает новая версия

ps auxf | grep postgres
 
psql -c "SELECT version();"

На новом кластере нет никакой статистики. Нужно запустить ANALYZE по кластеру

./analyze_new_cluster.sh

Может так получится что наш кастомный postgresql.conf и pg_hba.conf после обновления станут стандартными.
Правим все из старых в новые. Рестартуем PG.

*Пояснение к обновлению
У меня указано обновление по сути с переносом data в новый кластер.
Тоесть потребуется место по размеру как уже существующая БД.
Но можно с помощью параметра --link сделать жесткую ссылку на data старого кластера это будет без копирования data

При режиме --link
при обновлении будет нотка(для отката)
If you want to start the old cluster, you will need to remove
the ".old" suffix from /u01/postgres/12.1/pgsql/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.


Так же есть параметр --clone но мне он не совсем понятен пока.
Вот описание.
(Использовать эффективное клонирование файлов (в ряде систем это называется «reflink») вместо копирования файлов в новый кластер. В результате файлы данных могут копироваться практически мгновенно, как и с использованием -k/--link, но последующие изменения не будут затрагивать старый кластер.)

Автор: на Комментариев нет:

Залипший archive wal log

 На версии 12.

бывает что залипает архивирование wal логов. Логи онлайновые в pg_wal начинают копиться.
В логах Postgres будет примерно такое сообщение об ошибках.

2021-03-06 09:54:40.319 MSK [9448] LOG:  archive command failed with exit code 1
2021-03-06 09:54:40.319 MSK [9448] DETAIL:  The failed archive command was: test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094 && cp pg_wal/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094
2021-03-06 09:54:40.319 MSK [9448] WARNING:  archiving write-ahead log file "00000001000034F400000094" failed too many times, will try again later

Что бы этого избежать, нужно создать временную директорию для перекладывания логов

archive_command = 'rm -rf /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/* && cp %p /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/%f && test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/%f && mv /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/%f /u01/postgres/12.1/pgsql/pg_wal_archive/%f'
 
Предварительно создав временную папку:
 
/u01/postgres/12.1/pgsql/pg_wal_archive_tmp

Файл создается в временной директории, а потом перемещается в pg_wal_archive


Но иногда даже после создания временной директории может случится такая ошибка.
Все равно залипает архивация. Главное удалить косячный архив лог(не в коем случае не !wal!)

2021-03-06 10:19:47.152 MSK [13137] LOG:  archive command failed with exit code 1
2021-03-06 10:19:47.152 MSK [13137] DETAIL:  The failed archive command was: rm -rf /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/* && cp pg_wal/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/00000001000034F400000094 && test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094 && mv /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094
2021-03-06 10:19:47.152 MSK [13137] WARNING:  archiving write-ahead log file "00000001000034F400000094" failed too many times, will try again later

После
этого архивирование должно работать ок. Но обязательно нужно попросить
ответсвенного за РК , что бы был сделан Full backup т.к система резервного копирования может ругаться на пропуск 
инкрементальных
бекапов - PostgreSQL Database: [~Archive Log files are missing. Please run a FULL backup~] Data Backup Failed.





СРК
видит, что образовался пробел в  архив логах на опр момент времени.

Поэтому нужно запустить fullbackup. Дальше инкрементальные бекапы будут
создаваться в штатном режиме.
Автор: на Комментариев нет:
Ярлыки: ,

Настройка памяти в PostgreSQL

Обсуждение

Я не претендую на истину, так как мнения коллег очень расходятся.



Возьмем очень условный пример для нашей среды.
Предположим. 8 ГБ, 4 ЦПУ, 300 коннектов к бд.

Если воспользоваться pgtune автор инструмента считает почти по формулам, которые описаны в офф вики постгреса.

Вот как он считает память. Рассмотрим основное.
Но тут не соблюдается условие 1гб под ОС.

max_connections = 300
work_mem = 1747kB
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
 

 

Мы задаем лимит для потсгреса в 7гб(7168MB/7340032KB) памяти в limits.conf
Теперь работает с тем, что есть.

max_connections(тут уже cколько мы планируем предполагаем)

Параметр max_connections устанавливает максимальное количество клиентов, которые могут подключиться к PostgreSQL. Поскольку для каждого клиента требуется выделять память (work_mem), то этот параметр предполагает максимально возможное использование памяти для всех клиентов. Как правило, PostgreSQL может поддерживать несколько сотен подключений, но создание нового является дорогостоящей операцией. Поэтому, если требуются тысячи подключений, то лучше использовать пул подключений (отдельная программа или библиотека для продукта, что использует базу). - PGbouncer к примеру.

work_mem

work_mem параметр определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др. Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.
 
Т.к мы не знаем сколько у нас сортировок/агрегаций в одном запросе то рекумендуется в качестве начального значения для параметра можно взять 2–4% доступной памяти.
 
так же есть мнение от leopard
Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ
 
ВАЖНО!
общее количество
памяти, которое может быть выделено серверным процессам
определяется как work_mem * max_connections.
 
тоесть если мы берем
work_mem = 1747kB * 300 активных коннектов то получаем примерно 512МБ расход памяти на все 300 подключение в которых происходит сложный запрос.
 
еще пример с ruhighload.com
Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной
 
мои выводы и наблюдения.
что к базе не бывает никогда такого открытого кол-ва коннектов до "потолка" , если смотреть в графану то есть минимальное значение и среднее за минуту.
обычно до 100
значит можно
начать с стандарта для веб приложений что делают другие дба в 32/64MB
при этом тоже наблюдение..
Если в логах или explain видно, что создаются временные файлы для 
для внутренних операций сортировки и хеш-таблиц
То стоит увеличить в 2-3 раза это значение. После изменения этого параметра может дать большой прирост в производительности.
 
у нас же он 128МБ и 1000 коннектов.
Если реально столько будет соединений то база ляжет(?)

shared_buffers

25% от памяти сервера
PostgreSQL не читает данные напрямую с диска и не пишет их сразу на диск. Данные загружаются в общий буфер сервера, находящийся в разделяемой памяти, серверные процессы читают и пишут блоки в этом буфере, а затем уже изменения сбрасываются на диск.
shared_buffers = 7168/100*25=1792MB

effective_cache_size

Этот параметр не влияет на размер разделяемой памяти, выделяемой Postgres Pro, и не задаёт размер резервируемого в ядре дискового кеша; он используется только в качестве ориентировочной оценки.
Не выделяет память, это лишь указание оптимизатору запросов о количестве оперативной памяти используемой в ОС для кэша файловой системы.
Что нашел:
1/2 стандартная настройка. Планировщик будет сам определять как ему лучше поступить.
3/4 ( более 50% )от памяти выделенной ПГ. - будут чаще использоваться индексы
effective_cache_size = 7/4*3 = 5,25 (5GB)

maintenance_work_mem

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.
 
Есть мнение
от aka leopard(pgtune)
Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить не возможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 MB
Есть еще мнение что 
стоит сделать 10% от памяти сервера
 
увидел в гитхабе одного ДБА
т.е

7168 память под ПГ - 10% = 716.8 ˜ = 700 MB
Автор: на Комментариев нет:
Подписаться на: Сообщения (Atom)