-
Performance
April 6, 2009
New Feature for Sysbench - Generate Transactions at a Steady Rate
Perhaps I am becoming a regular patcher of sysbench...
I have developed a new feature for sysbench - the ability to generate
transactions at a steady rate determined by the user.
This mode is enabled using the following two new options:
- --tx-rate
- Rate at which sysbench should attempt to send transactions to the
database, in transactions per second. This is independent of
num_threads. The default is 0, which means to send as many
as possible (i.e., do not pause between the end of one transaction and
the start of another. It is also independent of other options like
--oltp-user-delay-min and --oltp-user-delay-max,
which add think time between individual statements generated by
sysbench. - --tx-jitter
- Magnitude of the variation in time to start transactions at, in
microseconds. The default is zero, which asks each thread to vary its
transaction period by up to 10 percent (i.e. 10\^6 /
tx-rate \* num-threads / 10). A standard pseudo-random
number generator is used to decide each transaction start time.
My need for these options is simple - I want to generate a steady
load for my MySQL database. It is one thing to measure the maximum
achievable throughput as you change your database configuration,
hardware, or num-threads. I am also interested in how the system (or
just mysqld's) utilization changes, at the same transaction rate, when
I change other variables.
An upcoming post will demonstrate a use of sysbench in this mode.
For the moment my new feature can be added to sysbench 0.4.12 (and
probably many earlier versions) via
this patch.
These changes are tested on Solaris, but I did choose only APIs that
are documented as also available on Linux. I have also posted my patch
on
sourceforge as a sysbench feature enhancement request.
How does --tx-rate interact with --oltp-user-delay-min and --oltp-user-delay-max? If I say -tx-rate=1000 and --oltp-user-delay-min=100 and --oltp-user-delay-max=100, how many transactions are sent over?
The --oltp-user-delay-{min,max} options insert a sleep after each statement issued by sysbench. For example there are ten statements for each transaction in the popular [--test=oltp --oltp-read-only=on]. Your example will ask sysbench to sleep for 100 microseconds after each statement. If I assume you have one thread, then you are asking for 1000 _transactions_ per second, that is a rate of one per ms, but we are spending an unknown time on the statements, plus 10 x 100 usec (or 1 ms) sleeping, so in your example --tx_rate will have no effect.
On the other hand, if you have --num-threads=2 or greater, and the statements take less than 1ms per transaction to complete, you should(\*) see 1000 TPS, with sleeps after each statement, and a sleep at the end of the transaction.
\* - you may find that the sleeps after each statement are longer due to the limitations of the API used by sysbench to sleep.