Databases Reference
In-Depth Information
simplest way to solve problems. For example, if Apache runs out of memory be-
cause it creates 1,000 worker processes that each need 50 MB of memory, you can
configure the application to require fewer Apache workers. You can also configure
the system to use less memory for each process.
• Is the application really using all the data it's getting? Fetching 1,000 rows but
displaying only 10 and throwing away the rest is a common mistake. (However, if
the application caches the other 990 rows for later use, it might be an intentional
optimization.)
• Is the application doing processing that ought to be done in the database, or vice
versa? Two examples are fetching all rows from a table to count them and doing
complex string manipulations in the database. Databases are good at counting
rows, and application languages are good at regular expressions. Use the best tool
for the job.
• Is the application doing too many queries? Object-relational mapping (ORM)
query interfaces that “protect programmers from having to write SQL” are often
to blame. The database server is designed to match data from multiple tables. Re-
move the nested loops in the code and write a join instead.
• Is the application doing too few queries? We know, we just said doing too many
queries can be a problem. But sometimes “manual joins” and similar practices can
be a
good
idea, because they can permit more granular and efficient caching, less
locking, and sometimes even faster execution when you emulate a hash join in
application code (MySQL's nested loop join method is not always efficient).
• Is the application connecting to MySQL unnecessarily? If you can get the data from
the cache, don't connect.
• Is the application connecting too many times to the same MySQL instance, perhaps
because different parts of the application open their own connections? It's usually
a better idea to reuse the same connection throughout.
• Is the application doing a lot of “garbage” queries? A common example is sending
a ping to see if the server is alive before sending the query itself, or selecting the
desired database before each query. It might be a good idea to always connect to
a specific database and use fully qualified names for tables. (This also makes it
easier to analyze queries from the log or via
SHOW PROCESSLIST
, because you can
execute them without needing to change the database.) “Preparing” the connection
is another common problem. The Java driver in particular does a lot of things
during preparation, most of which you can disable. Another common garbage
query is
SET NAMES UTF8
, which is the wrong way to do things anyway (it does not
change the client library's character set; it affects only the server). If your applica-
tion uses a specific character set for most of its work, you can avoid the need to
change the character set by configuring it as the default.
