En este artículo, voy a considerar aumentar la velocidad de los informes. Por informe, me refiero a cualquier consulta a una base de datos que utiliza funciones agregadas. Además, voy a tocar temas relacionados con los recursos gastados en la producción y el soporte de informes, tanto humanos como mecánicos.
En los ejemplos, usaré un conjunto de datos que contiene 52,608,000 registros.
Utilizando el ejemplo de reservas analíticas no difíciles, demostraré que incluso una computadora débil puede convertirse en una buena herramienta para analizar una cantidad de datos “decente” sin mucho esfuerzo.
Después de configurar experimentos no complicados, veremos que una tabla regular no es una fuente adecuada para consultas analíticas.
Si el lector puede descifrar fácilmente las abreviaturas OLTP y OLAP, puede tener sentido ir directamente a la sección
ColumnstoreDos enfoques para trabajar con datos
Aquí voy a ser breve, porque Hay más que suficiente información sobre este tema en Internet.
Entonces, en el nivel más alto, solo hay dos enfoques para trabajar con datos: OLTP y OLAP.
OLTP: se puede traducir como procesamiento de transacciones instantáneas. De hecho, estamos hablando del procesamiento en línea de transacciones cortas que funcionan con una pequeña cantidad de datos. Por ejemplo, grabar, actualizar o eliminar un pedido. En la gran mayoría de los casos, un pedido es una cantidad extremadamente pequeña de datos, durante el procesamiento del cual no puede temer los largos bloqueos impuestos por el RDBMS moderno.
OLAP: se puede traducir como procesamiento analítico de una gran cantidad de transacciones a la vez. Cualquier informe utiliza este enfoque particular, porque en la gran mayoría de los casos, el informe produce cifras resumidas y agregadas para ciertas secciones.
Cada enfoque tiene su propia tecnología. Por ejemplo, para OLTP es PostgreSQL, y para OLAP es Microsoft SQL Server Analysis Services. Si bien PostgresSQL utiliza un formato conocido para almacenar datos en tablas, se inventaron varios formatos diferentes para OLAP. Estas son tablas multidimensionales, cubos llenos de pares clave-valor y mi almacén de columnas favorito. Sobre este último con más detalle a continuación.
¿Por qué se necesitan dos enfoques?
Se observó que cualquier almacén de datos tarde o temprano enfrenta dos tipos de carga: lectura frecuente (escritura y actualización, por supuesto, también) de cantidades extremadamente pequeñas de datos y lectura rara, pero cantidades muy grandes de datos. De hecho, esta es una actividad, por ejemplo, de la taquilla y el jefe. La caja, que funciona todo el día, llena el almacenamiento con pequeños fragmentos de datos, mientras que al final del día el volumen acumulado, si el negocio va bien, alcanza un tamaño impresionante. A su vez, el gerente al final del día quiere saber cuánto dinero gana la taquilla por día.
Entonces, en OLTP tenemos tablas e índices. Estas dos herramientas son excelentes para registrar la actividad de taquilla con todos los detalles. Los índices proporcionan una búsqueda rápida de un pedido previamente registrado, por lo que cambiar un pedido es fácil. Pero para satisfacer las necesidades del líder, debemos considerar la cantidad total de datos acumulados por día. Además, como regla general, el gerente no necesita todos los detalles de todos los pedidos. Lo que realmente necesita saber es cuánto dinero ganó la taquilla en general. No importa dónde estaba la taquilla, cuándo había un descanso para almorzar, quién trabajaba para ello, etc. OLAP existe entonces, de modo que en un corto período de tiempo el sistema puede responder la pregunta: cuánto ha ganado la compañía en su conjunto sin una lectura secuencial de cada pedido y todos sus detalles. ¿Puede OLAP usar las mismas tablas e índices que OLTP? La respuesta es no, al menos no debería. En primer lugar, porque OLAP simplemente no necesita todos los detalles registrados en las tablas. Este problema se resuelve almacenando datos en otros formatos que no sean tablas bidimensionales. En segundo lugar, la información analizada a menudo se encuentra dispersa en diferentes tablas, lo que implica sus múltiples asociaciones, incluidas las asociaciones del tipo autounión. Para resolver este problema, como regla general, desarrollan un esquema de base de datos especial. Este esquema está optimizado para la carga OLAP, así como el esquema normalizado normal para la carga OLTP.
¿Qué sucede cuando OLAP usa un esquema OLTP?
De hecho, presenté esta sección para que este artículo cumpla claramente mis propios requisitos para el formato de dicho material, es decir. problema, solución, conclusión.
Enumeramos una serie de desventajas del uso de esquemas OLTP para el análisis de datos.
- Demasiados índices
A menudo, debe crear índices especiales para admitir informes. Estos índices implementan un esquema de almacenamiento de datos OLAP. No son utilizados por la parte OLTP de la aplicación, mientras ejercen una carga sobre ella, lo que requiere soporte constante y ocupa espacio en disco. - La cantidad de datos leídos excede la requerida.
- Falta de un esquema de datos claro.
El hecho es que a menudo la información presentada por los informes en un solo formulario se distribuye en diferentes tablas. Dicha información requiere una transformación constante sobre la marcha. El ejemplo más simple es la cantidad de ingresos, que consiste en dinero en efectivo y no en efectivo. Otro ejemplo sorprendente son las jerarquías de datos. Porque El desarrollo de aplicaciones es progresivo y no siempre se sabe lo que se necesitará en el futuro, la misma jerarquía de significado se puede almacenar en diferentes tablas. Y aunque la adquisición sobre la marcha se usa activamente en OLAP, estas son cosas ligeramente diferentes. - Excesiva complejidad de consultas.
Porque Un esquema OLTP difiere de un OLAP. Se necesita una capa de software fuertemente relacionada que lleve el esquema de datos OLTP a la forma correcta. - Complejidad de soporte, depuración y desarrollo.
En general, podemos decir que cuanto más complejo es el código base, más difícil es mantenerlo en buen estado. Este es un axioma. - La complejidad de la cobertura de prueba.
Muchas copias se rompen debido a discusiones sobre cómo obtener una base de datos llena de todos los scripts de prueba, pero es mejor decir que tener un esquema de datos más simple la tarea de cubrir con pruebas se simplifica muchas veces. - Depuración de rendimiento sin fin.
Existe una alta probabilidad de que el usuario solicite un informe que sea "pesado" para el servidor de la base de datos. Esta probabilidad aumenta con el tiempo. Cabe señalar que OLAP también es propenso a este problema, pero a diferencia de OLTP, el recurso OLAP en este asunto es mucho mayor.
Almacén de columnasEste artículo se centrará en el formato de almacenamiento del almacén de columnas, pero sin detalles de bajo nivel. Otros formatos mencionados anteriormente también merecen atención, pero este es un tema para otro artículo.
En realidad, el formato del almacén de columnas se conoce desde hace 30 años, pero no se implementó en el RDBMS hasta hace poco. La esencia del almacén de columnas es que los datos se almacenan no en filas, sino en columnas. Es decir en una página (todos conocidos de 8 Kb) el servidor registra datos de un solo campo. Y así con cada campo en la tabla a su vez. Esto es necesario para que no tenga que leer información adicional. Imaginemos una tabla con 10 campos y una consulta que solo tiene un campo especificado en la instrucción SELECT. Si se tratara de una tabla normal guardada en un formato basado en filas, el servidor se vería obligado a leer los 10 campos, pero al mismo tiempo devolvería solo uno. Resultó que el servidor leyó 9 veces más información de la necesaria. Columnstore resuelve completamente este problema, porque El formato de almacenamiento le permite leer solo un campo ordenado. Todo esto sucede porque la unidad de almacenamiento en un RDBMS es una página. Es decir el servidor siempre escribe y lee al menos una página. La única pregunta es cuántos campos están presentes en él.
Cómo Columnstore realmente puede ayudar
Para responder a esto uno debe tener números exactos. Vamos por ellos. Pero, ¿qué números pueden dar una imagen precisa?
- La cantidad de espacio en disco.
- Consulta de rendimiento.
- Tolerancia a fallas.
- Facilidad de implementación.
- ¿Qué nuevas habilidades debe tener un desarrollador para trabajar con nuevas estructuras?
Espacio en disco
Creemos una tabla simple, complétela con datos y verifiquemos cuánto espacio ocupa.
create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz');
Como notaron, creé una tabla externa. El hecho es que PostgreSQL no tiene soporte de almacén de columnas incorporado. Pero PostgreSQL tiene un poderoso sistema para extensiones. Uno de ellos hace posible crear tablas de almacén de columnas. Enlaces al final del artículo.
- pglz: le dice a la extensión que los datos deben comprimirse utilizando el algoritmo incorporado en PostgreSQL;
- trd - tiempo de transacción;
- op, it, wh - secciones analíticas o medidas;
- m1, m2, m3, m4, m5 - indicadores numéricos o medidas;
Insertemos una cantidad "decente" de datos y veamos cuánto espacio ocupa en el disco. Al mismo tiempo, verificamos el rendimiento del inserto. Porque Puse mis experimentos en una computadora portátil hogareña, soy un poco orgánico en la cantidad de datos. Además, lo cual es incluso bueno, usaré el HDD con el sistema operativo invitado Fedora 30. Host del sistema operativo - Windows 10 Home Edition. Procesador Intel Core 7. El sistema operativo invitado recibió 4 GB de RAM. Versión PostgreSQL: PostgreSQL 10.10 en x86_64-pc-linux-gnu, compilado por gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64 bits. Experimentaré con un conjunto de datos con el número de registros 52 608 000.
explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
El plan de implementación será el siguiente
Insertar en cstore_table (costo = 0.01..24902714242540.01 filas = 1000000000000000 ancho = 150) (tiempo real = 119560.456..119560.456 filas = 0 bucles = 1)
----> Bucle anidado (costo = 0.01..24902714242540.01 filas = 1000000000000000 ancho = 150) (tiempo real = 1.823..22339.976 filas = 52608000 bucles = 1)
----------> Función de escaneo en generate_series d (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.151..2.198 filas = 1096 bucles = 1)
----------> Materializar (costo = 0.01..27284555030.01 filas = 1000000000000 ancho = 16) (tiempo real = 0.002..3.196 filas = 48000 bucles = 1096)
----------------> Bucle anidado (costo = 0.01..17401742530.01 filas = 1000000000000 ancho = 16) (tiempo real = 1.461..15.072 filas = 48000 bucles = 1)
----------------------> Función Escanear en generate_series it (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 1.159..2.007 filas = 4000 bucles = 1)
----------------------> Materializar (costo = 0.01..26312333.01 filas = 1,000,000,000 ancho = 12) (tiempo real = 0.000..0.001 filas = 12 bucles = 4000)
----------------------------> Bucle anidado (costo = 0.01..16429520.01 filas = 1,000,000,000 ancho = 12) (tiempo real = 0.257 ..0.485 filas = 12 bucles = 1)
----------------------------------> Función de escaneo en generate_series wh (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.046..0.049 filas = 3 bucles = 1)
----------------------------------> Materializar (costo = 0.01..28917.01 filas = 1,000,000 de ancho = 8) (tiempo real = 0.070..0.139 filas = 4 bucles = 3)
---------------------------------------> Bucle anidado (costo = 0.01..20010.01 filas = 1000000 ancho = 8) (tiempo real = 0.173..0.366 filas = 4 bucles = 1)
-------------------------------------------> Función de escaneo en generate_series op ( costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.076..0.079 filas = 2 bucles = 1)
---------------------------------------------> Exploración de funciones en generate_series org (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.043..0.047 filas = 2 bucles = 2)
Tiempo de planificación: 0.439 ms
Tiempo de ejecución: 119692.051 ms
Tiempo de entrega total: 1.994867517 minutos
Tiempo de creación del conjunto de datos: 22.339976 segundos
Tiempo de inserción - 1.620341333 minutos
No logré evaluar el espacio en disco ocupado usando las funciones de PostgreSQL. No estoy seguro de por qué, pero muestra 0. Quizás este sea el comportamiento estándar para tablas externas. Utilizado para este administrador de archivos. Entonces, el volumen de espacio en disco ocupado es 226.2 Mb. Para evaluar mucho o poco, comparémoslo con una tabla normal.
explain (analyze) create table rbstore_table as select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
El plan de implementación será el siguiente
Bucle anidado (costo = 0.01..22402714242540.01 filas = 1000000000000000 ancho = 44) (tiempo real = 0.585..23781.942 filas = 52608000 bucles = 1)
---> Análisis de función en generate_series d (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.091..2.130 filas = 1096 bucles = 1)
---> Materializar (costo = 0.01..27284555030.01 filas = 1000000000000 ancho = 16) (tiempo real = 0.001..3.574 filas = 48000 bucles = 1096)
----------> Bucle anidado (costo = 0.01..17401742530.01 filas = 1000000000000 ancho = 16) (tiempo real = 0.489..14.044 filas = 48000 bucles = 1)
----------------> Función Escanear en generate_series it (costo = 0.00..10.00 filas = 1000 ancho = 4) (tiempo real = 0.477..1.352 filas = 4000 bucles = 1 )
----------------> Materializar (costo = 0.01..26312333.01 filas = 1000000000 ancho = 12) (tiempo real = 0.000..0.001 filas = 12 bucles = 4000)
----------------------> Bucle anidado (costo = 0.01..16429520.01 filas = 1,000,000,000 ancho = 12) (tiempo real = 0.010..0.019 filas = 12 bucles = 1)
----------------------------> Función de escaneo en generate_series wh (costo = 0.00..10.00 filas = 1000 ancho = 4) (real tiempo = 0.003..0.003 filas = 3 bucles = 1)
----------------------------> Materializar (costo = 0.01..28917.01 filas = 1,000,000 de ancho = 8) (tiempo real = 0.002. .0.004 filas = 4 bucles = 3)
----------------------------------> Bucle anidado (costo = 0.01..20010.01 filas = 1,000,000 de ancho = 8 ) (tiempo real = 0.006..0.009 filas = 4 bucles = 1)
----------------------------------------> Análisis de funciones en generate_series op (costo = 0.00 ..10.00 filas = 1000 ancho = 4) (tiempo real = 0.002..0.002 filas = 2 bucles = 1)
----------------------------------------> Análisis de funciones en la organización generate_series (costo = 0.00 ..10.00 filas = 1000 ancho = 4) (tiempo real = 0.001..0.001 filas = 2 bucles = 2)
Tiempo de planificación: 0.569 ms
Tiempo de ejecución: 378883.989 ms
El tiempo dedicado a la implementación de este plan no nos interesa, porque en la vida real, no se supone que tales inserciones. Nos interesa cuánto espacio en disco ocupa esta tabla. Una vez cumplida la solicitud de funciones del sistema, recibí 3.75 GB.
Entonces, cstore_table - 226 MB, rbstore_table - 3.75 GB. La diferencia de 16,99 veces es sorprendente, pero es poco probable que se pueda obtener la misma diferencia en la producción, principalmente debido a la distribución de datos. Como regla general, esta diferencia será menor y será aproximadamente 5 veces.
Pero espere, nadie usa datos sin procesar en un formato basado en filas para fines de análisis. Por ejemplo, intentan usar datos indexados para generar informes. Y porque Los datos "sin procesar" siempre serán, debe comparar los tamaños con los tamaños de los índices. Creemos al menos un índice. Sea un índice en el campo de fecha y tipo de operación: trd + op.
Entonces, indexé solo dos campos, y el índice tomó 1583 MB, que es mucho más que cstore_table. Pero, como regla, se requiere más de un índice para la carga OLAP. Será apropiado señalar aquí que cstore_table no necesita indexación adicional. Esta tabla actúa como un índice que cubre cualquier consulta.
De todo lo anterior, se puede llegar a una conclusión simple: al usar tablas de almacén de columnas, puede reducir la cantidad de espacio en disco utilizado.
Query Performance
Para evaluar el rendimiento, ejecutemos una consulta que devuelva datos de resumen de un mes específico para un tipo específico de operación.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd = '2011-01-01' and op = 1;
El plan de implementación será el siguiente
Agregado (costo = 793602.69..793602.70 filas = 1 ancho = 32) (tiempo real = 79.708..79.708 filas = 1 bucles = 1)
--Buffers: golpe compartido = 44226
---> Análisis externo en cstore_table (costo = 0.00..793544.70 filas = 23197 ancho = 5) (tiempo real = 23.209..76.628 filas = 24000 bucles = 1)
-------- Filtro: ((trd = '2011-01-01' :: fecha) Y (op = 1))
-------- Filas eliminadas por filtro: 26000
-------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16417
-------- Tamaño del archivo CStore: 120818897
-------- Buffers: hit compartido = 44226
Tiempo de planificación: 0.165 ms
Tiempo de ejecución: 79.887 ms
Y
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd = '2011-01-01' and op = 1;
El plan de implementación será el siguiente
Agregado (costo = 40053.80..40053.81 filas = 1 ancho = 8) (tiempo real = 389.183..389.183 filas = 1 bucles = 1)
--Buffers: lectura compartida = 545
---> Index Scan usando trd_op_ix en rbstore_table (costo = 0.56..39996.70 filas = 22841 ancho = 4) (tiempo real = 55.955..385.283 filas = 24000 bucles = 1)
-------- Índice Cond: ((trd = '2011-01-01 00:00:00' :: marca de tiempo sin zona horaria) Y (op = 1))
-------- Buffers: lectura compartida = 545
Tiempo de planificación: 112.175 ms
Tiempo de ejecución: 389.219 ms
389.219 ms frente a 79.887 ms. Aquí vemos que incluso en una cantidad relativamente pequeña de datos del almacén de columnas, una tabla es significativamente más rápida que un índice en una tabla basada en filas.
Cambiemos la solicitud e intentemos obtener la unidad para todo 2011.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
El plan de implementación será el siguiente
Agregado (costo = 946625.58..946625.59 filas = 1 ancho = 32) (tiempo real = 3123.604..3123.604 filas = 1 bucles = 1)
--Buffers: golpe compartido = 44226
---> Análisis externo en cstore_table (costo = 0.00..925064.70 filas = 8624349 ancho = 5) (tiempo real = 21.728..2100.665 filas = 8760000 bucles = 1)
-------- Filtro: ((trd> = '2011-01-01' :: fecha) AND (trd <= '2011-12-31' :: fecha) AND (op = 1))
-------- Filas eliminadas por filtro: 8760000
-------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16411
-------- Tamaño del archivo CStore: 120818897
-------- Buffers: hit compartido = 44226
Tiempo de planificación: 0.212 ms
Tiempo de ejecución: 3123.960 ms
Y
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
El plan de implementación será el siguiente
Finalizar agregado (costo = 885214.33..885214.34 filas = 1 ancho = 8) (tiempo real = 98512.560..98512.560 filas = 1 bucles = 1)
--Buffers: hit compartido = 2565 read = 489099
---> Reunir (costo = 885214.12..885214.33 filas = 2 ancho = 8) (tiempo real = 98427.034..98523.194 filas = 3 bucles = 1)
-------- Trabajadores previstos: 2
-------- Trabajadores lanzados: 2
-------- Buffers: hit compartido = 2565 read = 489099
---------> Agregado parcial (costo = 884214.12..884214.13 filas = 1 ancho = 8) (tiempo real = 97907.608..97907.608 filas = 1 bucles = 3)
-------------- Buffers: hit compartido = 2565 read = 489099
---------------> Análisis paralelo paralelo en rbstore_table (costo = 0.00..875264.00 filas = 3580047 ancho = 4) (tiempo real = 40820.004..97405.250 filas = 2920000 bucles = 3)
--------------------- Filtro: ((trd> = '2011-01-01 00:00:00' :: marca de tiempo sin zona horaria) AND (trd <= '2011-12-31 00:00:00' :: marca de tiempo sin zona horaria) Y (op = 1))
-------------------- Filas eliminadas por filtro: 14616000
-------------------- Buffers: hit compartido = 2565 read = 489099
Tiempo de planificación: 7.899 ms
Tiempo de ejecución: 98523.278 ms
98523,278 ms frente a 3123,960 ms. Quizás un índice parcial nos ayudaría, pero es mejor no arriesgarlo y crear una estructura adecuada basada en filas en la que se almacenen los valores listos para usar.
Agregados manuales
Una estructura adecuada para los agregados manuales podría ser una tabla regular basada en filas que contenga valores calculados previamente. Por ejemplo, puede contener un registro relacionado con 2011 con el tipo de operación igual a 1, mientras que en los campos m1, m2, m3, m4 y m5 el valor agregado se almacenará precisamente para estas secciones analíticas. Por lo tanto, al tener un conjunto suficiente de agregados e índices, las consultas analíticas adquieren un rendimiento sin precedentes. Curiosamente, Microsoft SQL Server Analysis Services tiene un asistente especial que le permite configurar el número y la profundidad de los valores calculados previamente.
Esta solución tiene las siguientes ventajas:
- Análisis en tiempo real.
No confunda el término "análisis en tiempo real". Aquí estamos hablando del hecho de que el incremento de la unidad ocurre durante un período de tiempo aceptable en la gran mayoría de los casos.
De hecho, esta ventaja es controvertida, pero no hablemos de eso. El hecho permanece. La arquitectura de la solución es tal que las unidades permanecen "frescas" casi siempre. - Completa independencia del volumen de datos.
Esta es una ventaja muy seria. No importa cuántos datos se procesen, tarde o temprano se procesarán y se recibirán los agregados. - Complejidad relativa.
Para obtener un análisis en tiempo real y la independencia del volumen de datos, la solución debe utilizar tecnologías avanzadas como la gestión de subprocesos múltiples y manual en el nivel DBMS. - Prueba de dificultad.
Aquí estamos hablando de pruebas unitarias y pruebas manuales. Creo que el lector no debería explicar que identificar errores de subprocesos múltiples no es una tarea fácil. - Mayores requisitos de espacio en disco.
El uso real del almacén de columnas
Aquí debemos sumergirnos nuevamente en la teoría y analizar la cuestión de qué son los datos analíticos con más detalle.
Tome el jefe promedio de la empresa. Como regla, le preocupan dos preguntas globales: "¿Cómo van las cosas en este momento?" y "¿Qué ha cambiado últimamente?".
Para responder la pregunta “¿Cómo van las cosas en este momento?”, Absolutamente no necesitamos datos históricos. Es decir no importa cómo fueron las cosas hace un mes.
Para mantenerse al tanto del pulso, a menudo se hace la pregunta. Este tipo de análisis de datos se llama operativo.
Para responder la pregunta "¿Qué ha cambiado últimamente?", Necesitamos datos históricos precisos. Además, como regla, el análisis se realiza en los mismos intervalos de tiempo. Por ejemplo, un mes se compara con un mes, año tras año, etc. Por supuesto, el sistema no debe limitar al usuario la capacidad de comparar períodos arbitrarios, pero dicho caso debe reconocerse como raro, porque comparar un año cerrado con un medio no cerrado tiene poco sentido. Una característica distintiva del análisis comparativo es que no se requiere con tanta frecuencia como operativo. Llamaremos a este tipo de análisis histórico.
Obviamente, el análisis operativo debe ocurrir rápidamente. En consecuencia, impone altas exigencias al rendimiento. Mientras que para el análisis histórico, tales requisitos no pueden presentarse. Aunque el desempeño del análisis histórico debe permanecer en un nivel muy alto. Al menos para que el sistema de análisis en sí siga siendo competitivo.
Entonces, de acuerdo con dos tipos de análisis, podemos distinguir dos tipos de datos analíticos: datos operativos e históricos. Desde el lado del usuario, no debe notarse con qué datos particulares está trabajando en este momento.
Es a partir de estas consideraciones que en los servidores de bases de datos ha surgido la posibilidad de dividir las tablas en secciones separadas.
Con respecto al almacén de columnas, es posible mezclar secciones en formatos basados en filas y en columnas. Se sabe que los datos del análisis operativo están sujetos a cambios frecuentes, lo que impide su almacenamiento en formato de almacén de columnas. Y dado el hecho de que los datos operativos no suceden demasiado, se pueden almacenar en formato basado en filas.
Los datos históricos no cambian. Hay muchos de estos datos y, por lo tanto, el formato de almacén de columnas les queda mejor. Recuerde que el rendimiento de las consultas en negrita en una fuente de almacén de columnas es mayor que en una fuente basada en filas.
Veamos un ejemplo de todo lo anterior.
A continuación, creo la tabla principal del almacén y le adjunto las secciones de análisis operativo e histórico.
create table warehouse ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) partition by range(trd); create foreign table historycal_data ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz'); insert into historycal_data select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, (1095 - 31)) as d; analyze historycal_data; create table operational_data as select ('2012-12-01'::date + make_interval(days => d))::date as trd , op , org , wh , it , 100::numeric(32, 2) as m1 , 100::numeric(32, 2) as m2 , 100::numeric(32, 2) as m3 , 100::numeric(32, 2) as m4 , 100::numeric(32, 2) as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 30) as d; create index trd_op_ix on operational_data (trd, op); analyze operational_data; alter table warehouse attach partition operational_data for values from ('2012-12-01') to ('2112-01-01'); alter table warehouse attach partition historycal_data for values from ('2010-01-01') to ('2012-12-01');
Todo esta listo. Intentemos ordenar un par de informes. Comencemos ordenando datos para un día del mes actual.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd = '2012-12-01' and op = 1;
Agregado (costo = 15203.37..15203.38 filas = 1 ancho = 32) (tiempo real = 17.320..17.320 filas = 1 bucles = 1)
--Buffers: golpe compartido = 3 lecturas = 515
---> Agregar (costo = 532.59..15140.89 filas = 24991 ancho = 5) (tiempo real = 1.924..13.838 filas = 24000 bucles = 1)
------- Buffers: hit compartido = 3 lecturas = 515
---------> Bitmap Heap Scan en datos_operativos (costo = 532.59..15140.89 filas = 24991 ancho = 5) (tiempo real = 1.924..11.992 filas = 24000 bucles = 1)
--------------- Vuelva a verificar Cond: ((trd = '2012-12-01' :: date) AND (op = 1))
--------------- Bloques de montón: exactos = 449
--------------- Buffers: hit compartido = 3 lecturas = 515
----------------> Escaneo de índice de mapa de bits en trd_op_ix (costo = 0.00..526.34 filas = 24991 ancho = 0) (tiempo real = 1.877..1.877 filas = 24000 bucles = 1 )
--------------------- Índice Cond: ((trd = '2012-12-01' :: fecha) Y (op = 1))
--------------------- Buffers: hit compartido = 2 lecturas = 67
Tiempo de planificación: 0.388 ms
Tiempo de ejecución: 100.941 ms
Ahora ordenaremos datos para todo el 2012, en el que el número de transacciones es de 8,784,000.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd between '2012-01-01' and '2012-12-31' and op = 1;
Agregado (costo = 960685.82..960685.83 filas = 1 ancho = 32) (tiempo real = 4124.681..4124.681 filas = 1 bucles = 1)
--Buffers: hit compartido = 45591 read = 11282
---> Agregar (costo = 0.00..938846.60 filas = 8735687 ancho = 5) (tiempo real = 66.581..3036.394 filas = 8784000 bucles = 1)
--------- Buffers: hit compartido = 45591 read = 11282
----------> Análisis externo en historycal_data (costo = 0.00..898899.60 filas = 7994117 ancho = 5) (tiempo real = 66.579..2193.801 filas = 8040000 bucles = 1)
--------------- Filtro: ((trd> = '2012-01-01' :: fecha) Y (trd <= '2012-12-31' :: fecha) Y (op = 1))
--------------- Filas eliminadas por filtro: 8040000
--------------- Archivo CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Tamaño del archivo CStore: 117401470
--------------- Buffers: hit compartido = 42966
----------> Seq Scan en datos_operativos (costo = 0.00..39947.00 filas = 741570 ancho = 5) (tiempo real = 0.019..284.824 filas = 744000 bucles = 1)
--------------- Filtro: ((trd> = '2012-01-01' :: fecha) Y (trd <= '2012-12-31' :: fecha) Y (op = 1))
--------------- Filas eliminadas por filtro: 744000
--------------- Buffers: hit compartido = 2625 read = 11282
Tiempo de planificación: 0.256 ms
Tiempo de ejecución: 4125.239 ms
Al final, veamos qué sucede si el usuario quiere, por ejemplo, sin intención maliciosa, ordenar un informe sobre todas las transacciones en el sistema, de las cuales hay 52 608 000.
explain (analyze, costs, buffers) select sum(m1) from warehouse
Agregado (costo = 672940.20..672940.21 filas = 1 ancho = 32) (tiempo real = 15907.886..15907.886 filas = 1 bucles = 1)
--Buffers: hit compartido = 17075 read = 11154
---> Agregar (costo = 0.00..541420.20 filas = 52608000 ancho = 5) (tiempo real = 0.192..9115.144 filas = 52608000 bucles = 1)
--------- Buffers: hit compartido = 17075 read = 11154
----------> Análisis externo en historycal_data (costo = 0.00..512633.20 filas = 51120000 ancho = 5) (tiempo real = 0.191..5376.449 filas = 51120000 bucles = 1)
--------------- Archivo CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Tamaño del archivo CStore: 117401470
--------------- Buffers: hit compartido = 14322
----------> Seq Scan en datos_operativos (costo = 0.00..28787.00 filas = 1488000 ancho = 5) (tiempo real = 0.032..246.978 filas = 1488000 bucles = 1)
--------------- Buffers: hit compartido = 2753 read = 11154
Tiempo de planificación: 0.157 ms
Tiempo de ejecución: 15908.096 ms
Tenga en cuenta que todavía estoy escribiendo mi artículo, como si nada hubiera pasado. Ni siquiera tuve que reiniciar mi portátil no tan potente con HDD y 4 GB de RAM. Aunque el tema del consumo de recursos requiere un estudio más cuidadoso.
Tolerancia a fallos
En parte, la tolerancia a fallas se probó justo en el momento de escribir este artículo. Mi computadora portátil está viva y, en general, no noté ninguna desaceleración en su trabajo, además de las habituales.
Deje que el lector me perdone por el hecho de que no tuve tiempo para resolver el problema de la tolerancia a fallas en detalle, pero puedo decir que la extensión en cuestión tiene tolerancia a fallas: es posible realizar una copia de seguridad.
Facilidad de implementación
Como resultado, al crear una tabla que almacena datos en un formato de almacén de columnas, no hay más opciones que un algoritmo de compresión. La compresión en sí misma es absolutamente necesaria.
El formato en sí tiene una cierta estructura. Al establecer los parámetros apropiados, puede lograr una cierta aceleración de las consultas analíticas o ajustar el grado de compresión de la información.
Como se demostró anteriormente, crear una tabla de almacén de columnas no es una molestia en absoluto. La extensión puede funcionar con 40 tipos de datos PostgreSQL. Los seminarios web hablaron sobre todos los tipos compatibles con PostgreSQL.
¿Qué nuevas habilidades debe tener un desarrollador para trabajar con nuevas estructuras?
El desarrollador de SQL no necesita ninguna habilidad especial para escribir consultas en las tablas del almacén de columnas. Dicha tabla es visible en todas las consultas, como una tabla regular basada en filas. Aunque esto no excluye la necesidad de optimización de consultas.
Conclusión
En este artículo, mostré cómo una tabla con un formato de almacenamiento de almacén de columnas puede ser útil. Esto ahorra espacio en disco y consultas analíticas de alto rendimiento. La facilidad de trabajar con la tabla reduce automáticamente el costo de crear un almacén de datos analíticos completo, porque Su uso no requiere el desarrollo de algoritmos complejos y difíciles de depurar. La prueba se simplifica.
A pesar del hecho de que los experimentos planteados anteriormente inspiran optimismo, muchos problemas no se han resuelto. Por ejemplo, qué plan de consulta se generará cuando la tabla de almacén de columnas se una a otras tablas. Espero continuar este trabajo en la próxima parte. La cantidad de partes dependerá de cómo se comporta cstore_fdw en datos más o menos reales.
Enlaces a materiales adicionales
Breve reseña cstore_fdwcstore_fdw en githubHoja de ruta cstore_fdw