In our previous article, we explored the basics of monitoring Ecto with AppSignal, covering everything from initial setup to tracking key metrics such as query execution time and resource consumption. We even set up custom instrumentation for database connection pools to gain deeper insights into our application's performance. However, setting up monitoring is just the first step toward maintaining a healthy, high-performing Elixir application. The real power comes from knowing what to do with all that data.

In this second part of our Ecto monitoring series, we'll take your monitoring strategy to the next level. We'll explore how to establish meaningful baseline metrics, set up intelligent alerts that notify you of issues before they affect users, and implement robust exception monitoring to catch database problems early.

Prerequisites

• Elixir, the Phoenix framework, and PostgreSQL installed on your development environment.
• Some experience with Elixir and the Phoenix framework.
• An AppSignal account. If you don't have one ready, sign up for a free trial.
• An Elixir/Phoenix app to follow along with. If you don't have one, fork the blog app we'll be using for this tutorial.

Let's get started.

Establishing Baseline Metrics for Ecto

Database monitoring is only valuable as long as you know how to distinguish between "healthy" and "unhealthy" database metrics. Obviously, whether a metric is healthy or not is relative to the application, the hosting environment, and a host of other variables.

All the same, it's important to establish baseline metrics to see whether your database is performing optimally or not.

Why Should You Use Baseline Metrics?

Baseline metrics allow you to define what normal database behavior looks like versus abnormal behavior which could impact your users negatively.

Baseline metrics define expected performance characteristics under normal conditions. They help us answer questions like:

• How many database operations per second should we expect?
• How are connection pools being utilized when our app is under load?
• How long does a query take?

By answering such questions, you will be able to:

• Use data-driven optimization - By knowing the healthy ranges under which your app's Ecto database operates, you can set up optimizations backed up by data. For example, if fetching posts from your blog app takes 30 ms, you could reliably set up database indexes that reduce this to, say, 20 ms.
• Early problem detection - By knowing what "normal" metrics look like, you can set up an early warning system to alert you to problems affecting Ecto in advance. For example, imagine a query that normally takes 10 ms to execute, which suddenly spikes to 200 ms in execution time. With a baseline already set up, such a spike is flagged as an anomaly that needs to be checked.
• Smart resource planning - Knowing how to allocate server resources for your app is very key. Instead of flying blind when it comes to allocating server resources, you can use baseline trends to figure out if your app's resources should be scaled up or down.

Now that we have an idea of what baseline metrics are and why we need them, let's learn how to set some up in the next section.

Collecting Initial Performance Data

Note: We won't go through the process of setting up a new application and adding the AppSignal integration; the previous article has a good walk-through, in case you need a refresher.

Before actually collecting any performance data for our baseline needs, we need to decide what data is important.

In the context of the blog app, the following operations make good examples:

• Fetching a post with comments from the database by hitting the GET /posts/:id endpoint.
• Creating a post using the POST /posts endpoint.
• Something that will put a strain on the database index, like listing all posts (GET /posts).

These examples allow us to collect our initial baseline metrics for Ecto.

Having established the baseline metrics we'll be collecting, the next step is to simulate web traffic that will allow AppSignal to collect the data we need and display it in a dashboard.

Using a Load Testing Tool to Collect Baseline Metrics

We'll use a load testing tool to simulate web traffic to some of the endpoints mentioned above.

There are many load testing tools you can choose from, but for the purposes of this tutorial, we'll go with the k6 load testing tool, an easy-to-use command-line tool for load testing. You can follow this guide to install k6 on your machine.

Once you have k6 ready to go, let's create a simple script that simulates website traffic for one hundred virtual users and fetches a blog post with comments on it.

Baseline Metrics for Fetching a Post With Comments

The blog app's Post context contains the get_post!() function. This fetches a post by id and also preloads any comments on that post:

# lib/blog_app/posts.ex
 
defmodule BlogApp.Posts do
  ...
 
  def get_post!(id), do: Repo.get!(Post, id) |> Repo.preload(:comments)
 
  ...
end

Let's create a simple k6 script that will simulate a hundred virtual users fetching a post:

Note: Create this script in a different folder from your app's folder.

// k6_simulations/script.js
 
import http from "k6/http";
import { sleep } from "k6";
 
export const options = {
  vus: 100, // 100 virtual users
  duration: "30s", // How long the test will run
};
 
export default function () {
  http.get("http://localhost:4000/posts/6"); // We fetch a post with a few comments...
  sleep(1);
}

In the script, we define:

• vus - the number of virtual users to simulate
• duration - how long the simulation will take (in seconds)
• sleep(1) - to simulate real user behavior where users pause between requests, as opposed to continuous requests, which wouldn't be normal.

Then run the script with:

k6 run script.js

After the script runs and you give AppSignal some time to gather the data from Ecto, you should get a dashboard as shown below:

From the graph, we can see that it takes about 30 ms to load the post and another 32 ms to load the associated comments.

Next, let's see how Ecto behaves when we fetch all posts at once using the same one hundred virtual users.

Baseline Metric When the Database Index Is Under Strain

For this one, modify the k6 script as shown below:

// k6_simulations/script.js
 
import http from "k6/http";
import { sleep } from "k6";
 
export const options = {
  vus: 100, // 100 virtual users
  duration: "30s", // How long the test will run
};
 
export default function () {
  http.get("http://localhost:4000/posts"); // We fetch all posts
  sleep(1);
}

Then run it with k6 run script.js, wait, and then view the resulting dashboard on AppSignal:

From the results, we can see Ecto takes around 117 ms to fetch all posts and comments.

Now that we have these metrics, the next question is: "What do we consider to be healthy query ranges for Ecto to compare against?"

Determining Healthy Ecto Ranges and Benchmarks

Determining healthy metrics for Ecto queries is very subjective because no two apps are completely identical in every way, and there are numerous variables that can affect query times.

That said, the following benchmark metrics are worth considering:

• Sub-50 ms - For common queries, such as fetching single posts and comments, this is a good benchmark to compare against. Our comparison metric of 30 ms - 32 ms falls within this range, which is good and possibly due to us preloading the comments in the get_post() function.
• 50 ms - 100 ms - This is considered normal for heavier queries, for example, fetching all posts and associated comments. From the results we got when we load-tested the blog app, we can see that 72 ms is cutting it really close and may indicate there's a need to optimize the query.
• Greater than 100 ms - Queries that fall within this range can be considered "very slow." Any query in this range needs immediate optimization because the effect of such a slow query will definitely affect user experience.

Now that we've determined the benchmark metrics we can measure our app's queries against, it would be very convenient if we could get notifications and alerts whenever our app experiences slow queries. AppSignal has an excellent notification and alerting layer that can help us with this.

In the next section, let's learn how to set up alerts and notifications.

Setting up Effective Alerts for Ecto On AppSignal

Having learned about baseline metrics and what to benchmark against, it wouldn't make sense to keep on manually checking dashboards or discovering performance issues after users complain. Instead, we can make use of AppSignal's anomaly detection to notify us when Ecto queries move from normal to abnormal behavior.

In this section, we'll configure alerts that will help you catch database performance problems before they impact your users. We'll set up alerts for slow queries, but the same techniques can be applied for other metrics such as high database connection usage and other critical metrics that could indicate potential issues with your Ecto setup.

For our first example, we'll set up an alert that will trigger whenever an Ecto query takes more than 100 ms to execute.

We will take the following steps:

• Setting up a trigger - A "trigger" is what determines if an alert should be raised.
• Configuring the trigger - Set up the trigger to fire whenever a metric exceeds pre-determined bounds.
• Setting up alert warmup and cooldown - As important as alerts can be, you also don't want them to fire off too many times. Warmups and cooldowns let you control this.
• Configuring the alerting service - Here, you get to choose how an alert will be delivered to you. AppSignal gives you a wide choice, from email to SMS alerts, and everything in between.

Setting Up a Trigger

To access triggers on the AppSignal dashboard, go to the left-hand side menu, then locate a menu category called "Anomaly detection," under which you'll find the "Triggers" menu:

Then, click on the "Add a trigger" button, which opens up a dialog like the one shown below:

On the new trigger dialog, we have a few options to set up:

• 1. Metric type - You can set up a trigger for a variety of metrics: error rates, slow actions, CPU usage, and many more. For our example, we'll go with "slow actions" since we want to get an alert whenever Ecto queries take too long to execute.
• 2. Namespace - Choose the namespace you want to set up the trigger in. Read more on namespaces in AppSignal's documentation.
• 3. When to trigger the alert - Here's where you determine the actual conditions under which the trigger will be raised. You can set up a trigger to go off based on the mean, the 90th, or 95th percentile of a metric's measure.
• 4. Alert warmup and cooldown - These help prevent notification spam. Warmup is the period AppSignal waits before sending the first alert after a trigger condition is met, ensuring the issue persists. Cooldown is the minimum time between subsequent alerts for the same trigger, preventing repeated notifications for ongoing issues.
• 5. Notification channel - The notification channel to be used. AppSignal gives you a wide choice, including email, SMS, PagerDuty, and others. See more about these in AppSignal's notification settings documentation.

After you've set up the trigger, it will appear in the active triggers list, as shown below:

Now, whenever conditions match the trigger you've set, the trigger fires, and the event that sets off the trigger is listed in the anomalies list, as shown in the screenshot below.

At the same time, you're sent a notification on the notification channel you've set up.

Moving on, let's now turn our attention to monitoring Ecto errors beyond what is captured by AppSignal's default settings.

Monitoring Custom Errors Using AppSignal

By default, AppSignal automatically instruments most of the common Ecto errors, such as non-existent database records. Now, imagine a scenario where you want to track the spam comment submissions within our blog app. How would you handle this? This scenario requires us to set up custom error tracking.

To begin with, we modify the Comment schema to check for spam comments:

defmodule BlogApp.Comments.Comment do
  use Ecto.Schema
  import Ecto.Changeset
  alias BlogApp.Posts.Post
 
  @spam_phrases ["this is a spam comment", "buy now", "make money fast"]
 
  schema "comments" do
    field :content, :string
    belongs_to :post, Post
 
    timestamps(type: :utc_datetime)
  end
 
  @doc false
  def changeset(comment, attrs) do
    comment
    |> cast(attrs, [:content])
    |> validate_required([:content])
    |> validate_no_spam()
  end
 
  defp validate_no_spam(changeset) do
    content = get_field(changeset, :content) || ""
 
    if Enum.any?(@spam_phrases, &String.contains?(String.downcase(content), &1)) do
      changeset
      |> add_error(:content, "Spam detected!")
    else
      changeset
    end
  end
end

The above code uses the validate_no_spam() function to check for spam comments. If a comment matching any of the pre-determined spam comments — "this is a spam comment", "buy now", or "make money fast" — is submitted by a user, a validation error is raised.

Next, we modify the post controller to track spam comments as shown below:

defmodule BlogAppWeb.PostController do
  ...
 
  @spam_error "Spam detected!"
 
  ...
 
  def add_comment(conn, %{"post_id" => post_id, "comment" => comment_params}) do
    post = Posts.get_post!(post_id)
 
    case Posts.create_comment(post, comment_params) do
      {:ok, _comment} ->
        conn
        |> put_flash(:info, "Comment added successfully.")
        |> redirect(to: ~p"/posts/#{post}")
 
      {:error, %Ecto.Changeset{} = changeset} ->
        # Track spam attempts
        if changeset.errors[:content] == {@spam_error, []} do
          track_spam_attempt(conn, post_id, comment_params)
        end
 
        render(conn, :show, post: post, changeset: changeset)
    end
  end
 
  defp track_spam_attempt(conn, post_id, comment_params) do
    # Get the user's IP
    ip =
      conn.remote_ip
      |> Tuple.to_list()
      |> Enum.join(".")
 
    # Prepare metadata
    metadata = %{
      content: comment_params["content"],
      post_id: post_id,
      user_ip: ip,
      path: conn.request_path,
      user_agent: get_req_header(conn, "user-agent") |> List.first(),
      timestamp: DateTime.utc_now()
    }
 
    # Send custom error to AppSignal
    Appsignal.send_error(
      :spam_attempt,
      "Spam comment detected",
      "Content: #{comment_params["content"]}",
      [
        namespace: "spam_detection",
        metadata: metadata,
        tags: ["spam", "user_content"]
      ]
    )
 
    # Also increment a counter metric
    Appsignal.increment_counter("spam_attempts", 1, metadata)
  end
end

Now with this bit of code, we have the following flow:

• User submits a spammy comment (for example, "make money fast").
• The schema validation fails via the validate_no_spam() function, and a {:content, {"Spam detected!", []}} error is added.
• The controller pattern matches the error and executes track_spam_attempt(), which then triggers a custom event that is sent to AppSignal.

And now, when a user submits a spam comment, this is tracked as an error and displayed on the AppSignal dashboard:

The actual submitted comment is also included:

To go further, you can even set up a custom dashboard to track the number of spam comment submissions — but we won't go into that for now.

Wrapping Up

In this article, we've learned how to establish meaningful baseline metrics to help you distinguish between healthy and problematic database behavior in Ecto. We then configured intelligent alerts using AppSignal that notify you before performance issues impact your users and implemented custom error tracking for spam detection.

With these monitoring strategies in place, you're now equipped to maintain a high-performing Ecto for Elixir application.

Happy coding!