Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL use benchmarking tool sysbench     - Linux performance optimization features Tuned and ktune (Linux)

- Hibernate Performance Optimization of reusing SessionFactory (Programming)

- PostgreSQL use pgpool achieve high availability (Database)

- Tomcat configuration memory and remote debug port (Server)

- Android in the coordinate system and a method to obtain the coordinates (Programming)

- a virtual machine created migrated to host RHEL6.4 on Ubuntu 14.04 (Linux)

- After you change the GRUB boot disk partition repair (Linux)

- IP Security Policy is to learn how to prevent Ping and closed ports (Linux)

- MySQL 5.6.12 binary log path switching binlog (Database)

- Windows and Ubuntu dual system, repair of two ways UEFI boot (Linux)

- Depth understanding of Python character set encoding (Programming)

- Availability Hadoop platform - Oozie Workflow (Server)

- Installation and Configuration OpenVPN server and client on Ubuntu 15.04 (Server)

- Android media library of analysis: MediaProvider (Programming)

- MongoDB upgrade from 2.4.9 to 2.6.0 and PHP record of mongo extension upgrade from 1.4.5 to 1.5.1 (Database)

- CentOS 7 open ports (Linux)

- MySQL simple operation notes under Linux (Database)

- Linux IPTables anti-DDOS attack Shell Scripting (Linux)

- Fatal NI connect error 12170 error in Alert Log (Database)

- HttpClient Tutorial (Programming)

 
         
  MySQL use benchmarking tool sysbench
     
  Add Date : 2018-11-21      
         
         
         
  Innodb engine for database applications, users may be more concerned and oltp disk performance, and therefore the main test fileio oltp two projects

Test fileio
[Root @ iZ257l556beZ ~] # sysbench --test = fileio help
sysbench 0.4.12: multi-threaded system evaluation benchmark


fileio options:
  --file-num = N number of files to create [128]
  --file-block-size = N block size to use in all IO operations [16384]
  --file-total-size = SIZE total size of files to create [2G]
  --file-test-mode = STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode = STRING file operations mode {sync, async, fastmmap, slowmmap} [sync]
  --file-async-backlog = N number of asynchronous operatons to queue per thread [128]
  --file-extra-flags = STRING additional flags to use on opening files {sync, dsync, direct} []
  --file-fsync-freq = N do fsync () after this number of requests (0 - do not use fsync ()) [100]
  --file-fsync-all = [on | off] do fsync () after each write operation [off]
  --file-fsync-end = [on | off] do fsync () at the end of test [on]
  --file-fsync-mode = STRING which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests = N merge at most this number of IO requests if possible (0 - do not merge) [0]
  --file-rw-ratio = N reads / writes ratio for combined test [1.5]


Wherein the test mode into continuous / sequential write (seqwr), continuous rewrite (seqrewr), continuous reading (seqrd), random read (rndrd), random write (rndwr), random access (rndrw) six of them.
In man manual test sample, for example, requires a three-step test, such as:

$ Sysbench --num-threads = 16 --test = fileio --file-total-size = 3G --file-test-mode = rndrw prepare - prepare
$ Sysbench --num-threads = 16 --test = fileio --file-total-size = 3G --file-test-mode = rndrw run - test
$ Sysbench --num-threads = 16 --test = fileio --file-total-size = 3G --file-test-mode = rndrw cleanup - Clear test file

The first step in the implementation of test files generation
sysbench --num-threads = 16 - test = fileio --file-total-size = 3G - file-test-mode = rndrw prepare


Run the test for the second operation:

$ Sysbench --num-threads = 16 --test = fileio --file-total-size = 3G --file-test-mode = rndrw run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000 - by max-requests parameter setting, the maximum number of requests
Read / Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync () each 100 requests.
Calling fsync () at the end of test, Enabled.
Using synchronous I / O mode
Doing random r / w test
Threads
Done.
Operations performed: 6000 Read, 4002 Write, 12800 Other = 22802 Total
Read 93.75Mb Written 62.531Mb Total transferred 156.28Mb (2.8256Mb / sec) --- random write performance
  180.84 Requests / sec executed - random write iops
Test execution summary:
    total time: 55.3085s
    total number of events: 10002
    total time taken by event execution: 306.1095
    per-request statistics:
         min: 0.00ms
         avg: 30.60ms
         max: 508.92ms
         . Approx 95 percentile: 162.07ms
Threads fairness:
    events (avg / stddev): 625.1250 / 66.54
    execution time (avg / stddev): 19.1318 / 1.07
Note: The actual test several models of each one of them the best test comparison.
OLTP database performance testing


[Root @ iZ257l556beZ soft] # sysbench --test = oltp help
sysbench 0.4.12: multi-threaded system evaluation benchmark
oltp options:
  --oltp-test-mode = STRING test type to use {simple, complex, nontrx, sp} [complex]
  --oltp-reconnect-mode = STRING reconnect mode {session, transaction, query, random} [session]
  --oltp-sp-name = STRING name of store procedure to call in SP test mode []
  --oltp-read-only = [on | off] generate only 'read' queries (do not modify database) [off]
  --oltp-skip-trx = [on | off] skip BEGIN / COMMIT statements [off]
  --oltp-range-size = N range size for range queries [100]
  --oltp-point-selects = N number of point selects [10]
  --oltp-simple-ranges = N number of simple ranges [1]
  --oltp-sum-ranges = N number of sum ranges [1]
  --oltp-order-ranges = N number of ordered ranges [1]
  --oltp-distinct-ranges = N number of distinct ranges [1]
  --oltp-index-updates = N number of index update [1]
  --oltp-non-index-updates = N number of non-index updates [1]
  --oltp-nontrx-mode = STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  --oltp-auto-inc = [on | off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay = N time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min = N minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max = N maximum time in microseconds to sleep after each request [0]
  --oltp-table-name = STRING name of test table [sbtest]
  --oltp-table-size = N number of records in test table [10000]
  --oltp-dist-type = STRING random numbers distribution {uniform, gaussian, special} [special]
  --oltp-dist-iter = N number of iterations used for numbers generation [12]
  --oltp-dist-pct = N percentage of values ​​to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res = N percentage of 'special' values ​​to use (for special distribution) [75]
General database options:
  --db-driver = STRING specifies database driver to use ( 'help' to get list of available drivers)
  --db-ps-mode = STRING prepared statements usage mode {auto, disable} [auto]
Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver
mysql options:
  --mysql-host = [LIST, ...] MySQL server host [localhost]
  --mysql-port = N MySQL server port [3306]
  --mysql-socket = STRING MySQL socket
  --mysql-user = STRING MySQL user [sbtest]
  --mysql-password = STRING MySQL password []
  --mysql-db = STRING MySQL database name [sbtest]
  --mysql-table-engine = STRING storage engine to use for the test table {myisam, innodb, bdb, heap, ndbcluster, federated} [innodb]
  --mysql-engine-trx = STRING whether storage engine used is transactional or not {yes, no, auto} [auto]
  --mysql-ssl = [on | off] use SSL connections, if available in the client library [off]
  --myisam-max-rows = N max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options = STRING additional options passed to CREATE TABLE []
pgsql options:
  --pgsql-host = STRING PostgreSQL server host [localhost]
  --pgsql-port = N PostgreSQL server port [5432]
  --pgsql-user = STRING PostgreSQL user [sbtest]
  --pgsql-password = STRING PostgreSQL password []
  --pgsql-db = STRING PostgreSQL database name [sbtest]

Create analog data

[Root @ iZ257l556beZ ~] # sysbench --test = oltp --oltp-table-size = 1000000 --db-driver = mysql --mysql-socket = / home / data / mydata / 3307 / mysql.sock --mysql -user = root --mysql-password = ESBecs00 --mysql-port = 3307 --mysql-host = 127.0.0.1 --mysql-db = pt prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark
Creating table 'sbtest' ...
Creating 500000 records in table 'sbtest' ...

Test run

[Root @ iZ257l556beZ ~] # sysbench --test = oltp --oltp-table-size = 500000 --db-driver = mysql --mysql-socket = / home / data / mydata / 3307 / mysql.sock --mysql -user = root --mysql-password = ESBecs00 --mysql-port = 3307 --mysql-host = 127.0.0.1 --mysql-db = pt run> res

View Test Results

[Root @ iZ257l556beZ ~] # more res
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Doing OLTP test.
Running mixed OLTP 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 10000
Threads
Done.
OLTP test statistics:
    queries performed:
        read: 140000
        write: 50000
        other: 20000
        total: 210000
    transactions: 10000 (. 120.50 per sec) ---- tps 10000 / 82.9887 = 120.5
    deadlocks: 0 (0.00 per sec.)
    read / write requests: 190000 (. 2289.47 per sec) ---- qps 190000 / 82.9887
    other operations: 20000 (241.00 per sec.)
Test execution summary:
    total time: 82.9887s
    total number of events: 10000
    total time taken by event execution: 82.9137
    per-request statistics:
         min: 5.98ms
         avg: 8.29ms
         max: 86.63ms
         . Approx 95 percentile: 10.78ms
Threads fairness:
    events (avg / stddev): 10000.0000 / 0.00
    execution time (avg / stddev): 82.9137 / 0.00
     
         
         
         
  More:      
 
- How to install and configure the KVM and Open vSwitch on Linux distributions (Linux)
- You know the difference between URL, URI and URN among you (Linux)
- RMAN parameters of ARCHIVELOG DELETION (Database)
- CentOS 6.4 RPM install MySQL-5.6.22-1 (Database)
- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)
- Oracle 10g after the failure to start the upgrade (Oracle instance terminated. Disconnection forced) (Database)
- Nagios (centreon) monitoring LVS (Server)
- Some Linux networking tools you might not know (Linux)
- Linux system find command Detailed (Linux)
- Oracle 11g to create a second instance on Linux (Database)
- Windows 7 hard disk to install Ubuntu 14.10 (Linux)
- Use OpenWrt build WDS wireless network extension on V2 WHR-G300N (Linux)
- Linux virtual machine how to access the Internet in a virtual machine when using NAT mode (Linux)
- Install apr support for Tomcat on Linux (Server)
- installation of Vim plugin YouCompleteMe under Ubuntu 15.04 (Linux)
- Simple Linux file system - df, du, ln (Linux)
- Node.js Getting the basics: Helloworld! (Linux)
- Java exception handling mechanism (Programming)
- Android Studio Getting Started Hello World (Programming)
- 25 Git Usage Tips (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.