análise de performance usando as estatísticas do postgresql

Analise de performance usando as estatısticas do PostgreSQL

Matheus de Oliveira<[email protected]>

* Keynote apresentada no PGDay Campinas 2014

http://www.pgdaycampinas.com.br/

Quem sou eu?

Matheus de Oliveira • Ciencias de Computacao – USP• DBA PostgreSQL na Dextra e instrutor na

Dextraining a mais de 2 anos• Apaixonado pelo PG (do psql ao codigo-fonte...)

• Aplicando atualmente uso de estatısticas nopgAnalytics (http://pganalytics.com.br/)

Dextra • Desenvolvimento sob-medida, consultoria/suporte• Mais de 15 anos trabalhando com o PostgreSQL• “Crafting Software, Transforming Business”• http://www.dextra.com.br/

Dextraining • Treinamento de verdade, “Aprenda com quem faz napratica”

• Mais de 10 anos na estrada• Mais de 10 mil profissionais capacitados em todo o

Brasil.• http://www.dextraining.com.br/

2 / 29Analise de performance usando as estatısticas do PostgreSQL – Matheus de Oliveira – PGDay Campinas 2014

http://pganalytics.com.br/

http://www.dextra.com.br/

http://www.dextraining.com.br/

Quem sou eu?


Dextraining a mais de 2 anos• Apaixonado pelo PG (do psql ao codigo-fonte...)• Aplicando atualmente uso de estatısticas no

pgAnalytics (http://pganalytics.com.br/)

Dextra • Desenvolvimento sob-medida, consultoria/suporte• Mais de 15 anos trabalhando com o PostgreSQL• “Crafting Software, Transforming Business”• http://www.dextra.com.br/








Quem sou eu?


Dextraining a mais de 2 anos• Apaixonado pelo PG (do psql ao codigo-fonte...)• Aplicando atualmente uso de estatısticas no

pgAnalytics (http://pganalytics.com.br/)Dextra • Desenvolvimento sob-medida, consultoria/suporte

• Mais de 15 anos trabalhando com o PostgreSQL• “Crafting Software, Transforming Business”• http://www.dextra.com.br/








We are Hiring

Interessados:

[email protected]

http://www.dextra.com.br/carreiras/

mailto:[email protected]

http://www.dextra.com.br/carreiras/

Estatısticas do PostgreSQL

Basicamente 2 tipos de estatısticas:

• estatısticas de distribuicao dos dados

• estatısticas de analise e monitoramento


Estatısticas de distribuicao dedados

It’s all about performance...

• O PostgreSQL prove:

◦ Mecanismos de cache eficiente◦ Estruturas de dados eficientes◦ Diversos metodos de acesso



• Para isso, o PostgreSQL precisa de:◦ Mecanismos de cache eficiente

• voce precisa oferecer recursos e configurar para seu uso otimizado

◦ Estruturas de dados eficientes

• voce precisa comandar a criacao dessas estruturas

◦ Diversos metodos de acesso

• necessario informacoes estatısticas sobre os dados para escolha do melhor• voce geralmente nao precisa se preocupar com isso... :)• mas as vezes precisa... : (








• necessario informacoes estatısticas sobre os dados para escolha do melhor

• voce geralmente nao precisa se preocupar com isso... :)• mas as vezes precisa... : (








• necessario informacoes estatısticas sobre os dados para escolha do melhor• voce geralmente nao precisa se preocupar com isso... :)

• mas as vezes precisa... : (








• necessario informacoes estatısticas sobre os dados para escolha do melhor• voce geralmente nao precisa se preocupar com isso... :)• mas as vezes precisa... : (


Indices... Indices... Indices...

1

8 16 28

1 7 8 9 12 16 21 25 28 30 32 36

B−tree Tabela:

28

12

9

7

8

16

25

30

32

21

36

...

...

...

...

...

...

...

...

...

...

...

...


Indices... Indices... Indices...

Buscas em ındices sao melhores quando:

• deseja-se encontrar um valor unico ou uma pequena faixa devalores

E nao sao boas quando:

• a busca retorna grande parte da tabela

E como o PostgreSQL sabe qual a proporcao de uma tabela aconsulta ira retornar?


Histogramas


Histogramas

SELECT * FROM vendasWHERE dtvenda

BETWEEN ’2013 -07 -01 ’AND ’2013 -12 -31 ’;

Pequena porcao da tabela(∼= 4% da tabela):

usar acesso indexado


Histogramas

SELECT * FROM vendasWHERE dtvenda

BETWEEN ’2014 -01 -01 ’AND ’2014 -06 -31 ’;

Grande porcao da tabela (∼= 82%da tabela):

usar acesso sequencial


Outras Informacoes

Alem dos histogramas – pg stats.histogram bounds 1 –, saoarmazenadas outras estatısticas, como:

• Seletividade dos atributos (numero de elementos distintos) –pg stats.n distinct

• Correlacao dos atributos (ordenacao fısica) –pg stats.correlation

• Atributos mais comuns – pg stats.most common *

• Media de tamanho dos atributos – pg stats.avg width

• Entre outras...

1OBS: o histograma do PostgreSQL e um pouco diferente do apresentadoaqui, mas segue a mesma ideia



• Estatısticas da distribuicao dos dados:

◦ sao atualizadas sob demanda pelo autovacuum (voce nao precisafazer isso)

◦ armazenadas na tabela de catalogo pg statistic (use a viewpg stats para consultar)

◦ utilizadas pelo planejador de consultas◦ se ficarem desatualizadas podem degradar a performance do sistema

drasticamente



• Estatısticas da distribuicao dos dados:◦ sao atualizadas sob demanda pelo autovacuum (voce nao precisa

fazer isso)◦ armazenadas na tabela de catalogo pg statistic (use a view

pg stats para consultar)◦ utilizadas pelo planejador de consultas◦ se ficarem desatualizadas podem degradar a performance do sistema

drasticamente


Estatısticas para Analise eMonitoramento

Estatısticas para Analise e Monitoramento

• Durante operacao, os processos incrementam contadores emedidores de tempo das operacoes realizadas (numero de tuplasinseridas numa tabela, tempo de escrita dos dados em disco,utilizacao de um ındice, etc.)

• Cada processo envia essas informacoes via comunicacao UDP parao stats collector

• O stats collector armazena esses dados em estruturas especiais

• O resultado sumarizado dessas informacoes estao presentes paraconsulta em views especiais, nomeadas pg stat * epg statio *


Verificacao do uso de memoria

• Verificacao de uso de cache pela pg stat database:

=> SELECT sum ( blks_hit ) /-> sum (( blks_read + blks_hit ):: numeric )-> FROM pg_stat_database-> WHERE blks_read + blks_hit <> 0;

cache_ratio-- ----------------------

0.73245583731133246875(1 row )

Valores muito baixo podem indicar pouca memoria parashared buffers


Verificacao do processo de CHECKPOINTs

• Verificando escrita de buffers pelo processo checkpointer VSbackends pg stat bgwriter:

=> SELECT buffers_checkpoint /-> ( buffers_checkpoint + buffers_backend )-> :: numeric AS checkpointer_ratio-> FROM pg_stat_bgwriter ;

checkpointer_ratio-- ------------------

0.923462373(1 row )

Buscamos um valor mais alto (sendo 1, 0 a taxa ideal), valoresproximos a 0, 0 podem indicar a necessidade de forcar mais

CHECKPOINTs ou aumentar a shared buffers


Verificacao do processo de CHECKPOINT

• Verificacao das requisicoes de CHECKPOINT pelapg stat bgwriter:

=> SELECT checkpoints_timed /-> ( checkpoints_timed + checkpoints_req )-> :: numeric AS timed_ratio-> FROM pg_stat_bgwriter ;

timed_ratio-- -----------

0.769053117(1 row )

Tambem buscamos taxas proximas a 1, 0, sendo que valoresmuito baixos podem indicar necessidade de aumento do

parametro checkpoint segments ou reducao docheckpoint timeout


Utilizacao de Arquivos Temporarios

• Tambem utilizando a pg stat database, podemos ver otamanho de arquivos temporarios gerados:

=> SELECT pg_size_pretty ( sum ( temp_bytes ))-> AS size-> FROM pg_stat_database ;

size-- -------

2500 MB(1 row )

Utilizacao de arquivos temporarios degenera a performance, podeser preciso aumentar o work mem


Verificacao de Tabelas com Muito “Seq-Scans”

• Usando a visao pg stat user tables:

=> SELECT relname , seq_scan , idx_scan-> FROM pg_stat_user_tables-> ORDER BY seq_scan DESC LIMIT 10;

relname | seq_scan | idx_scan-- -------+----------+----------

vendas | 5586 | 0cidades | 84 | 826

...

Que tal olhar com carinho as consultas dessas tabelas? Maslembre-se, para tabelas pequenas ou consultas que trazem

grandes porcoes da tabela “seq-scan” nao e ruim


Verificacao de Indices Nao Utilizados

• Para informacoes de ındices especıficos usamos apg stat user indexes:

=> SELECT relname , indexrelname-> FROM pg_stat_user_indexes-> WHERE idx_scan = 0;

relname | indexrelname-- -------+--------------------

vendas | vendas_total_idxvendas | vendas_empresa_idx

...

Indices nao utilizados, muitas vezes podem ser removidos. Masnem sempre!


Nem tudo sao flores...

• Como fazer a analise dessas estatısticas apenas num dadoperıodo?

• Elas sao incrementais, logo englobam um grande perıodo detempo:

=> SELECT stats_reset FROM pg_stat_database-> WHERE datname = current_database ();

stats_reset-- -----------------------------

2014 -04 -28 01:56:51.790651 -03(1 row )


Reinicializacao das Estatısticas

Estatısticas sao zeradas quando:

• uma queda do sistema acontece (ou “stop immediate” e usado)

• e feita a chamada das funcoes pg stat reset epg stat reset shared

Reiniciar as estatısticas inviabiliza a operacao plena do autovacuum


Solucoes...

• Salvar snapshot de estatısticas:

=> CREATE TABLE stat_bgwriter_20140910-> AS SELECT * FROM pg_stat_bgwriter ;=> CREATE TABLE stat_database_20140910-> AS SELECT * FROM pg_stat_database ;


Solucoes...

Apenas essas estatısticas nao vao ser suficientes, voce precisa:

• Verificacao dos comandos SQL executados no banco de dados:◦ buscar consultas direto da aplicacao :/◦ modulo pg stat statement◦ uso de analise de logs (pg log/)

• Informacoes historicas (snapshots, graficos, etc.)

• Controle das alteracoes realizadas no ambiente


Resumindo...

Resumo das Estatısticas

pg statistic pg stat * / pg statio *Quem? manualmente

autovacuumtodos os processos

Quando? manualmentesob-demanda

o tempo todo

Como? INSERT, UPDATE,DELETE

UDP para o stats collector

Onde? tabelapg statistic

arquivo(s) no diretorio$PGDATA/pg stat

Pra que? planejador de consul-tas

autovacuum, monitoramento,analise


Obrigado!

Duvidas?

Matheus de Oliveira<[email protected]>

IRC – irc.freenode.net:/join #postgresql,#postgresql-br,#dextra

Meu nick: MatheusOl

Twitter: @matioli matheusLinkedIn: br.linkedin.com/in/matheusdeoliveira/

SlideShare: slideshare.net/matheus de oliveira

mailto:[email protected]

https://twitter.com/matioli_matheus

http://br.linkedin.com/in/matheusdeoliveira/

http://www.slideshare.net/matheus_de_oliveira

análise de performance usando as estatísticas do postgresql

Technology