Transcripción del informe de 2015 de Alexey Lesovsky "Profundización en las estadísticas internas de PostgreSQL"
Descargo de responsabilidad del autor del informe: Observo que este informe tiene fecha de noviembre de 2015: han pasado más de 4 años y ha pasado mucho tiempo. La versión 9.4 discutida en el informe ya no es compatible. En los últimos 4 años, se han lanzado 5 nuevos lanzamientos en los que han aparecido muchas innovaciones, mejoras y cambios con respecto a las estadísticas, y parte del material está desactualizado y no es relevante. Como revisé, traté de marcar estos lugares para no confundir al lector. No reescribí estos lugares, hay muchos y, como resultado, resultará un informe completamente diferente.
El DBMS de PostgreSQL es un mecanismo enorme, y este mecanismo consta de muchos subsistemas, cuyo trabajo coordinado afecta directamente el rendimiento del DBMS. Durante la operación, se recopilan estadísticas e información sobre el funcionamiento de los componentes, lo que le permite evaluar la efectividad de PostgreSQL y tomar medidas para mejorar el rendimiento. Sin embargo, hay mucha de esta información y se presenta de forma bastante simplificada. Procesar esta información e interpretarla a veces es una tarea completamente no trivial, y el "zoológico" de herramientas y utilidades puede confundir fácilmente incluso a un DBA avanzado.
Buenas tardes Mi nombre es Aleksey. Como dijo Ilya, hablaré sobre las estadísticas de PostgreSQL.
Estadísticas de actividad de PostgreSQL. PostgreSQL tiene dos estadísticas. Estadísticas de actividad, de las que se hablará. Y estadísticas del programador sobre la distribución de datos. Hablaré específicamente sobre las estadísticas de actividad de PostgreSQL, que nos permiten juzgar el rendimiento y mejorarlo de alguna manera.
Le diré cómo usar las estadísticas de manera efectiva para resolver una variedad de problemas que tiene o puede tener.
¿Qué no estará en el informe? En el informe, no tocaré las estadísticas del programador, porque. este es un tema aparte para un informe separado sobre cómo se almacenan los datos en la base de datos y cómo el planificador de consultas se hace una idea de las características cualitativas y cuantitativas de estos datos.
Y no habrá revisiones de herramientas, no compararé un producto con otro. No habrá publicidad. Dejemos esto.
Quiero mostrarte que usar estadísticas es útil. Es necesario. Úsalo sin miedo. Todo lo que necesitamos es SQL simple y un conocimiento básico de SQL.
Y hablaremos sobre qué estadísticas elegir para resolver problemas.
Si miramos PostgreSQL y ejecutamos un comando en el sistema operativo para ver los procesos, veremos un "recuadro negro". Veremos algunos procesos que hacen algo, y por su nombre podemos imaginarnos más o menos lo que están haciendo allí, lo que están haciendo. Pero, de hecho, esta es una caja negra, no podemos mirar dentro.
Podemos ver la carga de la CPU en top, podemos ver la utilización de la memoria por parte de algunas utilidades del sistema, pero no podremos mirar dentro de PostgreSQL. Para ello necesitamos otras herramientas.
Y continuando más, te diré dónde se gasta el tiempo. Si representamos a PostgreSQL en forma de un esquema de este tipo, entonces será posible responder dónde se gasta el tiempo. Estas son dos cosas: es el procesamiento de las solicitudes de los clientes de las aplicaciones y las tareas en segundo plano que realiza PostgreSQL para mantenerlo en funcionamiento.
Si comenzamos a mirar en la esquina superior izquierda, podemos ver cómo se procesan las solicitudes de los clientes. La solicitud proviene de la aplicación y se abre una sesión de cliente para seguir trabajando. La solicitud se pasa al planificador. El planificador crea un plan de consulta. Lo envía más lejos para su ejecución. Hay algún tipo de bloque de datos de E/S asociado a tablas e índices. Los datos necesarios se leen de los discos a la memoria en un área especial llamada "búferes compartidos". Los resultados de la consulta, si son actualizaciones, eliminaciones, se registran en el registro de transacciones en WAL. Parte de la información estadística termina en un registro o un recopilador de estadísticas. Y el resultado de la solicitud se devuelve al cliente. Después de eso, el cliente puede repetir todo con una nueva solicitud.
¿Qué tenemos con las tareas en segundo plano y los procesos en segundo plano? Tenemos varios procesos que mantienen la base de datos funcionando y funcionando con normalidad. Estos procesos también se cubrirán en el informe: estos son autovacuum, checkpointer, procesos relacionados con la replicación, escritor de fondo. Me referiré a cada uno de ellos a medida que informe.
¿Cuáles son los problemas con las estadísticas?
- mucha información PostgreSQL 9.4 proporciona 109 métricas para ver datos estadísticos. Sin embargo, si la base de datos almacena muchas tablas, esquemas, bases de datos, todas estas métricas deberán multiplicarse por el número correspondiente de tablas, bases de datos. Es decir, hay aún más información. Y es muy fácil ahogarse en él.
- El siguiente problema es que las estadísticas están representadas por contadores. Si observamos estas estadísticas, veremos contadores en constante aumento. Y si ha pasado mucho tiempo desde que se reiniciaron las estadísticas, veremos miles de millones de valores. Y no nos dicen nada.
- No hay historia. Si tiene algún tipo de falla, algo cayó hace 15-30 minutos, no podrá usar las estadísticas y ver qué sucedió hace 15-30 minutos. Esto es un problema.
- La falta de una herramienta integrada en PostgreSQL es un problema. Los desarrolladores del kernel no proporcionan ninguna utilidad. No tienen nada de eso. Solo dan estadísticas en la base de datos. Úselo, hágale una solicitud, lo que quiera, luego hágalo.
- Dado que no hay una herramienta integrada en PostgreSQL, esto genera otro problema. Muchas herramientas de terceros. Cada empresa que tiene manos más o menos directas está tratando de escribir su propio programa. Y como resultado, la comunidad tiene muchas herramientas que puede usar para trabajar con estadísticas. Y en algunas herramientas hay algunas funciones, en otras herramientas no hay otras funciones, o hay algunas funciones nuevas. Y surge una situación en la que necesita usar dos, tres o cuatro herramientas que se superponen entre sí y tienen diferentes funciones. Esto es muy desagradable.
¿Qué se sigue de esto? Es importante poder tomar estadísticas directamente para no depender de programas, o de alguna manera mejorar estos programas usted mismo: agregue algunas funciones para obtener su beneficio.
Y necesitas conocimientos básicos de SQL. Para obtener algunos datos de las estadísticas, debe realizar consultas SQL, es decir, debe saber cómo se realizan las selecciones y las uniones.
Las estadísticas nos dicen varias cosas. Se pueden dividir en categorías.
- La primera categoría son los eventos que tienen lugar en la base de datos. Esto es cuando ocurre algún evento en la base de datos: una consulta, un acceso a la tabla, autovacuum, confirmaciones, entonces todos estos son eventos. Los contadores correspondientes a estos eventos se incrementan. Y podemos rastrear estos eventos.
- La segunda categoría son las propiedades de objetos como tablas, bases de datos. Tienen propiedades. Este es el tamaño de las tablas. Podemos seguir el crecimiento de las tablas, el crecimiento de los índices. Podemos ver cambios en la dinámica.
- Y la tercera categoría es el tiempo dedicado al evento. La solicitud es un evento. Tiene su propia medida específica de duración. Comenzó aquí, terminó aquí. Podemos rastrearlo. Ya sea el momento de leer un bloque del disco o escribir. Estas cosas también son rastreadas.
Las fuentes de estadísticas se presentan de la siguiente manera:
- En la memoria compartida (shared buffers) existe un segmento para colocar allí datos estáticos, también existen aquellos contadores que se incrementan constantemente cuando ocurren ciertos eventos, o surgen algunos momentos en el funcionamiento de la base de datos.
- Todos estos contadores no están disponibles para el usuario y ni siquiera están disponibles para el administrador. Estas son cosas de bajo nivel. Para acceder a ellos, PostgreSQL proporciona una interfaz en forma de funciones SQL. Podemos hacer selecciones selectas usando estas funciones y obtener algún tipo de métrica (o conjunto de métricas).
- Sin embargo, no siempre es conveniente utilizar estas funciones, por lo que las funciones son la base de las vistas (VIEW). Estas son tablas virtuales que proporcionan estadísticas sobre un subsistema específico o sobre algún conjunto de eventos en la base de datos.
- Estas vistas integradas (VISTAS) son la principal interfaz de usuario para trabajar con estadísticas. Están disponibles de forma predeterminada sin ninguna configuración adicional, puede usarlos inmediatamente, mirar, tomar información desde allí. Y también hay contribuciones. Las contribuciones son oficiales. Puede instalar el paquete postgresql-contrib (por ejemplo, postgresql94-contrib), cargar el módulo necesario en la configuración, especificar parámetros para él, reiniciar PostgreSQL y ya puede usarlo. (Nota. Dependiendo de la distribución, en versiones recientes de contrib el paquete es parte del paquete principal).
- Y hay contribuciones no oficiales. No se suministran con la distribución estándar de PostgreSQL. Deben compilarse o instalarse como una biblioteca. Las opciones pueden ser muy diferentes, dependiendo de lo que se le ocurrió al desarrollador de esta contribución no oficial.
Esta diapositiva muestra todas esas vistas (VISTAS) y algunas de esas funciones que están disponibles en PostgreSQL 9.4. Como podemos ver, hay muchos de ellos. Y es bastante fácil confundirse si lo está experimentando por primera vez.
Sin embargo, si tomamos la imagen anterior Как тратится время на PostgreSQL y compatible con esta lista, obtenemos esta imagen. Cada vista (VIEWs), o cada función, la podemos utilizar para un propósito u otro para obtener las estadísticas adecuadas cuando tenemos PostgreSQL ejecutándose. Y ya podemos obtener alguna información sobre el funcionamiento del subsistema.
Lo primero que veremos es pg_stat_database. Como podemos ver, esto es una representación. Contiene mucha información. La más variada información. Y da un conocimiento muy útil de lo que está pasando en la base de datos.
¿Qué podemos sacar de ahí? Comencemos con las cosas más simples.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Lo primero que podemos mirar es el porcentaje de aciertos de caché. El porcentaje de aciertos de caché es una métrica útil. Le permite estimar la cantidad de datos que se toman del caché de búfer compartido y la cantidad que se lee del disco.
Está claro que cuanto más hit de caché tengamos, mejor. Evaluamos esta métrica como un porcentaje. Y, por ejemplo, si tenemos un porcentaje de estos aciertos de caché superior al 90%, entonces esto es bueno. Si cae por debajo del 90%, entonces no tenemos suficiente memoria para mantener la cabeza caliente de datos en la memoria. Y para usar estos datos, PostgreSQL se ve obligado a acceder al disco y esto es más lento que si los datos se leyeran de la memoria. Y debe pensar en aumentar la memoria: aumente los búferes compartidos o aumente la memoria de hierro (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;¿Qué más se puede sacar de esta presentación? Puede ver las anomalías que se producen en la base de datos. ¿Qué se muestra aquí? Hay confirmaciones, reversiones, creación de archivos temporales, su tamaño, interbloqueos y conflictos.
Podemos usar esta solicitud. Este SQL es bastante simple. Y podemos ver estos datos por nosotros mismos.
Y aquí están los valores umbral. Nos fijamos en la proporción de confirmaciones y reversiones. Commits es una confirmación exitosa de la transacción. Rollbacks es una reversión, es decir, la transacción hizo algún trabajo, forzó la base de datos, consideró algo y luego ocurrió una falla y los resultados de la transacción se descartan. es decir. el número de reversiones en constante aumento es malo. Y debería evitarlos de alguna manera y editar el código para que esto no suceda.
Los conflictos están relacionados con la replicación. Y también deben evitarse. Si tiene algunas consultas que se ejecutan en la réplica y surgen conflictos, debe analizar estos conflictos y ver qué sucede. Los detalles se pueden encontrar en los registros. Y resuelve conflictos para que las solicitudes de aplicaciones funcionen sin errores.
Interbloqueos es también una mala situación. Cuando las solicitudes compiten por los recursos, una solicitud accedió a un recurso y tomó el bloqueo, la segunda solicitud accedió al segundo recurso y también tomó el bloqueo, y luego ambas solicitudes accedieron a los recursos de la otra y se bloquearon esperando que el vecino liberara el bloqueo. Esta es también una situación problemática. Deben abordarse a nivel de reescritura de aplicaciones y serialización del acceso a los recursos. Y si ve que sus puntos muertos aumentan constantemente, debe mirar los detalles en los registros, analizar las situaciones que han surgido y ver cuál es el problema.
Los archivos temporales (temp_files) también son malos. Cuando una solicitud de usuario no tiene suficiente memoria para acomodar los datos temporales operativos, crea un archivo en el disco. Y todas las operaciones que podría realizar en un búfer temporal en la memoria, ya las comienza a realizar en el disco. Es lento. Esto aumenta el tiempo de ejecución de la consulta. Y el cliente que envió una solicitud a PostgreSQL recibirá una respuesta un poco más tarde. Si todas estas operaciones se realizan en memoria, Postgres responderá mucho más rápido y el cliente esperará menos.
pg_stat_bgwriter: esta vista describe el funcionamiento de dos subsistemas de fondo de PostgreSQL: checkpointer и background writer.
Para empezar, analicemos los puntos de control, los llamados. checkpoints. ¿Qué son los puntos de control? Un punto de control es una posición en el registro de transacciones que indica que todos los cambios de datos confirmados en el registro se sincronizan correctamente con los datos en el disco. El proceso, según la carga de trabajo y la configuración, puede ser largo y consiste principalmente en sincronizar páginas sucias en búferes compartidos con archivos de datos en el disco. ¿Para qué sirve? Si PostgreSQL estuviera accediendo al disco todo el tiempo y tomando datos de allí, y escribiendo datos en cada acceso, sería lento. Por lo tanto, PostgreSQL tiene un segmento de memoria, cuyo tamaño depende de los parámetros en la configuración. Postgres asigna datos operativos en esta memoria para su posterior procesamiento o consulta. En el caso de solicitudes de cambio de datos, se modifican. Y obtenemos dos versiones de los datos. Uno está en la memoria, el otro está en el disco. Y periódicamente necesita sincronizar estos datos. Necesitamos que lo que se cambia en la memoria se sincronice con el disco. Esto requiere un punto de control.
El punto de control pasa por los búferes compartidos, marca las páginas sucias que se necesitan para el punto de control. Luego comienza el segundo paso a través de los búferes compartidos. Y las páginas que están marcadas para checkpoint, ya las sincroniza. Por lo tanto, los datos ya están sincronizados con el disco.
Hay dos tipos de puntos de control. Un punto de control se ejecuta en tiempo de espera. Este punto de control es útil y bueno - checkpoint_timed. Y hay puntos de control a pedido - checkpoint required. Tal punto de control ocurre cuando tenemos un registro de datos muy grande. Registramos muchos registros de transacciones. Y PostgreSQL cree que necesita sincronizar todo esto lo más rápido posible, hacer un punto de control y seguir adelante.
Y si miras las estadísticas pg_stat_bgwriter y mira lo que tienes checkpoint_req es mucho más grande que checkpoint_timed, entonces esto es malo. ¿Por qué malo? Esto significa que PostgreSQL está bajo estrés constante cuando necesita escribir datos en el disco. El punto de control por tiempo de espera es menos estresante y se ejecuta de acuerdo con el cronograma interno y, por así decirlo, se extiende en el tiempo. PostgreSQL tiene la capacidad de hacer una pausa en el trabajo y no sobrecargar el subsistema del disco. Esto es útil para PostgreSQL. Y las solicitudes que se ejecutan durante el punto de control no experimentarán estrés por el hecho de que el subsistema del disco está ocupado.
Y hay tres parámetros para ajustar el punto de control:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Le permiten controlar el funcionamiento de los puntos de control. Pero no me detendré en ellos. Su influencia es un tema aparte.
Atencion: La versión 9.4 considerada en el informe ya no es relevante. En las versiones modernas de PostgreSQL, el parámetro checkpoint_segments reemplazado por parámetros min_wal_size и max_wal_size.
El siguiente subsistema es el escritor de fondo: background writer. ¿Qué está haciendo? Se ejecuta constantemente en un bucle sin fin. Escanea páginas en búferes compartidos y vacía las páginas sucias que encuentra en el disco. De esta manera, ayuda al controlador de puntos de control a hacer menos trabajo durante el control.
¿Para qué más se necesita? Proporciona la necesidad de páginas limpias en búferes compartidos si se requieren de repente (en grandes cantidades e inmediatamente) para acomodar datos. Supongamos que surge una situación en la que la solicitud requiere páginas limpias y ya están en búferes compartidos. postgres backend simplemente los toma y los usa, no tiene que limpiar nada él mismo. Pero si de repente no hay tales páginas, el backend se detiene y comienza a buscar páginas para vaciarlas en el disco y tomarlas para sus propias necesidades, lo que afecta negativamente el tiempo de la solicitud que se está ejecutando actualmente. Si ves que tienes un parámetro maxwritten_clean grande, esto significa que el escritor de fondo no está haciendo su trabajo y necesita aumentar los parámetros bgwriter_lru_maxpagespara que pueda hacer más trabajo en un ciclo, borrar más páginas.
Y otro indicador muy útil es buffers_backend_fsync. Los backends no hacen fsync porque es lento. Pasan fsync al punto de control de la pila de IO. El punto de control tiene su propia cola, procesa fsync periódicamente y sincroniza páginas en la memoria con archivos en el disco. Si la cola del punto de control es grande y está llena, entonces el backend se ve obligado a hacer fsync por sí mismo y esto ralentiza el backend, es decir, el cliente recibirá una respuesta más tarde de lo que podría. Si ves que tienes este valor mayor que cero, entonces esto ya es un problema y debe prestar atención a la configuración del escritor de fondo y también evaluar el rendimiento del subsistema del disco.
Atencion: _El siguiente texto describe las vistas estadísticas asociadas con la replicación. La mayoría de los nombres de vistas y funciones se han renombrado en Postgres 10. La esencia de los renombrados fue reemplazar xlog en wal и location en lsn en nombres de funciones/vistas, etc. Ejemplo particular, función pg_xlog_location_diff() fue renombrado a pg_wal_lsn_diff()._
Aquí también tenemos mucho. Pero solo necesitamos elementos relacionados con la ubicación.
Si vemos que todos los valores son iguales, entonces esto es ideal y la réplica no se queda atrás del maestro.
Esta posición hexadecimal aquí es la posición en el registro de transacciones. Aumenta constantemente si hay alguna actividad en la base de datos: inserciones, eliminaciones, 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());Si estas cosas son diferentes, entonces hay algún tipo de retraso. Lag es la demora de la réplica del maestro, es decir, los datos difieren entre servidores.
Hay tres razones para el retraso:
- Es el subsistema del disco el que no puede manejar escrituras de sincronización de archivos.
- Son posibles errores de red, o sobrecarga de la red, cuando los datos no tienen tiempo de llegar a la réplica y esta no puede reproducirlos.
- Y el procesador. El procesador es un caso muy raro. Y lo he visto dos o tres veces, pero eso también puede pasar.
Y aquí hay tres consultas que nos permiten usar estadísticas. Podemos estimar cuánto se registra en nuestro registro de transacciones. Hay tal función pg_xlog_location_diff y podemos estimar el retraso de replicación en bytes y segundos. También usamos el valor de esta vista (VIEWs) para esto.
Nota: _En lugar de pg_xlog_ubicacióndiff(), puede usar el operador de resta y restar una ubicación de otra. Cómodo.
Con un retraso, que es en segundos, hay un momento. Si no hay actividad en el maestro, la transacción estuvo allí hace unos 15 minutos y no hay actividad, y si observamos este retraso en la réplica, veremos un retraso de 15 minutos. Vale la pena recordar esto. Y puede llevar a un estupor cuando vio este retraso.
pg_stat_all_tables es otra vista útil. Muestra estadísticas en tablas. Cuando tenemos tablas en la base de datos, hay alguna actividad con ellas, algunas acciones, podemos obtener 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;Lo primero que podemos ver son los escaneos de tablas secuenciales. El número en sí mismo después de estos pasajes no es necesariamente malo y no indica que debamos hacer algo ya.
Sin embargo, hay una segunda métrica: seq_tup_read. Este es el número de filas devueltas del escaneo secuencial. Si el número promedio supera los 1, 000 10, 000 50, 000 100, entonces esto ya es un indicador de que es posible que deba crear un índice en algún lugar para que los accesos sean por índice, o es posible optimizar las consultas que usan tales escaneos secuenciales para que esto no pasa.era.
Un ejemplo simple: digamos que una solicitud con un gran DESPLAZAMIENTO y LÍMITE vale la pena. Por ejemplo, se escanean 100 000 filas en una tabla y luego se toman 50 000 filas requeridas, y las filas escaneadas anteriores se descartan. Este también es un mal caso. Y tales solicitudes deben optimizarse. Y aquí hay una consulta SQL tan simple en la que puede verla y evaluar los 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;Los tamaños de las tablas también se pueden obtener usando esta tabla y usando funciones adicionales pg_total_relation_size(), pg_relation_size().
En general, hay metacomandos dt и di, que puede usar en PSQL y también ver los tamaños de tablas e índices.
Sin embargo, el uso de funciones nos ayuda a mirar los tamaños de las tablas, incluso teniendo en cuenta los índices, o sin tener en cuenta los índices, y ya hacer algunas estimaciones en función del crecimiento de la base de datos, es decir, cómo crece con nosotros, con qué intensidad, y ya sacar algunas conclusiones sobre la optimización del tamaño.
Escribir actividad. ¿Qué es un registro? Veamos la operación. UPDATE – la operación de actualización de filas en la tabla. De hecho, la actualización son dos operaciones (o incluso más). Esto es insertar una nueva versión de fila y marcar la versión de fila anterior como obsoleta. Más tarde, autovacuum vendrá y limpiará estas versiones obsoletas de las líneas, marcará este lugar como disponible para su reutilización.
Además, la actualización no se trata solo de actualizar una tabla. Todavía es una actualización de índice. Si tiene muchos índices en la tabla, entonces con la actualización, todos los índices en los que participan los campos actualizados en la consulta también deberán actualizarse. Estos índices también tendrán versiones de fila obsoletas que deberán limpiarse.
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;Y por su diseño, UPDATE es una operación de peso pesado. Pero se pueden hacer más fáciles. Comer hot updates. Aparecieron en PostgreSQL versión 8.3. ¿Y esto qué es? Esta es una actualización ligera que no hace que se reconstruyan los índices. Es decir, actualizamos el registro, pero solo se actualizó el registro en la página (que pertenece a la tabla), y los índices aún apuntan al mismo registro en la página. Hay un poco de una lógica de trabajo tan interesante, cuando llega un vacío, entonces tiene estas cadenas. hot reconstruye y todo continúa funcionando sin actualizar los índices, y todo sucede con menos desperdicio de recursos.
y cuando tienes n_tup_hot_upd grande, es muy bueno. Esto quiere decir que priman las actualizaciones ligeras y esto nos sale más barato en cuanto a recursos y todo va bien.
ALTER TABLE table_name SET (fillfactor = 70);Cómo aumentar el volumen hot updateov? Nosotros podemos usar fillfactor. Determina el tamaño del espacio libre reservado al llenar una página en una tabla usando INSERT. Cuando las inserciones van a la mesa, llenan completamente la página, no dejan espacios vacíos en ella. Luego se resalta una nueva página. Los datos se rellenan de nuevo. Y este es el comportamiento por defecto, factor de relleno = 100%.
Podemos establecer el factor de relleno al 70%. Es decir, con inserciones, se asignó una nueva página, pero solo se llenó el 70% de la página. Y nos queda un 30% en reserva. Cuando necesite hacer una actualización, lo más probable es que suceda en la misma página y la nueva versión de la fila se ajuste a la misma página. Y se hará hot_update. Esto hace que sea más fácil escribir en las tablas.
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));Cola de autovacío. Autovacuum es un subsistema para el que hay muy pocas estadísticas en PostgreSQL. Solo podemos ver en las tablas en pg_stat_activity cuantas aspiradoras tenemos en este momento. Sin embargo, es muy difícil entender cuántas mesas en la cola tiene sobre la marcha.
Nota: _Desde Postgres 10, la situación con el seguimiento del vacío de vacío ha mejorado mucho: apareció la vista pg_stat_progressvacío, lo que simplifica enormemente el tema del monitoreo de autovacío.
Podemos usar esta consulta simplificada. Y podemos ver cuándo se debe hacer el vacío. Pero, ¿cómo y cuándo debe empezar el vacío? Estas son las versiones antiguas de las cadenas de las que hablé anteriormente. Se ha producido una actualización, se ha insertado la nueva versión de la fila. Ha aparecido una versión obsoleta de la cadena. Mesa pg_stat_user_tables hay tal parámetro n_dead_tup. Muestra el número de filas "muertas". Y tan pronto como el número de filas muertas haya superado un cierto umbral, un autovacío vendrá a la mesa.
¿Y cómo se calcula este umbral? Este es un porcentaje muy específico del número total de filas en la tabla. Hay un parámetro autovacuum_vacuum_scale_factor. Define el porcentaje. Digamos 10% + hay un umbral base adicional de 50 líneas. ¿Y que pasa? Cuando tenemos más filas muertas que "10% + 50" de todas las filas de la tabla, ponemos la tabla en autovacío.
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));Sin embargo, hay un punto. Umbrales básicos para parámetros av_base_thresh и av_scale_factor pueden ser asignados individualmente. Y, en consecuencia, el umbral no será global, sino individual para la mesa. Por lo tanto, para calcular, debe usar trucos y trucos. Y si está interesado, puede consultar la experiencia de nuestros compañeros de Avito (el enlace de la diapositiva no es válido y se ha actualizado en el texto).
escribieron para que tenga en cuenta estas cosas. Hay un cubrepiés sobre dos sábanas. Pero él considera correctamente y con bastante eficacia nos permite evaluar dónde necesitamos mucho vacío para las mesas, donde hay poco.
¿Qué podemos hacer al respecto? Si tenemos una cola larga y el autovacío no da abasto, podemos aumentar el número de aspiradores o simplemente hacer que el aspirador sea más agresivo.para que se active antes, procesa la mesa en pedazos pequeños. Y así la cola disminuirá. - Lo principal aquí es monitorear la carga en los discos, porque. Lo del vacío no es gratis, aunque con la llegada de los dispositivos SSD/NVMe, el problema se ha vuelto menos notorio.
pg_stat_all_indexes son estadísticas sobre índices. ella no es grande Y podemos obtener información sobre el uso de índices a partir de él. Y por ejemplo, podemos determinar qué índices tenemos extra.
Como ya dije update no solo actualiza tablas, también actualiza índices. En consecuencia, si tenemos muchos índices en la tabla, al actualizar las filas de la tabla, los índices de los campos indexados también deben actualizarse, y si tenemos índices sin usar para los cuales no hay escaneos de índices, entonces se cuelgan con nosotros como lastre. Y tienes que deshacerte de ellos. Para esto necesitamos un campo idx_scan. Solo miramos el número de escaneos de índice. Si los índices tienen cero escaneos durante un período relativamente largo de almacenamiento de estadísticas (al menos 2-3 semanas), lo más probable es que estos sean índices malos, debemos deshacernos de ellos.
Nota: Al buscar índices no utilizados en el caso de clústeres de replicación de transmisión, debe verificar todos los nodos del clúster, porque las estadísticas no son globales, y si el índice no se usa en el maestro, entonces se puede usar en las réplicas (si hay una carga).
Dos enlaces:
Estos son ejemplos de consulta más avanzados sobre cómo buscar índices no utilizados.
El segundo enlace es una consulta bastante interesante. Hay una lógica muy no trivial en ello. Lo recomiendo para revisión.
¿Qué más se debe resumir mediante índices?
-
Los índices no utilizados son malos.
-
Ocupan espacio.
-
Ralentizar las operaciones de actualización.
-
Trabajo extra para la aspiradora.
Si eliminamos los índices no utilizados, solo mejoraremos la base de datos.
La siguiente vista es pg_stat_activity. Este es un análogo de la utilidad. ps, solo en PostgreSQL. Si psOh, miras los procesos en el sistema operativo, luego pg_stat_activity le mostrará la actividad dentro de PostgreSQL.
¿Qué podemos sacar de ahí?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Podemos ver la actividad general que está ocurriendo en la base de datos. Podemos hacer un nuevo despliegue. Ahí explotó todo, no se aceptan nuevas conexiones, abundan los errores en la aplicación.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Podemos ejecutar una consulta como esta y ver el porcentaje total de conexiones en relación con el límite máximo de conexiones y ver quiénes tenemos más conexiones. Y en este caso dado, vemos que el usuario cron_role abrió 508 conexiones. Y algo le pasó. Necesitas lidiar con eso y ver. Y es muy posible que se trate de algún tipo de número anómalo de conexiones.
Si tenemos una carga OLTP, las consultas deben ser rápidas, muy rápidas y no deben ser consultas largas. Sin embargo, si hay solicitudes largas, a corto plazo no hay nada de qué preocuparse, pero a largo plazo, las consultas largas dañan la base de datos, aumentan el efecto de hinchamiento de las tablas cuando se produce la fragmentación de la tabla. Tanto las consultas excesivas como las largas 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;Tenga en cuenta: con dicha solicitud, podemos definir solicitudes y transacciones largas. Usamos la función clock_timestamp() para determinar el tiempo de trabajo. Solicitudes largas que encontramos, podemos recordarlas, ejecutarlas explain, mirar planes y de alguna manera optimizar. Grabamos las solicitudes largas actuales y seguimos viviendo.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Las transacciones incorrectas son transacciones inactivas e inactivas en transacciones (abortadas).
¿Qué significa? Las transacciones tienen varios estados. Y uno de estos estados puede tomar en cualquier momento. Hay un campo para definir estados state en esta vista. Y lo usamos para determinar el estado.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Y, como dije arriba, estos dos estados inactivo en transacción e inactivo en transacción (abortado) son malos. ¿Lo que es? Esto es cuando la aplicación abrió una transacción, realizó algunas acciones y siguió con su negocio. La transacción permanece abierta. Se bloquea, no sucede nada en él, toma una conexión, se bloquea en las filas modificadas y potencialmente aún aumenta la hinchazón de otras tablas, debido a la arquitectura del motor transaccional de Postrges. Y tales transacciones también deben ser fusiladas, porque son perjudiciales en general, en cualquier caso.
Si ve que tiene más de 5-10-20 de ellos en su base de datos, entonces debe preocuparse y comenzar a hacer algo con ellos.
Aquí también usamos para el tiempo de cálculo clock_timestamp(). Disparamos transacciones, optimizamos la aplicación.
Como dije anteriormente, los bloqueos son cuando dos o más transacciones compiten por uno o un grupo de recursos. Para esto tenemos un campo waiting con valor booleano true o false.
Cierto: esto significa que el proceso está esperando, se debe hacer algo. Cuando un proceso está esperando, el cliente que inició el proceso también está esperando. El cliente en el navegador se sienta y también espera.
Atencion: _A partir de Postgres 9.6, el campo waiting eliminado y reemplazado por dos campos más informativos wait_event_type и wait_event._
¿Qué hacer? Si ve verdadero durante mucho tiempo, entonces debe deshacerse de tales solicitudes. Simplemente filmamos tales transacciones. Escribimos a los desarrolladores lo que debe optimizarse de alguna manera para que no haya carrera por los recursos. Y luego los desarrolladores optimizan la aplicación para que esto no suceda.
Y el caso extremo, pero al mismo tiempo potencialmente no fatal es aparición de interbloqueos. Dos transacciones han actualizado dos recursos, luego vuelven a acceder a ellos, ya a recursos opuestos. PostgreSQL en este caso toma y dispara la transacción en sí para que la otra pueda seguir funcionando. Esta es una situación sin salida y ella no se entiende a sí misma. Por lo tanto, PostgreSQL se ve obligado a tomar medidas extremas.
Y aquí hay dos consultas que le permiten rastrear bloqueos. Usamos la vista pg_locks, que le permite rastrear bloqueos pesados.
Y el primer enlace es el texto de la solicitud en sí. es bastante largo
Y el segundo enlace es un artículo sobre cerraduras. Es útil para leer, es muy interesante.
Entonces ¿Qué vemos? Vemos dos solicitudes. Transacción con ALTER TABLE es una transacción de bloqueo. Comenzó, pero no terminó, y la aplicación que publicó esta transacción está haciendo otras cosas en alguna parte. Y la segunda solicitud es actualizar. Espera a que la mesa del altar termine antes de continuar con su trabajo.
Así es como podemos averiguar quién encerró a quién, quién retiene a quién, y podemos seguir lidiando con esto.
El próximo módulo es pg_stat_statements. Como dije, es un módulo. Para usarlo, debe cargar su biblioteca en la configuración, reiniciar PostgreSQL, instalar el módulo (con un comando) y luego tendremos una nueva 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;¿Qué podemos sacar de ahí? Si hablamos de cosas simples, podemos tomar el tiempo promedio de ejecución de consultas. El tiempo está creciendo, lo que significa que PostgreSQL está respondiendo lentamente y es necesario hacer algo.
Podemos ver las transacciones de escritura más activas en la base de datos que cambian los datos en los búferes compartidos. Vea quién actualiza o elimina datos allí.
Y podemos mirar diferentes estadísticas para estas solicitudes.
nosotros pg_stat_statements utilizado para construir informes. Restablecemos las estadísticas una vez al día. Vamos a acumularlo. Antes de restablecer las estadísticas la próxima vez, creamos un informe. Aquí hay un enlace al informe. Puedes verlo.
¿Que estamos haciendo? Calculamos las estadísticas generales para todas las solicitudes. Luego, para cada consulta, contamos su contribución individual a esta estadística general.
¿Y qué podemos ver? Podemos ver el tiempo total de ejecución de todas las solicitudes de un tipo particular en el contexto de todas las demás solicitudes. Podemos observar el uso de CPU y E/S en relación con el panorama general. Y ya para optimizar estas solicitudes. Estamos creando consultas principales basadas en este informe y ya estamos pensando en qué optimizar.
¿Qué tenemos detrás de escena? Todavía hay algunas presentaciones que no consideré, porque el tiempo es limitado.
Hay pgstattuple también es un módulo adicional del paquete de contribuciones estándar. Te permite evaluar bloat mesas, los llamados. fragmentación de tablas. Y si la fragmentación es grande, debe eliminarla, use diferentes herramientas. y funcion pgstattuple funciona durante mucho tiempo. Y cuantas más tablas, más tiempo funcionará.
La siguiente contribución es pg_buffercache. Le permite inspeccionar los búferes compartidos: con qué intensidad y para qué tablas se utilizan las páginas del búfer. Y solo le permite mirar en los búferes compartidos y evaluar lo que está sucediendo allí.
El próximo módulo es pgfincore. Le permite realizar operaciones de tabla de bajo nivel a través de una llamada al sistema mincore(), es decir, le permite cargar la tabla en búferes compartidos o descargarla. Y permite, entre otras cosas, inspeccionar la caché de la página del sistema operativo, es decir, cuánto ocupa la tabla en la caché de la página, en los búferes compartidos, y simplemente te permite evaluar la carga en la tabla.
El próximo módulo es pg_stat_kcache. También utiliza la llamada al sistema. getrusage(). Y lo ejecuta antes y después de ejecutar la solicitud. Y en las estadísticas obtenidas, nos permite estimar cuánto gastó nuestra solicitud en E/S de disco, es decir, operaciones con el sistema de archivos y mira el uso del procesador. Sin embargo, el módulo es joven (khe-khe) y para su trabajo requiere PostgreSQL 9.4 y pg_stat_statements, que mencioné anteriormente.
-
La capacidad de usar estadísticas es útil. No necesita software de terceros. Puedes mirar, ver, hacer algo, actuar.
-
Usar estadísticas es fácil, es SQL simple. Recolectó una solicitud, la compiló, la envió, la miró.
-
Las estadísticas ayudan a responder preguntas. Si tiene preguntas, recurre a las estadísticas: mire, saque conclusiones, analice los resultados.
-
Y experimentar. Muchas solicitudes, muchos datos. Siempre puede optimizar alguna consulta existente. Puede hacer su propia versión de la solicitud que más le convenga mejor que la original y usarla.
referencias
Vínculos válidos que se encontraron en el artículo, en función de los cuales, estaban en el informe.
Autor escribe más
(esp)
El recopilador de estadísticas
Funciones de administración del sistema
Módulos de contribución
Utilidades SQL y ejemplos de código sql
Gracias a todos por su atención!
Fuente: habr.com