beer planet

• Tom Hanlon of MySQL presents
• Benchmarking tools
  • mysqlslap (with MySQL 5.1)
  • sql-bench
  • supersmack - Jeremy Zawodny's tool
  • Apache Bench (combined with some sample PHP scripts)
  • MySQL's benchmark() function
  • mybench
  • WAST
  • JMeter
• 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 MySQL's internal functions
  • SELECT BENCHMARK(10000, SHA1(MD5(rand())));
  • pros
    • simple to use
  • cons
    • only benchmarks functions, can be overcome (see next)
  • creative use: write your own queries as functions and then run benchmark() on them
• MyBench
  • simple
• WAST
  • pros
    • flexible
    • easy to use (GUI)
    • replay example click trail from browsing
  • cons
    • Windows only
• Jmeter
  • Java based app for testing web applications
  • hard to built tests but once you do, they run really well
  • configuration done using test plans, which is more involved than just typing a cmd line
  • can output tables, graphs
  • can be configured to run endlessly, as background noise
  • good documentation
  • flexible
• mysqlslap
  • internally developed benchmark tool
  • ships with 5.1
  • feature rich
  • mysqlslap –user=john
  • –auto-generate-sql
    or
    –query="select blabla …" or –query=file
  • –concurrency=100
  • –iterations=5
  • –engine=myisam
• audience members additionally mention grinder, openSTA, yslow (ties in with FireBug), selenium

• 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
• views - who is active, who is crashed, do we have quorum, etc
• message delivery - ordering and delivery guarantees
• proxying - virtualizes databases and hides database locations from applications
• latency, performance?
• 3 replication algorithms
• master/slave - accept updates at a single master and replicate changes to one or more slaves
• multi-master state machine - deliver a stream of updates in the same order simultaneously to a set of databases
• certification - optimistically execute transactions on one of a number of nodes and then apply to all nodes after confirming serialization. Currently not in MySQL but developed by Continuent (presenter's company)
• performance testing strategy
• run appropriate tests
• mixed load tests to check overall throughput and scaling
• micro-benchmarks to focus on specific issues
• use appropriate workloads
• scale-out use profiles are often read or write intensive
• cover key issues
• read latency through proxies
• read and write scaling
• slave latency for master/slave configurations
• group communication and replication bottlenecks
• aborts and deadlocks
• generate sufficient load in the right places
• many transactions/queries
• large data sets
• data types
• Bristlecone
• http://bristlecone.continuent.org
• open source
• svn checkout svn://forge.continuent.org/bristlecone/trunk/bristlecone bristlecone
• load test
• batch transaction loading
• micro-benchmarks
• Bristlecone Load Testing: Evaluator
• Java tool to generate mixed load on databases
• similar to pgbench but works cross-DBMS (how about sysbench?)
• can easily vary mix of select, insert, update, delete statements
• default select statement designed to "exercise" the db
• can choose lightweight queries as well
• parameters are defined in a simple config file
• can generate reports
• shows sample config file (xml) that generates 500 clients, lasts 600 seconds. Looks quite simple but very proprietary. Examples are included in the download.
• Evaluator Graphical Output
• shows a graph of requests/s and response time, very standard looking, updates live while the test is running, last 10 minutes are visible.
• Bristlecone Micro-Benchmarks: Benchmark
• Java tool to test specific operations while systematically varying parameters
• benchmarks run "scenarios" - specialized Java classes with interfaces similar to JUnit
• shows config file, java properties file this time instead of xml, you can vary a few parameters that will spawn multple variations of the test (cross join between all variations)
• current micro benchmarks
• basic read latency - low db stress
• ReadSimpleScenario
• ReadSimpleLargeScenario
• read scaling - high db stress
• ReadScalingAggregatesScenario
• ReadScalingInvertedKeysScenario
• write latency and scaling - low/high stress
• …
• deadlocks - variable transaction lenghts
• DeadLockScenario
• TPC-B scenario will be added shortly
• shows html output, simple table layout, easy to look at or load into a pivot table in Excel
• Bristlecone Testing Examples
• shows a mixed load query throughput test output graph between a standalone server and a 2-node cluster. cluster is approximately twice as productive as the standalone server
• shows a mixed load query response test output between the same standalone server and a 2-node cluster. The standalone server is visibly choking while the cluster is smooth
• shows a proxy query throughput against MySQL 5.1.23, MySQL Proxy 0.6.1, Myosotis Connector proxy, and uni/cluster proxy. MySQL 5.1.23 is significantly faster than any proxy. MySQL Proxy is the worst performing one, even though it's written in C and the others are in Java. Robert thinks it's due to Java handling multi-threading better than C
• shows a read scaling test output for a query that does SELECT COUNT(*) with 200 rows. MySQL 5.1.23 beats uni/cluster proxy until it passes 4 threads, where the proxy beats it.
• All tests used InnoDB
• shows a MySQL replication master overhead test results comparing a inserts per second on a single master vs a master with a slave. The master with a slave is about 30% slower. Peter Zaitsev raises an interesting question of the differences between just having the binlog turned on vs having it turned on AND a slave replicating. These differences weren't tested by the presenter and he's unsure on the result
• shows a replication latency MySQL vs Postgres test results, in which Postgres actually kicks MySQL's ass. A replica with default InnoDB settings performs very badly compared to tweaked settings (about 70% slower)

Updated: March 18th, 2008

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://superb-west.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.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