Database performance can make or break your Elixir application. While Ecto provides a powerful toolkit for database interactions, understanding how these operations perform in production is critical. Whether you're dealing with slow queries, connection pool issues, or mysterious N+1 problems, the ability to effectively monitor and optimize your database operations can be the difference between a sluggish application and one that delights your users.

In this introductory guide, you'll learn how to implement Ecto monitoring using AppSignal. We'll explore some basic monitoring strategies that will help you identify, diagnose, and resolve database performance issues before they impact your users. By the end of this article, you'll understand:

• How to set up Ecto monitoring in your Elixir application
• Which key metrics matter most for database performance
• How to detect and resolve common Ecto performance bottlenecks
• Best practices for setting up alerts and dashboards

Before we get started, let's see what you'll need to follow along with this tutorial.

Prerequisites

• An AppSignal account. You can sign up for a free trial if you don't have one.
• Elixir, Phoenix, and PostgreSQL installed
• A Phoenix application to follow along with. Fork the Phoenix blog app we'll be using for the tutorial if you don't have one ready.
• Some experience with Elixir and the Phoenix framework.

What Is Ecto for Elixir?

Ecto is a toolkit that gives you a seamless way to interact with different databases in Elixir applications. You could be wondering why I use the term "toolkit" versus the more common "object-relational mapper" (ORM). Ecto does map database tables into Elixir structs, but more than that, it also includes some distinct features that make it much more than an ORM:

• Query - Ecto includes a macro-based domain-specific language (DSL) to compose powerful queries for fetching data from a database.
• Schema - The module gives you all you need to build schemas (Elixir structs that map directly to your app's database tables).
• Changeset - Ecto's changeset is responsible for ensuring data integrity in your application.
• Repo - this is the main interface that wraps around and interacts with your app's database.

Because Ecto is composed of various modules for interacting with databases, there is a larger surface area where things can go wrong, making effective monitoring essential.

Head over to the Ecto documentation to learn more. For now, let's move on and learn how to integrate AppSignal with an Elixir app.

Integrating AppSignal with an Elixir Application

We'll be using a simple Phoenix blog application. Our first step is to add the AppSignal package like so:

# mix.exs
 
def deps do
  [
    {:appsignal_phoenix, "~> 2.0"}
  ]
end

Run mix deps.get to install the package, followed by the AppSignal package installer command mix appsignal.install <YOUR PUSH API KEY>. This should give you a few options to customize how the package will be installed:

Validating Push API key: Valid! 🎉
What is your application's name? [email_subscription_app]: Blog App

Choose the AppSignal configuration method:

There are two methods of configuring AppSignal in your application.
  Option 1: Using a "config/appsignal.exs" file. (1)
  Option 2: Using system environment variables.  (2)

Decide what will work for you, but the config file option makes it easier to customize the configuration the way you'd like.

One thing to take note of is that AppSignal will offer automatic instrumentation of Ecto queries as long as your app's OTP name matches what is configured in the AppSignal config file, as shown below:

# config/appsignal.exs
 
import Config
 
config :appsignal, :config,
  otp_app: :blog_phoenix, # the otp name here...
  name: "blog_phoenix",
  push_api_key: "YOUR PUSH API KEY",
  env: Mix.env

# config/dev.exs
 
config :blog_phoenix, BlogPhoenix.Repo,
  ...

And if everything goes as planned, your app should start sending data to AppSignal in a few minutes:

Next up, let's briefly go through the importance of monitoring Ecto.

Why Monitoring Ecto Is Crucial for Application Performance

Just like other parts of your application, your database and the Ecto interface wrapped around it require constant observation to detect issues early and to maintain good performance.

Without monitoring, performance bottlenecks can go unnoticed until they end up directly affecting your app's users. By setting up a well-structured monitoring system for Ecto, you are able to see issues before they grow into serious problems.

But in order to fix the issues that would end up affecting Ecto's performance, you must set up monitoring for the right metrics.

Monitoring Key Ecto Metrics

Focusing on key metrics will give you insights into the efficiency and responsiveness of your database queries, allowing you to identify bottlenecks and optimize database operations.

Below are a few key Ecto metrics that are good candidates for monitoring in AppSignal.

Query Execution Time

Tracking how long queries take to execute is a very important metric. Queries that are not properly optimized will affect the performance of your database.

For example, in our blog application, the query for fetching all blog posts used by the controller's index method is shown below:

# lib/blog_phoenix/blog.ex
 
defmodule BlogPhoenix.Blog do
  ...
 
  def list_posts do
    Repo.all(Post)
  end
 
  ...
end

As you can see, this query is not optimized in any way. To test it out, I seeded the app's database with two thousand posts, and then I made a request for the posts index view.

Let's see how this was instrumented and displayed on the AppSignal dashboard:

As expected, the unoptimized query led to a high-impact event on Ecto, which was captured in detail on the dashboard.

Later on, we'll look at a few ways to optimize queries, but for now, let's examine another metric that warrants monitoring: resource consumption.

Resource Consumption

Although not directly related to Ecto, it's likely your app will be hosted on a server. While it's hosted there, it's possible that whatever might affect the server's resources (such as processor speed, database read/writes, and memory) will also affect Ecto's performance.

For example, something totally unrelated to your app could cause the server to experience high disk read/writes. If your app performs a large number of database transactions at the same time, then it's possible that Ecto's performance will be affected. Being able to see the server's performance metrics provides an extra set of important insights that could help you optimize your application.

Next up is throughput.

Throughput

Throughput refers to the number of transactions completed within a given timeframe. The higher your app's throughput numbers, the better.

In the context of AppSignal monitoring, it's good to note that "throughput" does not show how many transactions or users your app can support as a whole. Rather, it is a snapshot into the number of transactions Ecto can process for a particular event:

So far, the instrumentation for the metrics we have looked at is provided automatically by the AppSignal package. However, there are other metrics that are just as important but which would need some extra work on our part.

Let's look at how to set up custom instrumentation for a particularly interesting metric: database connection pools.

Database Connection Pools

The connection pool size is an important metric to monitor because it directly affects Ecto's performance and therefore the overall performance of your app.

Each database connection consumes a bit of your server's resources (server processor time, memory, and so on). Too many connections will overload your server, directly affecting the performance of your app. But then again, having too few will mean that new requests have to wait for connections to become available, which also affects your app's performance.

As such, it becomes necessary to monitor your app's database pool size to ensure optimal performance.

By default, AppSignal will not offer automatic instrumentation for this metric, but that doesn't mean we cannot set up custom instrumentation for it. Let's do that next.

Setting up Ecto Custom Instrumentation

The first step is to set up a dedicated PoolMonitor Genserver to monitor the database pool size and collect metrics regularly:

# lib/blog_phoenix/pool_monitor.ex
 
defmodule BlogPhoenix.PoolMonitor do
  use GenServer
  require Logger
  alias Ecto.Adapters.SQL
 
  @spec start_link(any()) :: GenServer.on_start()
  def start_link(_opts) do
    GenServer.start_link(__MODULE__, %{})
  end
 
  @impl true
  def init(state) do
    schedule_metrics_collection()
    {:ok, state}
  end
 
  @impl true
  def handle_info(:collect_metrics, state) do
    _ = collect_pool_metrics()
    schedule_metrics_collection()
    {:noreply, state}
  end
 
  defp schedule_metrics_collection do
    Process.send_after(self(), :collect_metrics, :timer.seconds(15))
  end
 
  @spec collect_pool_metrics() :: :ok
  defp collect_pool_metrics do
    pool_size = BlogPhoenix.Repo.config[:pool_size]
 
    # Get current connection status using a SQL query
    used_connections =
      case SQL.query(BlogPhoenix.Repo, "SELECT count(*) FROM pg_stat_activity WHERE datname = current_database()") do
        {:ok, %{rows: [[count]]}} -> count
        _ -> 0
      end
 
    # Send metrics to AppSignal
    :ok = Appsignal.set_gauge(
      "database.pool.size_total",
      pool_size,
      %{repo: "BlogPhoenix.Repo"}
    )
 
    :ok = Appsignal.set_gauge(
      "database.pool.used_connections",
      used_connections,
      %{repo: "BlogPhoenix.Repo"}
    )
 
    # Calculate and send usage percentage
    usage_percentage = (used_connections / pool_size) * 100
    :ok = Appsignal.set_gauge(
      "database.pool.usage_percentage",
      usage_percentage,
      %{repo: "BlogPhoenix.Repo"}
    )
 
    Logger.debug("Pool metrics collected: size=#{pool_size}, used=#{used_connections}, usage=#{usage_percentage}%")
    :ok
  end
end

Without explaining everything the code is doing, let's highlight some parts of it:

• We make use of Ecto.Adapters.SQL.query/4 to safely execute raw SQL queries using Ecto. This way, we can fetch the database connection pool without interacting with the PostgreSQL database directly, which is the recommended way to interact with the database.
• The SQL query uses the PostgreSQL-specific pg_stat_activity to return the number of connection pools. You can read more about the PostgreSQL cumulative statistics system from the documentation.

Next, we ensure this new GenServer is included in the supervision tree:

# lib/blog_phoenix/application.ex
 
defmodule BlogPhoenix.Application do
  use Application
 
  @impl true
  def start(_type, _args) do
    children = [
      BlogPhoenixWeb.Telemetry,
      BlogPhoenix.Repo,
      BlogPhoenix.PoolMonitor # new Genserver
      ...
 
      opts = [strategy: :one_for_one, name: BlogPhoenix.Supervisor]
      Supervisor.start_link(children, opts)
  end
  ...
end

With that, we are now ready to visualize the metrics on the AppSignal dashboard.

Setting Up Custom Dashboards

As you might have guessed, since we've built a custom instrumentation, we'll need to set up custom dashboards to view the metrics.

Start by clicking on the "Add dashboard" button on the left-side menu, then the "Create a dashboard" button on the resulting screen:

Then give your dashboard an appropriate name and description:

Once your new custom dashboard is created, the next step is to add custom graphs.

Adding Custom Graphs

Click on the "add new graph" link:

Provide the relevant information for your graph, including a title, description, etc. You can read more in AppSignal's custom metrics documentation.

Let's take a closer look at the metric labeled number 7 on the screenshot. Remember the custom gauges we set up in the PoolMonitor GenServer code above?

...
 
  :ok = Appsignal.set_gauge(
      "database.pool.size_total",
      pool_size,
      %{repo: "BlogPhoenix.Repo"}
    )
...

We need to pull and visualize these using the custom graph. In my case, I chose database.pool.size_total, which results in the dashboard you can see below:

And that's it!

Wrapping Up

In this article, we learned how to monitor the Elixir database toolkit, Ecto. We've seen the importance of monitoring Ecto and how it affects overall app performance. Additionally, we went through how AppSignal offers automatic monitoring for various metrics. We even learned how to set up custom instrumentation for metrics that aren't automatically covered.

This was more of an introductory tutorial. In part two of this series, we'll dive deeper into how to establish baseline metrics, set up alerts, notifications, and robust exception monitoring for Ecto.

Until then, happy coding!