twitter.svg

MichalDolata

large_MocarskaFoto-3 copy.jpg

medium_MocarskaFoto-3 copy.jpg

small_MocarskaFoto-3 copy.jpg

thumbnail_MocarskaFoto-3 copy.jpg

MocarskaFoto-3 copy.jpg

## Witnessing errors connected with database performance is never a pleasant experience. Could you have prepared yourself to handle them? Let’s find out.

### Error types

Let’s divide into possible errors into categories. There are 2 most common error types in the Elixir ecosystem that indicate performance issues connected with a database. Both are raised in the form of `DBConnection.ConnectionError` exceptions but they have different messages:

- the first type informs you about not having enough connections available to communicate with your database instance. Let’s call them **pool timeout errors.** They look like this:
    
    ```
    (DBConnection.ConnectionError) connection not available and request was dropped from queue after 206ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
    
      1. Ensuring your database is available and that you can connect to it
      2. Tracking down slow queries and making sure they are running fast enough
      3. Increasing the pool_size (although this increases resource consumption)
      4. Allowing requests to wait longer by increasing :queue_target and :queue_interval
    
    See DBConnection.start_link/2 for more information
    ```
    
    You can see that the message proposes suggestions on how to solve the problem 🎉  I will try to dig deeper into those points later on.
    
- the second type usually means that there was a transaction/query that lasted too long and exceeded the timeout. I will refer to them as **query timeout.**
    
    ```
    (DBConnection.ConnectionError) tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)
    ```
    
    ```
    (DBConnection.ConnectionError) client #PID<0.368.0> timed out because it checked out the connection for longer than 15000ms
    ```
    

### Buying more time

There is a chance that you’ve noticed these errors when your systems performance was degraded. In the worst-case scenario, there are reports submitted by angry users so there can be a lot of pressure on you because the service isn’t working as desired. You want to act fast.

I’ve noticed during my work as a Site Reliability Engineer that people during incidents, want to react too fast. This can be deceptive. It is easy, during stressful situations to forget that actions cause reactions, and operating in the blink of an eye isn’t always the best solution. Because of this, it’s a good idea to have a list of actions that are safe to apply during incidents that won’t have a long-term impact if they fail and will buy you more time to resolve issues. 

I’ve prepared for you a list of actions that should be safe to apply if you noticed `DBConnection.ConnectionError` errors.

#### Start with tuning your settings

It should be quite easy with *pool timeout errors*. If you didn’t tune it before, there is a high chance that simply tweaking [`pool_size`](https://hexdocs.pm/ecto/Ecto.Repo.html#:~:text=%3Apool_size%20%2D%20the%20size%20of%20the%20pool%20used%20by%20the%20connection%20module.%20Defaults%20to%2010) will buy you some time and allow you to debug further in with better morale. But remember `pool_size` isn’t any magic and it has its limits. As described in the error message “*although this increases resource consumption” -* it will increase base memory usage in your app host and database.

It also won’t scale indefinitely, as you can see [here](https://www.percona.com/blog/2017/01/06/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloads/) there is a point where increasing the number of connections won’t increase the number of operations that your database can perform - it can even significantly drop. The breakpoint can differ on hardware or even on the [DB version](https://pganalyze.com/blog/postgres-14-performance-monitoring) so you need to experiment to find the optimal value for your `pool_size`.
![Influence of the number of concurrent connections on Transactions Per Seconds for two PostgreSQL versions (source: [https://pganalyze.com/blog/postgres-14-performance-monitoring](https://pganalyze.com/blog/postgres-14-performance-monitoring))](https://strapi-appunite.s3.eu-central-1.amazonaws.com/connection_scaling_d510ca4774.png)

_Influence of the number of concurrent connections on Transactions Per Seconds for two PostgreSQL versions (source: [https://pganalyze.com/blog/postgres-14-performance-monitoring](https://pganalyze.com/blog/postgres-14-performance-monitoring))
_

If increasing `pool_size` isn’t working for you, you can still tweak [`queue_target`](https://hexdocs.pm/db_connection/DBConnection.html#start_link/2-queue-config) or [`queue_interval`](https://hexdocs.pm/db_connection/DBConnection.html#start_link/2-queue-config). It can help you avoid `DBConnection.ConnectionError` but there won’t be a difference in response times. It will allow you to avoid 500 responses, which is usually enough for a short period of time.

There is a bigger problem with *query timeout*. I think issues with *query timeout* are more specific and that is why they are harder to pinpoint and mitigate. 

Probably the easiest and fastest way to mitigate this problem is to increase [`timeout`](https://hexdocs.pm/ecto/Ecto.Repo.html#:~:text=%3Atimeout%20%2D%20The%20time%20in%20milliseconds%20(as%20an%20integer)%20to%20wait%20for%20the%20query%20call%20to%20finish.%20%3Ainfinity%20will%20wait%20indefinitely%20(default%3A%2015_000)) but it won’t be good for long.

#### Other options

- Check if other clients aren’t overusing your database, if it is shared among more applications or services.
- If it is possible you can try to scale your database vertically to increase performance.

If nothing helps maybe you can turn off some of your features that aren’t core to your business? For example: generating reports, fetching and displaying data that isn’t necessary for core business processes. It can be hard to decide during an incident what can be turned off, so it is nice to prepare a list of things that your service can live without for some time.

It is even better if you use feature flags, you can use flag as a safety switch and prepare for turning off selected features. In other case you can easily integrate and control feature flags in runtime thanks to the great Elixir library [Fun with Flags](https://github.com/tompave/fun_with_flags/) or some SaaS, for example, [LaunchDarkly](https://launchdarkly.com/).

### What’s next?

So let’s assume that you mitigated problems and have the time to debug. It is crucial to find a **root cause.** There is a reason why in *pool timeout errors*, *Tracking down slow queries and making sure they are running fast enough* is the first step to do. It is also a good moment to start working on [postmortem](https://sre.google/sre-book/postmortem-culture/). It will help you to avoid similar issues in the future and also share knowledge with other teammates.

Finding the root cause can be challenging but in the long term it will pay off. If you don’t have experience in looking for the core of the issues you can try the [Five whys technique](https://en.wikipedia.org/wiki/Five_whys). With time and knowledge of your system, you will learn when you can stop asking why :D 

To help you with starting, I’ve enlisted issues that turned out to be root causes of `DBConnection.ConnectionError` errors in the project I’ve been working on.

#### Long-lasting queries

You can start by searching for long-lasting queries. You can use for example [query insights](https://cloud.google.com/sql/docs/postgres/using-query-insights) if you host your Postgres database on Google Cloud Platform. There are a lot of other ways you can you find long-lasting queries but it depends heavily on your environment.

If you found a slow query it is time to fix it. There can be a lot of reasons why the query is slow - maybe it isn’t using the index you desire. If you use PostgreSQL you can try [EXPLAIN ALYZE](https://www.postgresql.org/docs/current/sql-explain.html) for learning the execution plan of the query. Maybe it is joining too many unnecessary data or maybe fetching too many columns. It can be  painful if the query is executed many times per second, encoding, decoding, transfer by the network, all this matters in high rate traffic, so it’s also a place to look at.

#### Polluted transactions

I have also seen cases in which long-lasting operation was performed in transactions. You should rather execute all heavy computation or HTTP requests outside transactions, so you can check in the connection to the pool as soon as possible.

#### Wrong model

The wrong model can also be a problem as to why your database can’t handle your desired effect. If you have a lot more reads than write, maybe you should prepare a read model, [denormalize](https://en.wikipedia.org/wiki/Denormalization) data. You can also think about making stronger boundaries between your contexts so they don’t have to live in the same database. I recommend trying domain-driven design, which can help you find those boundaries.

#### Inappropriate configuration

Or maybe your issues were caused by the wrong configuration. If you didn’t tweak the configuration at all, there is a chance that traffic on your website needs more DB connections, then simply `pool_size` increasing should be fine (if you still have resources on the application host). If not maybe you should spread the load between more instances or vertically scale your host, which is good but still will limit you at some point.

#### Heavy migrations

If the problems occurs during applying migrations I highly recommend reading [Safe Ecto Migrations](https://fly.io/phoenix-files/safe-ecto-migrations/) by David Bernheisel. It is the complete guide how to use Ecto migrations safety so there is no point for adding more here. 

### What can be done better?

If you learned about issues from users, your monitoring probably needs some work. From my experience, most of the problems can be detected earlier and you won’t have to deal with `DBConnection.ConnectionError` in a hurry.

It is all about observability - one of the non-functional requirements that can be easily forgotten and which is crucial if you want to continuously deliver a stable and performant platform for your users.

If you collect metrics describing query times or even just your response times you can detect in advance that something can cause issues in the near future. Just define limits and set alarms if something will go off those limits. It is quite important to set up **automatic** alarms, without them there is a high chance that you will miss a moment to act before the fire and all your collected metrics won’t pay off.

_I really like the focus on the BEAM ecosystem on standardizing metrics. [telemetry](https://hex.pm/packages/telemetry) is a great foundation that allows libraries to export metrics and is easy to consume, which brings joy. So it should be quite easy to observe your Elixir applications._

_Also, there is a lot of work on [OpenTelemetry](https://opentelemetry.io/docs/instrumentation/erlang/) for BEAM, which can help you easily boost your observability to the next level, especially if you are working in a microservices environment._

_And also big kudos to [fly.io](http://fly.io) for supporting metrics at no cost (at the moment of writing) which can be easly used with Grafana Cloud Free. Thanks to that metrics won't cost you a penny._

### Summary

We can sum up dealing with `DBConnection.ConnectionError` into this 3 step process:

1. Buying more time (mitigating issues)
    - Tune your settings
    - Scale database instance
    - Decrease not critical load
2. Finding the root cause
    - Long-lasting queries
    - Polluted transactions
    - Wrong model
    - Inappropriate configuration
    - Heavy migrations
3. Learn from the incident
    - Postmortem

This basic 3 step pattern: *mitigate, find the root cause, learn from the incident* will also help you deal with other issues in production environment, not only `DBConnection.ConnectionError`. Feature flags and postmortems, I've introduced earlier are generic tools so introducing them will help you in many cases. So if you don’t use them already I recommend checking them out.

I hope that after reading this article dealing with `DBConnection.ConnectionError` and other issues in production will be a bit easier for you and what’s more important you will be prepared for them or just predict them and avoid.

large_mat-napo-uTPAh_xpFIE-unsplash.jpg

medium_mat-napo-uTPAh_xpFIE-unsplash.jpg

small_mat-napo-uTPAh_xpFIE-unsplash.jpg

thumbnail_mat-napo-uTPAh_xpFIE-unsplash.jpg

mat-napo-uTPAh_xpFIE-unsplash.jpg

Witnessing errors connected with database performance is never a pleasant experience. Could you have prepared yourself to handle them? Let’s find out.

How to deal with DBConnection.ConnectionError? by Michał Dolata

How to deal with DBConnection.ConnectionError?

[Elixir 1.9](http://blog.plataformatec.com.br/2019/04/whats-new-in-elixir-apr-19/#:~:text=mix%20release%20has,it%20a%20try!) introduced `mix release`, which has standardised the way releases in the Elixir ecosystem are prepared. It also provided Elixir core with an easy way to configure applications in runtime. Before, Elixir 1.9 community solved this problem in different ways. For example, by using [`{:system, "VAR"}`](https://hexdocs.pm/distillery/1.2.0/runtime-configuration.html#:~:text=for%20more%20information.-,Configuration%20Conventions,to%20use%20it%20as%20Hex%20package%2C%20take%20look%20at%20confex.,-Configuration%20Tools) tuple, `REPLACE_OS_VARS` provided in early versions of [distillery](https://hex.pm/packages/distillery) or using other external libraries. So finding out how the new project that you’ve just entered handled runtime configuration wasn’t that straightforward.

Luckily Elixir 1.9 introduced `config/releases.exs` and what’s even more important is that there’s [documented guidelines](https://hexdocs.pm/mix/Mix.Tasks.Release.html#module-runtime-configuration) which help provide runtime configuration for Elixir apps. Later on, Elixir 1.11 [introduced](https://elixir-lang.org/blog/2020/10/06/elixir-v1-11-0-released/) `config/runtime.exs` and made it the recommended place to put your runtime configuration. 

*Small personal notice: I love how the Elixir Core team listens to the community and targets the right problems at the right time.*

So now we have the convention recommended by Elixir guidelines on handling runtime configuration. So all the problems regarding runtime configuration should go away? Not exactly. 

## The problem

Recently I’ve noticed recurring bug in our code base. 

```elixir
defmodule MyApp.ExternalServiceClient do 
	@external_service_url Application.get_env(:my_app, :external_service_url)
	
	def get_info do
		Req.get!(@external_service_url)
	end
end 
```

Did you notice the problem here?

Module attributes are evaluated at compile time, so this code could not work as expected with the configuration provided at runtime. For example:

```elixir
config/config.exs
...
config :my_app, external_service_url: "http://dev-env.com/service"
...

config/runtime.exs
...
config :my_app, external_service_url: System.get_env("EXTERNAL_SERVICE_URL")
...
```

With the given configuration `MyApp.ExternalServiceClient.get_info/0` won’t crash in the release build but it won’t request URL provided in `EXTERNAL_SERVICE_URL` environment variable but request `http://dev-env.com/service`. In the worst-case scenario, if you don’t have a firewall between your environments, it can lead to nasty bugs, where your prod environment will target dev. You will notice this, only if QA or end-users will report that to you. It is definitely too late in an agile environment. 

## How to fix that?

Fixing this mistake is quite simple, you can create function which will call `Application.get_env/3` at runtime:

```elixir
defmodule MyApp.ExternalServiceClient do 
	defp external_service_url, do: Application.get_env(:my_app, :external_service_url)
	
	def get_info do
		Req.get!(external_service_url()
	end
end 
```

A bit harder part of getting rid of this kind of bug is preventing them.

## How to avoid this kind of error?

To be honest, during my Elixir journey, I’ve made the same mistake a few times. Maybe it is just me and my bad memory, but imagine that you have a dozen Elixir engineers. Even if you share the postmortem with them, there is a high probability that the mistake will be made again. If you have worked in larger teams or organisations, you’ve probably noticed that sharing knowledge can be challenging.

So what can be done about that?

First, you can use `Application.compile_env/3` [introduced](https://elixir-lang.org/blog/2020/01/27/elixir-v1-10-0-released/#tracking-of-compile-time-configuration) in Elixir 1.10, which helps you detect a bug faster. It will check if the value of configuration in runtime is the same as during compilation, and if there is a difference, it will raise an error during BEAM startup.

Raising errors during BEAM startup is quite late in the feedback loop, but it will help you avoid nasty errors, as I’ve described earlier.

Could we improve our solution? Sure, we can also make sure that our teammates won’t forget about using `Application.compile_env/3` in module attributes.

## Credo for the rescue

"[Credo](https://github.com/rrrene/credo) is a static code analysis tool for the Elixir language with a focus on teaching and code consistency."

If you haven’t been using it, you should definitely try it out and select checks that will help you improve your code's quality. You can also use it as a tool to enforce code style if formatted isn’t enough for you. As a lazy engineer, I highly recommend plugging it in your CI/CD so you won’t have to remember about running it 😀

How will it help you avoid the bug described earlier? Make sure your Credo config doesn’t disable [`Credo.Check.Warning.ApplicationConfigInModuleAttribute`](https://github.com/rrrene/credo/blob/2a0f06b19a0086d02acb43e9da11c5a0b738611f/lib/credo/check/warning/application_config_in_module_attribute.ex) check (it is turned by default from Credo 1.5.0). It will ensure that only `Application.compile_env/3` or `Application.compile_env!/2` is used in the module attribute to get the configuration.

That’s it! If you would like to talk with me, feel free to hit me up [@MichalDolata](https://twitter.com/michaldolata)

large_erik-karits-_-6xtXkvv0c-unsplash.jpg

medium_erik-karits-_-6xtXkvv0c-unsplash.jpg

small_erik-karits-_-6xtXkvv0c-unsplash.jpg

thumbnail_erik-karits-_-6xtXkvv0c-unsplash.jpg

erik-karits-_-6xtXkvv0c-unsplash.jpg

Elixir 1.9 introduced mix release, which has standardised runtime configuration. So all the problems with runtime configuration should go away? Not exactly.

Elixir runtime configuration trap by Michał Dolata

Elixir runtime configuration trap

Elixir and others by Michał Dolata