Guía introductoria sobre optimización de costos y rendimiento en BigQuery
Manos a la obra
En las dos secciones anteriores se cubrieron muchos temas: conocimientos previos, información general que conviene tener a la mano y la configuración de cierta infraestructura para analizar los datos.
Repositorio de GitHub
Todo el código que se usa a lo largo de esta serie está en el siguiente repositorio de GitHub: https://github.com/doitintl/bigquery-optimization-queries
Como los dos métodos de monitoreo generan esquemas muy distintos, separé los archivos SQL en dos carpetas (audit_log e information_schema) dentro del repositorio.
Cada directorio incluye un conjunto de archivos SQL para cada propósito y un README.md que explica para qué sirve cada script.
En el directorio raíz hay un script de Python llamado generate_sql_files.py que genera una copia de los archivos SQL con la información de tu proyecto y dataset.
Índice de consultas en el repositorio de GitHub
Como este repositorio de GitHub es una creación viva, escribí un artículo que lista cada consulta junto con sus descripciones aquí.
Se irá actualizando a medida que se agreguen nuevas consultas.
Algunos consejos antes de ejecutar consultas…
A partir de la siguiente sección y durante el resto de la serie, voy a mencionar muchas consultas y a examinar gran cantidad de datos. La interfaz de BigQuery se desempeña aceptablemente con muchas consultas y análisis, pero recomiendo ENCARECIDAMENTE exportar estos datos a una hoja de cálculo.
Google Sheets es la opción ideal porque BigQuery puede exportar resultados directamente allí (con algunas limitaciones de tamaño). Además, exportar los datos una sola vez a una hoja de cálculo sale más barato que volver a ejecutar la misma consulta varias veces para hacer tu análisis.
También conviene estar atento a los costos. Algunas de estas consultas pueden ser muy caras, así que siempre recomiendo revisar el costo de la consulta antes de ejecutarla. Agregué a cada consulta una variable llamada interval_in_days, que controla qué tan atrás se hace la consulta y se puede modificar para reducir la cantidad de datos que escanea. Algunos de nuestros clientes simplemente modifican las cláusulas WHERE para acotar los rangos que les interesan, así que el límite lo pones tú con estas consultas.
Nota rápida para clientes de DoiT
Todos los pasos de esta parte se pueden realizar dentro de la sección BQ Lens del CMP. El panel Explorer te permitirá hacerlos de forma visual; de hecho, si estás usando las consultas del sink de audit log, muchas son casi idénticas a las que ejecuta el CMP para mostrar los datos.
Cómo encontrar consultas costosas
El primer paso para optimizar costos en BigQuery es encontrar las consultas más costosas de tu entorno. Muchas veces ni siquiera se sabe que existen, así que en las palabras inmortales que GI Joe nos enseñó en los dibujos animados de los sábados por la mañana: "Saber es la mitad de la batalla".
Una vez identificadas las consultas, llega el momento de optimizarlas, identificar los procesos o usuarios problemáticos que las ejecutan y corregirlos.
Voy a hacer referencia a archivos SQL del repositorio de GitHub mencionado arriba (enlace). Ten en cuenta que puedes usar tanto los archivos de audit log como los de information_schema, ya que producen el mismo resultado.
Las consultas más costosas en general
La primera consulta que vas a usar en este análisis está en el archivo top_costly_queries.sql y posiblemente sea la más importante de todas en términos de costo. Esta consulta incluye el costo total que acumula una consulta durante el intervalo a lo largo de todas sus iteraciones. Así te mostrará las consultas realmente más costosas, que pueden ejecutarse varias veces dentro del intervalo y costar más de lo que aparentan a primera vista.
El propósito de esta consulta es doble: identificar la consulta más costosa de tu entorno y detectar si una consulta cara se está ejecutando más veces de las necesarias. A menudo, procesos como un cron job, una tarea dentro de un DAG en una instancia de Airflow, una Cloud Function, etc., se ejecutan varias veces (en muchos casos más de lo que deberían) corriendo una consulta. Y aún más frecuente es que el creador, el responsable del mantenimiento o quien paga la factura de la nube no tenga idea de cuánto cuestan esas consultas por iteración o por mes. ¡Ahí es donde esta consulta resulta muy útil!
Consultas individuales más costosas
La segunda consulta que vas a usar en esta sección está en el archivo top_billed_queries.sql. Esta lista las consultas más costosas de tu entorno, ordenadas de mayor a menor costo. A diferencia de la primera, no toma en cuenta que la consulta se ejecute varias veces; solo muestra el costo por ejecución.
Al ejecutarla, las consultas más costosas aparecerán de inmediato en la parte superior del resultado. Una buena práctica es tomar la primera consulta y revisar cuántos datos procesó (las columnas total*Billed). Luego, con ese número en mano, mira la consulta para entender qué está pasando que la hace tan costosa.
Muchas veces el origen serán los problemas que mencioné arriba, pero también puede haber otros factores en juego. Por eso recomiendo revisar las consultas para ver si hay algo evidente.
Nota sobre consultas duplicadas
Si los resultados devuelven exactamente la misma consulta varias veces y eso distorsiona los datos o dificulta la lectura, mira el archivo top_billed_queries_deduplicated.sql. Es exactamente la misma consulta, pero deduplica la consulta exacta que se ejecuta para que solo aparezca un resultado. Tarda más en ejecutarse y escanea más datos al hacerlo, pero devuelve resultados deduplicados.
Vale la pena mencionar que, al momento de escribir esto, BigQuery no provee una agregación nativa de "similitud de cadenas", como una distancia de Hamming o de Levenshtein, por lo que filtrar consultas similares con cambios pequeños como una fecha es relativamente difícil. Si lo necesitas, existen algunas soluciones con UDFs que implementan algoritmos de "similitud de cadenas" para BigQuery, aunque sugiero más bien crear una solución fuera de BigQuery, ya que las implementaciones en UDF de algoritmos costosos en cómputo como estos son bastante lentas.
Usuarios que más gastan
La tercera y última consulta que vas a usar para analizar costos es el archivo top_cost_users.sql. Lista las consultas más costosas de tu entorno, pero ordenadas por usuario y luego por la consulta más cara.
El propósito de esta consulta es mostrar qué usuarios o cuentas de servicio están gastando más dinero y en qué consultas. Muchas veces aparecen en esta lista procesos que ejecutan consultas ineficientes de los que ni te enterabas. En algunos casos extremos pueden ser procesos ubicados en otros proyectos o nubes. Agregar filtros adicionales para excluirlos también puede ser útil.
Otras consultas en el repositorio
Esas son tres consultas principales bastante generales para ayudarte a descubrir consultas que cuestan más dinero del esperado. En el repositorio de GitHub también hay consultas adicionales para propósitos más específicos, como encontrar consultas originadas en Looker, cuántas veces se ejecuta una consulta, cuánto cuestan las consultas con etiquetas específicas, etc. Son bastante puntuales, pero las hemos usado tanto yo como otros miembros de mi equipo en DoiT, así que las compartimos con la comunidad.
Cómo encontrar consultas con problemas de rendimiento
El siguiente tema importante es encontrar consultas que consumen más recursos de los necesarios y que podrían no estar funcionando como se espera. En muchos casos, las consultas que aparezcan aquí coincidirán con las más costosas, así que es probable que veas algunas en común.
En esta sección el término complejidad va a salir bastante. Definirlo es, bueno, complejo, pero por simplicidad lo defino aquí como "cuántos slots usa una consulta durante su ejecución". Este valor se define como la cantidad total de tiempo de slots dedicado a realizar trabajo dividido entre el tiempo total de ejecución de la consulta. Aquí va un ejemplo de cómo se hace para la vista JOBS_BY_PROJECT:
SELECT
SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
Para responder una pregunta que suelen hacerme, esto es una aproximación, ya que BigQuery lamentablemente no devuelve los valores exactos, y este cálculo es el mismo que muestra la interfaz de BigQuery.
Complejidad y conteo de slots
En cualquier sistema de bases de datos, cuando una consulta es lógicamente más compleja, eso suele tener una correlación directa con la complejidad de ejecutarla internamente. BigQuery no es la excepción y, además, suele existir una correlación directa entre esa complejidad y el costo de la consulta. Así que, en términos generales, reducir la complejidad también reduce los costos.
Dicho esto, la consulta principal del repositorio de GitHub para determinar la complejidad es top_complex_queries.sql. Esta consulta devuelve las consultas que usan más slots durante su tiempo de ejecución, así que tomar las primeras de esa lista te ayudará a identificar consultas que podrían tener problemas de rendimiento.
No es una ciencia exacta, ya que muchas veces las consultas complejas deben permanecer así y funcionar correctamente, pero es una forma de encontrar candidatas que podrían tener problemas.
Consultas de larga duración
Otra forma de identificar problemas de rendimiento es mirar las consultas que más tardan en ejecutarse. Como mencioné antes, tampoco es una ciencia exacta, ya que muchas veces una consulta tarda simplemente porque debe hacerlo.
La consulta de longest_running_queries.sql devuelve todas las consultas del intervalo ordenadas de la más larga a la más corta. Así, las que más tardan quedan al inicio para revisarlas. La columna runtimeToBytesBilledRatio dará una buena idea de cuántos bytes procesó la consulta por milisegundo. Por lo general, si este número es mayor a 1, conviene revisarla para ver si hay algo que se pueda optimizar y reducir la cantidad de datos procesados.
Más problemas de rendimiento
En una entrega futura de esta serie se cubrirá mucho más a fondo el ajuste de rendimiento, mostrando algunas de las trampas más comunes y poco conocidas del rendimiento de BigQuery, junto con métodos para superarlas.
Consultas de propósito general del repositorio
El último tema que aborda esta entrega de la serie es un conjunto de consultas en el repositorio de GitHub que muestran información o metadatos más generales que los cubiertos hasta ahora.
Consultas por tipo de job
Existen varios tipos de jobs, como queries, loads, extracts y table copies. Por mucho, los más comunes son queries y loads, así que en el repositorio están los archivos load_job_information.sql y query_job_information.sql, que devuelven estos tipos de jobs y los metadatos útiles sobre ellos.
También hay una versión más genérica llamada general_job_information.sql que devuelve todos los tipos de jobs y metadatos genéricos.
Consultas concurrentes
Voy a abrir esta sección señalando que Google acaba de hacer un cambio en diciembre de 2022 al comportamiento de BigQuery en relación con las consultas concurrentes. Antes había un límite estricto de 100 consultas concurrentes por proyecto, pero anticipándose a próximos cambios y al despliegue de las query queues, modificaron este valor para que escale con la cantidad de slots disponibles.
Cabe señalar que esto no está completamente documentado al momento de escribir, pero recopilé esta información a partir de soporte y la documentación existente.
Por defecto, este valor está en cero, lo que significa que BigQuery determina dinámicamente la concurrencia según los recursos disponibles que se pueden asignar al proyecto. Al cambiar a precios flat-rate, se puede configurar un parámetro llamado maximum concurrency en la reserva para solicitar que ese número sea la concurrencia máxima. Google no puede garantizar que se establezca exactamente así, pero intenta cumplirlo. La documentación oficial está en la página de query queues aquí.
Hecha la aclaración, vamos al grano con los problemas de las consultas concurrentes y cómo detectarlos.
Mientras se ejecutan consultas, hay momentos en que vas a correr demasiadas en paralelo y aparecerá el temido 503 "Service Unavailable" o el más reciente "Query was not admitted as the maximum concurrency has been reached". Esto significa que se superó tu nivel de concurrencia y las consultas no se programarán. Algo malo por razones obvias.
Aunque tu organización no haya alcanzado este límite en algunos proyectos, conviene tenerlo presente para evitarlo de forma preventiva. Hablaré de algunas estrategias para mitigarlo o para diseñar de modo que no ocurra en una entrega posterior, una vez que los nuevos cambios recién implementados por Google se puedan analizar y entender a fondo.
Mientras tanto, para conocer tus promedios de concurrencia por minutos y segundos, usa las consultas concurrent_queries_by_minute.sql y concurrent_queries_by_seconds.sql. Devuelven la cantidad promedio de consultas concurrentes en ejecución por cada minuto (o segundo) en el intervalo definido.
Conteo de consultas
Muchas veces una consulta se ejecuta varias veces, ya sea por un usuario o mediante algún proceso automatizado. Como cada ejecución cuesta dinero, es buena idea saber con qué frecuencia se ejecuta una consulta para determinar si está corriendo demasiado seguido y generando un costo excesivo.
La consulta query_counts.sql muestra la cantidad de veces que una consulta se ha ejecutado dentro del intervalo de tiempo. Es muy útil para identificar si una consulta específica se ejecuta más seguido de lo que debería y, por tanto, cuesta más dinero. También incluye el costo total de la consulta a lo largo del intervalo especificado para ayudarte a determinar si se está ejecutando y costando demasiado o no.
Para concluir
Con esto cierran las primeras tres entregas sobre optimización en BigQuery. En la siguiente sección incluí una tabla que muestra el contenido del repositorio de GitHub, la cual se actualizará a medida que se agreguen nuevas consultas y se documente la funcionalidad de cada una.
Pronto se sumarán más entregas a esta serie, ya que BigQuery atraviesa una etapa de cambios y aquí estaré para profundizar en cómo mantener tu uso optimizado conforme se vayan implementando.