EctoQueryExplorer

CI

EctoQueryExplorer is a tool used to collect and analyze data produced by Ecto query telemetry.

[!WARNING] EctoQueryExplorer is in its experimental stage. Use it at your own risk.

Movivation

We've already identified a slow query and now want to be able to:

  1. locate code that produced a slow database query,
  2. run EXPLAIN for the query in question,
  3. do 1 and 2 very fast.

We also want to be better understand a bigger picture: which code produces which queries, how often, how much time time it takes for those queries to finish.

Design

EctoQueryExplorer relies on query telemetry emitted by Ecto, specifically on stacktraces included in telemetry. Stacktraces were added to telemetry in https://github.com/elixir-ecto/ecto/pull/3798.

EctoQueryExplorer uses an ETS table to collect samples, queries, params, code locations and MFAs. Then EctoQueryExplorer saves collected data to SQLite3 to run analysis on top of.

SQLite3 schema looks like this:

SQLite3 database schema

Caveats

Installing

Installation boils down do installing ecto_query_explorer and configuring a new repository to store the data.

Perform the following steps:

  1. include the following line to deps list in mix.exs: 

     {:ecto_query_explorer, "~> 0.1"}

  2. add a new repository module: 

     defmodule MyApp.EctoQueryExplorerRepo do
   use Ecto.Repo,
     otp_app: :my_app,
     adapter: Ecto.Adapters.SQLite3
 end

  3. update config/config.exs: 

     # add EctoQueryExplorerRepo to the list of repositories
 config :my_app,
   ecto_repos: [
     MyApp.MyRepo,
     # ...existing repositories
     MyApp.EctoQueryExplorerRepo
   ]

 # add "stacktraces: true" option to all repositories you wish to collect telemetry from
 config :my_app, MyApp.MyRepo,
   stacktrace: true

 # configure EctoQueryExplorerRepo
 config :my_app, MyApp.EctoQueryExplorerRepo,
   database: "/tmp/ecto-query-explorer-#{config_env()}.sqlite3"

 # configure ecto_query_explorer
 config :ecto_query_explorer,
   otp_app: :my_app,
   repo: MyApp.EctoQueryExplorerRepo,
   ets_table_name: :ets_query_explorer_data,
   source_ecto_repos: [
     MyApp.MyRepo
   ]

  4. update application.ex, include repository and library setup: 

     # ...
 children = [
   # ...existing processes
   EctoQueryExplorer,
   MyApp.EctoQueryExplorerRepo
 ]

  5. create a database migration with the following contents: 

     # priv/ecto_query_explorer_repo/migrations/1_initial.exs
 defmodule EctoQueryExplorer.Repo.Migrations.Initial do
   use Ecto.Migration

   def up do
     EctoQueryExplorer.Migration0.up()
   end

   def down do
     EctoQueryExplorer.Migration0.down()
   end
 end

  6. run migration: 

     mix ecto.create --repo MyApp.EctoQueryExplorerRepo + migrate --repo MyApp.EctoQueryExplorerRepo

Usage

Start your application, and keep it running to collect some query data. After a while, call:

EctoQueryExplorer.Data.dump2sqlite()

If you'd like to store and analyse queries emitted by the test suite after it finishes, add the following code to test_helper.exs:

ExUnit.after_suite(fn _args ->
  EctoQueryExplorer.Data.dump2sqlite()
end)

After the SQLite3 database file is generated, you may use the library in 3 different ways.

Using convenience functions

The following convenience functions are provided by EctoQueryExplorer:

Using Ecto queries

Using sqlite3 CLI utility

Consider running the query using .mode table and .width auto for nicer outputs. Run queries like this:

sqlite3 -cmd ".mode table" -cmd ".width auto 160" /tmp/ecto-query-explorer-prod.sqlite3 'query string'

Here are some examples:

  1. get top 5 most popular queries: 

     select id, counter from queries order by counter desc limit 15

  2. get top 5 most popular queries, with snippet: 

     select id, counter, REPLACE(SUBSTR(q.text, 1, 150), char(10), \' \') snippet from queries q order by counter desc limit 10

  3. get top 5 most popular stacktraces: 

     select id from stacktraces order by counter desc limit 5

  4. get stacktrace with function names and line numbers: 

       select f.module || '.' || f.function || '/' || f.arity as mfa,
          l.file || ':' || l.line as location
     from stacktrace_entries se
     join functions f on f.id = se.function_id
     join locations l on l.id = se.location_id
    where se.stacktrace_id = 86249873
 order by se."index"'

  5. find all queries that have at least 25 parameters in them: 

     select id, text from queries where text like '%$25%' order by counter desc limit 5

License

EctoQueryExplorer is licensed under MIT license. See LICENSE for more details.