Databases Reference
In-Depth Information
Bulletin boards and threaded discussion forums
Threaded discussions are an interesting problem for MySQL users. There are hundreds
of freely available PHP and Perl-based systems that provide threaded discussions. Many
of them aren't written with database efficiency in mind, so they tend to run a lot of
queries for each request they serve. Some were written to be database-independent, so
their queries do not take advantage of the features of any one database system. They
also tend to update counters and compile usage statistics about the various discussions.
Many of the systems also use a few monolithic tables to store all their data. As a result,
a few central tables become the focus of heavy read and write activity, and the locks
required to enforce consistency become a substantial source of contention.
Despite their design shortcomings, most of these systems work well for small and me-
dium loads. However, if a website grows large enough and generates significant traffic,
it will become very slow. The obvious solution is to switch to a different storage engine
that can handle the heavy read/write volume, but users who attempt this are sometimes
surprised to find that the system runs even more slowly than it did before!
What these users don't realize is that the system is using a particular query, normally
something like this:
mysql>
SELECT COUNT(*) FROM table;
The problem is that not all engines can run that query quickly: MyISAM can, but other
engines might not. There are similar examples for every engine. Later chapters will help
you keep such a situation from catching you by surprise and show you how to find and
fix the problems if it does.
CD-ROM applications
If you ever need to distribute a CD-ROM- or DVD-ROM-based application that uses
MySQL data files, consider using MyISAM or compressed MyISAM tables, which can
easily be isolated and copied to other media. Compressed MyISAM tables use far less
space than uncompressed ones, but they are read-only. This can be problematic in
certain applications, but because the data is going to be on read-only media anyway,
there's little reason not to use compressed tables for this particular task.
Large data volumes
How big is too big? We've built and managed—or helped build and manage—many
InnoDB databases in the 3 TB to 5 TB range, or even larger. That's on a single server,
not sharded. It's perfectly feasible, although you have to choose your hardware wisely,
practice smart physical design, and plan for your server to be I/O-bound. At these sizes,
MyISAM is just a nightmare when it crashes.
If you're going really big, such as tens of terabytes, you're probably building a data
warehouse. In this case, Infobright is where we've seen the most success. Some very
