Otimizações de bancos de dados/ ajuste delicado

Ajustar delicadamente os parâmetros de seu banco de dados para extrair o máximo dele!

Olá,

Estas são algumas das minhas experiências, enquanto eu estava construindo um motor de busca e otimizando o banco de dados postgresql para velocidades sônicas!

Nossa configuração para o servidor Postgresql foi:
Redhat 7.2
Sistema PIV 2.00 Ghz
1024 MB RAM

Uma das primeiras coisas que notei depois de ligar o programa Servlet, foi que embora as consultas fossem devolvidas quase tão rapidamente como o anterior sistema baseado em MySQL, a carga no servidor era muito mais alta. Então eu comecei a ir ao âmago das coisas. Tinha otimizado MySQL antes, aumentando bastante a cache e os tamanhos buffer, e jogando mais ram para o problema. A única coisa mais importante que uma pessoa tem que fazer antes de executar o Postgresql, é arranjar suficiente espaço buffer partilhado. Mas depois,

Quanto é suficiente?

Há um debate acalorado sobre isso, entre as pessoas que dizem que logicamente toda a RAM poderia ser consagrada como contra aqueles que dizem que jogar mais RAM depois de um certo limite não tem qualquer utilidade. Quanto mais memória cache partilhada tiver, maior será a percentagem de seu banco de dados que não provoca nenhuma read()'s nem cópia de memória a partir da memória cache OS. Mas na generalidade, você vai colocar em cache um número menor de blocos, porque você os memorizará duas vezes. Um único disco I/O é muito mais caro do que centenas de cópias entre a memória cache OS e a memória partilhada postgres. Também considere todas as outras coisas que você está fazendo na máquina - apenas pequenas coisas, como cron e coisas do gênero. Tudo isso ocupa memória. Por isso, é perigoso não deixar o OS gerenciar um bom pedaço de memória.
Ocorre que estes dois factores opostos poderiam ser organizados e fazer um pouco de cada linha. O ponto ideal seria onde eles se cruzam.

Além disso, eu também otimizei consultas SQL criadas especificamente para o meu propósito. Uma grande desvantagem em PostgreSQL está na implementação da avaliação de consultas que contenham "DENTRO" e "EXISTE". Suponhamos:
Consulta 1. SELECCIONAR * DE db1 ONDE ID DENTRO ((SELECT id FROM db2 ONDE palavra = “qualquer”)) LIMITE 20;
Consulta 2. SELECCIONAR * DE db1 ONDE ID DENTRO IN(1234,2345,1242,1256,1245,1567,2222,22,345,234,567,456,35,56) ;

(onde ID é a chave primária)

A consulta posterior é digitalizada usando o índice de ID enquanto a primeira é executada em uma digitalização sequencial. Acho que isso é chamado de "piloto de erro" em que o banco de dados executa a sub consulta para cada linha no exterior da consulta. Em vez disso, se usarmos JOINS explícitas (como em baixo), então nós poderíamos forçar o banco de dados a usar um índice de digitalização.
Consulta Final:

selecionar * de db1, db2 a, db2 b
onde id = a.id e a.word = “palavra1”
e id = b.id e b.word = “palavra2”
etc.

NOTA: Você também pode executar uma digitalização sequencial, em vez de um índice de digitalização esperado, se os números de variáveis a serem digitalizadas são mais do que 30-40% do total de variáveis na tabela. Embora esta possa ser variada mudando os pesos atribuídos a random_page_cost, cpu_tuple_cost, cpu_index_cost e cpu_operator_cost utilizados pelo otimizador para fazer essas decisões.

Eu também decidi jogar mais memória RAM para o efeito. Atribuí 64 MB de memória RAM para o espaço de memória compartilhado. O arquivo/var/lib/pgsql/dados/postgresql.conf contém as definições para o servidor de banco de dados. O Postgresql utiliza um sistema de memória compartilhada como um buffer. Em um sistema Linux, você pode ver quanta memória partilhada foi atribuída pelo seu sistema executando o comando:
cat/proc/sys/kernel/shmmax

E para ver a utilização da memória compartilhada do sistema:
ipcs
O resultado será em bytes. Por defeito RedHat 7.2 atribui 32 MB de memória compartilhada, o que poderia não ser suficiente para postgresql. Eu aumentei esse limite para 64 MB fazendo o comando:
echo 67108864 > /proc/sys/kernel/shmmax

Você precisa colocar esta linha em seu arquivo de arranque postgresql, ou editando o arquivo /etc/rc.d/ rc.local para definições mais permanentes. Então em nosso postgresql.conf defini buffers_partilhados para 8192. Também defino a nossa mem_separação para 16384 (16 Megas para uma espécie de espaço de memória). Visto que a ligação pooling estava em efeito, defini ligações_máx. para 50.
E fsync foi também definido para falso.

buffers_partilhados = 8192
mem_separação = 16384
ligações_máx. = 50
fsync = falso

Um percalço que encontrei inicialmente foi que o sistema teve de construir e derrubar uma ligação postgresql com cada solicitação. Isto era intolerável, por isso comecei a usar as funcionalidades de ligação pooling de conexão fornecida pela Resin (http://caucho.com).