🥇 Mergullo profundo nas estatísticas internas de PostgreSQL. Alexey Lesovsky

Transcrición do informe de 2015 de Alexey Lesovsky "Mergullo profundo nas estatísticas internas de PostgreSQL"

Exención de responsabilidade do autor do informe: Observo que este informe ten data de novembro de 2015 - pasaron máis de 4 anos e pasou moito tempo. A versión 9.4 que se comenta no informe xa non é compatible. Durante os últimos 4 anos, lanzáronse 5 novos lanzamentos nos que apareceron moitas innovacións, melloras e cambios en materia de estatísticas, e parte do material está desactualizado e non é relevante. Mentres repasei, tentei marcar estes lugares para non enganar o lector. Non reescribín estes lugares, hai moitos e, como resultado, sairá un informe completamente diferente.

O DBMS PostgreSQL é un mecanismo enorme, e este mecanismo consta de moitos subsistemas, cuxo traballo coordinado afecta directamente o rendemento do DBMS. Durante o funcionamento, recóllense estatísticas e información sobre o funcionamento dos compoñentes, o que lle permite avaliar a eficacia de PostgreSQL e tomar medidas para mellorar o rendemento. Non obstante, hai moita esta información e preséntase dunha forma bastante simplificada. Procesar esta información e interpretala ás veces é unha tarefa completamente non trivial, e o "zoo" de ferramentas e utilidades pode confundir facilmente ata un DBA avanzado.

Boas tardes Chámome Aleksey. Como dixo Ilya, vou falar das estatísticas de PostgreSQL.

Estatísticas de actividade de PostgreSQL. PostgreSQL ten dúas estatísticas. Estatísticas da actividade, que se comentarán. E estatísticas do planificador sobre a distribución de datos. Falarei especificamente das estatísticas de actividade de PostgreSQL, que nos permiten xulgar o rendemento e melloralo dalgún xeito.

Direiche como usar as estatísticas de forma eficaz para resolver unha variedade de problemas que teñas ou poidas ter.

Que non aparecerá no informe? No informe, non vou tocar as estatísticas do programador, porque. este é un tema separado para un informe separado sobre como se almacenan os datos na base de datos e como o planificador de consultas se fai unha idea sobre as características cualitativas e cuantitativas destes datos.

E non haberá revisións de ferramentas, non compararei un produto con outro. Non haberá publicidade. Deixémolo.

Quero amosarche que usar estatísticas é útil. É preciso. Úsao sen medo. Todo o que necesitamos é SQL simple e un coñecemento básico de SQL.

E falaremos de que estatísticas escoller para resolver problemas.

Se miramos PostgreSQL e executamos un comando no sistema operativo para ver os procesos, veremos unha "caixa negra". Veremos algúns procesos que fan algo, e polo seu nome podemos imaxinar aproximadamente o que están facendo alí, o que están a facer. Pero, de feito, esta é unha caixa negra, non podemos mirar dentro.

Podemos ver a carga da CPU top, podemos ver a utilización da memoria por parte dalgunhas utilidades do sistema, pero non poderemos mirar dentro de PostgreSQL. Para iso necesitamos outras ferramentas.

E seguindo máis aló, vouvos dicir onde se pasa o tempo. Se representamos a PostgreSQL en forma de tal esquema, entón será posible responder onde se gasta o tempo. Estas son dúas cousas: é o procesamento das solicitudes dos clientes das aplicacións e as tarefas en segundo plano que realiza PostgreSQL para mantelo funcionando.

Se comezamos a mirar na esquina superior esquerda, podemos ver como se procesan as solicitudes dos clientes. A solicitude procede da aplicación e ábrese unha sesión cliente para seguir traballando. A solicitude pásase ao planificador. O planificador crea un plan de consulta. Envíao máis adiante para a súa execución. Hai algún tipo de datos de E/S de bloque asociados a táboas e índices. Os datos necesarios lense dos discos á memoria nunha área especial chamada "búfers compartidos". Os resultados da consulta, se son actualizacións, eliminacións, rexístranse no rexistro de transaccións en WAL. Algunha información estatística pasa ao rexistro ou ao recopilador de estatísticas. E o resultado da solicitude devólvese ao cliente. Despois diso, o cliente pode repetir todo cunha nova solicitude.

Que temos coas tarefas e procesos en segundo plano? Temos varios procesos que manteñen a base de datos en funcionamento nun modo de funcionamento normal. Estes procesos tamén serán tratados no informe: estes son autovacuum, checkpointer, procesos relacionados coa replicación, background writer. Tocarei cada un deles mentres informe.

Cales son os problemas coas estatísticas?

Moita información. PostgreSQL 9.4 ofrece 109 métricas para ver datos estatísticos. Non obstante, se a base de datos almacena moitas táboas, esquemas, bases de datos, entón todas estas métricas terán que ser multiplicadas polo número correspondente de táboas, bases de datos. É dicir, aínda hai máis información. E é moi doado afogar nel.
O seguinte problema é que as estatísticas están representadas por contadores. Se observamos estas estatísticas, veremos contadores en constante aumento. E se pasou moito tempo desde que se restableceron as estatísticas, veremos miles de millóns de valores. E non nos din nada.
Non hai historia. Se tes algún tipo de fallo, algo caeu hai 15-30 minutos, non poderás usar as estatísticas e ver o que pasou hai 15-30 minutos. Este é un problema.
A falta dunha ferramenta integrada en PostgreSQL é un problema. Os desenvolvedores do núcleo non proporcionan ningunha utilidade. Non teñen nada así. Só dan estatísticas na base de datos. Utilízao, faille unha solicitude, o que queiras, despois faino.
Dado que non hai ningunha ferramenta integrada en PostgreSQL, isto causa outro problema. Moitas ferramentas de terceiros. Toda empresa que ten mans máis ou menos directas está tentando escribir o seu propio programa. E como resultado, a comunidade ten moitas ferramentas que pode usar para traballar con estatísticas. E nalgunhas ferramentas hai algunhas funcións, noutras ferramentas non hai outras, ou hai algunhas funcións novas. E xorde a situación de que cómpre empregar dúas, tres ou catro ferramentas que se solapan entre si e teñen funcións diferentes. Isto é moi molesto.

Que se segue disto? É importante poder sacar estatísticas directamente para non depender de programas, ou mellorar dalgún xeito estes programas ti mesmo: engade algunhas funcións para sacar o teu beneficio.

E necesitas coñecementos básicos de SQL. Para obter algúns datos das estatísticas, cómpre facer consultas SQL, é dicir, saber como se fan a selección, a unión.

As estatísticas dinnos varias cousas. Pódense dividir en categorías.

A primeira categoría son os eventos que teñen lugar na base de datos. Isto é cando ocorre algún evento na base de datos: unha consulta, un acceso á táboa, autovacuum, commits, entón estes son todos eventos. Increméntanse os contadores correspondentes a estes eventos. E podemos seguir estes eventos.
A segunda categoría son as propiedades de obxectos como táboas, bases de datos. Teñen propiedades. Este é o tamaño das táboas. Podemos seguir o crecemento das táboas, o crecemento dos índices. Podemos ver cambios na dinámica.
E a terceira categoría é o tempo dedicado ao evento. A solicitude é un evento. Ten a súa propia medida específica de duración. Comezou aquí, aquí rematou. Podemos rastrexalo. Ou o momento de ler un bloque desde o disco ou escribir. Estas cousas tamén son rastrexadas.

As fontes estatísticas preséntanse do seguinte xeito:

Na memoria compartida (shared buffers) hai un segmento para colocar alí datos estáticos, tamén hai aqueles contadores que se incrementan constantemente cando se producen certos eventos, ou xorden algún momento no funcionamento da base de datos.
Todos estes contadores non están dispoñibles para o usuario e nin sequera están dispoñibles para o administrador. Son cousas de baixo nivel. Para acceder a eles, PostgreSQL proporciona unha interface en forma de funcións SQL. Podemos facer seleccións seleccionadas usando estas funcións e obter algún tipo de métrica (ou conxunto de métricas).
Non obstante, non sempre é conveniente usar estas funcións, polo que as funcións son a base das vistas (VIEWs). Trátase de táboas virtuais que proporcionan estatísticas sobre un subsistema específico ou sobre algún conxunto de eventos da base de datos.
Estas vistas integradas (VIEWs) son a principal interface de usuario para traballar con estatísticas. Están dispoñibles de forma predeterminada sen ningunha configuración adicional, podes usalos inmediatamente, miralos e tomar información desde alí. E tamén hai contribucións. As contribucións son oficiais. Pode instalar o paquete postgresql-contrib (por exemplo, postgresql94-contrib), cargar o módulo necesario na configuración, especificar os parámetros para el, reiniciar PostgreSQL e pode usalo. (Nota. Dependendo da distribución, nas versións recentes de contrib o paquete forma parte do paquete principal).
E hai contribucións non oficiais. Non se proporcionan coa distribución estándar de PostgreSQL. Deben compilarse ou instalarse como biblioteca. As opcións poden ser moi diferentes, dependendo do que inventou o desenvolvedor desta contribución non oficial.

Esta diapositiva mostra todas esas vistas (VISTAS) e algunhas desas funcións que están dispoñibles en PostgreSQL 9.4. Como podemos ver, hai moitos. E é moi doado confundirse se o estás a experimentar por primeira vez.

Porén, se tomamos a foto anterior Как тратится время на PostgreSQL e compatible con esta lista, obtemos esta imaxe. Cada vista (VIEWs), ou cada función, podemos empregar para un propósito ou outro para obter as estatísticas adecuadas cando temos PostgreSQL en execución. E xa podemos obter información sobre o funcionamento do subsistema.

O primeiro que veremos é pg_stat_database. Como podemos ver, esta é unha representación. Contén moita información. A información máis variada. E dá un coñecemento moi útil do que temos a suceder na base de datos.

Que podemos levar de aí? Comecemos polas cousas máis sinxelas.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

O primeiro que podemos ver é a porcentaxe de acceso á caché. A porcentaxe de acceso á memoria caché é unha métrica útil. Permítelle estimar cantos datos se toman da caché dos búfers compartidos e cantos se lien do disco.

Está claro que canto máis acertos de caché teñamos, mellor. Avaliamos esta métrica como unha porcentaxe. E, por exemplo, se temos unha porcentaxe destes accesos á caché superior ao 90 %, isto é bo. Se cae por debaixo do 90 %, entón non temos memoria suficiente para manter a cabeza quente de datos na memoria. E para usar estes datos, PostgreSQL vese obrigado a acceder ao disco e isto é máis lento que se os datos fosen lidos da memoria. E cómpre pensar en aumentar a memoria: ou aumentar os búfers compartidos ou aumentar a memoria 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;

Que máis se pode sacar desta presentación? Podes ver as anomalías que se producen na base de datos. Que se mostra aquí? Hai commits, rollbacks, creación de ficheiros temporais, o seu tamaño, puntos mortos e conflitos.

Podemos utilizar esta solicitude. Este SQL é bastante sinxelo. E podemos ver estes datos por nós mesmos.

E aquí están os valores límite. Observamos a proporción de commits e rollbacks. Commits é unha confirmación exitosa da transacción. Rollbacks é un rollback, é dicir, a transacción fixo algún traballo, tensa a base de datos, considerou algo e, a continuación, produciuse un fallo e descartanse os resultados da transacción. é dicir. o número de retrocesos en constante aumento é malo. E deberías evitalos dalgún xeito e editar o código para que isto non ocorra.

Os conflitos están relacionados coa replicación. E tamén deben evitarse. Se tes algunhas consultas que se executan na réplica e xorden conflitos, entón tes que analizar estes conflitos e ver que pasa. Os detalles pódense atopar nos rexistros. E resolver conflitos para que as solicitudes de aplicacións funcionen sen erros.

Os bloqueos tamén son unha mala situación. Cando as solicitudes compiten por recursos, unha solicitude accedeu a un recurso e tomou o bloqueo, a segunda solicitude accedeu ao segundo recurso e tamén tomou o bloqueo e, a continuación, ambas as solicitudes accedían aos recursos da outra e bloqueaban a espera de que o veciño liberase o bloqueo. Esta é tamén unha situación problemática. Deben abordarse a nivel de reescritura de aplicacións e acceso en serie aos recursos. E se ves que os teus puntos mortos aumentan constantemente, cómpre mirar os detalles dos rexistros, analizar as situacións que xurdiron e ver cal é o problema.

Os ficheiros temporais (temp_files) tamén son malos. Cando unha solicitude de usuario non ten memoria suficiente para acomodar os datos operativos e temporais, crea un ficheiro no disco. E todas as operacións que podería realizar nun búfer temporal na memoria, comeza a realizar xa no disco. É lento. Isto aumenta o tempo de execución da consulta. E o cliente que enviou unha solicitude a PostgreSQL recibirá unha resposta un pouco máis tarde. Se todas estas operacións se realizan na memoria, Postgres responderá moito máis rápido e o cliente agardará menos.

pg_stat_bgwriter - Esta vista describe o funcionamento de dous subsistemas de fondo PostgreSQL: checkpointer и background writer.

Para comezar, analicemos os puntos de control, os chamados. checkpoints. Que son os puntos de control? Un punto de control é unha posición no rexistro de transaccións que indica que todos os cambios de datos realizados no rexistro están sincronizados correctamente cos datos do disco. O proceso, dependendo da carga de traballo e da configuración, pode ser longo e consiste principalmente en sincronizar páxinas sucias en búfers compartidos con ficheiros de datos no disco. Para que serve? Se PostgreSQL estivese accedendo ao disco todo o tempo e tomando datos de alí e escribindo datos en cada acceso, sería lento. Polo tanto, PostgreSQL ten un segmento de memoria, cuxo tamaño depende dos parámetros da configuración. Postgres asigna datos operativos nesta memoria para o seu procesamento ou consulta. No caso de solicitudes de cambio de datos, cámbianse. E temos dúas versións dos datos. Un está na memoria, o outro está no disco. E periodicamente cómpre sincronizar estes datos. Necesitamos que o que se modifica na memoria se sincronice co disco. Isto require un punto de control.

Checkpoint pasa por búfers compartidos, marca as páxinas sucias que son necesarias para o checkpoint. A continuación, comeza o segundo paso a través dos búfers compartidos. E as páxinas que están marcadas para o punto de control, xa as sincroniza. Así, os datos xa están sincronizados co disco.

Hai dous tipos de puntos de control. Un punto de control execútase no tempo de espera. Este punto de control é útil e bo - checkpoint_timed. E hai puntos de control baixo demanda - checkpoint required. Tal punto de control ocorre cando temos un rexistro de datos moi grande. Gravamos moitos rexistros de transaccións. E PostgreSQL cre que ten que sincronizar todo isto o máis rápido posible, facer un punto de control e seguir adiante.

E se miraches as estatísticas pg_stat_bgwriter e mira o que tes checkpoint_req é moito maior que checkpoint_timed, entón isto é malo. Por que mal? Isto significa que PostgreSQL está baixo estrés constante cando precisa escribir datos no disco. Checkpoint por timeout é menos estresante e execútase segundo o calendario interno e, por así dicilo, estendido no tempo. PostgreSQL ten a capacidade de facer unha pausa no traballo e non esforzar o subsistema de disco. Isto é útil para PostgreSQL. E as solicitudes que se executan durante o punto de control non experimentarán estrés polo feito de que o subsistema de disco estea ocupado.

E hai tres parámetros para axustar o punto de control:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Permiten controlar o funcionamento dos puntos de control. Pero non vou determe neles. A súa influencia é unha cuestión aparte.

Внимание: A versión 9.4 considerada no informe xa non é relevante. Nas versións modernas de PostgreSQL, o parámetro checkpoint_segments substituído por parámetros min_wal_size и max_wal_size.

O seguinte subsistema é o escritor de fondo − background writer. Que está facendo? Funciona constantemente nun bucle interminable. Analiza páxinas en búfers compartidos e lava as páxinas sucias que atopa no disco. Deste xeito, axuda ao checkpoint a facer menos traballo durante o checkpoint.

Para que máis lle fai falta? Prevé a necesidade de páxinas limpas nos búfers compartidos se son necesarias de súpeto (en grandes cantidades e inmediatamente) para acomodar datos. Supoñamos que xurdiu unha situación cando a solicitude requiría páxinas limpas e xa están en búfers compartidos. Postgres backend só os leva e utilízaos, non ten que limpar nada el mesmo. Pero se de súpeto non hai tales páxinas, o backend fai unha pausa e comeza a buscar páxinas para descargalas no disco e tomalas para as súas propias necesidades, o que afecta negativamente ao tempo da solicitude que se está a executar. Se ves que tes un parámetro maxwritten_clean grande, isto significa que o escritor de fondo non está a facer o seu traballo e cómpre aumentar os parámetros bgwriter_lru_maxpagespara que poida facer máis traballo nun ciclo, limpa máis páxinas.

E outro indicador moi útil é buffers_backend_fsync. Os backends non fan fsync porque é lento. Pasan fsync ao punteiro de verificación da pila de E/S. O checkpointer ten a súa propia cola, procesa fsync periodicamente e sincroniza páxinas na memoria con ficheiros no disco. Se a cola do checkpointer é grande e chea, entón o backend está obrigado a facer fsync por si mesmo e isto ralentiza o backend, é dicir, o cliente recibirá unha resposta máis tarde do que podería. Se ves que tes este valor maior que cero, isto xa é un problema e cómpre prestar atención á configuración do escritor en segundo plano e tamén avaliar o rendemento do subsistema de disco.

Внимание: _O texto seguinte describe as vistas estatísticas asociadas á replicación. A maioría dos nomes de vista e función foron renomeados en Postgres 10. A esencia dos renomeamentos era substituír xlog en wal и location en lsn en nomes de funcións/vistas, etc. Exemplo particular, función pg_xlog_location_diff() foi renomeado por pg_wal_lsn_diff()._

Tamén temos moito aquí. Pero só necesitamos elementos relacionados coa localización.

Se vemos que todos os valores son iguais, entón é ideal e a réplica non se queda atrás do mestre.

Esta posición hexadecimal aquí é a posición no rexistro de transaccións. Aumenta constantemente se hai algunha actividade na base de datos: insercións, eliminacións, 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 estas cousas son diferentes, entón hai algún tipo de atraso. Lag é o atraso da réplica do mestre, é dicir, os datos difiren entre os servidores.

Hai tres razóns para o atraso:

É o subsistema de disco que non pode xestionar as escrituras de sincronización de ficheiros.
Trátase de posibles erros de rede, ou sobrecarga de rede, cando os datos non teñen tempo de chegar á réplica e esta non pode reproducilo.
E o procesador. O procesador é un caso moi raro. E iso vin dúas ou tres veces, pero iso tamén pode pasar.

E aquí hai tres consultas que nos permiten utilizar as estatísticas. Podemos estimar canto se rexistra no noso rexistro de transaccións. Hai tal función pg_xlog_location_diff e podemos estimar o atraso de replicación en bytes e segundos. Tamén usamos o valor desta vista (VIEWs) para iso.

Nota: _En lugar de pg_xlog_locationdiff(), pode usar o operador de subtracción e restar unha localización doutra. Cómodo.

Cun atraso, que é en segundos, hai un momento. Se non hai actividade no mestre, a transacción estivo alí hai uns 15 minutos e non hai actividade, e se observamos este atraso na réplica, veremos un atraso de 15 minutos. Isto paga a pena lembralo. E pode provocar un estupor cando miraches este atraso.

pg_stat_all_tables é outra vista útil. Mostra estatísticas en táboas. Cando temos táboas na base de datos, hai algunha actividade con ela, algunhas accións, podemos obter esta información desde esta vista.

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;

O primeiro que podemos ver son as exploracións secuenciais de táboas. O número en si despois destas pasaxes non é necesariamente malo e non indica que teñamos que facer algo xa.

Non obstante, hai unha segunda métrica: seq_tup_read. Este é o número de filas devoltos pola exploración secuencial. Se o número medio supera os 1, 000, 10, 000, entón este xa é un indicador de que pode ter que construír un índice nalgún lugar para que os accesos sexan por índice, ou sexa posible optimizar consultas que utilizan este tipo de exploracións secuenciais para que isto non ocorre. foi.

Un exemplo sinxelo: digamos que paga a pena unha solicitude cun OFFSET e LIMIT grandes. Por exemplo, escanéanse 100 filas dunha táboa e despois tómanse 000 filas necesarias e descartaranse as filas escaneadas anteriores. Este tamén é un mal caso. E tales solicitudes deben ser optimizadas. E aquí tes unha consulta SQL tan sinxela na que podes vela e avaliar os números recibidos.

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 tamaños da táboa tamén se poden obter usando esta táboa e usando funcións adicionais pg_total_relation_size(), pg_relation_size().

En xeral, hai metacomandos dt и di, que podes usar en PSQL e tamén ver tamaños de táboas e índices.

Porén, o uso das funcións axúdanos a mirar os tamaños das táboas, mesmo tendo en conta índices, ou sen ter en conta os índices, e xa facer algunhas estimacións en función do crecemento da base de datos, é dicir, como medra con nós, con que intensidade, e xa extrae algunhas conclusións sobre a optimización do tamaño.

Escribir actividade. Que é un disco? Vexamos a operación UPDATE – a operación de actualización de filas da táboa. De feito, actualizar son dúas operacións (ou máis). Isto é inserir unha nova versión de fila e marcar a versión antiga de fila como obsoleta. Máis tarde, virá o baleiro automático e limpará estas versións obsoletas das liñas, marca este lugar como dispoñible para a súa reutilización.

Ademais, actualizar non se trata só de actualizar unha táboa. Aínda é unha actualización do índice. Se tes moitos índices na táboa, entón con actualización tamén terás que actualizar todos os índices nos que participan os campos actualizados na consulta. Estes índices tamén terán versións de filas obsoletas que deberán 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 debido ao seu deseño, UPDATE é unha operación pesada. Pero poden ser máis fáciles. Comer hot updates. Apareceron na versión 8.3 de PostgreSQL. E isto que é? Esta é unha actualización lixeira que non fai que os índices sexan reconstruídos. É dicir, actualizamos o rexistro, pero só se actualizou o rexistro da páxina (que pertence á táboa) e os índices seguen apuntando ao mesmo rexistro da páxina. Hai unha lóxica de traballo tan interesante, cando chega o baleiro, entón ten estas cadeas hot reconstrúese e todo segue funcionando sen actualizar os índices, e todo pasa con menos desperdicio de recursos.

E cando teñas n_tup_hot_upd grande, está moi ben. Isto significa que prevalecen as actualizacións lixeiras e isto é máis barato para nós en termos de recursos e todo está ben.

ALTER TABLE table_name SET (fillfactor = 70);

Como aumentar o volume hot updateov? Podemos usar fillfactor. Determina o tamaño do espazo libre reservado ao encher unha páxina nunha táboa mediante INSERT. Cando as insercións van á táboa, enchen completamente a páxina, non deixan espazos baleiros nela. A continuación, destaca unha nova páxina. Os datos enchéranse de novo. E este é o comportamento predeterminado, factor de recheo = 100%.

Podemos establecer o factor de recheo no 70%. É dicir, coas insercións, asignouse unha nova páxina, pero só se encheu o 70% da páxina. E quédanos un 30% en reserva. Cando necesites facer unha actualización, o máis probable é que se produza na mesma páxina e a nova versión da fila encaixará na mesma páxina. E farase hot_update. Isto facilita a escritura nas táboas.

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 autovacuo. Autovacuum é un subsistema deste tipo para o que hai moi poucas estatísticas en PostgreSQL. Só podemos ver nas táboas de pg_stat_activity cantos baleiros temos neste momento. Non obstante, é moi difícil entender cantas mesas ten na cola en movemento.

Nota: _Desde Postgres 10, a situación co seguimento do baleiro mellorou moito - apareceu a vista pg_stat_progressbaleiro, o que simplifica enormemente o tema da vixilancia automática do baleiro.

Podemos usar esta consulta simplificada. E podemos ver cando se debe facer o baleiro. Pero, como e cando debe comezar o baleiro? Estas son as versións antigas das cordas das que falei anteriormente. Produciuse a actualización, inseriuse a nova versión da fila. Apareceu unha versión obsoleta da cadea. Táboa pg_stat_user_tables existe tal parámetro n_dead_tup. Mostra o número de filas "mortas". E en canto o número de filas mortas supere un determinado limiar, chegará á mesa un baleiro automático.

E como se calcula este limiar? Esta é unha porcentaxe moi específica do número total de filas da táboa. Hai un parámetro autovacuum_vacuum_scale_factor. Define a porcentaxe. Digamos que 10 % + hai un limiar base adicional de 50 liñas. E que pasa? Cando temos máis filas mortas que "10% + 50" de todas as filas da táboa, poñemos a táboa no baleiro automático.

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));

Non obstante, hai un punto. Limiares básicos para parámetros av_base_thresh и av_scale_factor pode ser asignado individualmente. E, en consecuencia, o limiar non será global, senón individual para a mesa. Polo tanto, para calcular, hai que usar trucos e trucos. E se che interesa, podes ver a experiencia dos nosos compañeiros de Avito (o enlace da diapositiva non é válido e actualizouse no texto).

Escribiron para plugin muninque teña en conta estas cousas. Hai un pano para pés en dúas follas. Pero pensa correctamente e con bastante eficacia permítenos valorar onde necesitamos moito baleiro para mesas onde hai pouco.

Que podemos facer respecto diso? Se temos unha longa cola e o baleiro automático non pode soportar, entón podemos aumentar o número de traballadores do baleiro ou simplemente facer que o baleiro sexa máis agresivo.para que se dispare antes, procesa a táboa en anacos pequenos. E así a cola diminuirá. - O principal aquí é supervisar a carga nos discos, porque. O baleiro non é gratuíto, aínda que coa chegada dos dispositivos SSD/NVMe, o problema fíxose menos notable.

pg_stat_all_indexes son estatísticas sobre índices. Ela non é grande. E podemos obter información sobre o uso dos índices. E, por exemplo, podemos determinar que índices temos extra.

Como xa dixen, update non só é actualizar táboas, tamén é actualizar índices. En consecuencia, se temos moitos índices na táboa, ao actualizar as filas da táboa, tamén se deben actualizar os índices dos campos indexados e se temos índices non utilizados para os que non hai escaneos de índices, entón colgan connosco como lastre. E cómpre desfacerse deles. Para iso necesitamos un campo idx_scan. Só miramos o número de exploracións de índice. Se os índices teñen cero exploracións durante un período relativamente longo de almacenamento de estatísticas (polo menos 2-3 semanas), entón o máis probable é que estes sexan índices malos, necesitamos desfacernos deles.

Nota: Cando se buscan índices non utilizados no caso dos clústeres de replicación en tempo real, cómpre comprobar todos os nodos do clúster, porque as estatísticas non son globais e, se o índice non se usa no mestre, pódese usar en réplicas (se hai carga).

Dúas ligazóns:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Estes son exemplos de consulta máis avanzados sobre como buscar índices non utilizados.

A segunda ligazón é unha consulta bastante interesante. Hai unha lóxica moi non trivial. Recomendo para revisión.

Que máis se debería resumir por índices?

Os índices non utilizados son malos.
Ocupan espazo.
Ralentiza as operacións de actualización.
Traballo extra para o baleiro.

Se eliminamos os índices non utilizados, só melloraremos a base de datos.

A seguinte vista é pg_stat_activity. Este é un análogo da utilidade ps, só en PostgreSQL. Se ps'Ohm, mira os procesos no sistema operativo, entón pg_stat_activity mostrarache a actividade dentro de PostgreSQL.

Que podemos levar de aí?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Podemos ver a actividade global que está a suceder na base de datos. Podemos facer un novo despregamento. Alí explotou todo, non se aceptan novas conexións, están a verter erros na aplicación.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Podemos realizar unha consulta como esta e ver a porcentaxe total de conexións en relación ao límite máximo de conexións e ver quen temos máis conexións. E neste caso dado, vemos ese usuario cron_role abriu 508 conexións. E pasoulle algo. Hai que tratar con iso e ver. E é moi posible que este sexa algún tipo de número anómalo de conexións.

Se temos unha carga OLTP, as consultas deberían ser rápidas, moi rápidas e non deberían haber consultas longas. Non obstante, se hai solicitudes longas, a curto prazo non hai nada de que preocuparse, pero a longo prazo, as consultas longas danan a base de datos, aumentan o efecto de inchazo das táboas cando se produce a fragmentación da táboa. Tanto as consultas inchadas como as longas deben eliminarse.

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;

Teña en conta: con tal solicitude, podemos definir solicitudes e transaccións longas. Usamos a función clock_timestamp() para determinar o tempo de traballo. Solicitudes longas que atopamos, podemos lembralas, executalas explain, mira os plans e dalgunha maneira optimiza. Filmamos as solicitudes longas actuais e seguimos vivindo.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

As transaccións incorrectas están inactivas nas transaccións e inactivas nas transaccións (abortadas).

Qué significa? As transaccións teñen varios estados. E un destes estados pode levar en calquera momento. Hai un campo para definir estados state nesta visión. E usámolo para determinar o estado.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

E, como dixen máis arriba, estes dous estados inactivo en transacción e inactivo en transacción (abortado) son malos. Que é? É entón cando a aplicación abriu unha transacción, fixo algunhas accións e fixo o seu negocio. A transacción segue aberta. Párgase, non pasa nada nel, leva unha conexión, bloquea as filas modificadas e potencialmente aínda aumenta o inchazo doutras táboas, debido á arquitectura do motor transaccional Postrges. E tales transaccións tamén deberían dispararse, porque son prexudiciais en xeral, en calquera caso.

Se ves que tes máis de 5-10-20 deles na túa base de datos, entón tes que preocuparte e comezar a facer algo con eles.

Aquí tamén usamos para o tempo de cálculo clock_timestamp(). Filmamos transaccións, optimizamos a aplicación.

Como dixen anteriormente, os bloqueos son cando dúas ou máis transaccións compiten por un ou un grupo de recursos. Para iso temos un campo waiting con valor booleano true ou false.

Verdade: isto significa que o proceso está á espera, hai que facer algo. Cando un proceso está esperando, entón o cliente que iniciou o proceso tamén está esperando. O cliente no navegador séntase e tamén espera.

Внимание: _A partir de Postgres 9.6, o campo waiting eliminado e substituído por dous campos informativos máis wait_event_type и wait_event._

¿Que facer? Se ves a verdade durante moito tempo, deberías desfacerte de tales solicitudes. Acabamos de disparar este tipo de transaccións. Escribimos aos desenvolvedores o que hai que optimizar dalgún xeito para que non haxa carreira polos recursos. E entón os desenvolvedores optimizan a aplicación para que isto non ocorra.

E un caso extremo, pero potencialmente non fatal é aparición de bloqueos. Dúas transaccións actualizaron dous recursos, despois acceden de novo a eles, xa a recursos opostos. PostgreSQL neste caso toma e dispara a propia transacción para que a outra poida seguir traballando. Esta é unha situación sen saída e ela non se entende a si mesma. Polo tanto, PostgreSQL vese obrigado a tomar medidas extremas.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

E aquí hai dúas consultas que che permiten rastrexar os bloqueos. Usamos a vista pg_locks, que che permite rastrexar peches pesados.

E a primeira ligazón é o propio texto da solicitude. É bastante longo.

E a segunda ligazón é un artigo sobre peches. É útil para ler, é moi interesante.

Entón, que vemos? Vemos dúas solicitudes. Transacción con ALTER TABLE é unha transacción de bloqueo. Comezou, pero non terminou, e a aplicación que publicou esta transacción está a facer outras cousas nalgún lugar. E a segunda solicitude é a actualización. Espera a que remate a mesa para continuar co seu traballo.

Así é como podemos descubrir quen encerrou a quen, quen ten a quen, e podemos tratar isto aínda máis.

O seguinte módulo é pg_stat_statements. Como dixen, é un módulo. Para usalo, cómpre cargar a súa biblioteca na configuración, reiniciar PostgreSQL, instalar o módulo (cun comando) e despois teremos unha nova vista.

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;

Que podemos levar de aí? Se falamos de cousas sinxelas, podemos tomar o tempo medio de execución da consulta. O tempo está crecendo, o que significa que PostgreSQL responde lentamente e hai que facer algo.

Podemos ver as transaccións de escritura máis activas na base de datos que cambian os datos nos búfers compartidos. Consulta quen actualiza ou elimina datos alí.

E só podemos mirar diferentes estatísticas para estas consultas.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Nós pg_stat_statements usado para crear informes. Restablecemos as estatísticas unha vez ao día. Acumulémolo. Antes de restablecer as estatísticas a próxima vez, creamos un informe. Aquí tedes unha ligazón ao informe. Podes velo.

Que estamos facendo? Calculamos as estatísticas xerais de todas as solicitudes. Despois, para cada consulta, contamos a súa contribución individual a esta estatística global.

E que podemos ver? Podemos ver o tempo total de execución de todas as solicitudes dun tipo particular no fondo de todas as demais solicitudes. Podemos ver o uso da CPU e E/S en relación coa imaxe xeral. E xa para optimizar estas peticións. Estamos a crear consultas principais baseadas neste informe e xa estamos a pensar sobre que optimizar.

Que temos entre bastidores? Aínda quedan algúns envíos que non considerei, porque o tempo é limitado.

Ten pgstattuple tamén é un módulo adicional do paquete estándar de contribucións. Permite avaliar bloat táboas, as chamadas. fragmentación da táboa. E se a fragmentación é grande, cómpre eliminala, usar diferentes ferramentas. E función pgstattuple funciona durante moito tempo. E cantas máis táboas, máis tempo funcionará.

A seguinte contribución é pg_buffercache. Permítelle inspeccionar os búfers compartidos: con que intensidade e para que táboas se utilizan as páxinas do búfer. E só permíteche mirar nos búfers compartidos e avaliar o que está a suceder alí.

O seguinte módulo é pgfincore. Permítelle realizar operacións de táboa de baixo nivel mediante unha chamada ao sistema mincore(), é dicir, permítelle cargar a táboa en búfers compartidos ou descargala. E permite, entre outras cousas, inspeccionar a caché de páxinas do sistema operativo, é dicir, canto ocupa a táboa na caché de páxinas, en búferes compartidos, e simplemente permite avaliar a carga da táboa.

O seguinte módulo é pg_stat_kcache. Tamén usa a chamada do sistema getrusage(). E execútao antes e despois de que se execute a solicitude. E nas estatísticas obtidas, permítenos estimar canto gastou a nosa solicitude en E/S do disco, é dicir, operacións co sistema de ficheiros e analiza o uso do procesador. Non obstante, o módulo é novo (khe-khe) e para o seu traballo require PostgreSQL 9.4 e pg_stat_statements, que mencionei anteriormente.

A capacidade de usar estatísticas é útil. Non precisa software de terceiros. Podes mirar, ver, facer algo, actuar.
Usar estatísticas é doado, é simple SQL. Recolleches unha solicitude, recompilaches, envialas, miraches.
As estatísticas axudan a responder preguntas. Se tes preguntas, recorre ás estatísticas: mira, saca conclusións, analiza os resultados.
E experimentar. Moitas solicitudes, moitos datos. Sempre pode optimizar algunha consulta existente. Podes facer a túa propia versión da solicitude que che conveña mellor que a orixinal e usala.