En este extracto editado de una serie sobre optimización de costos y rendimiento en BigQuery, exploramos cómo detectar las queries que más te están costando en tu entorno.
Un extracto editado de una serie sobre optimización de costos y rendimiento en BigQuery
La primera parte de esta serie explicó qué necesitas tener listo para ejecutar queries en BigQuery, y la segunda parte repasó los errores más comunes con los que te vas a topar al optimizar tu uso de BigQuery. En este artículo vemos cómo identificar las queries más costosas de tu entorno. Todo el código de la serie está disponible en este repositorio de GitHub.
Las queries más costosas en general
La primera query que tienes que revisar está en el archivo top_costly_queries.sql. Posiblemente la query más importante en términos de costo: incluye el costo total que acumula una query a lo largo del intervalo, sumando todas sus ejecuciones. Su propósito es doble: identificar la query más costosa de tu entorno y detectar si una query costosa se está ejecutando más veces de las necesarias.
Las queries individuales más costosas
La segunda query que vas a usar en esta sección está en el archivo top_billed_queries.sql. Lista las queries más costosas de tu entorno, ordenadas de mayor a menor costo. A diferencia de la primera, esta no toma en cuenta cuántas veces se ejecuta la query: simplemente muestra el costo por ejecución.
Al correr esta query, las más costosas saltarán al inicio del resultado. Identifica la query principal y revisa primero cuántos datos procesó (las columnas total*Billed). Luego toma ese número y analiza la query para entender qué la hace tan cara.
Nota sobre queries duplicadas
Si los resultados muestran la misma query varias veces y eso distorsiona la vista o dificulta la revisión, mejor consulta el archivo top_billed_queries_deduplicated.sql. Es la misma query, pero deduplica la query exacta que se ejecuta, así solo aparece un resultado. Tarda más en correr y escanea más datos al hacerlo, pero devuelve resultados sin duplicados.
BigQuery no ofrece de forma nativa una agregación de "similitud de strings", como una distancia de Hamming o de Levenshtein, así que filtrar queries similares con cambios mínimos —como una fecha— resulta relativamente difícil. Si lo necesitas, hay algunas soluciones UDF que implementan ciertos algoritmos de "similitud de strings" para BigQuery, pero conviene crear la solución fuera de BigQuery, porque las implementaciones UDF de algoritmos tan costosos en cómputo son bastante lentas.
Los usuarios más costosos
Una tercera query útil para examinar costos es el archivo top_cost_users.sql. Lista las queries más costosas de tu entorno, pero ordenadas por usuario y luego por la query más costosa.
El propósito es mostrar qué usuarios o cuentas de servicio están gastando más dinero y en qué queries. Esta lista suele incluir procesos que ejecutan queries ineficientes de los que tal vez ni te enteres. En algunos casos extremos, esos procesos viven en otros proyectos o en otras nubes. Agregar filtros adicionales para excluirlos también puede ayudar.
Otras queries del repositorio
El repositorio de GitHub también incluye queries adicionales para fines más específicos: encontrar queries originadas en Looker, contar cuántas veces se ejecuta una query, calcular el costo de queries con etiquetas específicas, etc. Son bastante puntuales, pero las hemos usado en DoiT y por eso las compartimos con la comunidad.
Cómo encontrar queries con problemas de rendimiento
El siguiente gran tema es encontrar queries que consumen más recursos de los necesarios y no rinden como se espera. Suelen ser también las más costosas, así que puede haber cierto traslape.
En esta sección vamos a definir el término "complejidad" como la cantidad de slots que usa una query durante su ejecución. Este valor se calcula como el tiempo total de slots dedicado al trabajo, dividido entre el tiempo total de ejecución de la query. Aquí va un ejemplo aplicado a la vista JOBS_BY_PROJECT:
SELECTSAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCountFROM`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECTEs una aproximación porque, lamentablemente, BigQuery no devuelve los valores exactos, y este es el mismo cálculo que muestra la UI de BigQuery.
Complejidad y conteo de slots
En cualquier sistema de bases de datos, una mayor complejidad lógica de una query suele correlacionarse de forma directa con la complejidad de ejecutarla internamente. BigQuery no es la excepción. Además, suele existir una correlación directa entre esa complejidad y el costo. En términos generales, reducir la complejidad también reduce los costos.
Dicho esto, la query principal del repositorio de GitHub para medir complejidad es top_complex_queries.sql. Devuelve las queries que más slots consumen durante su ejecución, así que tomar las primeras del listado ayuda a identificar candidatas con posibles problemas de rendimiento. No es una ciencia exacta, porque muchas queries complejas tienen que serlo para funcionar correctamente, pero es una buena forma de detectar candidatas problemáticas.
Queries de larga duración
Otra forma de detectar problemas de rendimiento es revisar las queries que más tardan en ejecutarse. Sin embargo, una query a veces tarda simplemente porque no le queda otra.
La query en longest_running_queries.sql devuelve todas las queries del periodo ordenadas de mayor a menor duración. Esto pone arriba las que más tardan, listas para analizar. La columna runtimeToBytesBilledRatio te da una buena idea de cuántos bytes procesa la query por milisegundo. Por regla general, si ese número es mayor que 1, vale la pena revisarla y ver si hay algo que se pueda optimizar para reducir la cantidad de datos procesados.
Una nota sobre los problemas de rendimiento
En una próxima entrega de esta serie cubriré el ajuste de rendimiento con más detalle, mostrando algunos de los tropiezos más comunes —y menos conocidos— del rendimiento en BigQuery, además de cómo superarlos.
Queries de uso general del repositorio
El último tema de esta entrega es un conjunto de queries del repositorio de GitHub que muestran información o metadatos más generales que los vistos hasta ahora.
Queries por tipo de job
De lejos, los tipos de jobs más comunes (queries, loads, extracts y copias de tablas) son queries y loads. Por eso, los archivos load_job_information.sql y query_job_information.sql del repositorio devuelven estos tipos de jobs junto con metadatos útiles. Una versión más genérica, llamada general_job_information.sql, devuelve todos los tipos de jobs y metadatos generales sobre ellos.
Queries concurrentes
En diciembre de 2022, Google modificó el comportamiento de BigQuery en relación con las queries concurrentes. Anticipándose a los cambios y al lanzamiento de las colas de queries, elevó el límite fijo de 100 queries concurrentes por proyecto para que escale según la cantidad de slots disponibles. Al momento de escribir esto no estaba documentado del todo, pero hemos recopilado esta información a partir de soporte y de la documentación existente.
Por defecto, este valor está en cero, lo que significa que BigQuery determina dinámicamente la concurrencia con base en los recursos disponibles que pueden asignarse al proyecto. Al cambiar a precios flat-rate, se puede configurar una opción en la reserva llamada maximum concurrency para solicitar ese número como concurrencia máxima. Google no garantiza que se cumpla al pie de la letra, pero intenta hacerlo. La documentación oficial está en la página de colas de queries aquí. Hecha la aclaración, entremos en los problemas con las queries concurrentes.
Si ejecutas demasiadas queries en paralelo, te encontrarás con el temido mensaje 503 "Service Unavailable" o, más recientemente, "Query was not admitted as the maximum concurrency has been reached." Esto significa que se superó tu nivel de concurrencia y las queries no se programarán. Es algo malo por razones obvias y conviene evitarlo.
Cuando los nuevos cambios de Google se puedan analizar y entender por completo, compartiremos algunas estrategias para mitigarlos o diseñar todo de manera que no ocurran. Mientras tanto, usa las queries concurrent_queries_by_minute.sql y concurrent_queries_by_seconds.sql para conocer tus promedios de concurrencia por minuto y por segundo. Devuelven el promedio de queries concurrentes en ejecución por cada minuto (o segundo) dentro del intervalo definido.
Conteo de queries
Las queries suelen ejecutarse varias veces, ya sea por un usuario o por algún proceso automatizado. La query query_counts.sql muestra cuántas veces se ha ejecutado una query dentro del intervalo de tiempo. Es muy útil para detectar si una query específica se está ejecutando más seguido de lo que debería, y por lo tanto gastando dinero de más. También incluye el costo total de la query en el intervalo especificado.
Qué hacer a continuación
Este post es una versión condensada de mi serie de artículos sobre cómo optimizar tus queries de BigQuery, que se irá ampliando a medida que BigQuery vaya implementando cambios. Si ya eres cliente de DoiT, puedes realizar todos estos pasos dentro de la funcionalidad BQ Lens de DoiT Cloud Intelligence™. Mientras tanto, escríbenos a DoiT para aprovechar nuestra amplia y profunda experiencia en BigQuery, machine learning e inteligencia de negocios.