00mjk / sysbench-runner Goto Github PK

sysbench-runner - Automated MySQL benchmarking using multiple configurations and workloads

Why:
The idea is to simplify and automate the repetitive task of benchmarking a MySQL server using a variety of configurations and workloads. sysbench-runner is a wrapper for the sysbench oltp tests (http://sysbench.sourceforge.net/docs/#database_mode). 

The general workflow for using sysbench is something like:

- Make configuration change
- Restart server
- Run sysbench tests with various numbers of worker threads
- Collate results
- Repeat

This is both frustrating and time consuming as for significant chunks of time you are left waiting for sysbench to return some data so you can tweak your config and run it again. It can also be error prone as the volume of data and number of configurations increases.

With sysbench-runner the workflow changes to be:
- Create multiple configurations, with each containing a change to be tested
- Run run-multiconfig against these configurations with a predefined list of worker thread settings to be tested
- Go do something fun and interesting for an hour or two
- Come back and review the aggregate results

This is simpler, less time consuming and less error prone.


Prerequisites:
- sysbench-0.4.12 (may work on other versions)
- A local MySQL server with a database called sbtest which has been prepared using the sysbench "prepare" command (http://sysbench.sourceforge.net/docs/#general_syntax)
- a directory containing the MySQL config files (my.cnf) that you which to test against

Usage:

WARNING: 
- Do not run this script on a working MySQL server. It will stop & start the service and put load on the server.
- The -c (clean) option deletes binary logs and InnoDB logs and may result in data loss.

There are three scripts in this package aggregate-results, run-multiconfig and run-sysbench. aggregate-results and run-sysbench can be called directly, but are more commonly called via run-multiconfig.


run-multiconfig
=-=-=-=-=-=-=-=-=-=-

Main wrapper, controls reading config files and starting and stopping mysql. 

Because run-multiconfig starts and stops the server and performs file operations it can only be run on locally via a socket.

Options:
   -i	MySQL pid file (required)
   -d	Config directory [./conf]
   -s	MySQL socket [/tmp/mysql.sock]
   -u	MySQL username [root]
   -p	MySQL password
   -w	ReadWrite mode (on/off) [on]
   -o	Timeout for each sysbench run [60]
   -r	Directory to print results to [./results]
   -t	List of thread counts to iterate [8,16,32,64,128]
   -c	Delete binlogs and ib_logfiles when complete (on/off) [off]

Minimal usage:

	./run-multiconfig -i /var/mysql/data/mysql.pid -p password

Complete usage (with default values):

	./run-multiconfig -i /var/mysql/data/mysql.pid -d ./conf -h localhost \
	-s /tmp/mysql.sock -u root -p password -w on -o 60 -r ./results \
	-t 8,16,32,64,128 -c off


run-sysbench
=-=-=-=-=-=-=-=-=-=-

Called from run-multiconfig or can be run directly. Handles calls to sysbench and storing results.

run-sysbench can be run on remote hosts. If a non-standards socket (-s) is provided the script will use the host and port values instead.

Options:
   -h	MySQL host [localhost]
   -h	MySQL port [localhost]
   -s	MySQL socket [/tmp/mysql.sock]
   -u	MySQL username [root]
   -p	MySQL password
   -w	ReadWrite mode (on/off) [on]
   -o	Timeout for each sysbench run [60]
   -r	Directory to print results to [./results]
   -t	List of thread counts to iterate [8,16,32,64,128]

Minimal usage:

	./run-sysbench -p password

Complete usage (with default values):

	./run-sysbench -s /tmp/mysql.sock -u root -p password -w on \
	-o 60 -r ./results -t 4,8,16,32,64,128,256

	./run-sysbench -h localhost -p 3306 -u root -p password -w on \
	-o 60 -r ./results -t 4,8,16,32,64,128,256
	

aggregate-results
=-=-=-=-=-=-=-=-=-=-

Called from run-multiconfig or can be run directly. Reads directory containing results created by run-sysbench and aggregates them into a CSV.
Main wrapper, controls reading config files and starting and stopping mysql.

Options:
   -r	Directory containing results [./results]

Minimal usage:

	./aggregate-results

Complete usage (with default values):

	./aggregate-results -r ./results