Ahorre un centavo en grandes volúmenes en PostgreSQL

Continuando con el tema del registro de grandes flujos de datos planteado por artículo anterior sobre particionamiento, en esto veremos las formas en que puedes Reducir el tamaño “físico” del contenido almacenado. en PostgreSQL y su impacto en el rendimiento del servidor.

hablaremos de Configuración de TOAST y alineación de datos. “En promedio”, estos métodos no ahorrarán demasiados recursos, pero sin modificar en absoluto el código de la aplicación.

Sin embargo, nuestra experiencia resultó muy productiva en este sentido, ya que el almacenamiento de casi cualquier seguimiento por su naturaleza es en su mayoría solo para agregar en términos de datos registrados. Y si se pregunta cómo puede enseñarle a la base de datos a escribir en el disco 200MB / s la mitad - por favor bajo cat.

Pequeños secretos del big data

Por perfil laboral nuestro servicio, vuelan regularmente hacia él desde las guaridas. paquetes de texto.

Y desde complejo VLSIcuya base de datos monitoreamos es un producto de múltiples componentes con estructuras de datos complejas, luego consultas para un máximo rendimiento resulta bastante así “multivolumen” con lógica algorítmica compleja. Por lo tanto, el volumen de cada instancia individual de una solicitud o el plan de ejecución resultante en el registro que nos llega resulta ser "en promedio" bastante grande.

Veamos la estructura de una de las tablas en las que escribimos datos "sin procesar", es decir, aquí está el texto original de la entrada del registro:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Un cartel típico (ya seccionado, claro, así que se trata de una plantilla de sección), donde lo más importante es el texto. A veces bastante voluminoso.

Recuerde que el tamaño "físico" de un registro en un PG no puede ocupar más de una página de datos, pero el tamaño "lógico" es un asunto completamente diferente. Para escribir un valor volumétrico (varchar/text/bytea) en un campo, utilice Tecnología TOSTADA:

PostgreSQL utiliza un tamaño de página fijo (normalmente 8 KB) y no permite que las tuplas abarquen varias páginas. Por lo tanto, es imposible almacenar directamente valores de campos muy grandes. Para superar esta limitación, los valores de campos grandes se comprimen y/o dividen en varias líneas físicas. Esto pasa desapercibido para el usuario y tiene poco impacto en la mayoría del código del servidor. Este método se conoce como TOSTADA...

De hecho, para cada tabla con campos "potencialmente grandes", automáticamente Se crea una tabla emparejada con "rebanado" cada registro "grande" en segmentos de 2 KB:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Es decir, si tenemos que escribir una cadena con un valor “grande” data, entonces se producirá la grabación real no solo a la mesa principal y su PK, sino también a TOAST y su PK.

Reducir la influencia de TOAST

Pero la mayoría de nuestros discos todavía no son tan grandes, debe caber en 8KB - ¿Cómo puedo ahorrar dinero en esto?

Aquí es donde el atributo viene en nuestra ayuda. STORAGE en la columna de la tabla:

• EXTENDIDO permite tanto la compresión como el almacenamiento por separado. Este opción estándar para la mayoría de los tipos de datos compatibles con TOAST. Primero intenta realizar la compresión y luego la almacena fuera de la tabla si la fila aún es demasiado grande.
• PRINCIPAL permite la compresión pero no el almacenamiento por separado. (De hecho, se seguirá realizando un almacenamiento separado para dichas columnas, pero solo como último recurso, cuando no hay otra manera de reducir la cadena para que quepa en la página).

De hecho, esto es exactamente lo que necesitamos para el texto: comprimirlo lo máximo posible, y si no cabe del todo ponerlo en TOAST. Esto se puede hacer directamente sobre la marcha, con un comando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Cómo evaluar el efecto

Dado que el flujo de datos cambia todos los días, no podemos comparar números absolutos, sino en términos relativos. participación más pequeña Lo escribimos en TOAST, mucho mejor. Pero aquí existe un peligro: cuanto mayor es el volumen "físico" de cada registro individual, más "amplio" se vuelve el índice, porque tenemos que cubrir más páginas de datos.

sección antes de los cambios:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

sección después de los cambios:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

De hecho, nosotros Comencé a escribir en TOAST 2 veces menos a menudo., que descargó no solo el disco, sino también la CPU:

Observo que también nos hemos vuelto más pequeños en la “lectura” del disco, no solo en la “escritura”, ya que al insertar un registro en una tabla, también tenemos que “leer” parte del árbol de cada índice para determinar su posición futura en ellos.

¿Quién puede vivir bien en PostgreSQL 11?

Después de actualizar a PG11, decidimos continuar "afinando" TOAST y notamos que a partir de esta versión el parámetro estuvo disponible para afinar. toast_tuple_target:

El código de procesamiento TOAST solo se activa cuando el valor de la fila que se almacenará en la tabla es mayor que TOAST_TUPLE_THRESHOLD bytes (generalmente 2 KB). El código TOAST comprimirá y/o moverá los valores de campo fuera de la tabla hasta que el valor de la fila sea menor que TOAST_TUPLE_TARGET bytes (valor variable, también generalmente 2 KB) o el tamaño no se pueda reducir.

Decidimos que los datos que habitualmente tenemos son “muy cortos” o “muy largos”, por lo que decidimos limitarnos al mínimo valor posible:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Veamos cómo las nuevas configuraciones afectaron la carga del disco después de la reconfiguración:

¡Nada mal! Promedio la cola al disco ha disminuido aproximadamente 1.5 veces y el disco "ocupado" es del 20 por ciento. ¿Pero tal vez esto afectó de alguna manera a la CPU?

Al menos no empeoró. Sin embargo, es difícil juzgar si incluso esos volúmenes aún no pueden aumentar la carga promedio de la CPU. 5%.

Al cambiar los lugares de los términos, la suma… ¡cambia!

Como saben, con un centavo se ahorra un rublo y, con nuestros volúmenes de almacenamiento, se trata de 10TB/mes Incluso una pequeña optimización puede generar buenos beneficios. Por lo tanto, prestamos atención a la estructura física de nuestros datos: cómo exactamente campos "apilados" dentro del registro cada una de las mesas.

porque debido a alineación de datos esto es sencillo afecta el volumen resultante:

Muchas arquitecturas proporcionan alineación de datos en los límites de las palabras de la máquina. Por ejemplo, en un sistema x32 de 86 bits, los números enteros (tipo entero, 4 bytes) se alinearán en un límite de palabra de 4 bytes, al igual que los números de coma flotante de doble precisión (coma flotante de doble precisión, 8 bytes). Y en un sistema de 64 bits, los valores dobles se alinearán con límites de palabras de 8 bytes. Ésta es otra razón de incompatibilidad.

Debido a la alineación, el tamaño de una fila de la tabla depende del orden de los campos. Por lo general, este efecto no es muy notable, pero en algunos casos puede provocar un aumento significativo de tamaño. Por ejemplo, si mezcla campos char(1) y enteros, normalmente se desperdiciarán 3 bytes entre ellos.

Empecemos por los modelos sintéticos:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

¿De dónde vinieron un par de bytes adicionales en el primer caso? Es sencillo - Smallint de 2 bytes alineados en un límite de 4 bytes antes del siguiente campo, y cuando es el último, no hay nada ni necesidad de alinear.

En teoría, todo está bien y puedes reorganizar los campos como quieras. Comprobémoslo con datos reales usando el ejemplo de una de las tablas, cuya sección diaria ocupa entre 10 y 15 GB.

Estructura inicial:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Sección después de cambiar el orden de las columnas: exactamente Mismos campos, solo orden diferente.:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

El volumen total de la sección está determinado por la cantidad de "hechos" y depende únicamente de procesos externos, así que dividamos el tamaño del montón (pg_relation_size) por el número de registros que contiene, es decir, obtenemos tamaño promedio del registro almacenado real:

Menos 6% de volumen, ¡Excelente!

Pero, por supuesto, no todo es tan color de rosa; después de todo, en los índices no podemos cambiar el orden de los campos, y por lo tanto “en general” (pg_total_relation_size) ...

...todavía aquí también ahorró 1.5%sin cambiar una sola línea de código. ¡Sí Sí!

Observo que la opción anterior para organizar campos no es la más óptima. Porque no desea "romper" algunos bloques de campos por razones estéticas, por ejemplo, un par (pack, recno), que es el PK de esta tabla.

En general, determinar la disposición "mínima" de los campos es una tarea de "fuerza bruta" bastante sencilla. Por lo tanto, puede obtener resultados aún mejores con sus datos que con los nuestros: ¡pruébelo!

Fuente: habr.com