EctoQueryExplorer

EctoQueryExplorer is a tool used to gather and analyse Ecto query telemetry.

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.

looks like this:

Caveats

• EctoQueryExplorer will not attempt to persist data across application restarts,
• usage in clustered environments have not been tested,
• the Readme file you're looking at is the only documentation right now.

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. include the the following contents to config/config.exs:

   # add EctoQueryExplorerRepo to the list of repositories
   config :my_app,
     ecto_repos: [MyApp.MyRepo, MyApp.EctoQueryExplorerRepo]
   
   # enable stacktraces in MyApp.MyRepo config
   config :my_app, MyApp.MyRepo,
     stacktrace: true
   
   # configure EctoQueryExplorerRepo
   config :my_app, MyApp.EctoQueryExplorerRepo,
     database: "/tmp/ecto-query-explorer-#{config_env()}.sqlite3"
   
   # tell ecto_query_explorer about EctoQueryExplorerRepo
   config :ecto_query_explorer,
     repo: MyApp.EctoQueryExplorerRepo,
     otp_app: :my_app
   

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

   # ...
   children = [
     MyApp.EctoQueryExplorerRepo,
     {EctoQueryExplorer, Application.get_all_env(:ecto_query_explorer)},
   ]
   

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:

• EctoQueryExplorer.Queries.filter_by_query/1 - accepts a string containing a regular expression and returns a query with samples, including location data.

  See "The LIKE, GLOB, REGEXP, MATCH, and extract operators" for more details about the regular expressions.

  Example:

  EctoQueryExplorer.Queries.filter_by_query("update%")
  

• EctoQueryExplorer.Queries.filter_by_parameter/1 - use this one if you're looking for a query that was executing with a specific parameter value,

  Example:

  user_id = "da06de53-34bb-4b6a-9f36-0e8478b05458"
  EctoQueryExplorer.Queries.filter_by_parameter(user_id)
  

  Notes:

  1. for a given query, parameters are saved only for 3 slowest samples,
  2. when calling this function, all stored parameters will fetched from the SQLite3 table and decoded using :erlang.binary_to_term/1 function.

• EctoQueryExplorer.Queries.filter_by_mfa/3 - return all queries, which were produced by stacktraces that contain given module, function and arity.

  Example:

  EctoQueryExplorer.Queries.filter_by_mfa(User, :register, 1)
  

• EctoQueryExplorer.Queries.filter_by_location/2 - return all queries, which were produced by stacktraces that contain given file and line number.

  Example:

  EctoQueryExplorer.Queries.filter_by_location("lib/my_app/path/to/code/module.ex", 12)
  

• EctoQueryExplorer.Queries.explain/1 - accepts query ID, and returns a map containing query plan. Can be used to produce JSON for tools like https://explain.dalibo.com.

  Example:

  report = EctoQueryExplorer.Queries.explain(123_234_345)
  Jason.encode!(report) # JSON to be used for further analysis or plan visualisation
  

  Notes:

  • the query will be wrapped in a transaction, which is reverted after producing the explain report.
  • query the database using familiar Ecto queries. Examples:
  • query the file directly using sqlite3 command line utility. Examples:

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 counter, text from queries order by counter desc limit 5
   

2. get top 5 most popular stacktraces:

   select id from stacktraces order by counter desc limit 5
   

3. 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
   

4. get size of the database:

   sqlite3-stats.sql

   with
   records as (
     select 'queries' name, count(1) total_records from queries
     union
     select 'samples' name, count(1) total_records from samples
     union
     select 'functions' name, count(1) total_records from functions
     union
     select 'locations' name, count(1) total_records from locations
     union
     select 'stacktraces' name, count(1) total_records from stacktraces
     union
     select 'stacktrace_entries' name, count(1) total_records from stacktrace_entries
     union
     select 'params' name, count(1) total_records from params
   ),
   sizes as (
       select SUM(pgsize) bytes,
              name
         from dbstat
        where name in ('queries', 'samples', 'functions', 'locations', 'stacktrace_entries', 'params', 'stacktraces')
     group by name
   )
     select r.name,
            total_records,
            bytes
       from records r
       join sizes s on s.name = r.name
   order by bytes desc
   

License

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