Otimize o PostgreSQL para testes rápidos

Estou mudando para o PostgreSQL do SQLite para um aplicativo típico do Rails.

O problema é que as especificações em execução ficaram lentas com o PG.
No SQLite demorou ~ 34 segundos, no PG é ~ 76 segundos, que é mais do que 2x mais lento .

Então, agora eu quero aplicar algumas técnicas para trazer o desempenho das especificações em par com SQLite sem modificações de código (idealmente apenas definindo as opções de conexão, o que provavelmente não é possível).

Algumas coisas óbvias no topo da minha cabeça são:

  • Disco RAM (boa configuração com RSpec no OSX seria bom de ver)
  • Tabelas sem registro (pode ser aplicado em todo o database para que eu não mude todos os scripts?)

Como você deve ter entendido, eu não me importo com confiabilidade e com o resto (o database é apenas uma coisa descartável aqui).
Eu preciso tirar o máximo proveito do PG e torná-lo o mais rápido possível .

Melhor resposta ideal seria descrever os truques para fazer exatamente isso, configuração e as desvantagens desses truques.

ATUALIZAÇÃO: fsync = off + full_page_writes = off diminuiu apenas o tempo para ~ 65 segundos (~ -16 segundos). Bom começo, mas longe do alvo de 34.

ATUALIZAÇÃO 2: Tentei usar o disco de RAM, mas o ganho de desempenho estava dentro de uma margem de erro. Então, não parece valer a pena.

ATUALIZAÇÃO 3: * Encontrei o maior gargalo e agora minhas especificações são tão rápidas quanto as SQLite.

O problema foi a limpeza do database que fez o truncamento . Aparentemente, o SQLite é muito rápido lá.

Para “consertar”, eu abro uma transação antes de cada teste e o rolo de volta no final.

Alguns números para ~ 700 testes.

  • Truncamento: SQLite – 34s, PG – 76s.
  • Transação: SQLite – 17s, PG – 18s.

Aumento de velocidade de 2x para o SQLite. Aumento de velocidade 4x para PG.

Primeiro, use sempre a última versão do PostgreSQL. As melhorias de desempenho estão sempre chegando, então você provavelmente está perdendo seu tempo se estiver ajustando uma versão antiga. Por exemplo, o PostgreSQL 9.2 melhora significativamente a velocidade do TRUNCATE e, claro, adiciona varreduras somente de índice. Mesmo pequenos lançamentos devem ser sempre seguidos; veja a política de versão .

Não é

NÃO coloque um tablespace em um RAMdisk ou outro armazenamento não durável .

Se você perder um tablespace, todo o database pode estar danificado e ser difícil de usar sem um trabalho significativo. Há muito pouca vantagem para isso em comparação com apenas usando tabelas UNLOGGED e ter muita memory RAM para cache de qualquer maneira.

Se você realmente quer um sistema baseado em disco, initdb um novo cluster no ramdisk initdb uma nova instância do PostgreSQL no ramdisk, assim você tem uma instância do PostgreSQL completamente descartável.

Configuração do servidor PostgreSQL

Ao testar, você pode configurar seu servidor para operação não durável, porém mais rápida .

Este é um dos únicos usos aceitáveis ​​para a configuração fsync=off no PostgreSQL. Essa configuração praticamente diz ao PostgreSQL para não se incomodar com gravações ordenadas ou com qualquer outra coisa desagradável de integridade de dados e proteção contra falhas, dando-lhe permissão para destruir totalmente seus dados se você perder energia ou tiver uma pane no sistema operacional.

Escusado será dizer que você nunca deve ativar o fsync=off na produção, a menos que você esteja usando o Pg como um database temporário para dados que você pode gerar novamente a partir de outro lugar. Se e somente se você estiver fazendo para desligar o fsync, ele também pode desativar o full_page_writes , já que ele não faz mais nada. Tenha em atenção que fsync=off e full_page_writes aplicam-se ao nível do cluster , pelo que afectam todas as bases de dados na sua instância do PostgreSQL.

Para uso em produção, é possível usar synchronous_commit=off e definir um commit_delay , pois você obterá muitos dos mesmos benefícios do fsync=off sem o risco de corrupção de dados. Você tem uma pequena janela de perda de dados recentes se você habilitar o commit asynchronous – mas é isso.

Se você tiver a opção de alterar levemente o DDL, também poderá usar UNLOGGED tabelas UNLOGGED na Pág. 9.1+ para evitar completamente o registro do WAL e obter um aumento real de velocidade com o custo das tabelas sendo apagadas se o servidor travar. Não há opção de configuração para deixar todas as tabelas sem registro, ele deve ser definido durante CREATE TABLE . Além de ser bom para testar isso é útil se você tiver tabelas cheias de dados gerados ou não importantes em um database que contenha coisas que você precisa para estar seguro.

Verifique seus registros e veja se você está recebendo avisos sobre muitos pontos de verificação. Se você é, você deve aumentar seus checkpoint_segments . Você também pode querer ajustar seu checkpoint_completion_target para suavizar as gravações.

Sintonize shared_buffers para ajustar sua carga de trabalho. Isso depende do sistema operacional, depende do que mais está acontecendo com sua máquina e requer algumas tentativas e erros. Os padrões são extremamente conservadores. Você pode precisar aumentar o limite máximo de memory compartilhada do sistema operacional se aumentar o shared_buffers no PostgreSQL 9.2 e abaixo; 9.3 e acima mudaram como eles usam memory compartilhada para evitar isso.

Se você estiver usando apenas algumas conexões que fazem muito trabalho, aumente o work_mem para dar a elas mais memory RAM para jogar, etc. work_mem uma configuração work_mem muito alta pode causar problemas de falta de memory, porque ela é executada. classificar não por conexão, portanto, uma consulta pode ter muitos tipos nesteds. Você só precisa aumentar o work_mem se puder ver os tipos sendo derramados no disco em EXPLAIN ou registrados com a configuração log_temp_files (recomendado), mas um valor mais alto também permite que o Pg escolha planos mais inteligentes.

Como dito por outro autor aqui, é aconselhável colocar o xlog e as tabelas / índices principais em HDDs separados, se possível. Partições separadas são bastante inúteis, você realmente quer unidades separadas. Essa separação tem muito menos benefício se você estiver executando com fsync=off e quase nenhum se estiver usando tabelas UNLOGGED .

Por fim, ajuste suas consultas. Certifique-se de que seu random_page_cost e seq_page_cost reflitam o desempenho de seu sistema, assegure que seu effective_cache_size esteja correto, etc. Use EXPLAIN (BUFFERS, ANALYZE) para examinar planos de consulta individuais e ative o módulo auto_explain para relatar todas as consultas lentas. Com frequência, você pode melhorar o desempenho das consultas criando apenas um índice apropriado ou aprimorando os parâmetros de custo.

AFAIK não há como configurar um database ou cluster inteiro como UNLOGGED . Seria interessante poder fazer isso. Considere perguntar na lista de discussão do PostgreSQL.

Ajuste do SO do host

Há alguns ajustes que você pode fazer no nível do sistema operacional também. A principal coisa que você pode querer fazer é convencer o sistema operacional a não liberar as gravações no disco de forma agressiva, já que você realmente não se importa quando / se elas chegam ao disco.

No Linux, você pode controlar isso com as configurações dirty_* subsistema de memory virtual , como dirty_writeback_centisecs .

O único problema em ajustar as configurações de write-back a ser muito frouxo é que um flush de algum outro programa pode fazer com que todos os buffers acumulados do PostgreSQL sejam liberados também, causando grandes paradas enquanto tudo bloqueia as gravações. Você pode ser capaz de aliviar isso executando o PostgreSQL em um sistema de arquivos diferente, mas alguns streams podem ser no nível do dispositivo ou no nível do host inteiro, não no nível do sistema de arquivos, então você não pode confiar nisto.

Este ajuste realmente requer brincar com as configurações para ver o que funciona melhor para sua carga de trabalho.

Em kernels mais recentes, você pode querer garantir que vm.zone_reclaim_mode seja definido como zero, pois pode causar sérios problemas de desempenho com sistemas NUMA (a maioria dos sistemas atualmente) devido a interações com a forma como o PostgreSQL gerencia shared_buffers .

Consulta e ajuste de carga de trabalho

Estas são coisas que exigem alterações de código; eles podem não se adequar a você. Algumas são coisas que você pode aplicar.

Se você não estiver trabalhando em lotes em transactions maiores, inicie. Muitas transactions pequenas são caras, então você deve fazer lotes sempre que for possível e prático fazê-lo. Se você estiver usando o commit async, isso é menos importante, mas ainda altamente recomendado.

Sempre que possível, use tabelas temporárias. Eles não geram tráfego WAL, então eles são muito mais rápidos para inserções e atualizações. Às vezes vale a pena juntar vários dados em uma tabela temporária, manipulando-a da maneira que você precisa, e então fazendo um INSERT INTO ... SELECT ... para copiá-la para a tabela final. Observe que as tabelas temporárias são por session; se sua session terminar ou você perder sua conexão, a tabela temporária desaparecerá e nenhuma outra conexão poderá ver o conteúdo das tabelas temporárias de uma session.

Se você estiver usando o PostgreSQL 9.1 ou mais recente, poderá usar tabelas UNLOGGED para dados que você pode perder, como o estado da session. Estes são visíveis em diferentes sessões e preservados entre conexões. Eles ficam truncados se o servidor for desligado sem limpeza, para que não possam ser usados ​​para qualquer coisa que você não possa recriar, mas eles são ótimos para caches, visualizações materializadas, tabelas de estado etc.

Em geral, não DELETE FROM blah; . Use TRUNCATE TABLE blah; em vez de; é muito mais rápido quando você está jogando todas as linhas em uma tabela. Trunque muitas tabelas em uma chamada TRUNCATE se puder. Há uma ressalva se você está fazendo muitos TRUNCATES de pequenas tabelas uma e outra vez, no entanto; consulte: Velocidade de truncamento do Postgresql

Se você não tiver índices em foreign keys, as DELETE s envolvendo as chaves primárias referenciadas por essas foreign keys serão terrivelmente lentas. Certifique-se de criar esses índices, se você espera DELETE da (s) tabela (s) referenciada (s). Índices não são necessários para TRUNCATE .

Não crie índices que você não precisa. Cada índice tem um custo de manutenção. Tente usar um conjunto mínimo de índices e permita que as verificações de índice de bitmap as combinem, em vez de manter muitos índices de várias colunas enormes e caros. Onde os índices são necessários, tente preencher a tabela primeiro e, em seguida, crie índices no final.

Hardware

Ter RAM suficiente para manter o database inteiro é uma grande vitória se você conseguir administrá-lo.

Se você não tem RAM suficiente, o armazenamento mais rápido pode ser melhor. Mesmo um SSD barato faz uma enorme diferença sobre a rust girando. Não confie em SSDs baratos para produção, eles geralmente não são seguros contra falhas e podem consumir seus dados.

Aprendendo

O livro de Greg Smith, PostgreSQL 9.0 High Performance, permanece relevante apesar de se referir a uma versão um pouco mais antiga. Deve ser uma referência útil.

Participe da lista de discussão geral do PostgreSQL e siga-a.

Leitura:

  • Ajustando seu servidor PostgreSQL – wiki do PostgreSQL
  • Número de conexões de database – wiki do PostgreSQL

Use layout de disco diferente:

  • disco diferente para $ PGDATA
  • disco diferente para $ PGDATA / pg_xlog
  • disco diferente para arquivos temporários (por database $ PGDATA / base // pgsql_tmp) (veja a nota sobre work_mem)

Ajustes do postgresql.conf:

  • shared_memory: 30% da RAM disponível, mas não mais que 6 a 8 GB. Parece ser melhor ter menos memory compartilhada (2 GB – 4 GB) para cargas de trabalho intensivas de gravação
  • work_mem: principalmente para consultas selecionadas com classificações / agregações. Isso é por configuração de conexão e a consulta pode alocar esse valor várias vezes. Se os dados não couberem, o disco é usado (pgsql_tmp). Marque “explicar analisar” para ver quanta memory você precisa
  • fsync e synchronous_commit: os valores padrão são seguros, mas se você puder tolerar os dados perdidos, poderá desligar.
  • random_page_cost: se você tem SSD ou array RAID rápido você pode diminuir isso para 2.0 (RAID) ou até mesmo menor (1.1) para SSD
  • checkpoint_segments: você pode ir até 32 ou 64 e alterar checkpoint_completion_target para 0.9. Valor mais baixo permite recuperação mais rápida após a colisão