SYSBENCH for Mysql

SYSBENCH for Mysql
http://www.howtoforge.com/how-to-benchmark-your-system-cpu-file-io-mysql-with-sysbench
http://dev.mysql.com/downloads/benchmarks.html
http://blog.yannickjaquier.com/linux/sysbench.html
DOC UFFICIALE: http://sysbench.sourceforge.net/docs/
1.1 SysBench Benchmark Tool
Sysbench is a popular open source benchmark to test open source DBMSs. The DBT2 Benchmark Tool can be
used to run automated test runs of Sysbench for a single MySQL Server instance running InnoDB or MyISAM
or running a MySQL Cluster set-up with a single MySQL Server instance.
All automated benchmark programs assume that the machines can be accessed using ssh. All benchmarks
require a MySQL source or binary tarball packed with gzip. The Sysbench benchmark also requires usage of
the Sysbench tarball downloadable below. This tarball (Sysbench 0.4.12.5) contains a number of extra
features added to Sysbench 0.4.12 which are used by the DBT2 Benchmark Tool. The DBT2 Benchmark Tool
tarball (dbt2-0.37.50) is based on dbt2-0.37 with a lot of changes and additions to automate benchmark runs.
MySQL Server Version: 5.1, 5.5, 5.6 Development Milestone Releases
How To Benchmark Your System (CPU, File IO, MySQL) With sysbench
http://www.serveradminblog.com/2010/02/sysbench-on-centos-howto/
SysBench on CentOS – HowTo
If you want to test server performance, you can think about SysBench. SysBench is a modular, cross-platform
and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a
database under intensive load. The idea of this benchmark suite is to quickly get an impression about system
performance without setting up complex database benchmarks or even without installing a database at all.
Current features allow to test the following system parameters:
* file I/O performance
* scheduler performance
* memory allocation and transfer speed
* POSIX threads implementation performance
* database server performance (OLTP benchmark)
(Primarily written for MySQL server benchmarking, SysBench will be further extended to support multiple
database backends, distributed benchmarks and third-party plug-in modules)
I couldn’t find CentOS RPM so here are few tips how to install it manually.
Download Sysbench (current version is 0.4.12)
Download rpm:
wget http://garr.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.12.tar.gz
Then unpack it and install with
#
#
#
#
#
#
tar -xvzf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
libtoolize --force --copy
(se non funziona  yum install libtool.i386)
./autogen.sh
./configure --with-mysql=/usr/local/mysql
make
# make install
Prima del testing impostare la env;
export LD_LIBRARY_PATH=/usr/local/mysql/lib questa stronzata !!!!!
http://blog.yannickjaquier.com/linux/sysbench.html
Prepared ambiente x benchmark
For MySQL test, you’ll need to prepare database for testing with
Creare il database di benchmark: Altrimenti usa l’opzione --mysql-db=test
sql> create database sbtest;
Creare l’ambiente:
# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=500000 -mysql-user=test_database --mysql-password=test_database_password --mysqlsocket=/tmp/mysql.sock prepare
(replace test_database with valid username and test_database_password with valid password)
This command will create sample table inside test_database and it will have 500 000 rows (InnoDB engine).
Now to start read test
# sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-tablesize=500000 --mysql-socket=/tmp/mysql.sock --oltp-read-only --mysql-user=root
mysql-password=sps2007 run
--
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
queries performed:
read:
write:
other:
total:
transactions:
deadlocks:
read/write requests:
other operations:
1400028
0
200004
1600032
100002 (2839.78 per sec.)
0 (0.00 per sec.)
1400028 (39756.98 per sec.)
200004 (5679.57 per sec.)
Test execution summary:
total time:
35.2146s
total number of events:
100002
total time taken by event execution: 562.5428
per-request statistics:
min:
2.05ms
avg:
5.63ms
max:
95.35ms
approx. 95 percentile:
18.20ms
Threads fairness:
events (avg/stddev):
6250.1250/108.88
execution time (avg/stddev): 35.1589/0.01
Con --max-requests=50.000.000
disco niente :
record , il carico si intensifica nella cpu , i/0 su
Prestazioni #1 500k only-read 16threads
Solo select .
Esegue solamente cpu , i grafici sono dalle 15:35
Thread confermati dalle Connessioni
Cpu /
Activity è 40k statement /sec
Disk Usage:
Prestazioni #2 50k , read , 16threads
Abbassando il numero di rows , avg statements/sec rimane a 40k
Db transaction costante a 2900 statem/sec
Per costante si intende per rows pari a 50k e 500k
Prestazioni #3 5k , read , 16threads
Abbassando il numero di rows , avg statements/sec rimane a 40k
Db transaction costante a 2900 statem/sec
Per costante si intende per rows pari a 50k e 500k
Prestazioni #3 1M , read , 16threads
For read-write test you can try
# sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-tablesize=500000 --mysql-socket=/tmp/mysql.sock --oltp-test-mode=complex --mysqluser=root --mysql-password=sps2007 run
To clean up the system afterwards (i.e., remove the test table), run:
# sysbench --test=oltp --mysql-db=test --mysql-user=root --mysqlpassword=yourrootsqlpassword cleanup
INFO SISTEMA
mysql> show variables like '%max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
test oltp
http://sysbench.sourceforge.net/docs/#database_mode
2 Swinbench check