Estatísticas SQL com Grafana, InfluxDB e Telegraf

Introdução

O desempenho do banco de dados tornou-se um fator crítico de sucesso em um ambiente de aplicações moderno. Portanto, identificar e otimizar as consultas SQL que consomem mais recursos é essencial para garantir uma experiência de usuário fluida e manter a estabilidade da aplicação.

Este artigo explorará uma abordagem rápida para analisar as estatísticas de execução de consultas SQL em uma instância InterSystems IRIS para identificar áreas de otimização dentro de uma macro-aplicação.

Em vez de focar no monitoramento em tempo real, configuraremos um sistema que coleta e analisa estatísticas pré-calculadas pelo IRIS uma vez por hora. Essa abordagem, embora não permita o monitoramento instantâneo, oferece um excelente compromisso entre a riqueza de dados disponíveis e a simplicidade de implementação.

Usaremos o Grafana para visualização e análise de dados, o InfluxDB para armazenamento de séries temporais e o Telegraf para coleta de métricas. Essas ferramentas, reconhecidas por seu poder e flexibilidade, nos permitirão obter uma visão clara e explorável.

Mais especificamente, detalharemos a configuração do Telegraf para recuperar estatísticas. Também configuraremos a integração com o InfluxDB para armazenamento e análise de dados, e criaremos dashboards personalizados no Grafana. Isso nos ajudará a identificar rapidamente as consultas que exigem atenção especial.

Para facilitar a orquestração e o deploy desses vários componentes, empregaremos o Docker.

Pré-requisitos

Before you start, make sure you have the following:

• Git: O Git é necessário para clonar o repositório do projeto que contém os arquivos de configuração e scripts..
• Docker ou Docker Desktop: O Docker pode ser usado para conteinerizar as aplicações InfluxDB, Telegraf e Grafana, tornando-as mais fáceis de implantar e gerenciar.
• InterSystems IRIS instance:Pelo menos a versão 2022.1, idealmente 2023.1 ou superior, com o pacote sql-stats-api instalado. Este pacote é essencial para expor as estatísticas SQL do IRIS e permitir que o Telegraf as colete. [ [Link to OpenExchange]

Embora nosso docker-compose inclua uma instância IRIS, ela não conterá nenhum dado de estatísticas SQL, pois será construída e iniciada do zero. Portanto, não será uma escolha conveniente para testar o sistema. É por isso que recomendamos fortemente que você tenha outra instância IRIS "ativa" (aquela com um histórico de consultas SQL) para ter a capacidade de visualizar dados reais e testar a ferramenta de análise.

Sobre as estatísticas

O IRIS coleta estatísticas de execução de consultas SQL com granularidade horária e diária. As estatísticas horárias identificam variações de desempenho ao longo do dia, enquanto as estatísticas diárias fornecem uma visão geral da atividade do banco de dados.

Abaixo, você pode ver os dados que coletaremos para cada consulta SQL:

• Número de execuções: Indica o número de vezes que a consulta foi executada.
• Tempo total de execução: Mede o tempo total de execução da consulta.
• Variância dos tempos de execução: É usada para identificar variações de desempenho e problemas isolados.
• Número total de linhas retornadas (RowCount): Disponível para IRIS 2023.1 e superior, esta métrica indica o número total de linhas retornadas pela consulta. Pode ajudá-lo a identificar consultas com alto consumo de recursos.
• Número total de comandos executados: Também disponível para IRIS 2023.1 e superior, esta métrica facilita uma análise mais detalhada da atividade do banco de dados e identifica consultas que poderiam ser otimizadas pela redução do número de operações.

Essas informações podem ser acessadas através das seguintes tabelas:

• INFORMATION_SCHEMA.STATEMENT_DAILY_STATS
• INFORMATION_SCHEMA.STATEMENT_HOURLY_STATS

Essas tabelas estão disponíveis desde o IRIS 2022.1. Abaixo, apresentamos um exemplo de consulta SQL para recuperar as estatísticas:

SELECT ds.* 
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS ds
INNER JOIN INFORMATION_SCHEMA.STATEMENTS st On ds.Statement = st.Hash


SELECT DATEADD('hh',"Hour",$PIECE(hs."Day",'||',2)) As DateTime, hs.*
FROM INFORMATION_SCHEMA.STATEMENT_HOURLY_STATS hs
INNER JOIN INFORMATION_SCHEMA.STATEMENTS st On $PIECE(hs."Day",'||',1) = st.Hash

Para versões anteriores ao IRIS 2022.1, recomendo fortemente o artigo de David Loveluck, que explica como recuperar estatísticas semelhantes.

Arquitetura

O projeto é baseado na interação de quatro componentes chave: IRIS, Grafana, InfluxDB e Telegraf. O diagrama abaixo ilustra a arquitetura geral do sistema e o fluxo de dados entre os vários componentes:

• InterSystems IRIS:É a instância que utilizaremos para recuperar as estatísticas.
• Pacote sql-stats-api: Este pacote ObjectScript expõe os dados estatísticos do IRIS via API REST. Ele oferece dois formatos de saída: JSON para uso geral e Line Protocol, um formato otimizado para ingestão rápida de dados de séries temporais no InfluxDB.
• Telegraf: É um agente de coleta de métricas que fornece a ligação entre o IRIS e o InfluxDB. Neste projeto, utilizaremos duas instâncias do Telegraf:
  • Um agente consulta periodicamente a API REST do IRIS para recuperar as estatísticas SQL em tempo real.
  • Outro agente opera no modo de "varredura de diretório". Ele monitora um diretório com arquivos armazenados e os transmite para o InfluxDB, permitindo a integração de dados inacessíveis via API REST.
• InfluxDB:  Este banco de dados de séries temporais armazena e gerencia as estatísticas SQL coletadas pelo Telegraf, já que sua arquitetura é otimizada para esse tipo de dado. O InfluxDB também oferece integração nativa com o Grafana, facilitando a visualização e análise dos dados. Escolhemos o InfluxDB em vez do Prometheus porque o último é mais voltado para o monitoramento em tempo real e não é adequado para armazenar dados agregados, por exemplo, somas ou médias por hora ou dia, que são essenciais para nossa análise.
• Grafana: É uma ferramenta de visualização que permite criar dashboards personalizados e interativos para analisar o desempenho do SQL. Ele recupera dados do InfluxDB e oferece uma variedade de gráficos e widgets para visualizar as estatísticas de forma clara e utilizável.

Instalação

Comece clonando o repositório:

git clone https://github.com/lscalese/iris-sql-dashboard.git
cd irisiris-sql-dashboard

Configurando o ambiente

Este projeto usa o Docker para orquestrar o Grafana, InfluxDB, Telegraf e IRIS. Por razões de segurança, informações confidenciais, como chaves de API e senhas, são armazenadas em um arquivo .env.

Crie o arquivo .env usando o exemplo fornecido abaixo:

cp .env.example .env

Edite o arquivo .env para configurar as variáveis:

Configuração das variáveis

• TZ:Fuso horário. Você deve modificar esta variável de acordo com seu fuso horário para garantir que os dados tenham o carimbo de data/hora correto.
• DOCKER_INFLUXDB_INIT_PASSWORD: Esta é a senha do Administrador para acessar o InfluxDB.
• IRIS_USER: É um usuário IRIS da instância Docker do IRIS (_system por padrão).
• IRIS_PASSWORD :É a senha da instância Docker do IRIS (SYS por padrão).

As chaves de API permitem as seguintes conexões:

• GRAFANA_INFLUX_API_KEY : Grafana <-> InfluxDB.
• TELEGRAF_INFLUX_API_KEY : Telegraf <-> InfluxDB.

Geração de Chaves de API

Por razões de segurança, o InfluxDB requer chaves de API para autenticação e autorização. Essas chaves são usadas para identificar e autorizar vários componentes (Telegraf, Grafana) a acessar o InfluxDB.

O script init-influxdb.sh, incluído no repositório, facilita a geração dessas chaves. Ele será executado automaticamente na primeira vez que o contêiner inflxudb2 for iniciado:

docker compose up -d influxdb2

Após alguns segundos, o arquivo .env será atualizado com suas chaves de API geradas.

Nota: esta etapa só deve ser realizada na primeira vez que você iniciar o contêiner..

Verifique se você tem acesso à interface de administração do InfluxDB através da URLhttp://localhost:8086/

Faça login com o nome de usuário "admin" e a senha especificada na variável de ambiente“DOCKER_INFLUXDB_INIT_PASSWORD” no arquivo ".env". Ao navegar em "Load Data >> Buckets", você deverá encontrar um bucket "IRIS_SQL_STATS" pré-configurado.

Ao acessar "Load Data >> API Tokens", você deverá encontrar nossas 2 chaves de API: "Grafana_IRIS_SQL_STATS" e "Telegraf_IRIS_SQL_STATS":

O ambiente está pronto agora e podemos passar para o próximo passo!

Início

Como o ambiente foi configurado e as chaves de API foram geradas, você pode finalmente iniciar o conjunto de contêineres. Para fazer isso, execute o seguinte comando no diretório raiz do projeto:

docker compose up -d

Este comando iniciará todos os serviços definidos no arquivo docker-compose.yml em segundo plano: InfluxDB, Telegraf, Grafana e a instância IRIS.

Dashboard Grafana

O Grafana agora está disponível em http://localhost:3000.

Conectar ao Grafana

Abra seu navegador da web e acesse http://localhost:3000. O nome de usuário e a senha padrão são admin/admin. No entanto, você será solicitado a alterar a senha no primeiro login.

Verificando a fonte de dados InfluxDB

A fonte de dados InfluxDB já está pré-configurada no Grafana. Tudo o que você precisa fazer é verificar se ela está funcionando corretamente.

Vá para "Connections > Data sources".

Você deverá ver uma fonte de dados chamada "influxdb".

Clique nela para modificá-la. Em seguida, clique em "Save & Test". A mensagem "Datasource is working. 1 bucket found" deverá aparecer na tela agora.

Explorando os painéis

Neste ponto, você verificou que a comunicação entre o Grafana e o InfluxDB está estabelecida, o que significa que você pode explorar os painéis predefinidos.

Prossiga para "Dashboards".

Você encontrará dois painéis predefinidos:

• InfluxDB - SQL Stats: Este painel exibe estatísticas gerais de execução de consultas SQL, por exemplo, número de execuções, tempo total de execução e variação do tempo de execução.

• InfluxDB - SQL Stats Details: Este painel fornece informações mais detalhadas sobre cada consulta SQL, por exemplo, o número total de linhas retornadas ou comandos executados.

Por que os painéis estão vazios?

Se você abrir os painéis, notará que eles estão vazios. Isso ocorre porque nosso agente Telegraf está atualmente conectado à instância IRIS fornecida no repositório Docker, que não contém dados estatísticos em suas tabelas. As estatísticas SQL são coletadas apenas se a instância IRIS estiver ativa e mantiver um histórico de consultas SQL.

Na próxima seção, exploraremos como injetar dados na instância IRIS para visualizar as estatísticas no Grafana.

 

Telegraf

O sistema de monitoramento opera dois agentes Telegraf com funções específicas:

• telegraf-iris.conf: Este agente coleta dados em tempo real de uma instância IRIS ativa. Ele consulta a API REST do IRIS para recuperar estatísticas SQL e enviá-las para o InfluxDB.
• telegraf-directory-scan.conf: Este agente integra dados históricos armazenados em arquivos. Ele monitora o diretório ./telegraf/in/, lê arquivos contendo estatísticas SQL e os envia para o InfluxDB.

Para coletar dados em tempo real, você deve conectar o Telegraf a uma instância IRIS ativa com o pacote sql-stats-api instalado. Este pacote expõe as estatísticas SQL por meio de uma API REST, permitindo que o Telegraf as acesse.

Configurando o telegraf-iris.conf

Para conectar o Telegraf à sua instância IRIS, você deve modificar o arquivo./telegraf/config/telegraf-iris.conf. Abaixo, você pode encontrar um exemplo da configuração:

[[inputs.http]]
  ## One or more URLs from which to read formatted metrics
  urls = [
    "http://iris:52773/csp/sqlstats/api/daily",
    "http://iris:52773/csp/sqlstats/api/hourly"
  ]
  ## HTTP method
  method = "GET"

  ## Optional HTTP headers
  headers = {"Accept" = "text/plain"}
  ## Optional HTTP Basic Auth Credentials
  username = "${IRIS_USER}"
  password = "${IRIS_PASSWORD}"
  data_format = "influx"

Certifique-se de que${IRIS_USER} e ${IRIS_PASSWORD}estejam definidos corretamente no seu arquivo.env .
Nota: Você pode copiar o arquivo e editar os parâmetros para conectar o Telegraf a várias instâncias IRIS.

Reinicie o Telegraf:

Após modificar o arquivo de configuração, você precisa reiniciar o contêiner Telegraf para que as alterações tenham efeito:

docker compose up -d telegraf --force-recreate

Recuperando dados históricos

Para recuperar estatísticas históricas de SQL, use o método CreateInfluxFile do ObjectScript na sua instância IRIS:

; Adapt the path to your needs
Set sc = ##class(dc.sqlstats.services.SQLStats).CreateInfluxFile("/home/irisowner/dev/influxdb-lines.txt",,1)

Este script gravará o histórico de estatísticas de SQL em arquivos de texto, com um comprimento máximo de 10.000 linhas por arquivo. Em seguida, você pode colocar esses arquivos no diretório ./telegraf/in/ para processá-los e injetá-los no InfluxDB.

Verificando a injeção de dados

Você pode verificar se os dados foram injetados com precisão no InfluxDB com a ajuda da interface web. Vá para o "Data Explorer" e confira:

Visualização de dados no Grafana

Uma vez que os dados tenham sido injetados, você poderá visualizá-los nos seus painéis Grafana provisionados.

Chegamos ao final deste artigo. Espero que tenha sido útil para você e que tenha ensinado como configurar facilmente um sistema para monitorar e analisar estatísticas de SQL em suas instâncias IRIS.

Como você deve ter notado, este artigo se concentrou nos aspectos práticos da configuração e do emprego de várias ferramentas. Não pesquisamos todos os detalhes do funcionamento interno do InfluxDB, o formato do Line Protocol ou a linguagem de consulta Flux, nem examinamos a infinidade de plugins disponíveis para o Telegraf.

Esses tópicos, por mais fascinantes que sejam, exigiriam um artigo muito mais longo. Eu o(a) encorajo fortemente a consultar a documentação oficial para InfluxDB Começar com o InfluxDB e o Diretório de Pluginsdo Telegraf para aprofundar seu conhecimento e descobrir todas as possibilidades oferecidas por essas ferramentas.

Não hesite em compartilhar suas experiências nos comentários.

Obrigado por ler e até a próxima!