MySQL Optimization, part 1 - 6.1.4 The MySQL Benchmark Suite (
Page 2 of 8 )
This section should contain a technical description of the MySQL benchmark
suite (and crash-me), but that description has not yet been written.
Currently, you can get a good idea of the benchmarks by looking at the code and
results in the sql-bench directory in any MySQL source
distribution.
This benchmark suite is meant to tell any user what operations a given SQL
implementation performs well or poorly.
Note that this benchmark is single-threaded, so it measures the minimum time
for the operations performed. We plan to add multi-threaded tests to the
benchmark suite in the future.
To use the benchmark suite, the following requirements must be satisfied:
-
The benchmark suite is provided with MySQL source distributions. You can
either download a released distribution from http://dev.mysql.com/downloads/, or use the current
development source tree (see Section 2.3.3, "Installing from the Development
Source Tree").
-
The benchmark scripts are written in Perl and use the Perl DBI module to
access database servers, so DBI must be installed. You will also need the
server-specific DBD drivers for each of the servers you want to test. For
example, to test MySQL, PostgreSQL, and DB2, you must have the
DBD::mysql, DBD::Pg, and DBD::DB2 modules installed.
See Section 2.7, "Perl Installation Notes."
After you obtain a MySQL source distribution, you will find the benchmark
suite in its sql-bench directory. To run the benchmark tests, build
MySQL, then change location into the sql-bench directory and execute
the run-all-tests script:
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
server_name is one of the supported servers. To get a list of
all options and supported servers, invoke this command:
shell> perl run-all-tests --help
The crash-me script also is located in the sql-bench
directory. crash-me tries to determine what features a database
supports and what its capabilities and limitations are by actually running
queries. For example, it determines:
-
What column types are supported
-
How many indexes are supported
-
What functions are supported
-
How big a query can be
-
How big a VARCHAR column can be
You can find the results from crash-me for many different database
servers at http://dev.mysql.com/tech-resources/crash-me.php. For more
information about benchmark results, visit http://dev.mysql.com/tech-resources/benchmarks/.
6.1.5 Using Your Own Benchmarks
You should definitely benchmark your application and database to find out
where the bottlenecks are. By fixing a bottleneck (or by replacing it with a
"dummy module"), you can then easily identify the next bottleneck. Even if the
overall performance for your application currently is acceptable, you should at
least make a plan for each bottleneck, and decide how to solve it if someday you
really need the extra performance.
For an example of portable benchmark programs, look at the MySQL benchmark
suite. See Section 6.1.4, "The MySQL Benchmark Suite." You can take any program
from this suite and modify it for your needs. By doing this, you can try
different solutions to your problem and test which really is fastest for
you.
Another free benchmark suite is the Open Source Database Benchmark,
available at http://osdb.sourceforge.net/.
It is very common for a problem to occur only when the system is very heavily
loaded. We have had many customers who contact us when they have a (tested)
system in production and have encountered load problems. In most cases,
performance problems turn out to be due to issues of basic database design (for
example, table scans are not good at high load) or problems with the
operating system or libraries. Most of the time, these problems would be a
lot easier to fix if the systems were not already in production.
To avoid problems like this, you should put some effort into benchmarking
your whole application under the worst possible load! You can use Super Smack
for this. It is available at http://jeremy.zawodny.com/mysql/super-smack/. As the name
suggests, it can bring a system to its knees if you ask it, so make sure to use
it only on your development systems.
6.2 Optimizing SELECT Statements and Other
Queries
First, one factor affects all statements: The more complex your permission
setup is, the more overhead you will have.
Using simpler permissions when you issue GRANT statements enables
MySQL to reduce permission-checking overhead when clients execute statements.
For example, if you don't grant any table-level or column-level privileges, the
server need not ever check the contents of the tables_priv and
columns_priv tables. Similarly, if you place no resource limits on any
accounts, the server does not have to perform resource counting. If you have a
very high query volume, it may be worth the time to use a simplified grant
structure to reduce permission-checking overhead.
If your problem is with some specific MySQL expression or function, you can
use the BENCHMARK() function from the mysql client program to
perform a timing test. Its syntax is
BENCHMARK(loop_count,expression). For example:
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
This result was obtained on a Pentium II 400MHz system. It shows that MySQL
can execute 1,000,000 simple addition expressions in 0.32 seconds on that
system.
All MySQL functions should be very optimized, but there may be some
exceptions. BENCHMARK() is a great tool to find out if this is a
problem with your query.
This article is excerpted from MySQL
Administrator's Guide, by MySQL AB (editor) (Sams, 2004;
ISBN 0672326345). Check it out at your
favorite bookstore today. Buy
this book now. |