MySQL Conference Liveblogging: Benchmarking Tools (Wednesday 4:25PM)

Posted by Artem Russakovskii on April 16th, 2008 in Databases

  • Tom Hanlon of MySQL presents
  • Benchmarking tools
  • sql-bench
    • pros
      • ubiquitous
      • long history of use
    • cons
      • single thread
      • Perl
      • not always real-life test cases (create 10k tables?)
    • list of tests follows
  • supersmack
    • configurable, flexible
    • 1000 queries, 50 users
      • super-smack -d mysql select-key-smack 50 1000
    • can modify queries to be closer to what your own application uses
    • pros
      • benches concurrent connections
      • well documented
    • cons
      • test language sucks
  • Apache Bench
    • webserver benchmarking tool
    • point to a webserver, utilizes concurrent users
    • siege, httperf, httpload are similar
    • 404 errors deliver really quickly, so make sure to check for those
  • benchmark()
    • tests

  • Robert Hodges from Continuent presents
  • About Continuent
  • leading provider of open source database availability and scaling solutions
  • solutions
    • uni/cluster – multi-master database clustering that replicates data across multiple databases and load balances reads
    • uses "database virtualization"
  • scale-out design motivation
    • protection from db and site failures
    • continuous operation during upgrades
  • how come not everyone has it already?
  • creating identical replicas across different hosts is hard
    • Brewer's conjecture
  • trade-offs
    • DDL support
    • inconsistent reads between replicas
    • deadlocks
    • sequences
    • non-deterministic SQL
  • therefore many scale-out approaches are non-transparent
  • 3 basic scale-out technologies
    • data replication
    • where are updates processed? master/master vs master/slave
    • when are updates replicated? sync vs async
  • group communication – coordinates messages between distributed processes
  • Share

    sysbench – Linux Test Bench

    Posted by Artem Russakovskii on October 12th, 2007 in Databases, Linux

    Updated: October 22nd, 2011

    sysbench – Linux test bench. Easy as pie to test CPU, memory, threads, mysql, and disk performance.

    Full description is available here: http://sysbench.sourceforge.net/docs/

    install mysql, mysql-devel
    wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz
    tar xvzf sysbench*gz
    cd sysbench*
    ./configure && make install

    mysql tests

    This will run 10 separate consecutive mysql tests using an InnoDB table type, each with 100 mysql threads, doing a total of 1000 various SQL operations per test. Then it will print the total time they took to finish:

    sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \
      --mysql-host='HOST' --mysql-table-engine=innodb prepare
    time perl -e "foreach(1..10){print \`sysbench --max-requests=1000 --test=oltp \
      --mysql-user=USER --mysql-password=PASS --mysql-db=test --mysql-host='HOST' \
      --mysql-table-engine=innodb --num-threads=100 run\`}"
    sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \
      --mysql-host='HOST' cleanup