test_mysql_server_performance_with_sysbench

Test MYSQL server perofrmance with sysbench

this is only to get you started, i am not an expert on sysbench but wanted to get a quick and dirty benchmark run in to compare basic performance between an old and a new server i had at hand.

perpare the database

first lets create a user and database for our tests, just to make sure we don't accidently overwrite or delete anything important :)

start a mysql client as root or otherwise privileged user where you can create new users and db's. for example on the linux cli like this:

mysql -h <dbhost> -u root -p 

now add a user and database and grant full control over the database to the user:

CREATE DATABASE sysbench;
# for versions 5.7 or newer:
#CREATE USER 'sysbench'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MySYSBENCHsecret';
# for 5.6 or older versions
CREATE USER 'sysbench'@'%' IDENTIFIED BY 'MySYSBENCHsecret';
GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'%';
FLUSH PRIVILEGES;

leave the mysql prompt and install sysbench. most distributions come with it pre-packaged, so you can could simply install it from the package manager..

apt-get install sysbench 

however, if you want to compare different systems which may have different distriutions or versions of distributions, you might get a more comparable result if you compile from source..

apt-get install make automake libtool pkg-config libaio-dev libmysqlclient-dev libssl-dev
mkdir -p ~/tmp/sysbench
cd ~/tmp/sysbench
wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.tar.gz
tar xvf 1.0.20.tar.gz sysbench-1.0.20/
cd sysbench-1.0.20/
./autogen.sh 
./configure 
make -j 

optionally install it on the system with make install if you want to.. or just run it from ~/tmp/sysbench/sysbench-1.0.20/src/sysbench

prepare everything for a read/write benchmark:

src/sysbench --mysql-host=localhost --mysql-user=sysbench --mysql-password=MySYSBENCHsecret --mysql-db=sysbench oltp_read_write prepare

and finally run the benchmark:

src/sysbench --mysql-host=localhost --mysql-user=sysbench --mysql-password=MySYSBENCHsecret --mysql-db=sysbench oltp_read_write run

when you're done, clean up! login again with your mysql root account, then delete our user and database:

DROP USER 'sysbench'@'%';
DROP DATABASE sysbench;
FLUSH PRIVILEGES;
  • test_mysql_server_performance_with_sysbench.txt
  • Last modified: 19.03.2021 17:24
  • by Pascal Suter