Oracle 5.0, Справочное руководство

Страница: 661 / 3232

The MySQL Benchmark Suite
641
becomes more difficult the more capabilities you require. If you want an application that is fast with
many database systems, it becomes even more difficult.
All database systems have some weak points. That is, they have different design compromises that
lead to different behavior.
To make a complex application portable, you need to determine which SQL servers it must work with,
and then determine what features those servers support. You can use the MySQL
crash-me
program
to find functions, types, and limits that you can use with a selection of database servers.
crash-me
does not check for every possible feature, but it is still reasonably comprehensive, performing about
450 tests. An example of the type of information
crash-me
can provide is that you should not use
column names that are longer than 18 characters if you want to be able to use Informix or DB2.
The
crash-me
program and the MySQL benchmarks are all very database independent. By taking a
look at how they are written, you can get a feeling for what you must do to make your own applications
database independent. The programs can be found in the
sql-bench
directory of MySQL source
distributions. They are written in Perl and use the DBI database interface. Use of DBI in itself solves
part of the portability problem because it provides database-independent access methods. See
Section 8.1.3, “The MySQL Benchmark Suite” .
If you strive for database independence, you need to get a good feeling for each SQL server's
bottlenecks. For example, MySQL is very fast in retrieving and updating rows for
MyISAM
tables, but
has a problem in mixing slow readers and writers on the same table. Transactional database systems
in general are not very good at generating summary tables from log tables, because in this case row
locking is almost useless.
To make your application really database independent, you should define an easily extendable
interface through which you manipulate your data. For example, C++ is available on most systems, so
it makes sense to use a C++ class-based interface to the databases.
If you use some feature that is specific to a given database system (such as the
REPLACE
statement,
which is specific to MySQL), you should implement the same feature for other SQL servers by coding
an alternative method. Although the alternative might be slower, it enables the other servers to perform
the same tasks.
With MySQL, you can use the
/*! */
syntax to add MySQL-specific keywords to a statement. The
code inside
/* */
is treated as a comment (and ignored) by most other SQL servers. For information
about writing comments, see Section 9.6, “Comment Syntax” .
If high performance is more important than exactness, as for some Web applications, it is possible
to create an application layer that caches all results to give you even higher performance. By letting
old results expire after a while, you can keep the cache reasonably fresh. This provides a method
to handle high load spikes, in which case you can dynamically increase the cache size and set the
expiration timeout higher until things get back to normal.
In this case, the table creation information should contain information about the initial cache size and
how often the table should normally be refreshed.
An attractive alternative to implementing an application cache is to use the MySQL query cache. By
enabling the query cache, the server handles the details of determining whether a query result can be
reused. This simplifies your application. See
Section 8.6.3, “The MySQL Query Cache” .
8.1.3. The MySQL Benchmark Suite
This benchmark suite is meant to tell any user what operations a given SQL implementation performs
well or poorly. You can get a good idea for how the benchmarks work by looking at the code and
results in the
sql-bench
directory in any MySQL source distribution.
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.