Evaluating Pooling behaviour.

[robotarmy]

Hi I'm trying to evaluate the behavior of pooling with respect to very slow wait_timeout values for mysql.

I've created a long running test that i'm monitoring with

 watch -n0 '/usr/local/opt/mysql@5.7/bin/mysql -uroot -e "show full processlist"'

I'm surprised to see that my processlist only shows 1 query at a time. It's possible that i'm misunderstanding the nature of Task.async

output consistently shows single connection:

Id      User    Host    db      Command Time    State   Info
8800    root    localhost:53974 myxql_test      Execute 3       User sleep      SELECT 5, sleep(3)
8878    root    localhost       NULL    Query   0       starting        show full processlist

defmodule MyXQL.PoolBehaviourTest do
  use ExUnit.Case, async: true
  alias MyXQL.{Client, Protocol}
  import MyXQL.Protocol.{Flags, Records}

  def pool_opts() do
    TestHelper.opts ++ [
      timeout: 2000, # greater than server session
      pool_size: 10,
      idle_interval: 1,
      queue_interval: 10000
    ]
  end

  def configure_server do
    TestHelper.mysql!("""
    set session wait_timeout=1;
    """)
  end

  test "pooling with short db session and long client timeout" do
    configure_server
    {:ok, pid} = MyXQL.start_link(pool_opts())
    IO.puts("am i pooling?")

    run = fn x ->
      assert {:ok, _} = MyXQL.query(pid, "SELECT #{x}, sleep(1)")
    end

    run_wait = fn x ->
      assert {:ok, _} = MyXQL.query(pid, "SELECT #{x}, sleep(3)")
    end

    #
    # assuptions
    # running 5 queries async that sleep will eat up the connection pool
    #
    tasks1 = 0..5
    |> Enum.map(fn x ->
      Task.async(fn ->
        run.(x)
      end)
    end)

    Process.sleep(2000) # assumption : time out the idle interval

    tasks2 =   5..10
    |> Enum.map(fn x ->
      Task.async(fn -> run_wait.(x) end)
    end)

    tasks1 |> Enum.map(&Task.await(&1, 5000))
    tasks2 |> Enum.map(&Task.await(&1,10000))

  end
end

[josevalim]

TestHelper.opts sets the pool_size to 1 and the options that come first have higher priority. Use Keyword.merge instead of ++. :)

[robotarmy]

@josevalim <3