Feature request: add option --oltp-conns-per-thread

[renecannao]

As far as I know, in sysbench there is a 1:1 mapping between a connection and a thread. This normally works well for stress testing, but not in all cases. For example, I would like to simulate a workload where only 1% of the connections are active, while 99% of the connections are idle: this is a pretty common pattern in many workload. A workaround can be achieved using --oltp-user-delay-min and --oltp-user-delay-max , but isn't really easy to define that only a percentage of connections should be active at any time. I have a fork where I added this functionality on branch 0.4 : https://github.com/renecannao/sysbench/commit/8acea2736c73adb44bca2801760cfefb5f8dca4b . It is an half baked functionality that works in 0.4 only and not even completed (for example doesn't support reconnect), but it is ok for my needs. It adds a --oltp-conns-per-thread , and each thread will open so many connections, but only 1 connection at the time will be active. Would be great to have something similar in the main repo.

Thanks, René

[akopytov]

Thanks for the feature request. I have some thoughts on implementing it in a more general way (something like --max-event-concurrency that would work for all benchmark types, not just for SQL ones).

However, something similar can be implemented in Lua by adding the following to the beginning on function event() in oltp.lua:

   if oltp_max_active_threads and thread_id >= tonumber(oltp_max_active_threads) then
      os.execute("sleep " .. max_time)
      return
   end

It's ugly, and it's not quite the same as --oltp-conns-per-thread from your branch, but may be good enough.

[renecannao]

Thank you for the feedback. os.execute("sleep ...") is indeed a ugly hack :) Because:

• it may needs to fork tents of thousands of time per second
• with a timer/sleep I can't directly control the parallelism, but only try to guess what could be a good time to sleep.

Big 👍 on --max-event-concurrency, I like it! But still won't solve the issue with the 1:1 mapping between threads and connections. If I want to have 1M connections to MySQL (or ProxySQL) but only 1k actives at any time, I need sysbench to start only 1k threads and not 1M threads. So the approach I took in my branch solves my problem, but unfortunately is not generic.

Related to this (maybe I should open a different issue): sysbench seems to serialize the creation of threads and connections. That means that if it needs to create several thousands of connections, they are created one by one with no parallelism, and this takes really a lot of time.

Thanks you!

[akopytov]

Yes, the os.execute("sleep ...") is more a Lua ugliness than a sysbench one. Should be saner with FFI calls once the LuaJIT is merged.

Regarding multiple connections per thread. That is not possible with the current Lua API, but should be possible with the new API I'm planning to implement as a part of the LuaJIT refactoring.

Regarding connection creation speed. In sysbench 0.4 both creation (i.e. pthread_create()) and initialization (e.g. connecting to the server) are indeed serialized. In 0.5/1.0 initialization is parallelized, which eliminates the problem, at least for me (I usually run benchmarks with up to 2k connections).

[renecannao]

Great! So I guess I should wait LuaJIT to see this feature request to come into main branch. Thank you Alexey!

[akopytov]

So multiple connections per thread are now possible in the master branch (to be released as sysbench 1.0 soon). It is now possible to create and use multiple connections like this:

function thread_init()
  drv = sysbench.sql.driver()
  con = {}
  for i=1,10 do
    con[i] = drv:connect()
  end
end

function event()
  con[1]:query("DROP TABLE IF EXISTS t")
  con[2]:query("CREATE TABLE t(a INT)")
  for i=1,10 do
    con[i]:query("INSERT INTO t VALUES (" .. i .. ")")
  end
end

Adding an option to the standard OLTP scripts is now possible and patches are welcome :)

I'll probably put the --max-event-concurrency idea on hold, since it does not solve your primary problem anyway.

[amq]

Looks like CockroachDB would benefit heavily from this. See https://www.cockroachlabs.com/docs/stable/parallel-statement-execution.html

[akopytov]

Looks like CockroachDB would benefit heavily from this. See https://www.cockroachlabs.com/docs/stable/parallel-statement-execution.html

That was an interesting read, thanks for the link. However, I don't think it has anything to do with this feature request. This one is about ability to create multiple client connections per thread.

Parallel statement execution in CockroachDB boils down to appending RETURNING NOTHING to statements issued by sysbench so that they are potentially executed in parallel by CockroachDB. All you have to do to implement that is to modify queries in oltp_common.lua and append RETURNING NOTHING where necessary.