Home arrow MySQL arrow Page 2 - MySQL Benchmarking Tools and Utilities

Benchmarking Tools: sql-bench and Super Smack - MySQL

You are reading the second segment of the four-part article series covering MySQL benchmarking concepts and strategies, benchmarking tools and utilities, and the most common query optimizations, design of optimal schemas, and server tuning tips. In this part we will present some of the popular benchmarking and monitoring tools and utilities. By the end of this article, youíll know how to use and work with each of them.

  1. MySQL Benchmarking Tools and Utilities
  2. Benchmarking Tools: sql-bench and Super Smack
  3. Benchmarking Tools: SysBench and the Rest
  4. Taking a Break
By: Barzan "Tony" Antal
Rating: starstarstarstarstar / 5
November 11, 2008

print this article



Letís kick-start the presentation of benchmarking utilities with the official MySQL Benchmarking Suite called ďsql-benchĒ that comes with MySQL distributions. This bundled benchmarking suite provides a general overview of the tested SQL server as a whole. This means that it measures the overall performance of the server and can be used as a basis for comparison with others, not necessarily testing new tweaks.

We will do our best to point out the advantages and drawbacks of each utility. It is important to know when to use which, how, and why exactly. SQL-bench is serialized; it is single-threaded and unable to take advantage of multi-threading (multi-CPU or dual/quad-core setups). SQL-bench brings absolutely no SMP capabilities. However, it has been said that the MySQL team plans to add multi-threading support in the future.

You can execute this benchmarking utility by launching the run-all-tests script residing in the /sql-bench directory. The execution time-frame of the entire suite takes a while -- depending on your hardware specifications, a few minutes to a few hours.

This article gives you a general guideline of each utility thatís presented within. But it does not replace the original documentation and/or manual that comes with the benchmarking tools. Be sure to read those before trying to run the scripts and command-line described here. Each utility has various parameters and each one of them is documented in-depth in the official manuals.

The aforementioned sql-bench ought to be launched with the following syntax:

./run-all-tests --server=mysql --user=root --log --fast

In the above syntax, using the parameter --log results in verbose extensive real-time logging in a subdirectory called output. During the benchmarking process you can check out these logs. This benchmark measures the wallclock seconds required to execute the queries. After this value there are various other values in parentheses. You need to subtract from the wallclock seconds that second smaller value.

The aforementioned subtraction is important if you want to find out exactly the time-frame it takes for the SQL server to respond. The total count of wallclock seconds contains the script execution and other external factors as well. You shouldnít count those. This benchmark is still used frequently because it comes bundled; itís scripted in Perl, doesnít really reflect real-life performance, and it is just single-threaded.

Going forward, thereís the highly popular stress-tester called Super Smack. The beauty of this tool lies in its multi-threading capabilities and highly customizable architecture. The user is able to specify the count of concurrent connections and also the number of iterations each client will execute. Additionally, you can specify the tests that should be performed on your test data along with tables. Itís very customizable.

As a result, creating your own set of Super Smack tests takes a bit of research and time. Super Smack is amazingly well-documented; however, you need to actually read those manuals. You need to create your own test tables, fill them up either randomly on-the-fly or from an input file, and explicitly specify the set of queries which are going to be performed during the benchmarking session.

However, you can run Super Smack without preparing a customized set of tests yourself. In that case, the table http_auth is automatically generated (if not found) and populated from the smack data file words.dat bundled with Super Smack. However, you still need to specify the count of concurrent connections and the number of iterations when running Super Smack in the command line. Here are a few examples:

super-smack update-select.smack 50 20000

// 50 concurrent users and each executing 20,000 iterations of the test queries

super-smack -d mysql select-key-smack 75 1000

// 75 concurrent users and each executing 1,000 iterations

Summing these up, Super Smack is probably one of the best benchmarking utilities due to its incredible flexibility, configurability, and functions that just wonít get old. No matter what kind of unique requirements you do have, if you really take the time to take control of Super Smack, you can be absolutely positive that the custom set of tests you will create will be of great benefit to you. Due to this, itís quite advanced.

>>> More MySQL Articles          >>> More By Barzan "Tony" Antal

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: