How to Monitor MyXQL (Elixir) with Vigilmon

MyXQL is the pure-Elixir MySQL driver that ships as the default Ecto adapter for MySQL and MariaDB databases. It speaks the MySQL binary protocol over TCP, supports MySQL 5.7+ and MariaDB, and integrates with DBConnection to provide configurable connection pooling, prepared statements, and streaming query results.

But a MySQL-backed application carries unique failure modes. A saturated connection pool queues all database operations, cascading into request timeouts. A misconfigured max_connections setting lets one noisy query crowd out everything else. Network partitions between your app and MySQL silently stall connections without immediate errors. These problems are invisible without active monitoring.

This tutorial adds production observability to an Elixir application using MyXQL:

• An HTTP health check with connection pool and MySQL server metrics
• HTTP uptime monitoring with Vigilmon
• Slow query detection via Ecto telemetry
• Heartbeat monitoring for scheduled database maintenance tasks
• Alerts on pool saturation and query latency

Step 1: Add MyXQL to your project

# mix.exs
defp deps do
  [
    {:ecto_sql, "~> 3.11"},
    {:myxql, "~> 0.7"},
    {:plug_cowboy, "~> 2.0"},
    {:plug, "~> 1.14"},
    {:jason, "~> 1.4"},
    {:req, "~> 0.4"},
    {:telemetry, "~> 1.2"}
  ]
end

Configure Ecto to use MyXQL:

# config/config.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.MyXQL,
  hostname: System.get_env("DB_HOST", "localhost"),
  port: String.to_integer(System.get_env("DB_PORT", "3306")),
  username: System.get_env("DB_USER", "myapp"),
  password: System.get_env("DB_PASS"),
  database: System.get_env("DB_NAME", "myapp_prod"),
  pool_size: String.to_integer(System.get_env("DB_POOL_SIZE", "10")),
  queue_target: 50,       # ms before queued checkouts log a warning
  queue_interval: 1_000   # ms window for queue_target averaging

Step 2: Set up the Repo and supervision tree

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.MyXQL
end

# lib/my_app/application.ex
defmodule MyApp.Application do
  use Application

  def start(_type, _args) do
    children = [
      MyApp.Repo,
      MyApp.Telemetry,          # attach Ecto telemetry handlers
      MyAppWeb.Endpoint
    ]

    Supervisor.start_link(children, strategy: :one_for_one, name: MyApp.Supervisor)
  end
end

Step 3: Attach slow-query telemetry

# lib/my_app/telemetry.ex
defmodule MyApp.Telemetry do
  use GenServer
  require Logger

  @slow_query_threshold_ms 200

  def start_link(_), do: GenServer.start_link(__MODULE__, %{}, name: __MODULE__)

  @impl true
  def init(state) do
    :telemetry.attach(
      "myxql-slow-query",
      [:my_app, :repo, :query],
      &__MODULE__.handle_query_event/4,
      nil
    )

    {:ok, state}
  end

  def handle_query_event(_event, measurements, metadata, _config) do
    duration_ms = System.convert_time_unit(measurements.total_time, :native, :millisecond)

    if duration_ms >= @slow_query_threshold_ms do
      Logger.warning("[slow_query] #{duration_ms}ms — #{metadata.query}")
    end
  end
end

Ecto emits [:my_app, :repo, :query] telemetry events automatically — no additional instrumentation in your query code is required.

Step 4: Add a health check with connection pool metrics

# lib/my_app_web/plugs/health_check.ex
defmodule MyAppWeb.Plugs.HealthCheck do
  import Plug.Conn

  def init(opts), do: opts

  def call(%Plug.Conn{request_path: "/health"} = conn, _opts) do
    checks = %{
      database: check_database(),
      pool: check_pool()
    }

    status = if Enum.all?(checks, fn {_, v} -> v == :ok end), do: 200, else: 503

    conn
    |> put_resp_content_type("application/json")
    |> send_resp(status, Jason.encode!(%{
      status: (if status == 200, do: "ok", else: "degraded"),
      checks: checks,
      pool_stats: get_pool_stats()
    }))
    |> halt()
  end

  def call(conn, _opts), do: conn

  defp check_database do
    case Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1", []) do
      {:ok, _} -> :ok
      {:error, _} -> :error
    end
  rescue
    _ -> :error
  end

  defp check_pool do
    stats = get_pool_stats()

    cond do
      stats.idle == 0 and stats.busy == stats.size -> :error  # pool fully saturated
      true -> :ok
    end
  end

  defp get_pool_stats do
    # DBConnection exposes pool metrics via the process dictionary of the pool supervisor
    pool = MyApp.Repo.get_dynamic_repo() || MyApp.Repo

    try do
      %{
        size: MyApp.Repo.config()[:pool_size] || 10,
        busy: count_busy_connections(),
        idle: count_idle_connections()
      }
    rescue
      _ -> %{size: 0, busy: 0, idle: 0}
    end
  end

  defp count_busy_connections do
    # DBConnection pool workers broadcast state — approximate via process registry
    pool_name = MyApp.Repo.get_dynamic_repo() || MyApp.Repo
    pool_size = MyApp.Repo.config()[:pool_size] || 10

    checked_out =
      :sys.get_state(DBConnection.Ownership.pool_name(pool_name, []))
      |> then(fn state -> map_size(state.owner_to_meta) end)

    min(checked_out, pool_size)
  rescue
    _ -> 0
  end

  defp count_idle_connections do
    pool_size = MyApp.Repo.config()[:pool_size] || 10
    pool_size - count_busy_connections()
  end
end

Mount it in your endpoint:

# lib/my_app_web/endpoint.ex
defmodule MyAppWeb.Endpoint do
  use Phoenix.Endpoint, otp_app: :my_app

  plug MyAppWeb.Plugs.HealthCheck
  plug MyAppWeb.Router
end

Test it:

curl http://localhost:4000/health
# {"status":"ok","checks":{"database":"ok","pool":"ok"},"pool_stats":{"size":10,"busy":1,"idle":9}}

Step 5: Set up HTTP monitoring in Vigilmon

Point Vigilmon at your health endpoint:

1. Sign up at vigilmon.online
2. Click New Monitor → HTTP
3. Enter https://yourdomain.com/health
4. Set check interval: 1 minute (paid) or 5 minutes (free)
5. Under Advanced, add a keyword check for "ok" to verify the health status
6. Save

Why external monitoring matters for MySQL-backed applications:

MySQL connectivity issues rarely produce immediate application crashes — instead they appear as query timeouts, slow responses, and pool queue buildup. External monitoring catches:

• Silent TCP connection drops between app and MySQL (symptom: health check times out)
• Pool saturation during traffic spikes (symptom: 503 on health check)
• MySQL server restarts or failover events (symptom: brief downtime windows)

Vigilmon's response time history is useful here: a sudden latency increase on your health check often signals pool pressure or MySQL server load before user-visible errors begin.

Step 6: Heartbeat monitoring for database maintenance tasks

Periodic maintenance tasks — data archival, statistics updates, index rebuilds — should have heartbeat monitors so failures don't silently accumulate:

# lib/my_app/workers/db_maintenance_worker.ex
defmodule MyApp.Workers.DbMaintenanceWorker do
  use GenServer
  require Logger

  @interval :timer.hours(6)

  def start_link(_), do: GenServer.start_link(__MODULE__, %{}, name: __MODULE__)

  @impl true
  def init(state) do
    schedule_tick()
    {:ok, state}
  end

  @impl true
  def handle_info(:run, state) do
    case run_maintenance() do
      :ok ->
        ping_heartbeat()
        Logger.info("DB maintenance cycle complete")
      {:error, reason} ->
        Logger.error("DB maintenance failed: #{inspect(reason)}")
    end

    schedule_tick()
    {:noreply, state}
  end

  defp schedule_tick, do: Process.send_after(self(), :run, @interval)

  defp run_maintenance do
    # Example: archive old rows to a history table
    MyApp.Repo.transaction(fn ->
      MyApp.Repo.query!("""
        INSERT INTO orders_archive SELECT * FROM orders
        WHERE inserted_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
      """)
      MyApp.Repo.query!("""
        DELETE FROM orders WHERE inserted_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
      """)
    end)

    :ok
  rescue
    e -> {:error, Exception.message(e)}
  end

  defp ping_heartbeat do
    url = Application.get_env(:my_app, :vigilmon)[:db_maintenance_heartbeat_url]
    if url do
      case Req.get(url, receive_timeout: 10_000) do
        {:ok, _} -> :ok
        {:error, reason} -> Logger.warning("Maintenance heartbeat failed: #{inspect(reason)}")
      end
    end
  end
end

Add it to your supervision tree and create a Heartbeat monitor in Vigilmon with an interval of 370 minutes (6-hour job with a 10-minute grace window).

Step 7: Alerts via Slack

In Vigilmon, go to Notifications → New Channel → Slack and paste your incoming webhook URL.

Enable the channel on all monitors. MySQL-related incidents commonly look like:

• HTTP health check 503 (pool fully saturated or MySQL unreachable)
• Response time spike over 2 seconds (slow queries backing up)
• Maintenance heartbeat missed (archive job crashed or ran over time)

Configure Vigilmon to page your on-call channel for HTTP downtime and heartbeat misses immediately, and alert on response time degradation after a 2-minute window to filter transient spikes.

Step 8: Status page

1. Status Pages → New Status Page in Vigilmon
2. Add your HTTP health monitor and all heartbeat monitors
3. Share the URL with your team and in your runbook

README badge:

![Uptime](https://vigilmon.online/badge/your-monitor-slug)

What you've built

| What | How | |------|-----| | Health check endpoint | HealthCheck plug with database ping + pool stats | | Pool saturation detection | DBConnection pool state inspection | | Slow query logging | Ecto telemetry handler at 200 ms threshold | | HTTP uptime monitoring | Vigilmon HTTP monitor → /health | | Latency trend tracking | Vigilmon response time history | | Slack downtime alerts | Vigilmon Slack notification channel | | Maintenance heartbeat | Heartbeat ping after each archival cycle | | Status page | Vigilmon public status page |

MyXQL gives your Elixir application a fast, standards-compliant MySQL connection. Vigilmon ensures those connections — and the queries they carry — stay healthy in production.

Next steps

• Export pool and query metrics to Prometheus via telemetry_metrics and TelemetryMetricsPrometheus for long-term trend analysis
• Use Vigilmon's response time history to correlate latency spikes with specific deployment times
• Set a Vigilmon alert threshold on health endpoint response time to catch pool pressure before requests start queuing
• Add a second Vigilmon monitor on your MySQL port (TCP monitor) to distinguish app-level issues from network-level MySQL unreachability

Get started free at vigilmon.online.