Databases Reference
In-Depth Information
sysbench
sysbench
(
https://launchpad.net/sysbench
)
is a multithreaded system benchmarking
tool. Its goal is to get a sense of system performance, in terms of the factors im-
portant for running a database server. For example, you can measure the perfor-
mance of file I/O, the OS scheduler, memory allocation and transfer speed, POSIX
threads, and the database server itself.
sysbench
supports scripting in the Lua lan-
guage (
http://www.lua.org
)
, which makes it very flexible for testing a variety of
scenarios. It is our favorite all-around benchmarking tool for MySQL, operating
system, and hardware performance.
MySQL's BENCHMARK() Function
MySQL has a handy
BENCHMARK()
function that you can use to test execution speeds for
certain types of operations. You use it by specifying a number of times to execute and
an expression to execute. The expression can be any scalar expression, such as a scalar
subquery or a function. This is convenient for testing the relative speed of some oper-
ations, such as seeing whether
MD5()
is faster than
SHA1()
:
mysql>
SET @input := 'hello world';
mysql>
SELECT BENCHMARK(1000000, MD5(@input));
+---------------------------------+
| BENCHMARK(1000000, MD5(@input)) |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (2.78 sec)
mysql>
SELECT BENCHMARK(1000000, SHA1(@input));
+----------------------------------+
| BENCHMARK(1000000, SHA1(@input)) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (3.50 sec)
The return value is always
0
; you time the execution by looking at how long the client
application reported the query took. In this case, it looks like
MD5()
is faster. However,
using
BENCHMARK()
correctly is tricky unless you know what it's really doing. It simply
measures how fast the server can execute the expression; it does not give any indication
of the parsing and optimization overhead. And unless the expression includes a user
variable, as in our example, the second and subsequent times the server executes the
expression might be cache hits.
9
Although it's handy, we don't use
BENCHMARK()
for real benchmarks. It's too hard to
figure out what it really measures, and it's too narrowly focused on a small part of the
overall execution process.
9. One of the authors made this mistake and found that 10,000 executions of a certain expression ran
just as fast as 1 execution. It was a cache hit. In general, this type of behavior should always make
you suspect either a cache hit or an error.
