Minimal example of HTTP server doing asynchronous database queries?

Question

I'm playing with different asynchronous HTTP servers to see how they can handle multiple simultaneous connections. To force a time-consuming I/O operation I use the pg_sleep PostgreSQL function to emulate a time-consuming database query. Here is for instance what I did with Node.js:

var http = require('http');
var pg = require('pg');
var conString = "postgres://al:al@localhost/al";
/* SQL query that takes a long time to complete */
var slowQuery = 'SELECT 42 as number, pg_sleep(0.300);';

var server = http.createServer(function(req, res) {
  pg.connect(conString, function(err, client, done) {
    client.query(slowQuery, [], function(err, result) {
      done();
      res.writeHead(200, {'content-type': 'text/plain'});
      res.end("Result: " + result.rows[0].number);
    });
  });
})

console.log("Serve http://127.0.0.1:3001/")
server.listen(3001)

So this a very simple request handler that does an SQL query taking 300ms and returns a response. When I try benchmarking it I get the following results:

$ ab -n 20 -c 10 http://127.0.0.1:3001/
Time taken for tests:   0.678 seconds
Complete requests:      20
Requests per second:    29.49 [#/sec] (mean)
Time per request:       339.116 [ms] (mean)

This shows clearly that requests are executed in parallel. Each request takes 300ms to complete and because we have 2 batches of 10 requests executed in parallel, it takes 600ms overall.

Now I'm trying to do the same with Elixir, since I heard it does asynchronous I/O transparently. Here is my naive approach:

defmodule Toto do
  import Plug.Conn

  def init(options) do
    {:ok, pid} = Postgrex.Connection.start_link(
      username: "al", password: "al", database: "al")
    options ++ [pid: pid]
  end

  def call(conn, opts) do
    sql = "SELECT 42, pg_sleep(0.300);"
    result = Postgrex.Connection.query!(opts[:pid], sql, [])
    [{value, _}] = result.rows
    conn
    |> put_resp_content_type("text/plain")
    |> send_resp(200, "Result: #{value}")
  end
end

In case that might relevant, here is my supervisor:

defmodule Toto.Supervisor do
  use Application

  def start(type, args) do
    import Supervisor.Spec, warn: false

    children = [
      worker(Plug.Adapters.Cowboy, [Toto, []], function: :http),
    ]
    opts = [strategy: :one_for_one, name: Toto.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

As you might expect, this doesn't give me the expected result:

$ ab -n 20 -c 10 http://127.0.0.1:4000/
Time taken for tests:   6.056 seconds
Requests per second:    3.30 [#/sec] (mean)
Time per request:       3028.038 [ms] (mean)

It looks like there's no parallelism, requests are handled one after the other. What am I doing wrong?

Answer 1

Elixir should be completely fine with this setup. The difference is that your node.js code is creating a connection to the database for every request. However, in your Elixir code, init is called once (and not per request!) so you end-up with a single process that sends queries to Postgres for all requests, which then becomes your bottleneck.

The easiest solution would be to move the connection to Postgres out of init and into call. However, I would advise you to use Ecto which will set up a connection pool to the database too. You can also play with the pool configuration for optimal results.