The Good, the Blog & the Ugly - Tim Cook's Weblog
- All
- MySQL
- Performance
- Personal
- Sun
Monday April 06, 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.
Posted at 03:42PM Apr 06, 2009 by timc in Performance | Comments[2]
This is a personal weblog, I do not speak for my employer.
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?
Posted by neel on April 06, 2009 at 06:46 PM PDT #
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.
Posted by Tim Cook on April 07, 2009 at 09:35 AM PDT #