Transcrição do relatório de 2015 de Alexey Lesovsky "Mergulho profundo nas estatísticas internas do PostgreSQL"
Isenção de responsabilidade do autor do relatório: Observo que este relatório é datado de novembro de 2015 - mais de 4 anos se passaram e muito tempo se passou. A versão 9.4 discutida no relatório não é mais suportada. Nos últimos 4 anos, foram lançados 5 novos lançamentos nos quais surgiram muitas inovações, melhorias e mudanças em relação às estatísticas, e parte do material está desatualizado e não é relevante. Ao revisar, tentei marcar esses lugares para não enganar você, leitor. Não reescrevi esses lugares, são muitos e, como resultado, vai sair um relatório completamente diferente.
O PostgreSQL DBMS é um mecanismo enorme, e esse mecanismo consiste em muitos subsistemas, cujo trabalho coordenado afeta diretamente o desempenho do DBMS. Durante a operação, são coletadas estatísticas e informações sobre a operação dos componentes, o que permite avaliar a eficácia do PostgreSQL e tomar medidas para melhorar o desempenho. No entanto, há muitas dessas informações e elas são apresentadas de forma bastante simplificada. Processar essas informações e interpretá-las às vezes é uma tarefa completamente não trivial, e o "zoológico" de ferramentas e utilitários pode facilmente confundir até mesmo um DBA avançado.
Boa tarde Meu nome é Aleksey. Como disse Ilya, falarei sobre estatísticas do PostgreSQL.
Estatísticas de atividade do PostgreSQL. O PostgreSQL tem duas estatísticas. Estatísticas de atividade, que serão discutidas. E estatísticas do agendador sobre distribuição de dados. Falarei especificamente sobre as estatísticas de atividade do PostgreSQL, que nos permitem julgar o desempenho e de alguma forma melhorá-lo.
Vou lhe dizer como usar estatísticas de forma eficaz para resolver uma variedade de problemas que você tem ou pode ter.
O que não estará no relatório? No relatório, não vou tocar nas estatísticas do agendador, porque. este é um tópico separado para um relatório separado sobre como os dados são armazenados no banco de dados e como o planejador de consultas tem uma ideia das características qualitativas e quantitativas desses dados.
E não haverá análises de ferramentas, não vou comparar um produto com outro. Não haverá publicidade. Vamos deixar isso de lado.
Quero mostrar a você que o uso de estatísticas é útil. É necessário. Use-o sem medo. Tudo o que precisamos é de SQL simples e um conhecimento básico de SQL.
E falaremos sobre quais estatísticas escolher para resolver problemas.
Se olharmos para o PostgreSQL e executarmos um comando no sistema operacional para visualizar os processos, veremos uma “caixa preta”. Veremos alguns processos que fazem algo e, pelo nome, podemos imaginar aproximadamente o que eles estão fazendo ali, o que estão fazendo. Mas, na verdade, esta é uma caixa preta, não podemos olhar para dentro.
Podemos olhar para a carga da CPU em top, podemos ver a utilização de memória por alguns utilitários do sistema, mas não poderemos ver dentro do PostgreSQL. Para isso, precisamos de outras ferramentas.
E continuando, direi onde o tempo é gasto. Se representarmos o PostgreSQL na forma de tal esquema, será possível responder onde o tempo é gasto. São duas coisas: é o processamento de solicitações de clientes de aplicativos e as tarefas em segundo plano que o PostgreSQL executa para mantê-lo funcionando.
Se começarmos a olhar para o canto superior esquerdo, podemos ver como as solicitações dos clientes são processadas. A solicitação vem do aplicativo e uma sessão do cliente é aberta para trabalhos futuros. A solicitação é passada para o agendador. O planejador cria um plano de consulta. Envia ainda mais para execução. Existe algum tipo de bloco de dados de E/S associado a tabelas e índices. Os dados necessários são lidos dos discos para a memória em uma área especial chamada "buffers compartilhados". Os resultados da consulta, se forem updates, deletes, são registrados no log de transações no WAL. Algumas informações estatísticas terminam em um log ou coletor de estatísticas. E o resultado da solicitação é devolvido ao cliente. Depois disso, o cliente pode repetir tudo com uma nova solicitação.
O que temos com tarefas e processos em segundo plano? Temos vários processos que mantêm o banco de dados funcionando em modo operacional normal. Esses processos também serão abordados no relatório: autovacuum, checkpointer, processos relacionados à replicação, gravador em segundo plano. Vou tocar em cada um deles ao relatar.
Quais são os problemas com as estatísticas?
- Muita informação. O PostgreSQL 9.4 fornece 109 métricas para visualizar dados estatísticos. No entanto, se o banco de dados armazenar muitas tabelas, esquemas, bancos de dados, todas essas métricas deverão ser multiplicadas pelo número correspondente de tabelas, bancos de dados. Ou seja, há ainda mais informações. E é muito fácil se afogar nele.
- O próximo problema é que as estatísticas são representadas por contadores. Se olharmos para essas estatísticas, veremos contadores aumentando constantemente. E se muito tempo se passou desde que as estatísticas foram redefinidas, veremos bilhões de valores. E eles não nos dizem nada.
- Não há história. Se você tiver algum tipo de falha, algo caiu 15 a 30 minutos atrás, você não poderá usar as estatísticas e ver o que aconteceu 15 a 30 minutos atrás. Isso é problema.
- A falta de uma ferramenta embutida no PostgreSQL é um problema. Os desenvolvedores do kernel não fornecem nenhum utilitário. Eles não têm nada disso. Eles apenas fornecem estatísticas no banco de dados. Use-o, faça um pedido a ele, o que você quiser, então faça.
- Como não há nenhuma ferramenta incorporada ao PostgreSQL, isso causa outro problema. Muitas ferramentas de terceiros. Toda empresa que tem mãos mais ou menos diretas está tentando escrever seu próprio programa. E como resultado, a comunidade tem muitas ferramentas que você pode usar para trabalhar com estatísticas. E em algumas ferramentas existem alguns recursos, em outras ferramentas não há outros recursos ou existem alguns novos recursos. E surge uma situação em que você precisa usar duas, três ou quatro ferramentas que se sobrepõem e têm funções diferentes. Isso é muito chato.
O que se segue disso? É importante poder obter estatísticas diretamente para não depender de programas, ou de alguma forma melhorar esses programas você mesmo: adicione algumas funções para obter seu benefício.
E você precisa de conhecimento básico de SQL. Para obter alguns dados das estatísticas, você precisa fazer consultas SQL, ou seja, você precisa saber como select, join são feitos.
As estatísticas nos dizem várias coisas. Eles podem ser divididos em categorias.
- A primeira categoria são os eventos que ocorrem no banco de dados. É quando algum evento ocorre no banco de dados: uma consulta, um acesso à tabela, autovacuum, commits, então são todos eventos. Os contadores correspondentes a esses eventos são incrementados. E podemos rastrear esses eventos.
- A segunda categoria são as propriedades de objetos como tabelas, bancos de dados. Eles têm propriedades. Este é o tamanho das mesas. Podemos acompanhar o crescimento das tabelas, o crescimento dos índices. Podemos ver mudanças na dinâmica.
- E a terceira categoria é o tempo gasto no evento. A solicitação é um evento. Tem sua própria medida específica de duração. Começou aqui, terminou aqui. Podemos rastreá-lo. Tanto o tempo de leitura de um bloco do disco quanto a escrita. Essas coisas também são rastreadas.
As fontes de estatísticas são apresentadas a seguir:
- Na memória compartilhada (buffers compartilhados) existe um segmento para colocar dados estáticos, existem também aqueles contadores que são incrementados constantemente quando ocorrem determinados eventos ou surgem alguns momentos na operação do banco de dados.
- Todos esses contadores não estão disponíveis para o usuário e nem mesmo para o administrador. Essas são coisas de baixo nível. Para acessá-los, o PostgreSQL fornece uma interface na forma de funções SQL. Podemos fazer seleções selecionadas usando essas funções e obter algum tipo de métrica (ou conjunto de métricas).
- No entanto, nem sempre é conveniente usar essas funções, portanto, as funções são a base para as visualizações (VIEWs). São tabelas virtuais que fornecem estatísticas sobre um subsistema específico ou sobre algum conjunto de eventos no banco de dados.
- Essas exibições integradas (VIEWs) são a principal interface do usuário para trabalhar com estatísticas. Eles estão disponíveis por padrão sem nenhuma configuração adicional, você pode usá-los imediatamente, assistir, obter informações de lá. E também há contribuições. As contribuições são oficiais. Você pode instalar o pacote postgresql-contrib (por exemplo, postgresql94-contrib), carregar o módulo necessário na configuração, especificar parâmetros para ele, reiniciar o PostgreSQL e usá-lo. (Observação. Dependendo da distribuição, em versões recentes do contrib o pacote faz parte do pacote principal).
- E há contribuições não oficiais. Eles não são fornecidos com a distribuição padrão do PostgreSQL. Eles devem ser compilados ou instalados como uma biblioteca. As opções podem ser muito diferentes, dependendo do que o desenvolvedor dessa contribuição não oficial criou.
Este slide mostra todas essas visualizações (VIEWs) e algumas dessas funções que estão disponíveis no PostgreSQL 9.4. Como podemos ver, são muitos. E é muito fácil ficar confuso se você estiver experimentando isso pela primeira vez.
No entanto, se tirarmos a foto anterior Как тратится время на PostgreSQL e compatível com esta lista, obtemos esta imagem. Cada visão (VIEWs), ou cada função, podemos usar para um propósito ou outro para obter as estatísticas apropriadas quando temos o PostgreSQL rodando. E já podemos obter algumas informações sobre o funcionamento do subsistema.
A primeira coisa que veremos é pg_stat_database. Como podemos ver, trata-se de uma representação. Ele contém muitas informações. As mais variadas informações. E dá um conhecimento muito útil do que está acontecendo no banco de dados.
O que podemos levar de lá? Vamos começar com as coisas mais simples.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;A primeira coisa que podemos observar é a porcentagem de acertos do cache. A porcentagem de acesso ao cache é uma métrica útil. Ele permite que você estime quantos dados são retirados do cache de buffers compartilhados e quantos são lidos do disco.
É claro que quanto mais cache hit tivermos, melhor. Avaliamos essa métrica como uma porcentagem. E, por exemplo, se tivermos uma porcentagem desses acertos de cache maior que 90%, isso é bom. Se cair abaixo de 90%, não temos memória suficiente para manter a cabeça quente dos dados na memória. E para usar esses dados, o PostgreSQL é forçado a acessar o disco e isso é mais lento do que se os dados fossem lidos da memória. E você precisa pensar em aumentar a memória: aumente os buffers compartilhados ou aumente a memória de ferro (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;O que mais pode ser retirado desta apresentação? Você pode ver as anomalias que ocorrem no banco de dados. O que é mostrado aqui? Existem commits, rollbacks, criação de arquivos temporários, seu tamanho, deadlocks e conflitos.
Podemos usar este pedido. Este SQL é bastante simples. E podemos ver esses dados por nós mesmos.
E aqui estão os valores limite. Nós olhamos para a proporção de commits e rollbacks. Commits é uma confirmação bem-sucedida da transação. Rollbacks é um rollback, ou seja, a transação funcionou, sobrecarregou o banco de dados, considerou algo e, em seguida, ocorreu uma falha e os resultados da transação foram descartados. ou seja o número de reversões aumentando constantemente é ruim. E você deve evitá-los de alguma forma e editar o código para que isso não aconteça.
Os conflitos estão relacionados à replicação. E devem ser evitados também. Se você tiver algumas consultas que são executadas na réplica e surgirem conflitos, será necessário analisar esses conflitos e ver o que acontece. Detalhes podem ser encontrados nos logs. E resolva conflitos para que as solicitações do aplicativo funcionem sem erros.
Deadlocks também é uma situação ruim. Quando as requisições competem por recursos, uma requisição acessou um recurso e pegou o lock, a segunda requisição acessou o segundo recurso e também pegou o lock, e então ambas as requisições acessaram os recursos uma da outra e ficaram bloqueadas esperando que o vizinho liberasse o lock. Essa também é uma situação problemática. Eles precisam ser abordados no nível de reescrever aplicativos e serializar o acesso aos recursos. E se você perceber que seus impasses estão aumentando constantemente, você precisa olhar os detalhes dos logs, analisar as situações que surgiram e ver qual é o problema.
Arquivos temporários (temp_files) também são ruins. Quando uma solicitação do usuário não tem memória suficiente para acomodar os dados temporários operacionais, ela cria um arquivo no disco. E todas as operações que ele poderia realizar em um buffer temporário na memória, ele começa a realizar já no disco. É lento. Isso aumenta o tempo de execução da consulta. E o cliente que enviou uma solicitação ao PostgreSQL receberá uma resposta um pouco mais tarde. Se todas essas operações forem executadas na memória, o Postgres responderá muito mais rápido e o cliente esperará menos.
pg_stat_bgwriter - Esta visão descreve a operação de dois subsistemas de fundo do PostgreSQL: checkpointer и background writer.
Para começar, vamos analisar os pontos de controle, os chamados. checkpoints. O que são pontos de verificação? Um ponto de verificação é uma posição no log de transações que indica que todas as alterações de dados confirmadas no log foram sincronizadas com êxito com os dados no disco. O processo, dependendo da carga de trabalho e das configurações, pode ser demorado e consiste principalmente na sincronização de páginas sujas em buffers compartilhados com arquivos de dados em disco. Para que serve? Se o PostgreSQL estivesse acessando o disco o tempo todo e tirando dados de lá, e gravando dados a cada acesso, ficaria lento. Portanto, o PostgreSQL possui um segmento de memória, cujo tamanho depende dos parâmetros na configuração. Postgres aloca dados operacionais nesta memória para processamento ou consulta posterior. No caso de solicitações de alteração de dados, eles são alterados. E obtemos duas versões dos dados. Um está na memória, o outro está no disco. E periodicamente você precisa sincronizar esses dados. Precisamos que o que foi alterado na memória seja sincronizado com o disco. Isso requer um ponto de verificação.
O ponto de verificação passa por buffers compartilhados, marca as páginas sujas que são necessárias para o ponto de verificação. Em seguida, inicia a segunda passagem pelos buffers compartilhados. E as páginas que estão marcadas para checkpoint, ele já sincroniza. Assim, os dados já estão sincronizados com o disco.
Existem dois tipos de pontos de controle. Um ponto de verificação é executado no tempo limite. Este ponto de verificação é útil e bom - checkpoint_timed. E há postos de controle sob demanda - checkpoint required. Esse ponto de verificação ocorre quando temos um registro de dados muito grande. Registramos muitos logs de transações. E o PostgreSQL acredita que precisa sincronizar tudo isso o mais rápido possível, fazer um checkpoint e seguir em frente.
E se você olhar para as estatísticas pg_stat_bgwriter e veja o que você tem checkpoint_req é muito maior que checkpoint_timed, então isso é ruim. Porquê Mal? Isso significa que o PostgreSQL está sob estresse constante quando precisa gravar dados no disco. O checkpoint por tempo limite é menos estressante e é executado de acordo com a programação interna e, por assim dizer, estendido ao longo do tempo. O PostgreSQL tem a capacidade de pausar no trabalho e não sobrecarregar o subsistema de disco. Isso é útil para o PostgreSQL. E as solicitações executadas durante o ponto de verificação não sofrerão estresse pelo fato de o subsistema de disco estar ocupado.
E há três parâmetros para ajustar o ponto de verificação:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Eles permitem que você controle a operação dos pontos de controle. Mas não vou me alongar sobre eles. Sua influência é uma questão separada.
Nota: A versão 9.4 considerada no relatório não é mais relevante. Nas versões modernas do PostgreSQL, o parâmetro checkpoint_segments substituído por parâmetros min_wal_size и max_wal_size.
O próximo subsistema é o gravador de fundo - background writer. O que ele está fazendo? Funciona constantemente em um loop infinito. Varre as páginas em buffers compartilhados e libera as páginas sujas que encontra no disco. Desta forma, ajuda o checkpointer a fazer menos trabalho durante o checkpointing.
Para que mais ele é necessário? Ele fornece a necessidade de páginas limpas em buffers compartilhados se forem repentinamente necessárias (em grandes quantidades e imediatamente) para acomodar dados. Suponha que surja uma situação em que páginas limpas sejam necessárias para concluir uma solicitação e elas já estejam em buffers compartilhados. Postgre backend ele apenas os pega e usa, não precisa limpar nada sozinho. Mas se de repente não houver tais páginas, o back-end faz uma pausa e começa a procurar páginas para liberá-las para o disco e levá-las para suas próprias necessidades - o que afeta negativamente o tempo da solicitação em execução no momento. Se você perceber que tem um parâmetro maxwritten_clean grande, isso significa que o gravador de fundo não está fazendo seu trabalho e você precisa aumentar os parâmetros bgwriter_lru_maxpagespara que ele possa trabalhar mais em um ciclo, limpe mais páginas.
E outro indicador muito útil é buffers_backend_fsync. Os back-ends não fazem fsync porque são lentos. Eles passam o fsync pelo checkpointer da pilha IO. O checkpointer possui sua própria fila, processa fsync periodicamente e sincroniza páginas na memória com arquivos no disco. Se a fila do checkpointer for grande e cheia, o back-end é forçado a fazer fsync e isso torna o back-end mais lento, ou seja, o cliente receberá uma resposta mais tarde do que poderia. Se você ver que tem esse valor maior que zero, isso já é um problema e você precisa prestar atenção às configurações do gravador em segundo plano e também avaliar o desempenho do subsistema de disco.
Nota: _O texto a seguir descreve as exibições estatísticas associadas à replicação. A maioria dos nomes de exibição e função foram renomeados no Postgres 10. A essência das renomeações foi substituir xlog em wal и location em lsn em nomes de funções/visualizações, etc. Exemplo particular, função pg_xlog_location_diff() foi renomeado para pg_wal_lsn_diff()._
Temos muito aqui também. Mas só precisamos de itens relacionados à localização.
Se percebermos que todos os valores são iguais, isso é ideal e a réplica não fica atrás do mestre.
Esta posição hexadecimal aqui é a posição no log de transações. Aumenta constantemente se houver alguma atividade no banco de dados: inserções, exclusões, etc.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Se essas coisas forem diferentes, haverá algum tipo de atraso. Lag é o atraso da réplica do mestre, ou seja, os dados diferem entre os servidores.
Há três razões para o atraso:
- É o subsistema de disco que não pode lidar com gravações de sincronização de arquivos.
- São possíveis erros de rede, ou sobrecarga de rede, quando os dados não têm tempo de chegar à réplica e esta não consegue reproduzi-la.
- E o processador. O processador é um caso muito raro. E já vi isso duas ou três vezes, mas também pode acontecer.
E aqui estão três consultas que nos permitem usar estatísticas. Podemos estimar quanto é registrado em nosso log de transações. Existe tal função pg_xlog_location_diff e podemos estimar o atraso de replicação em bytes e segundos. Também usamos o valor desta visão (VIEWs) para isso.
Nota: _Em vez de pg_xlog_locationfunção diff(), você pode usar o operador de subtração e subtrair um local de outro. Confortável.
Com um atraso, que é em segundos, há um momento. Se não houver atividade no mestre, a transação estava lá há cerca de 15 minutos e não há atividade e, se observarmos esse atraso na réplica, veremos um atraso de 15 minutos. Vale a pena lembrar disso. E pode levar a um estupor quando você assistiu a esse atraso.
pg_stat_all_tables é outra visualização útil. Ele mostra estatísticas em tabelas. Quando temos tabelas no banco de dados, há alguma atividade com ele, algumas ações, podemos obter essas informações dessa visão.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;A primeira coisa que podemos observar são as varreduras sequenciais de tabelas. O próprio número após essas passagens não é necessariamente ruim e não indica que precisamos fazer algo já.
No entanto, há uma segunda métrica - seq_tup_read. Este é o número de linhas retornadas da verificação sequencial. Se o número médio ultrapassar 1, 000, 10, 000, então isso já é um indicador de que você pode precisar construir um índice em algum lugar para que os acessos sejam por índice, ou é possível otimizar as consultas que usam essas varreduras sequenciais para que isso não acontece.
Um exemplo simples - digamos que uma solicitação com um grande OFFSET e LIMIT valha a pena. Por exemplo, 100 linhas em uma tabela são verificadas e, depois disso, 000 linhas necessárias são obtidas e as linhas verificadas anteriores são descartadas. Este também é um caso ruim. E tais solicitações precisam ser otimizadas. E aqui está uma consulta SQL tão simples na qual você pode ver e avaliar os números recebidos.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Os tamanhos da tabela também podem ser obtidos usando esta tabela e usando funções adicionais pg_total_relation_size(), pg_relation_size().
Em geral, existem metacomandos dt и di, que você pode usar em PSQL e também ver tamanhos de tabelas e índices.
Porém, o uso de funções nos ajuda a olhar os tamanhos das tabelas, mesmo levando em conta índices, ou sem levar em conta índices, e já fazer algumas estimativas com base no crescimento do banco de dados, ou seja, como ele cresce conosco, com qual intensidade, e já tirar algumas conclusões sobre otimização de dimensionamento.
Atividade de gravação. O que é um registro? Vejamos a operação UPDATE – a operação de atualização de linhas na tabela. Na verdade, a atualização é duas operações (ou até mais). Isso está inserindo uma nova versão de linha e marcando a versão de linha antiga como obsoleta. Mais tarde, o autovacuum virá e limpará essas versões obsoletas das linhas, marque este local como disponível para reutilização.
Além disso, atualizar não é apenas atualizar uma tabela. Ainda é uma atualização de índice. Se você tiver muitos índices na tabela, então, com atualização, todos os índices nos quais os campos atualizados na consulta participam também precisarão ser atualizados. Esses índices também terão versões de linha obsoletas que precisarão ser limpas.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;E devido ao seu design, UPDATE é uma operação pesada. Mas eles podem ser facilitados. Comer hot updates. Eles apareceram no PostgreSQL versão 8.3. E o que é isso? Esta é uma atualização leve que não faz com que os índices sejam reconstruídos. Ou seja, atualizamos o registro, mas apenas o registro na página (que pertence à tabela) foi atualizado, e os índices ainda apontam para o mesmo registro na página. Tem um pouco dessa lógica de trabalho tão interessante, quando vem um vácuo, aí tem essas correntes hot reconstrói e tudo continua funcionando sem atualizar os índices, e tudo acontece com menos desperdício de recursos.
E quando você tiver n_tup_hot_upd grande, é muito bom. Isso significa que as atualizações leves prevalecem e isso é mais barato para nós em termos de recursos e está tudo bem.
ALTER TABLE table_name SET (fillfactor = 70);como aumentar o volume hot updateov? Podemos usar fillfactor. Determina o tamanho do espaço livre reservado ao preencher uma página em uma tabela usando INSERTs. Quando as inserções vão para a tabela, elas preenchem completamente a página, não deixam espaço vazio nela. Em seguida, uma nova página é destacada. Os dados são preenchidos novamente. E este é o comportamento padrão, fillfactor = 100%.
Podemos definir o fillfactor para 70%. Ou seja, com inserções, uma nova página foi alocada, mas apenas 70% da página foi preenchida. E ainda temos 30% na reserva. Quando você precisar fazer uma atualização, provavelmente acontecerá na mesma página e a nova versão da linha caberá na mesma página. E o hot_update será feito. Isso facilita a escrita nas tabelas.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Fila de autovácuo. Autovacuum é um subsistema para o qual existem muito poucas estatísticas no PostgreSQL. Podemos apenas ver nas tabelas em pg_stat_activity quantos aspiradores temos no momento. Porém, é muito difícil entender quantas mesas na fila ele tem em movimento.
Nota: _Desde o Postgres 10, a situação com o rastreamento do vácuo melhorou muito - a visualização pg_stat_progress apareceuvácuo, o que simplifica muito a questão do monitoramento do autovácuo.
Podemos usar essa consulta simplificada. E podemos ver quando o vácuo deve ser feito. Mas, como e quando o vácuo deve começar? Estas são as versões antigas das strings sobre as quais falei anteriormente. A atualização ocorreu, a nova versão da linha foi inserida. Uma versão obsoleta da string apareceu. Mesa pg_stat_user_tables existe tal parâmetro n_dead_tup. Ele mostra o número de linhas "mortas". E assim que o número de linhas mortas ultrapassar um determinado limite, um autovácuo chegará à mesa.
E como esse limite é calculado? Esta é uma porcentagem muito específica do número total de linhas na tabela. Existe um parâmetro autovacuum_vacuum_scale_factor. Ele define a porcentagem. Digamos que 10% + haja um limite básico adicional de 50 linhas. E o que acontece? Quando temos mais linhas mortas do que "10% + 50" de todas as linhas da tabela, colocamos a tabela em autovacuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));No entanto, há um ponto. Limites básicos para parâmetros av_base_thresh и av_scale_factor podem ser atribuídos individualmente. E, consequentemente, o limite não será global, mas individual para a tabela. Portanto, para calcular, você precisa usar truques e truques. E se você estiver interessado, pode ver a experiência de nossos colegas da Avito (o link no slide é inválido e foi atualizado no texto).
Eles escreveram para que leva em conta essas coisas. Há um lençol em duas folhas. Mas ele considera corretamente e com bastante eficácia nos permite avaliar onde precisamos de muito vácuo para as mesas, onde há pouco.
O que podemos fazer sobre isso? Se tivermos uma longa fila e o aspirador automático não aguentar, podemos aumentar o número de aspiradores ou simplesmente tornar o aspirador mais agressivopara que seja acionado mais cedo, processe a tabela em pequenos pedaços. E assim a fila diminuirá. - O principal aqui é monitorar a carga nos discos, porque. O vácuo não é gratuito, embora com o advento dos dispositivos SSD / NVMe, o problema tenha se tornado menos perceptível.
pg_stat_all_indexes são estatísticas sobre índices. Ela não é grande. E podemos obter informações sobre o uso de índices a partir dele. E, por exemplo, podemos determinar quais índices temos extras.
Como já disse, update não está apenas atualizando tabelas, mas também atualizando índices. Assim, se tivermos muitos índices na tabela, ao atualizar as linhas da tabela, os índices dos campos indexados também precisam ser atualizados e se tivermos índices não utilizados para os quais não há varreduras de índice, eles ficarão conosco como lastro. E você precisa se livrar deles. Para isso precisamos de um campo idx_scan. Nós apenas olhamos para o número de varreduras de índice. Se os índices não tiverem varreduras durante um período relativamente longo de armazenamento de estatísticas (pelo menos 2 a 3 semanas), provavelmente esses são índices ruins, precisamos nos livrar deles.
Nota: Ao procurar por índices não utilizados no caso de clusters de replicação de streaming, você precisa verificar todos os nós do cluster, porque as estatísticas não são globais e, se o índice não for usado no mestre, ele poderá ser usado em réplicas (se houver uma carga).
Dois links:
Estes são exemplos de consulta mais avançados sobre como procurar índices não utilizados.
O segundo link é uma consulta bastante interessante. Há uma lógica nada trivial nisso. Recomendo para revisão.
O que mais deve ser resumido por índices?
-
Índices não utilizados são ruins.
-
Eles ocupam espaço.
-
Desacelere as operações de atualização.
-
Trabalho extra para o aspirador.
Se removermos índices não utilizados, apenas tornaremos o banco de dados melhor.
A próxima visualização é pg_stat_activity. Este é um análogo do utilitário ps, somente no PostgreSQL. Se ps'ohm você assiste os processos no sistema operacional, então pg_stat_activity mostrará a atividade dentro do PostgreSQL.
O que podemos levar de lá?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Podemos ver a atividade geral que está acontecendo no banco de dados. Podemos fazer uma nova implantação. Tudo explodiu lá, novas conexões não são aceitas, erros estão surgindo no aplicativo.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Podemos executar uma consulta como esta e ver a porcentagem total de conexões em relação ao limite máximo de conexões e ver quem temos mais conexões. E neste caso, vemos que o usuário cron_role abriu 508 conexões. E algo aconteceu com ele. Você precisa lidar com isso e ver. E é bem possível que seja algum tipo de número anômalo de conexões.
Se tivermos uma carga OLTP, as consultas devem ser rápidas, muito rápidas e não devem ser consultas longas. No entanto, se houver solicitações longas, a curto prazo não há nada com que se preocupar, mas no longo prazo, as consultas longas prejudicam o banco de dados, elas aumentam o efeito de inchaço das tabelas quando ocorre a fragmentação da tabela. Tanto o inchaço quanto as consultas longas precisam ser descartados.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Observação: com essa solicitação, podemos definir solicitações e transações longas. Usamos a função clock_timestamp() para determinar o tempo de trabalho. Pedidos longos que encontramos, podemos lembrá-los, executá-los explain, observe os planos e otimize de alguma forma. Filmamos os longos pedidos atuais e continuamos.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Transações ruins são ociosas na transação e ociosas nas transações (abortadas).
O que isso significa? As transações têm vários estados. E um desses estados pode ocorrer a qualquer momento. Existe um campo para definir os estados state Nesta visão. E nós o usamos para determinar o estado.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';E, como eu disse acima, esses dois estados ocioso na transação e ocioso na transação (abortado) são ruins. O que é isso? É quando o aplicativo abre uma transação, executa algumas ações e cuida de seus negócios. A transação permanece aberta. Ele trava, nada acontece nele, leva uma conexão, bloqueia em linhas alteradas e potencialmente ainda aumenta o inchaço de outras tabelas, devido à arquitetura do mecanismo transacional do Postrges. E tais transações também devem ser baleadas, porque são prejudiciais em geral, em qualquer caso.
Se você perceber que tem mais de 5-10-20 deles em seu banco de dados, precisará se preocupar e começar a fazer algo com eles.
Aqui também usamos para o tempo de cálculo clock_timestamp(). Disparamos transações, otimizamos o aplicativo.
Como disse acima, locks são quando duas ou mais transações competem por um ou um grupo de recursos. Para isso temos um campo waiting com valor booleano true ou false.
Verdadeiro - isso significa que o processo está esperando, algo precisa ser feito. Quando um processo está esperando, o cliente que iniciou o processo também está esperando. O cliente no navegador senta e também espera.
Nota: _A partir do Postgres 9.6, o campo waiting removido e substituído por mais dois campos informativos wait_event_type и wait_event._
O que fazer? Se você vê a verdade por um longo tempo, deve se livrar de tais solicitações. Nós apenas filmamos essas transações. Escrevemos para os desenvolvedores o que precisa ser otimizado de alguma forma para que não haja corrida por recursos. E então os desenvolvedores otimizam o aplicativo para que isso não aconteça.
E um caso extremo, mas potencialmente não fatal, é ocorrência de impasses. Duas transações atualizaram dois recursos, então eles os acessam novamente, já para recursos opostos. O PostgreSQL neste caso pega e dispara a própria transação para que a outra continue funcionando. Esta é uma situação sem saída e ela não se entende. Portanto, o PostgreSQL é forçado a tomar medidas extremas.
E aqui estão duas consultas que permitem rastrear bloqueios. Usamos a visão pg_locks, que permite rastrear fechaduras pesadas.
E o primeiro link é o próprio texto da solicitação. É bem longo.
E o segundo link é um artigo sobre fechaduras. É útil ler, é muito interessante.
Então, o que vemos? Vemos dois pedidos. Transação com ALTER TABLE é uma transação de bloqueio. Começou, mas não terminou, e o aplicativo que postou essa transação está fazendo outras coisas em algum lugar. E o segundo pedido é atualização. Ele espera que o alter table termine antes de continuar seu trabalho.
É assim que podemos descobrir quem prendeu quem, quem está segurando quem, e podemos lidar com isso mais adiante.
O próximo módulo é pg_stat_statements. Como eu disse, é um módulo. Para utilizá-lo, você precisa carregar sua biblioteca na configuração, reiniciar o PostgreSQL, instalar o módulo (com um comando), e então teremos uma nova visualização.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;O que podemos levar de lá? Se falamos de coisas simples, podemos pegar o tempo médio de execução da consulta. O tempo está crescendo, o que significa que o PostgreSQL está respondendo lentamente e algo precisa ser feito.
Podemos ver as transações de escrita mais ativas no banco de dados que alteram dados em buffers compartilhados. Veja quem atualiza ou exclui dados lá.
E podemos apenas olhar para estatísticas diferentes para essas consultas.
Nós pg_stat_statements usado para construir relatórios. Redefinimos as estatísticas uma vez por dia. Vamos acumulá-lo. Antes de redefinir as estatísticas da próxima vez, construímos um relatório. Aqui está um link para o relatório. Você pode assistir.
O que estamos fazendo? Calculamos as estatísticas gerais para todas as consultas. Então, para cada consulta, contamos sua contribuição individual para essa estatística geral.
E o que podemos ver? Podemos ver o tempo total de execução de todas as solicitações de um determinado tipo em relação a todas as outras solicitações. Podemos observar o uso de CPU e E/S em relação ao quadro geral. E já para otimizar esses pedidos. Estamos criando as principais consultas com base neste relatório e já estamos pensando no que otimizar.
O que temos nos bastidores? Ainda há algumas submissões que não considerei, porque o tempo é limitado.
Tem pgstattuple também é um módulo adicional do pacote contribs padrão. Ele permite que você avalie bloat tabelas, assim chamadas. fragmentação da tabela. E se a fragmentação for grande, você precisa removê-la, use ferramentas diferentes. e função pgstattuple funciona por muito tempo. E quanto mais mesas, mais tempo funcionará.
A próxima contribuição é pg_buffercache. Ele permite que você inspecione os buffers compartilhados: com que intensidade e para quais tabelas as páginas do buffer são utilizadas. E apenas permite que você examine os buffers compartilhados e avalie o que está acontecendo lá.
O próximo módulo é pgfincore. Ele permite que você execute operações de tabela de baixo nível por meio de uma chamada de sistema mincore(), ou seja, permite carregar a tabela em buffers compartilhados ou descarregá-la. E permite, entre outras coisas, inspecionar o cache da página do sistema operacional, ou seja, quanto a tabela ocupa no cache da página, em buffers compartilhados, e simplesmente permite avaliar a carga na tabela.
O próximo módulo é pg_stat_kcache. Ele também usa a chamada do sistema getrusage(). E o executa antes e depois que a solicitação é executada. E nas estatísticas obtidas, permite estimar quanto nossa requisição gastou em I/O de disco, ou seja, operações com o sistema de arquivos e olha o uso do processador. Porém, o módulo é jovem (khe-khe) e para seu funcionamento requer PostgreSQL 9.4 e pg_stat_statements, que mencionei anteriormente.
-
A capacidade de usar estatísticas é útil. Você não precisa de software de terceiros. Você pode olhar, ver, fazer algo, atuar.
-
Usar estatísticas é fácil, é SQL simples. Você coletou uma solicitação, compilou, enviou, olhou para ela.
-
As estatísticas ajudam a responder a perguntas. Se você tiver dúvidas, consulte as estatísticas - olhe, tire conclusões, analise os resultados.
-
E experimente. Muitos pedidos, muitos dados. Você sempre pode otimizar alguma consulta existente. Você pode fazer sua própria versão da solicitação que melhor lhe convier do que a original e usá-la.
referências
Links válidos que foram encontrados no artigo, com base nos quais, estavam no relatório.
autor escreva mais
(eng.)
O coletor de estatísticas
Funções de administração do sistema
Módulos de contribuição
Utilitários SQL e exemplos de código SQL
Obrigado a todos por sua atenção!
Fonte: habr.com