Ajuste de desempenho PostGresql para uma execução de consulta mais rápida

Objetivo

Nosso objetivo é fazer uma execução de consulta dummy executada mais rapidamente no banco de dados PostGresql usando apenas as ferramentas embutidas disponíveis
no banco de dados.

Sistema operacional e versões de software

• Sistema operacional: Red Hat Enterprise Linux 7.5
• Programas: PostgreSQL Server 9.2

Requisitos

Instalação da base do servidor PostgreSQL. Acesso à ferramenta de linha de comando PSQL e propriedade do banco de dados de exemplo.

Convenções

• # - requer que os comandos Linux sejam executados com privilégios root diretamente como usuário root ou por uso de sudo comando
• $ - dados os comandos do Linux a serem executados como um usuário não privilegiado regular

Introdução

PostgreSQL é um banco de dados de código aberto confiável disponível no repositório de muitas distribuição moderno. A facilidade de uso, a capacidade de usar extensões e a estabilidade que ele fornece tudo adicionado à sua popularidade.
Enquanto fornece a funcionalidade básica, como responder às consultas SQL, armazenar dados inseridos de forma consistente, lidar com transações, etc. a maioria das soluções de banco de dados maduras fornecem ferramentas e knows sobre como
Sintonize o banco de dados, identifique possíveis gargalos e possa resolver problemas de desempenho vinculados à medida que o sistema alimentado pela solução fornecida cresce.

PostgreSQL não é exceção e neste
guia vamos usar a ferramenta embutida explicar Para fazer uma consulta de corrida lenta completa mais rápida. Está longe de ser um banco de dados do mundo real, mas pode -se levar a dica sobre o uso das ferramentas embutidas. Usaremos um servidor PostgreSQL versão 9.2 no Red Hat Linux 7.5, mas as ferramentas mostradas neste guia estão presentes em bancos de dados muito mais antigos e versões do sistema operacional também.

O problema a ser resolvido

Considere esta tabela simples (os nomes das colunas são auto-explicativos):

Foobardb =# \ d+ Tabela de funcionários "Public.funcionários "Coluna | Tipo | Modificadores | Armazenamento | Estatísticas Alvo | Descrição ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- EMP_ID | NUMERIC | NÃO NULL PADRÃO NEXTVAL ('FUNCIONÁRIO_SEQ' :: regclass) | principal | | primeiro_name | text | não nulo | estendido | | last_name | text | não nulo | estendido | | birth_year | numeric | não nulo | main | birth_month | numérico | não nulo | principal | birth_dayofmonth | numérico | NÃO NULL | PRINCIPAL | 

Com registros como:

Foobardb =# Selecione * From Funcionários Limite 2; EMP_ID | primeiro_name | Último nome | nascimento_year | nascimento_month | birth_dayofmonth --------+------------+-----------+------------+- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

Neste exemplo, somos a boa empresa e implantamos um aplicativo chamado HBAPP que envia um e -mail de "feliz aniversário" para o funcionário em seu aniversário. O aplicativo consulta o banco de dados todas as manhãs para encontrar os destinatários do dia (antes do horário de trabalho, não queremos matar nosso banco de dados de RH fora de bondade).
O aplicativo executa a seguinte consulta para encontrar os destinatários:

Foobardb =# Selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIRVH_DAYOFMONTH = 20; EMP_ID | primeiro_name | Último nome --------+------------+----------- 1 | Emily | James 

Tudo funciona bem, os usuários recebem seu e -mail. Muitos outros aplicativos usam o banco de dados e a tabela de funcionários dentro, como contabilidade e bi. A boa companhia cresce e aumenta a mesa dos funcionários. Com o tempo, o aplicativo dura muito tempo e a execução se sobrepõe ao início do horário de trabalho, resultando em tempo de resposta do banco de dados lento em aplicações de missão crítica. Temos que fazer algo para tornar essa consulta mais rápida, ou o aplicativo não será empregado, e com ela haverá menos gentileza em boa companhia.

Para este exemplo, não usaremos ferramentas avançadas para resolver o problema, apenas um fornecido pela instalação base. Vamos ver como o planejador de banco de dados executa a consulta com explicar.

Não estamos testando em produção; Criamos um banco de dados para teste, criamos a tabela e inserimos dois funcionários mencionados acima. Usamos os mesmos valores para a consulta o tempo todo neste tutorial,
Então, em qualquer corrida, apenas um recorde corresponderá à consulta: Emily James. Então executamos a consulta com precedente Explique analisar Para ver como é executado com dados mínimos na tabela:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- --- Seq Scan nos funcionários (custo = 0.00… 15.40 linhas = 1 largura = 96) (tempo real = 0.023… 0.025 linhas = 1 loops = 1) filtro: ((birth_month = 3 :: numeric) e (birth_dayofmonth = 20 :: numeric)) linhas removidas pelo filtro: 1 tempo de execução total: 0.076 ms (4 linhas) 

Isso é muito rápido. Possivelmente tão rápido quanto era quando a empresa implantou o HBAPP pela primeira vez. Vamos imitar o estado da produção atual Foobardb Ao carregar tantos funcionários (falsos) no banco de dados quanto na produção (Nota: precisaremos do mesmo tamanho de armazenamento no banco de dados de teste e na produção).

Simplesmente usaremos o Bash para preencher o banco de dados de teste (assumindo que temos 500.000 funcionários em produção):

$ para j em 1… 500000; Echo "Inserir nos funcionários (First_Name, Last_Name, Birt_Year, Birth_month, Birth_dayofmonth) valores ('Usuário $ J', 'Test', 1900,01,01);"; feito | PSQL -D Foobardb 

Agora temos 500002 funcionários:

Foobardb =# Selecione a contagem (*) dos funcionários; CONTA -------- 500002 (1 Linha) 

Vamos executar a consulta explicar novamente:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- -------- Scan SEQ em funcionários (custo = 0.00… 11667.63 linhas = 1 largura = 22) (tempo real = 0.012… 150.998 linhas = 1 loops = 1) filtro: ((birth_month = 3 :: numeric) e (birth_dayofmonth = 20 :: numérico)) linhas removidas por filtro: 500001 Total Runtime: 151.059 ms 

Ainda temos apenas uma partida, mas a consulta é significativamente mais lenta. Devemos notar o primeiro nó do planejador: Scan SEQ que significa varredura seqüencial - o banco de dados lê o todo
tabela, enquanto precisamos de apenas um registro, como um grep faria em Bash. Na verdade, pode ser realmente mais lento que o grep. Se exportarmos a tabela para um arquivo CSV chamado /TMP/EXP500K.CSV:

 foobardb =# copie os funcionários para '/tmp/exp500k.CSV 'Delimitador', 'Cabeçalho CSV; Copie 500002 

E grep as informações de que precisamos (procuramos no dia 20 do 3º mês, os dois últimos valores no arquivo CSV em cada
linha):

$ time grep ", 3,20" /tmp /exp500k.CSV 1, Emily, James, 1983,3,20 Real 0m0.067S Usuário 0M0.018S SYS 0M0.010s 

Isto é, armazenando em cache de lado, considerado mais lento e lento à medida que a mesa cresce.

A solução é de indexação de causa. Nenhum funcionário pode ter mais de uma data de nascimento, que consiste em exatamente um Ano de Nascimento, mês de nascimento e birth_dayofmonth - Portanto, esses três campos fornecem um valor único para esse usuário em particular. E um usuário é identificado por seu emp_id (Pode haver mais de um funcionário da empresa com o mesmo nome). Se declararmos uma restrição nesses quatro campos, também será criado um índice implícito:

Foobardb =# ALTER TABLE FUNCIONÁRIOS ADICIONAR RESTRAIÇÃO BINTADE_UNIQ ÚNICA (EMP_ID, BIRVHT_YEAR, BINTRADE_MONTH, BIORH_DAYOFMONTH); AVISO: ALTER TABLE / ADICIONE O ÍNDICE IMPLICIAL "BIORH_UNIQ" para "funcionários da tabela" 

Então, obtivemos um índice para os quatro campos, vamos ver como nossa consulta é executada:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- ---------- Scan SEQ em funcionários (custo = 0.00… 11667.19 linhas = 1 largura = 22) (tempo real = 103.131… 151.084 linhas = 1 loops = 1) filtro: ((bift_month = 3 :: numeric) e (birth_dayofmonth = 20 :: numérico)) linhas removidas por filtro: 500001 Total Runtime: 151.103 ms (4 linhas) 

Isso é idêntico ao último, e podemos ver que o plano é o mesmo, o índice não é usado. Vamos criar outro índice por uma restrição única em emp_id, mês de nascimento e birth_dayofmonth apenas (afinal, não questionamos Ano de Nascimento em hbapp):

Foobardb =# funcionários da tabela de alteração Adicionar restrições birth_uniq_m_dom Única (EMP_ID, BIORH_MONTH, BIORH_DAYOFMONTH); AVISO: ALTER TABLE / ADICIONE O ÍNDICE IMPLICADO "BIORH_UNIQ_M_DOM" para a tabela "funcionários" 

Vamos ver o resultado de nossa sintonia:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- --------- Scan SEQ em funcionários (custo = 0.00… 11667.19 linhas = 1 largura = 22) (tempo real = 97.187… 139.858 linhas = 1 loops = 1) filtro: ((birth_month = 3 :: numeric) e (birth_dayofmonth = 20 :: numérico)) linhas removidas por filtro: 500001 Total de execução: 139.879 ms (4 linhas) 

Nada. A diferença acima vem do uso de caches, mas o plano é o mesmo. Vamos mais longe. Em seguida, criaremos outro índice em emp_id e mês de nascimento:

Foobardb =# ALTER TABLE FUNCIONÁRIOS ADICIONAR RESTRAIÇÃO BINTADE_UNIQ_M ÚNICA (EMP_ID, BIORH_MONTH); AVISO: ALTER TABLE / ADICIONE O ÍNDICE IMPLICIAL "BIORH_UNIQ_M" para "funcionários da tabela" 

E execute a consulta novamente:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- -----------------------.00… 11464.19 linhas = 1 largura = 22) (tempo real = 0.089… 95.605 linhas = 1 loops = 1) ÍNDICE COND: (BIRNH_MONTH = 3 :: numeric) Filtro: (BIRth_dayofmonth = 20 :: numérico) Total Runtime: 95.630 ms (4 linhas) 

Sucesso! A consulta é 40% mais rápida, e podemos ver que o plano mudou: o banco de dados não escanei mais a tabela inteira, mas usa o índice em mês de nascimento e emp_id. Criamos todas as misturas dos quatro campos, apenas um permanece. Vale a pena tentar:

Foobardb =# ALTER TABLE FUNCIONÁRIOS ADICIONAR RESTRAIÇÃO BINTADE_UNIQ_DOM ÚNICA (EMP_ID, BIRth_Dayofmonth); AVISO: ALTER TABLE / ADICIONE O ÍNDICE IMPLICIAL "BIORH_UNIQ_DOM" para "funcionários da tabela" 

O último índice é criado em campos emp_id e birth_dayofmonth. E o resultado é:

foobardb =# Explique analise selecione EMP_ID, First_Name, Last_Name dos funcionários onde BIRth_MONTH = 3 e BIORH_DAYOFMONTH = 20; Plano de consulta -------------------------------------------------- -------------------------------------------------------- -----------------.00… 11464.19 linhas = 1 largura = 22) (tempo real = 0.025… 72.394 linhas = 1 loops = 1) ÍNDICE COND: (BIRth_dayofmonth = 20 :: numeric) Filtro: (BIORH_MONTH = 3 :: numérico) Total Runtime: 72.421 ms (4 linhas) 

Agora nossa consulta é cerca de 49% mais rápida, usando o último (e apenas o último) do índice criado. Nossa mesa e índices relacionados parecem o seguinte:

Foobardb =# \ d+ Tabela de funcionários "Public.funcionários "Coluna | Tipo | Modificadores | Armazenamento | Estatísticas Alvo | Descrição ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- EMP_ID | NUMERIC | NÃO NULL PADRÃO NEXTVAL ('FUNCIONÁRIO_SEQ' :: regclass) | principal | | primeiro_name | text | não nulo | estendido | | last_name | text | não nulo | estendido | | birth_year | numeric | não nulo | main | birth_month | numérico | não nulo | principal | birth_dayofmonth | numérico | Não nulo | Main | | ÍNDICES: "funcionários_pkey" Chave primária, Btree (EMP_ID) "BIORH_UNIQ" Restrição única, Btree (EMP_ID, BIORH_YEEN, BIORH_MONTH, BIORH_DAYOFMONTH) "BIORH_UNIQ_DOM" Restrições exclusivas, BINTEE (EMP_ID_DOD_OFMONHO) " Restrição, btree (emp_id, birth_month) "bift_uniq_m_dom" restrição única, btree (emp_id, birth_month, birth_dayofmonth) tem oids: não 

Não precisamos dos índices intermediários criados, o plano afirma claramente que não os usará, por isso os deixamos deixá -los:

Foobardb =# ALTER TABLE FUNCIONÁRIOS DESLIGADO RESTRAIÇÃO BINTADE_UNIQ; Alterar a tabela foobardb =# alteração de funcionários da tabela soltar restrição de nascimento_uniq_m; Alterar a tabela foobardb =# alteração de funcionários da tabela soltar restrição de nascimento_uniq_m_dom; ALTERAR A TABELA 

No final, nossa tabela ganha apenas um índice adicional, que é baixo custo para uma velocidade dupla de HBAPP:

Foobardb =# \ d+ Tabela de funcionários "Public.funcionários "Coluna | Tipo | Modificadores | Armazenamento | Estatísticas Alvo | Descrição ------------------+---------+------- ----------------------------------------------+--- -------+--------------+------------- EMP_ID | NUMERIC | NÃO NULL PADRÃO NEXTVAL ('FUNCIONÁRIO_SEQ' :: regclass) | principal | | primeiro_name | text | não nulo | estendido | | last_name | text | não nulo | estendido | | birth_year | numeric | não nulo | main | birth_month | numérico | não nulo | principal | birth_dayofmonth | numérico | NÃO NULL | PRINCIPAL | | ÍNDICES: "funcionários_pkey" Chave primária, Btree (EMP_ID) "BIORH_UNIQ_DOM" Restrição única, Btree (EMP_ID, Birth_dayofonth) tem OIDS: Não 

E podemos apresentar nosso ajuste à produção adicionando o índice que vimos ser mais útil:

ALTER TABLE FUNCIONÁRIOS ADICIONAR RESTRAIÇÃO BINTADE_UNIQ_DOM ÚNICA (EMP_ID, BIRVH_DAYOFMONTH);

Conclusão

Escusado será dizer que este é apenas um exemplo fictício. É improvável que você armazene a data de nascimento do seu funcionário em três campos separados enquanto pode usar um campo de tipo de data, permitindo operações relacionadas a data de maneira muito mais fácil do que comparar valores de mês e dia como inteiros. Observe também que as poucas consultas explicadas acima não estão em forma como teste excessivo. Em um cenário do mundo real, você precisa testar o impacto do novo objeto de banco de dados em qualquer outro aplicativo que use o banco de dados, bem como componentes do seu sistema que interagem com o HBAPP.

Por exemplo, nesse caso, se pudermos processar a tabela para destinatários em 50% do tempo de resposta original, podemos praticamente produzir 200% dos e -mails do outro lado do aplicativo (digamos, o HBAPP é executado em sequência para Todas as 500 empresas subsidiárias da Nice Company), que podem resultar em pico de carga em outro lugar - talvez os servidores de email recebam muitos e -mails de "feliz aniversário" para transmitir pouco antes de enviarem os relatórios diários para a gerência, resultando em atrasos de entrega. Também está um pouco longe da realidade que alguém que sintonize um banco de dados crie índices com tentativa e erro cegos - ou pelo menos, esperamos que isso seja assim em uma empresa que emprega tantas pessoas.

Observe que, no entanto, ganhamos 50% de aumento de desempenho na consulta apenas usando o pós -férresql incorporado explicar recurso para identificar um único índice que pode ser útil na situação especificada. Também mostramos que qualquer banco de dados relacional não é melhor do que uma pesquisa clara de texto se não os usarmos, pois eles devem ser usados.

Tutoriais do Linux relacionados:

• Coisas para instalar no Ubuntu 20.04
• Ubuntu 20.04 Instalação PostGresql
• Ubuntu 22.04 Instalação PostGresql
• Uma introdução à automação, ferramentas e técnicas do Linux
• Otimização de desempenho do Linux: ferramentas e técnicas
• Coisas para fazer depois de instalar o Ubuntu 20.04 fossa focal linux
• Download do Linux
• Arquivos de configuração do Linux: os 30 primeiros mais importantes
• Como persistir dados para pós -grausql em java
• Coisas para instalar no Ubuntu 22.04