Too many connections with Dream and mysql

I have the following errors multiple times :

22.01.23 17:25:58.628       dream.log  WARN REQ 156 Aborted by: Failed to connect to <mariadb://user:_@localhost/database>: Error 1040, Too many connections.
22.01.23 17:25:58.628       dream.log  WARN Raised at Dream__server__Session.middleware.(fun) in file "src/server/", line 25, characters 2-86
22.01.23 17:25:58.628      dream.http ERROR REQ 156 Failed to connect to <mariadb://user:_@localhost/database>: Error 1040, Too many connections.
22.01.23 17:25:58.628      dream.http ERROR REQ 156 Raised at Stdlib__map.Make.find in file "", line 136, characters 10-25
22.01.23 17:25:58.628      dream.http ERROR REQ 156 Called from Logs.Tag.find in file "src/", line 154, characters 14-32

I am using the Dream framework and MariaDB. The SQL pool is initialized by Dream.sql_pool ~size:20 db_url just to avoid too much simultaneous connections.

1 Like

Can you give a minimal code sample that causes the issue? Is your database server being used by any other application?

It is an image server (and the thumbnail page is not divided into small pages, then there are many simultaneous connections, but the pool should prevent an heavy use at the database).

let query_thumb = [%rapper
             get_opt {sql| SELECT @octets{thumb_image}
                           FROM images WHERE oid = %int{oid} |sql}]

let do_thumb request =
  match Dream.query request "id" with
  | Some x ->
     let oid = int_of_string x in
     Dream.sql request (fun db ->
         match%lwt query_thumb ~oid db with
         | Ok Some x -> Dream.respond
         | _ -> Dream.html "error")
  | _ -> Dream.html "error"

No heavy use simultaneously by an other application.

Is the MySQL server max_connections value set too low?

Or alternatively, try limiting the Dream SQL pool’s connection count to the max_connections?

The maximum number is the default 151 which is quite high.

The pool is initialised by

Dream.sql_pool ~size:20 db_url

Then, I shouldn’t have such an error.

My guess is the pool management is broken somewhere.

I will try a little Caqti only (no Dream) concurrent Caqti program and raise an issue.

If this is a small program and easy to make the change I would also be curious as to whether the same issue occurs with PostgreSQL. With Postgres also there is the option of using pgbouncer, a dedicated connection pool server, so the OCaml code doesn’t need to manage the pool.

I have tried a program which doesn’t use Dream, start 10000 parallel threads (Lwt) which use the same pool… no issue !

I guess I should seek where my program differ from the Dream way of using Caqti… but I made my program inspired by the Dream source !

Issue spotted with a fix…


How do you actually initialize the pool? (I.e. where is the pool created in your stack of Dream handler)
I’m curious as to why I’ve never seen that issue.

Btw, don’t expect a fix for your issue, antron is not contributing to dream at the moment.

I had initialized the pool with Dream.sql_pool ~size=20 url.

The issue deals with the statement:

let sql_pool ?size url =
   let pool_cell = ref None in

Then each time sql_pool is called (once per request), a new pool_cell reference is create and afterwards initialized with a new pool.

Inverting these to line and make pool_cell a global variable solves this issue.

If you look at the definition of sql_pool, you’ll see that it is a function that returns a function:

let sql_pool ?size uri =
    let pool_cell = ref None in
  (* returns an anonymous function *) 
  fun inner_handler request ->

  begin match !pool_cell with
  | Some pool ->
     (* use existing pool *)

  | None -> 
    (* .. *)
    (* create a new pool *)
    pool_cell := Some pool
    (* .. *)

Each time sql_pool is called it does allocate a new SQL pool and also returns a anonymous function, but each time this returned function is called, no new pool is allocated.

If you configure your entrypoint as suggested in the readme:

let () =
  @@ Dream.sql_pool "sqlite3:db.sqlite" (* a single pool_cell ref is allocated here *)
  @@ fun request ->
    Dream.sql request (fun db ->
      (* ... *) |> Dream.html)

Then Dream.sql_pool is only called once, and each request, the anonymous function it creates is called, which doesn’t allocate multiple sql pools.

Your inversion should only change the behaviour of Dream.sql pool if you were calling Dream.sql_pool inside a request handler, which I believe is not the intended usage.


I have tried both (the original version and my « fixed » version). The first can’t handle more than 150 simultaneous request the second does. I do insert sql_pool in the middleware chain as specified. But it uses a key from the session and then depends of the request. Perhaps it is the key point. I will try a simpler approach.

That what I was getting at, sql_pool is meant to be called only once in the setup and should be independent of the requests.
I use sql_pool in the way described above and I’ve never observed a leak, so I was wondering if I didn’t look hard enough or you have a different usage pattern.

I guess that having an sql_middleware based on sql_pool and a request break things. With a simplier use, it works.