test_mysql_server_performance_with_sysbench [pswiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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: 
<code mysql>
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;
</code>
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.. 
<code>
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 
</code>
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: 
<code mysql>
DROP USER 'sysbench'@'%';
DROP DATABASE sysbench;
FLUSH PRIVILEGES;
</code>