Database otimizações / fine tuning

Afinar o seu banco de dados parâmetros para extrair o máximo sair dela!

Olá,

Estas são algumas das minhas experiências enquanto eu estava construindo um motor de busca e optmising o postgresql para sónica-database velocidades!

Nossa configuração para o servidor Postgresql foi:
RedHat 7.2
PIV 2,00 Ghz sistema
1024MB RAM

Uma das primeiras coisas que notei depois de ligar o Servlet programa, que foi embora consultas foram devolvidos quase tão rápido como o MySQL a partir da base no sistema anterior, a carga sobre o servidor era muito maior. Então eu comecei a descer em profundidade os detalhes das coisas. Tive otimizada MySQL antes, aumentando cache e buffer tamanhos e jogando por mais ram para o problema. A única coisa mais importante que um tem que fazer antes de executar o Postgresql, é suficiente para proporcionar espaço compartilhado tampão. Mas então,
Quanto é suficiente?
Há um debate acalorado sobre ele, entre as pessoas que dizem que logicamente toda a RAM poderia ser consagrado como contra aqueles que dizem que jogando mais RAM depois de um certo limite, não tem qualquer utilidade. Quanto mais memória cache partilhada tiver, maior será a porcentagem de seu banco de dados que não provoca nenhuma lido () 's nem ósmio a partir da cópia de memória buffer cache.But global, você vai cache um número menor de blocos, porque você vai ser buffering-lo duas vezes . Quando você copia um bloco a partir do buffer de memória partilhada OS, o exemplar ainda existente no SO-tampão. Então, esse bloco está agora tamponado por duas vezes. Um único disco I / O é muito mais caro do que entre as centenas de exemplares OS buffer cache e postgres' memória partilhada. Também considerar todas as outras coisas que você está fazendo na máquina - apenas pequenas coisas, e tal como o cron. Tudo o que tem memória. Por isso, é perigoso para não deixar que o SO gerenciar um bom pedaço de memória.
Ocorre que estes dois factores opostos poderia ser plotados e faça um pouco de cada linha. O ideal seria ponto onde eles cruzados.

Além de eu também optimizado consultas SQL adaptados especificamente para o meu propósito. Uma grande desvantagem em PostgreSQL está na implementação da avaliação de consultas que contenham "IN" e "existe". Suponha:
Consulta 1. SELECT * FROM db1 WHERE ID IN ((SELECT id db2 DE ONDE palavra = 'qualquer')) LIMIT 20;
Query 2. SELECT * FROM db1 WHERE ID IN (1234,2345,1242,1256,1245,1567,2222,22345234567456,35,56);

(onde ID é a chave primária)

A posterior consulta é digitalizado usando o índice de ID enquanto a primeira é executado em uma varredura sequencial. Acho que isso é chamado de "piloto de erro" banco de dados onde corre a subconsulta para cada linha no exterior consulta. Em vez disso, se fizermos uma explícita PARA (conforme abaixo), então nós poderíamos força do banco de dados para usar um índice digitalizar vez.
Final Consulta:
select * from db1, db2 a, b db2
onde id = a.id e a.word = 'palavra1'
e id = b.id e b.word = 'word2'
etc

NOTA: Você também pode correr em uma varredura sequencial, em vez de esperar um índice varredura, se o número de tuplas a serem digitalizados são mais do que 30-40% do total de tuplas na tabela. Embora esta possa ser alterada, mudando os pesos atribuídos a random_page_cost, cpu_tuple_cost, cpu_index_cost e cpu_operator_cost utilizado pelo otimizador para fazer essas decesions.

Eu também decidiu que jogar mais memória RAM para o efeito. Eu atribuídos 64MB de memória RAM para o espaço compartilhado. O arquivo / var / lib / pgsql / dados / postgresql.conf contém as definições de servidor de banco de dados. Postgresql sistema utiliza memória partilhada 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 ler sobre uso de memória compartilhada do sistema:
IPCS
O resultado será, em bytes. Por padrão RedHat 7.2 aloca 32MB de memória compartilhada, o que poderia não ser suficiente para postgresql. Eu aumentou esse limite para 64 MB, ao fazer o comando:
echo 67108864> / proc / sys / kernel / shmmax

Você precisa colocar esta linha em seu arquivo postgresql arranque, ou editando o arquivo / etc / rc.d / rc.local arquivo para uma maior permanência em nosso setting.Then eu postgresql.conf set shared_buffers para 8192.I também definir a nossa sort_mem a 16384 (16Megs uma espécie de espaço de memória). Desde pool de conexão foi, com efeito, vou definir max_connections a 50.
Fsync e também foi escolhida para false.

shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false

Um percalço que encontrei inicialmente era de que o sistema teve que construir-se e derrubar um postgresql ligação com cada solicitação. Isto era intolerável, por isso comecei a usar o pool de conexão características fornecidas pela Resin (http://caucho.com).

-----
Varun

Agradecimentos: Curt, Bruce, Andrew et all para limpar minhas dúvidas!