sql - standard - Vertica y se une
unpivot oracle (5)
Mi pregunta es la siguiente: ¿es normal que una proyección previa a la unión ralentice la inserción de datos y, en caso negativo, cuál podría ser el culpable? Si es normal, entonces es un factor clave para nosotros y ¿existen otras técnicas que podamos usar para acelerar las uniones?
Supongo que la cantidad afectada variaría según los conjuntos de datos y las estructuras con las que está trabajando. Pero, dado que esta es la variable que cambió, creo que es seguro decir que la proyección previa a la unión está causando la lentitud. Usted está ganando tiempo de consulta a expensas del tiempo de inserción.
Alguien por favor corríjame si alguno de los siguientes está mal. Voy por la memoria y por la información recogida con las conversaciones con otros.
Puede acelerar sus uniones sin una proyección previa a la unión de varias maneras. En este caso, el ID de referencia. Creo que si segmenta sus proyecciones para ambas tablas con el predicado de unión que ayudaría. Cualquier cosa que puedas hacer para filtrar los datos.
Al observar su plan de explicación, está realizando una combinación hash en lugar de una combinación de combinación, que probablemente desee ver.
Por último, me gustaría saber a través del plan de explicación o de las tablas del sistema si su consulta está usando las proyecciones que recomienda Database Designer. Si no es así, especifíquelos explícitamente en su consulta y vea si eso ayuda.
Estoy adaptando una herramienta de análisis web para usar Vertica como DB. Estoy teniendo problemas reales para optimizing joins . Intenté crear proyecciones previas a la unión para algunas de mis consultas, y aunque las consultas se hicieron más rápidas, redujo la carga de datos en la tabla de hechos a un rastreo.
Un simple INSERT INTO ... SELECT * FROM que usamos para cargar datos en la tabla de hechos desde una tabla de preparación pasa de tomar ~ 5 segundos a más de 20 minutos.
Debido a esto, eliminé todas las proyecciones anteriores a la unión e intenté usar el Diseñador de bases de datos para diseñar proyecciones específicas de la consulta, pero no es suficiente. Incluso con esas proyecciones, una combinación simple toma ~ 14 segundos, algo que toma ~ 1 segundo con una proyección previa a la unión.
Mi pregunta es la siguiente: ¿es normal que una proyección previa a la unión ralentice la inserción de datos y, en caso negativo, cuál podría ser el culpable? Si es normal, entonces es un factor clave para nosotros y ¿existen otras técnicas que podamos usar para acelerar las uniones?
Estamos ejecutando Vertica en un clúster de 5 nodos, cada nodo tiene 2 x CPU de cuatro núcleos y 32 GB de memoria. Las tablas en mi consulta de ejemplo tienen 188,843,085 y 25,712,878 filas respectivamente.
La salida de EXPLAIN se ve así:
EXPLAIN SELECT referer_via_.url as referralPageUrl, COUNT(DISTINCT sessio
n.id) as visits FROM owa_session as session JOIN owa_referer AS referer_vi
a_ ON session.referer_id = referer_via_.id WHERE session.yyyymmdd BETWEEN
''20121123'' AND ''20121123'' AND session.site_id = ''49'' GROUP BY referer_via_
.url ORDER BY visits DESC LIMIT 250;
Access Path:
+-SELECT LIMIT 250 [Cost: 1M, Rows: 250 (STALE STATISTICS)] (PATH ID: 0)
| Output Only: 250 tuples
| Execute on: Query Initiator
| +---> SORT [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID: 1)
| | Order: count(DISTINCT "session".id) DESC
| | Output Only: 250 tuples
| | Execute on: All Nodes
| | +---> GROUPBY PIPELINED (RESEGMENT GROUPS) [Cost: 1M, Rows: 1 (STALE
STATISTICS)] (PATH ID: 2)
| | | Aggregates: count(DISTINCT "session".id)
| | | Group By: referer_via_.url
| | | Execute on: All Nodes
| | | +---> GROUPBY HASH (SORT OUTPUT) (RESEGMENT GROUPS) [Cost: 1M, Rows
: 1 (STALE STATISTICS)] (PATH ID: 3)
| | | | Group By: referer_via_.url, "session".id
| | | | Execute on: All Nodes
| | | | +---> JOIN HASH [Cost: 1M, Rows: 1 (STALE STATISTICS)] (PATH ID:
4) Outer (RESEGMENT)
| | | | | Join Cond: ("session".referer_id = referer_via_.id)
| | | | | Execute on: All Nodes
| | | | | +-- Outer -> STORAGE ACCESS for session [Cost: 463, Rows: 1 (ST
ALE STATISTICS)] (PUSHED GROUPING) (PATH ID: 5)
| | | | | | Projection: public.owa_session_projection
| | | | | | Materialize: "session".id, "session".referer_id
| | | | | | Filter: ("session".site_id = ''49'')
| | | | | | Filter: (("session".yyyymmdd >= 20121123) AND ("session"
.yyyymmdd <= 20121123))
| | | | | | Execute on: All Nodes
| | | | | +-- Inner -> STORAGE ACCESS for referer_via_ [Cost: 293K, Rows:
26M] (PATH ID: 6)
| | | | | | Projection: public.owa_referer_DBD_1_seg_Potency_2012112
2_Potency_20121122
| | | | | | Materialize: referer_via_.id, referer_via_.url
| | | | | | Execute on: All Nodes
Creo que tu consulta podría usar algo más de ser explícito. Tampoco uses ese Diablo ENTRE Intenta esto:
EXPLAIN SELECT
referer_via_.url as referralPageUrl,
COUNT(DISTINCT session.id) as visits
FROM owa_session as session
JOIN owa_referer AS referer_via_
ON session.referer_id = referer_via_.id
WHERE session.yyyymmdd <= ''20121123''
AND session.yyyymmdd > ''20121123''
AND session.site_id = ''49''
GROUP BY referer_via_.url
-- this `visits` column needs a table name
ORDER BY visits DESC LIMIT 250;
Diré que estoy realmente perplejo en cuanto a por qué usaría la misma DATE con BETWEEN tal vez quiera analizar.
Esta es mi opinión que proviene de una formación académica que trabaja con bases de datos de columnas, incluida Vertica (recién graduada de doctorado en sistemas de bases de datos).
Blockquote Mi pregunta es la siguiente: ¿es normal que una proyección previa a la unión ralentice la inserción de datos y, en caso negativo, cuál podría ser el culpable? Si es normal, entonces es un factor clave para nosotros y ¿existen otras técnicas que podamos usar para acelerar las uniones? Blockquote
Sí, la actualización de las proyecciones es muy lenta y lo ideal sería hacerlo solo en grandes lotes para amortizar el costo de la actualización. La razón fundamental es que cada proyección representa otra copia de los datos (de cada columna de la tabla que forma parte de la proyección).
Una inserción de una sola fila requiere agregar un valor (un atributo) a cada columna en la proyección. Por ejemplo, una inserción de una sola fila en una tabla con 20 atributos requiere al menos 20 actualizaciones de columna. Para empeorar las cosas, cada columna está ordenada y comprimida. Esto significa que insertar el nuevo valor en una columna requiere múltiples operaciones en grandes porciones de datos : leer datos / descomprimir / actualizar / ordenar / comprimir datos / escribir datos nuevamente. Vertica tiene varias optimizaciones para actualizaciones pero no puede ocultar completamente el costo.
Las proyecciones se pueden considerar como el equivalente de índices de varias columnas en un almacén de filas tradicional (MySQL, PostgreSQL, Oracle, etc.). La ventaja de las proyecciones en comparación con los índices B-Tree tradicionales es que leerlos (usarlos para responder una consulta) es mucho más rápido que usar índices tradicionales. Las razones son múltiples: no hay necesidad de acceder a los datos principales como para los índices no agrupados, de menor tamaño debido a la compresión, etc. El lado opuesto es que son mucho más difíciles de actualizar. Compensaciones ...
Para acelerar la unión:
Diseñe la tabla de sesión como particionada en la columna "aaaammdd". Esto habilitará la poda de particiones.
Agregue la condición en la columna "aaaammdd" a _referer_via_ y particione en ella, si es posible (lo más probable es que no)
tener la columna site_id como sea posible cerca del principio de orden por lista en (super) proyección de sesión usada
- tener ambas tablas segmentadas en referer_id y id de forma correspondiente.
Y tener más nodos en cluster ayuda.
Parece que tienes muchas ESTADÍSTICAS STALE. Responding a las estadísticas de STALE es importante. Porque esa es la razón por la cual sus consultas son lentas. Sin estadísticas sobre los datos subyacentes, el optimizador de consultas de Vertica no puede elegir el mejor plan de ejecución. Y responder a las estadísticas de STALE solo mejora el rendimiento de SELECT, no el rendimiento de actualización.
Si actualiza sus tablas regularmente, recuerde que hay cosas adicionales que debe considerar en VERTICA. Por favor verifique la respuesta que publiqué en esta question . Espero que eso ayude a mejorar tu velocidad de actualización.
Explore la configuración de AHM como se explica en esa respuesta. Si no necesita poder seleccionar filas eliminadas en una tabla más adelante, a menudo es una buena idea no mantenerlas alrededor. Hay formas de mantener solo la última versión de época de los datos. O purgar manualmente los datos eliminados. Déjame saber como va.