📝 Procedimento | Melhores práticas de banco de dados - Postgre

Melhores práticas de banco de dados postgre

Objetivo:

Definir as melhores práticas para performance em um banco de dados postgre

Explicação:

Iremos definir as melhores práticas no banco de dados e no sistema operacional

Linux 2.6 e versões posteriores:

Para definir o "NOOP" na inicialização, adicione a opção elevator na linha do kernel no arquivo /etc/grub.conf:

elevator=noop

• Não recomendamos definir manualmente a opção elevator para um disco, pois seria necessário realizar esse ajuste sempre que um novo disco ou dispositivo for adicionado ao sistema operacional.
• Adicione a seguinte linha ao arquivo de configuração do bootloader grub.conf:

iommu=soft elevator=noop apm=off transparent_hugepage=never powersaved=off

Parâmetros de Swap e Página em VMs:
Configure o parâmetro de "swappiness" da VM para 0 no arquivo /etc/sysctl.conf.

# sysctl -w vm.swappiness=0

O controle de swappiness define quão agressivamente o kernel troca páginas de memória. Valores mais altos aumentam a agressividade, enquanto valores mais baixos reduzem a quantidade de troca. Um valor de 0 instrui o kernel a não iniciar a troca até que a quantidade de páginas livres e baseadas em arquivos seja menor que o limite superior (high-water mark) de uma zona.

• Permita que o kernel do Linux gerencie melhor a sobrecarga de memória (memory overcommit):

vm.overcommit_memory = 1

• Configure o mecanismo de hugepages para permitir que o kernel do Linux utilize os recursos de múltiplos tamanhos de página disponíveis em arquiteturas modernas de hardware:

vm.nr_hugepages=Configurar o mesmo valor de memória do buffer do banco de dados para a memória máxima da VM.

• Porcentagem da memória total disponível que contém páginas livres e páginas recuperáveis; o número de páginas a partir do qual os threads de limpeza em segundo plano do kernel começam a gravar os dados sujos no disco:

vm.dirty_background_ratio = 5

• Contains, as a percentage of total available memory containing free pages and reclaimable pages, the number of pages at which a process that generates disk writes starts writing out dirty data:

vm.dirty_ratio = 15

• Quando os dados sujos são antigos o suficiente para serem elegíveis para os threads de limpeza do kernel escreverem no disco:

vm.dirty_expire_centisecs = 500

• Com que frequência os threads de limpeza do kernel acordam periodicamente para escrever dados antigos no disco:

vm.dirty_writeback_centisecs=100

Tamanho Máximo de I/O no Linux
O tamanho máximo de I/O padrão definido por Max_Sectors_KB restringe o maior tamanho de I/O que o sistema operacional emite para um dispositivo de bloco. O tamanho do I/O gerado depende do agendador (elevator) que você usa, do driver e do tipo de I/O emitido pelas suas aplicações. No entanto, leituras e gravações grandes geralmente acontecem no tamanho máximo de I/O.

Nota: O tamanho máximo de I/O do Linux se aplica principalmente às versões do kernel 4 ou superiores. Verifique no seu kernel.

O UDEV (espaço de usuário /dev) pode garantir que todos os dispositivos de bloco conectados à sua VM tenham o mesmo tamanho máximo de I/O consistente aplicado, mesmo que os dispositivos sejam conectados dinamicamente. Para isso, crie o arquivo 71-block-max-sectors.rules em /etc/udev/rules.d/ com a seguinte linha:

ACTION=="add|change", SUBSYSTEM=="block", RUN+="/bin/sh -c '/bin/echo 1024 > /sys%p/queue/max_sectors_kb'"

Se você não tiver o UDEV em sua distribuição, pode usar o rc.local como alternativa e adicionar o seguinte comando:

echo 1024 > /sys/block/sd?/queue/max_sectors_kb

Esse exemplo requer um reinício.
Nota: Sem o UDEV, você precisará alterar o valor de max_sectors_kb em cada dispositivo de disco do seu sistema operacional.

Dicas Adicionais de Configuração do SO 

• Para redes de alto desempenho, aumente as filas de recebimento e transmissão de rede. Adicione a seguinte linha no rc.local:

/sbin/ethtool -G ethX rx 4096 tx 4096

• Para sistemas de banco de dados de alto desempenho, adicione as seguintes opções à configuração do bootloader (grub), além das listadas acima (PVSCSI necessário):

vmw_pvscsi.cmd_per_lun=254 vmw_pvscsi.ring_pages=32

• No Windows, use o seguinte comando para ajustar a fila do sistema operacional:

REG ADD HKLM\SYSTEM\CurrentControlSet\services\pvscsi\Parameters\Device
/v DriverParameter /t REG_SZ /d "RequestRingPages=32,MaxQueueDepth=254"

• Adicione a opção -x em /etc/sysconfig/ntpd na configuração do servidor de tempo:

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

• Garanta que a política do firewall e as configurações do SELinux permitam a comunicação com as portas do banco de dados.

File System

Para maximizar o desempenho e a escalabilidade do seu banco de dados no Linux, recomendamos o uso do Logical Volume Manager (LVM). O LVM cria um volume lógico que agrega múltiplos discos para distribuir leituras e gravações de maneira eficiente. Além disso, ele funciona como um volume escalável para manutenção.

Recomendações ao utilizar LVM:

• Ao usar o LVM, recomendamos striping de volumes (não concatene) sobre múltiplos discos. Utilize o comando lvs --segments para verificar se você está usando striping ou concatenação.
• Mantenha os volumes lógicos (LVs) e os volumes físicos (PVs) para arquivos de dados separados dos LVs e PVs usados para logs de rede e logs arquivados.

Exemplo de processo para criar um LV para volume de dados do PostgreSQL:

Identifique os dispositivos de disco:
Suponha que você tenha criado quatro vDisks e os tenha apresentado ao sistema operacional. O Linux os reconhecerá como sdb, sdc, sdd e sde. Para identificar os dispositivos, use o

fdisk -l

• Crie o volume físico para todos esses dispositivos:

pvcreate /dev/sdb /dev/sdc /dev/sdd /dev/sde

• Crie o grupo de volumes (VG) a partir desses PVs:

vgcreate pgDataVG /dev/sdb /dev/sdd /dev/sdc /dev/sde

• Crie o LV para este VG:

lvcreate -l 100%FREE -i4 -I1M -n pgDataLV pgDataVG

• O comando usa todo o espaço do VG para o LV e nomeia o LV como pgDataLV.
• No comando anterior, altere -i4 para o número de discos no seu VG. Se você tiver oito discos, use -i8. Esse parâmetro distribui o LV entre todos os discos do VG.

Dicas adicionais de LV:

• Tenha pelo menos dois LVs: um para arquivos de dados e outro para arquivos de logs.
• Tenha quatro vDisks em cada LV. O tamanho dos vDisks depende do tamanho e design do seu banco de dados. Como padrão, inicie com vDisks de 50 GB para dados e 10 GB para logs.

• Desative a leitura antecipada para cada LV:

lvchange -r 0 /dev/pgDataVG/pgDataLV

• Crie o sistema de arquivos:

mkfs.ext4 -b 4k /dev/pgDataVG/pgDataLV

• XFS pode proporcionar uma melhoria significativa de desempenho, especialmente para bancos de dados de processamento analítico online (OLAP). No entanto, muitos clientes preferem usar EXT4, pois é estável, confiável e compatível com versões antigas do Linux. Se optar por usar o XFS, utilize a opção de montagem descrita abaixo.

Montagem do LV:

Se você criou um diretório em /pgsql/data, use as seguintes opções para montar o LV no diretório (certifique-se de que estão no arquivo /etc/fstab para efeito permanente):

• Para EXT4:

mount -o noatime,barrier=0 /dev/pgDataVG/pgDataLV /pgsql/data

• Para XFS:

mount -o inode64,noatime,logbufs=8 /dev/pgDataVG/pgDataLV /pgsql/data

Recomendações adicionais:

• Ao formatar um disco no Windows, use o tamanho de unidade de alocação de 4.096 para os discos de dados e logs do PostgreSQL. No Windows, use pontos de montagem em vez de letras de unidade.
• O equivalente do Windows para LVs são os storage spaces. Para bancos de dados maiores, considere explorar o uso de storage spaces.
• Distribua todos os discos associados a múltiplos LVs entre múltiplos adaptadores PVSCSI (não necessário para AHV). O design abaixo exemplifica dois grupos de LVs, adequados para atender aos requisitos de desempenho de bancos de dados de médio a grande porte.

PostgreSQL

Recomendações gerais para bancos de dados PostgreSQL:

1. Alta disponibilidade: Utilize alta disponibilidade no hipervisor sempre que possível.
2. Clustering: Use clustering do PostgreSQL, se necessário.
3. Automação do VMware DRS: Mantenha a automação do VMware DRS no nível padrão (3).
4. Gerenciamento de vizinhos barulhentos: Deixe o hipervisor e o DRS gerenciarem vizinhos barulhentos e picos de carga.
5. Configuração de alta disponibilidade: Utilize pelo menos n + 1 para a configuração de alta disponibilidade.
6. Regras de antiafinidade: Use regras de antiafinidade no hipervisor para separar nós do cluster, VMs de aplicativos e VMs de banco de dados.
7. Política de controle de admissão: Utilize uma política de controle de admissão baseada em percentual para ambientes VMware.
8. Prioridades de reinício de VMs: Revise as prioridades de reinício das VMs do PostgreSQL.

Instalação do PostgreSQL:

• Para baixar e instalar a versão de código aberto do PostgreSQL, visite o site oficial do PostgreSQL.
• Para instalar e configurar a versão empresarial, visite o site da EnterpriseDB.

Arquivos de dados do PostgreSQL:

• Os arquivos de dados do PostgreSQL (localizados no diretório pgdata) e os arquivos de write-ahead logging (WAL), localizados no diretório pg_wal, são geralmente configurados durante a instalação.
• Nota: No PostgreSQL versão 9.5 e anteriores, o diretório contendo os logs é nomeado pg_xlog. A partir do PostgreSQL versão 10, ele passa a ser chamado pg_wal.

Configurações recomendadas no arquivo postgresql.conf:

O arquivo postgresql.conf, localizado no diretório de dados, armazena parâmetros que influenciam o comportamento e o desempenho do sistema de banco de dados PostgreSQL. Utilize as configurações otimizadas conforme a tabela a seguir como guia. Revise cada configuração para avaliar sua aplicabilidade ao seu cenário de banco de dados e os efeitos dos valores definidos pelo usuário.

Parâmetros relacionados ao logging:

Para mais informações sobre os parâmetros de logging do PostgreSQL, consulte a seção Error Reporting and Logging da documentação do PostgreSQL. Error Reporting and Logging.