High Performance MySQL [Electronic resources] نسخه متنی

The Basic Layout of This Book

We fit a lot of complicated topics in this book. Here
we'll explain how we put them together in an order
that hopefully makes them easy for you to learn.

Back to Basics

The first two chapters are dedicated to the basicsthings
you'll need to be familiar with before you get to
additional configuration details.

Chapter 1, reviews some rudimentary configuration
basics. This book assumes a pretty good command of foundational MySQL
administration, but we'll go over the fundamentals
briefly before digging deeper into the world of MySQL.

After that, Chapter 2, covers the various storage
engines, or table types, that are part of MySQL. This is important
because storage engine selection is one of the few things that can be
nontrivial to change after you create a table. We review the various
benefits (and potential pitfalls) of the various storage engines, and
try to provide enough information to help you decide which engine is
best for your particular application and environment.

Things to Reference as You Read the Rest of the Book

The next two chapters cover things you'll find
yourself referencing time and again throughout the course of the
book.

Chapter 3, discusses the basics of
benchmarkingdetermining what sort of workloads your server can
handle, how fast it can perform certain tasks, and so on.
You'll want to benchmark your application both
before and after a major change, so you can judge how effective your
changes are. What seems to be a positive change may turn out to be a
negative one under real-world stress.^[1]

^[1] Management
folks also tend to like metrics they can point at and say,
"See, this is how much our system improved after we
spent $39.95 on that O'Reilly book!
Wasn't that a great
investment?"

In Chapter 4, we cover the various nuances of
indexes. Many of the things we discuss in later chapters hinge on how
well your application puts MySQL's indexes to work.
A firm understanding of indexes and how to optimize their use is
something you'll find yourself returning to
repeatedly throughout the process.

Places to Tune Your Application

The next two chapters discuss areas in which the MySQL administrator,
application designer, or MySQL programmer can make changes to improve
performance of a MySQL application.

In Chapter 5, we discuss how the MySQL programmer
might improve the performance of the MySQL queries themselves. This
includes basics, such as how the query parser will parse the queries
provided, as well as how to optimize queries for ideal performance.

Once the queries are optimized, the next step is to make sure the
server's configuration is optimized to return those
queries in the fastest possible manner. In Chapter 6, we discuss some ways to get the most out of
your hardware, and to suggest hardware configurations that may
provide better performance for larger-scale applications.

Scaling Upward After Making Changes

Once you've got a server up and running as best it
can, you may find that one server simply isn't
enough. In Chapter 7, we discuss
replicationthat is, getting your data copied automatically to
multiple servers. When combined with the load-balancing lessons in
Chapter 8, this will provide you with the
groundwork for scaling your applications in a significant way.

Make Sure All That Work Isn't for Naught

Once you have configured your application, gotten it up and running,
and replicated your database across multiple servers, your next task
as a MySQL administrator is to keep it all going.

In Chapter 9, we discuss various backup and
recovery strategies for your MySQL databases. These strategies help
minimize your downtime in the event of inevitable hardware failure
and ensures that your data survives such catastrophes.

Finally, Chapter 10, provides you with a firm grasp
of some of the security issues involved in running a MySQL server.
More importantly, we offer many suggestions to allow you to prevent
outside parties from harming the servers you have spent all this time
trying to configure and optimize.

The Miscellany

There's a couple things we delve into that either
don't "fit" in a
particular chapter or are referenced often enough by multiple
chapters that they deserve a bit of special attention all to
themselves.

In Appendix A, we cover the output of the
SHOW STATUS and
SHOW INNODB
STATUS commands. We attempt to decipher for the
average administrator what all those variables mean and offer some
ways to find potential problems based on their values relative to
each other.

Appendix B, covers a program called
mytop, which Jeremy wrote as an easy-to-use
interface to what your MySQL server is presently doing. It functions
much like the Unix top command and can be
invaluable at all phases of the tuning process to find which MySQL
threads are using the most resources.

Finally, in Appendix C, we discuss
phpMyAdmin, a web-based tool for administration
of a MySQL server. phpMyAdmin can simplify many
of the administrator's routine jobs and allow users
to issue queries against the database without having to build a
client or have shell access to the server.