Query Analysis and Index Tuning 18
The reason this is a full table scan is that there are no suitable indexes to use. We can use the
INFORMATION_SCHEMA table STATISTICS to show all the indexes on the rental table:
mysql> SELECT COLUMN_NAME, INDEX_NAME, SEQ_IN_INDEX AS pos
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA=’sakila’ AND TABLE_NAME=’rental’;
+ + + +
| COLUMN_NAME | INDEX_NAME | pos |
+ + + +
| rental_id | PRIMARY | 1 |
| rental_date | rental_date | 1 |
| inventory_id | rental_date | 2 |
| customer_id | rental_date | 3 |
| inventory_id | idx_fk_inventory_id | 1 |
| customer_id | idx_fk_customer_id | 1 |
| staff_id | idx_fk_staff_id | 1 |
+ + + +
7 rows in set (0.11 sec)
There is no index that includes the return_date field, so add an index to optimize this query:
mysql> USE sakila;
Database changed
mysql> ALTER TABLE rental ADD INDEX (return_date);
Query OK, 16044 rows affected (12.08 sec)
Records: 16044 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT return_date FROM rental\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: index
possible_keys: NULL

key: return_date
key_len: 9
ref: NULL
rows: 16249
Extra: Using index
1 row in set (0.00 sec)
Now the type is index, which means a full scan of an index is being done. The index being
scanned is the
return_date index (key), which we just created, with a length (key_len)of9.
Is there a way to further optimize this query?
Looking at Table 18-1, data access strategy types below
index involve using only parts of an
index. The query we are analyzing returns every value of the
return_date field. Therefore,
there is no way to avoid accessing every value in the
return_date index. mysqld needs to
617
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
access a value in order to return it, and every value is returned, so every value must be accessed.
This need to access every value is also shown by the lack of
Using where in the Extra field.
Index consequences
I
n Chapter 6, we explained how indexes work. Indexes can make data retrieval faster because
they are ordered subsets of data, and can be searched faster than the entire set of data, which
may be ordered differently than an index. There is a cost to maintaining indexes. Data changes are
slower because the data needs to be inserted into the table and any appropriate indexes need to be
updated. An index needs uses disk space, memory, and processing power to stay up to date.
When analyzing queries, remember that there are tradeoffs for actions. Many times, adding an index

will make an application run faster because the query runs faster. However, there are times when
adding an index makes an application run more slowly, because although the
SELECT query runs
faster, the
INSERT, UPDATE,andDELETE queries run more slowly.
It helps to be familiar with the nature of all the queries against the database. If you find that selecting
a field from a table that stores user session information is slow, adding an index may make the
application slower because there are many changes to user session information. From time to time,
you may want to reexamine indexes to ensure that they are being used. An index that is not being
used is a waste of resources.
Optimizing away Using filesort
The Extra value Using filesort is not desirable; it means that mysqld has to pass through
the data an extra time in order to sort it. If the
Extra value Using filesort showsupina
subquery, it is best to optimize the query by eliminating the subquery. In queries that do not
involve subqueries, the
Extra value Using filesort may occur in the EXPLAIN plan for
queries that use
ORDER BY, DISTINCT,andGROUP BY.
ON
the
WEBSITE
ON
the
WEBSITE
More information on how to create and use subqueries can be found on the
accompanying website for this book at www.wiley.com/go/mysqladminbible.
For example, the following EXPLAIN plan is for a query to find the customer name and active
status based on an e-mail lookup, sorted by last name:
mysql> EXPLAIN SELECT first_name, last_name, active

-> FROM customer WHERE email=’’
-> ORDER BY last_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
618
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
table: customer
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 541
Extra: Using where; Using filesort
1 row in set (0.00 sec)
In order to optimize away the Using filesort, you need to have an index that
mysqld can use instead of sorting. In most cases, mysqld can only use one index, so
you will need to have an index that handles both the sorting and the filter of
WHERE
email=’’
:
mysql> ALTER TABLE customer ADD INDEX (email, last_name);
Query OK, 599 rows affected (0.56 sec)
Records: 599 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT first_name, last_name, active
-> FROM customer WHERE email=’’
-> ORDER BY last_name\G
*************************** 1. row ***************************

id: 1
select_type: SIMPLE
table: customer
type: ref
possible_keys: email
key: email
key_len: 203
ref: const
rows: 1
Extra: Using index condition; Using where
1 row in set (0.00 sec)
You have removed the undesirable Extra value Using filesort, and added the desirable
Using index condition. You have also gone from a data access strategy (type)offulltable
scan (
ALL) to one of looking up a nonunique index value (ref).
Often, first instincts may not fully optimize a query. For example, your first instinct in opti-
mizing this query might have been to add an index on only the
email field. This would have
optimized the data access strategy, but the query would still have an
Extra value of Using
filesort
. Having one index for both fields allows mysqld to use that index to optimize
the data access strategy and the filesort. It is always a good idea to test as many optimization
solutions as possible see the sidebar ‘‘Testing ideas.’’
619
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
Testing ideas
I
n the example from the section ‘‘Optimizing away Using filesort,’’ you might have tried to see if

mysqld would use an index on last_name only; if that was your first instinct, you can try out
the following commands to see if the index would work:
ALTER TABLE customer DROP KEY email;
ALTER TABLE customer ADD INDEX (last_name);
EXPLAIN SELECT first_name, last_name, active
FROM customer WHERE email=’’
ORDER BY last_name\G
Sometimes, the first idea you have to optimize a query will not actually optimize the query. In this
case, the index on
last_name does not help because mysqld needs to filter for the WHERE clause
first, before ordering. If
mysqld wastousetheindexonlast_name, it would have to go through
the entire index, and for each row in the index, look up the
email field from the data to see if it
matched. If there were a match, the
last_name would be put in the result set, and the first_name
and active field would be looked up and also put in the result set. Those lookups are a lot of extra
work, and the query optimizer rightfully uses a full table scan, even with an index on
last_name.
There will be other times when the best solution for optimization is not the best solution overall for
the application. In this example, an index was added on (
email, last_name)andtheEXPLAIN
plan showed a key length (key_len) of 203. That is a very large key to keep up to date, and if it
slows down the application, it may be more beneficial to use an index with a shorter length, even
if it means
mysqld hastodoafilesort.
Optimizing away Range checked for each record
As shown in Table 18-2, the Extra value Range checked for each record is faster than
a full table scan (
type: ALL) but slower than a full index scan (type: index). To optimize

queries with this
Extra value, create or modify an index so that the query optimizer has a good
index to use. Often, optimizing queries to get rid of
Range checked for each record results
in a data access strategy (
type)ofrange, ref or eq_ref.
Optimizing away Using temporary
Unlike in previous discussions, optimizing away an Extra value of Using temporary cannot
be done by adding an index.
Using temporary is undesirable, as it means that a temporary
table must be used to store intermediate results. There are several ways to optimize this,
depending on why a temporary table is used:
■ If
ORDER BY and GROUP BY are both present, and use different fields and/or ordering,
the way to optimize this is to get rid of either the
ORDER BY or the GROUP BY.Thismay
620
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
be done by splitting the query into two queries. It may be possible to combine the two
queries by using
UNION so that intermediate results do not need to be stored in a tempo-
rary table.
■ Thepresenceof
ORDER BY and DISTINCT may cause a temporary table to be
used. The way to optimize this is to get rid of either the
ORDER BY or the DISTINCT.This
may be done by splitting the query into two queries. It may be possible to combine the
two queries by using
UNION so that intermediate results do not need to be stored in a

temporary table.
■ If the
SQL_CALC_FOUND_ROWS keyword is used, the number of rows is stored in a tem-
porary table, which can be retrieved by issuing
SELECT FOUND ROWS(). To optimize, get
rid of
SQL_CALC_FOUND_ROWS. Depending on what you are counting, you might count
results periodically and have an estimate for a time period (i.e., run a query every 10 min-
utes to put the number into table and read the table, doing one count every 10 minutes
instead of one count every time the query is issued).
■ The
SQL_SMALL_RESULT keywordisusedinaSELECT statement with DISTINCT or
GROUP BY.TheSQL_SMALL_RESULT keyword is a hint to the optimizer that the result
is small, and thus it should use a temporary table instead of a filesort. To optimize, get rid
of
SQL_SMALL_RESULT. If you need the SQL_SMALL_RESULT keyword because a tempo-
rary table is more desirable than a filesort, then you cannot optimize
Using temporary
away.
If you use optimizer hints, be sure to run periodic testing. Only through periodic test-
ing can you determine whether a temporary table or a filesort is better for your particular
situation.
■
ORDER BY or GROUP BY is used on a field that is not the first table in the join queue (the
first row returned in the
EXPLAIN plan). One way to optimize this query is to change or
eliminate the
ORDER BY clause. Another way would be to change the filter so that the table
order changes.
For example, the following query uses the

customer table first in the join queue, but is
sorting based on
rental_date, a field in the rental table:
mysql> EXPLAIN SELECT first_name, last_name FROM rental
-> INNER JOIN customer USING (customer_id)
-> ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 591
Extra: Using temporary; Using filesort
621
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.customer.customer_id
rows: 13

Extra:
2 rows in set (0.00 sec)
To optimize this query, we could change the ORDER BY to use a field in the customer table,
or we could change the query to use the rental table first in the join queue. Join table
order can be forced by using a join type of
STRAIGHT_JOIN (which cannot use the USING
syntax):
mysql> EXPLAIN SELECT first_name, last_name FROM rental
-> STRAIGHT_JOIN customer ON rental.customer_id=customer.
customer_id
-> ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: index
possible_keys: idx_fk_customer_id
key: rental_date
key_len: 13
ref: NULL
rows: 16291
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2

ref: sakila.rental.customer_id
rows: 1
Extra:
2 rows in set (0.00 sec)
However, this may or may not actually make the query better — Using filesort is
gone, but the data access strategy for the rental table is much slower. In general, using
techniques like index hints and
STRAIGHT_JOIN are dangerous query optimization
622
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
strategies, because changes in the amount of data, the cardinality of data, and the schema
may change the optimal query plan. If you must use these techniques, reassess their
validity every few months and whenever complaints of database slowness arise.
A better way to change the order of the join queue is to limit the rows examined in the
desired table. For example, you can limit the rows examined in
rental table to a certain
range:
mysql> EXPLAIN SELECT first_name, last_name FROM rental
-> INNER JOIN customer USING (customer_id)
-> WHERE rental_date BETWEEN ’2005-01-01 00:00:00’ AND
-> ’2005-01-31 00:00:00’ ORDER BY rental_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: rental_date,idx_fk_customer_id
key: rental_date
key_len: 8

ref: NULL
rows: 1
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.rental.customer_id
rows: 1
Extra:
2 rows in set (0.00 sec)
It is beneficial to optimize away Using temporary because in certain cases, temporary tables
will be written to disk. These situations include: when a temporary table exceeds the smaller
of
tmp_table_size and max_heap_table_size, when a temporary table includes BLOB or
TEXT data types, when DISTINCT or GROUP BY clauses contain fields that use more than 512
bytes, and when any field is more than 512 bytes in a
UNION or UNION ALL query.
Using an index by eliminating functions
Sometimes, an index exists but is not being used. For example, the film table has the following
indexes:
mysql> SELECT COLUMN_NAME, INDEX_NAME, SEQ_IN_INDEX AS pos
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA=’sakila’ AND TABLE_NAME=’film’;
623
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part IV Extending Your Skills
+ + + +
| COLUMN_NAME | INDEX_NAME | pos |
+ + + +
| film_id | PRIMARY | 1 |
| title | idx_title | 1 |
| language_id | idx_fk_language_id | 1 |
| original_language_id | idx_fk_original_language_id | 1 |
+ + + +
4 rows in set (0.01 sec)
However, the following query does not use the index on title, as you might expect it would:
mysql> EXPLAIN SELECT title FROM film WHERE LEFT(title,2)=’Tr’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 953
Extra: Using where
1 row in set (0.00 sec)
The reason for this is that there is an index on title,buttheWHERE clause is filtering based
on a function of the
title field. Values (such as ’Tr’) cannot be compared to a function
(
LEFT(title,2)) using an index in mysqld, unless the index is on the function itself.
Unfortunately,

mysqld does not support an index on functions, and so it is not possible to
define an index on
LEFT(title,2) even if you had the desire.
To optimize this type of query, see if you can take away the function. In this case, you can
replace
LEFT(title,2)=’Tr’ with title LIKE ’Tr%’ to get rid of the function on title.
Just by changing the query to get rid of the function, you can change your data access strategy
from a
type of ALL to a type of range:
mysql> EXPLAIN SELECT title FROM film WHERE title LIKE ’Tr%’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: idx_title
key: idx_title
key_len: 766
ref: NULL
rows: 15
Extra: Using where
1 row in set (0.00 sec)
624
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
This type of optimization is done most frequently to queries involving date ranges.
Compare:
EXPLAIN SELECT inventory_id, customer_id FROM rental
WHERE DATE(return_date)=’2005-05-30’\G
with:

EXPLAIN SELECT return_date FROM rental
WHERE return_date BETWEEN ’2005-05-30 00:00:00’ and ’2005-05-30
23:59:59’
However, there are other ways in which functions can be optimized out of a query. Table 18-4
shows some common optimizations:
TABLE 18-4
Common Ways to Optimize by Eliminating Functions
WHERE clause Function Optimization
LEFT(stringfield) = ’Tr’ stringfield LIKE ’Tr%’
DATE(datefield) = ’2005-05-30’ or
LAST_DAY(field)=’2005-05-30’ or
LEFT(datefield, 10) = ’2005-05-30’
SUBSTRING_INDEX(datefield,’ ’) =
’2005-05-30’
field BETWEEN ’2005-05-30
00:00:00’ AND ’2005-05-30
23:59:59’
ABS(field) > 20 field > 20 or field < -20
field +1>20 field > 19
FLOOR(field)=1 field >= 1ANDfield< 2
CONCAT(field,’day’)=’Saturday’ field=’Satur’
FROM_UNIXTIME(field)=’2005-05-30
00:00:00’
field= 1117425600
LEFT(INET_NTOA(field),10)=’192.168.1.’ field BETWEEN 3232235777 AND
3232236031
You may be wondering why anyone would ever create WHERE clauses like the ones in Table 18-
4. Most of the time it happens because of the way the developer is thinking. Developers write
queries to answer questions, so these types of
WHERE clauses happen when the developer writes

a query to ‘‘find sales on May 30’’ or to ‘‘find distances greater than 20’’. In an ideal world, no
query would be saved to code unless it were optimized. In practice, developers write queries,
625
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
and DBAs optimize queries — if the developer writes a suboptimal query, in many organizations
the DBA will find it only when it slows down the application.
Optimizing the last two queries in Table 18-4 requires some work to retrieve the numerical val-
ues. To optimize
FROM_UNIXTIME(field)=’2005-05-30 00:00:00’, you have to find the
UNIX timestamp value for the datetime. There is a function to do that:
mysql> SELECT UNIX_TIMESTAMP(’2005-05-30 00:00:00’);
+ +
| UNIX_TIMESTAMP(’2005-05-30 00:00:00’) |
+ +
| 1117425600 |
+ +
1 row in set (0.05 sec)
To optimize LEFT(INET_NTOA(field),10)=’192.168.1.’, you first have to figure out what
the query is looking for. This filter finds rows that have
field with the numerical equivalent
of an IP address whose left 10 characters are
’192.168.1.’. Another way to look at the fil-
ter is that it finds rows that have
field with the numerical equivalent of an IP address between
192.168.1.1 and 192.168.1.255.
This new way to look at the data presents you with a way to eliminate the function from the
WHERE clause. If you find the numerical equivalent of the boundary IPs, you can use those in
the
BETWEEN comparison shown in Table 18-4. Again, mysqld has a function that will let you

look those values up:
mysql> select INET_ATON(’192.168.1.1’), INET_ATON(’192.168.1.255’);
+ + +
| INET_ATON(’192.168.1.1’) | INET_ATON(’192.168.1.255’) |
+ + +
| 3232235777 | 3232236031 |
+ + +
1 row in set (0.00 sec)
There are functions that simply cannot be eliminated. For example, it is difficult to eliminate
WHERE clauses such as MOD(field,10)=2 and LENGTH(field)<5.
Non-index schema changes
Sometimes the best way to optimize a query is to change the data structure. Consider the follow-
ing query:
mysql> EXPLAIN SELECT first_name, last_name, email
-> FROM staff
-> WHERE email LIKE ’%sakilastaff.com’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
626
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
table: staff
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where

1 row in set (0.00 sec)
An index on staff would not help, because text indexes work from the beginning of the word
(see Chapter 6). Much like a dictionary, an index is set up to look for words that begin a cer-
tain way, not words that end a certain way. The way to optimize this query would be to have an
index that could look up words that end a certain way.
Such an index does not exist. However, what you can do is add a field to the table that contains
the text reversed, with a regular index on that. For example:
mysql> ALTER TABLE staff ADD COLUMN revemail VARCHAR(50) DEFAULT
NULL,
-> ADD INDEX (revemail);
Query OK, 2 rows affected (0.38 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UPDATE staff SET revemail=REVERSE(email);
Query OK, 2 rows affected (0.08 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT email, revemail FROM staff;
+ + +
| email | revemail |
+ + +
| | |
| | |
+ + +
2 rows in set (0.03 sec)
mysql> EXPLAIN SELECT first_name, last_name, email
-> FROM staff
-> WHERE email LIKE ’%sakilastaff.com’\G
You can use the REVERSE() function to show you what the comparison string should be, and
then run an
EXPLAIN to see if the new field and index help:
mysql> SELECT REVERSE(’%sakilastaff.com’);

+ +
| REVERSE(’%sakilastaff.com’) |
+ +
| moc.ffatsalikas% |
+ +
1 row in set (0.00 sec)
627
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
mysql> EXPLAIN SELECT first_name, last_name, email
-> FROM staff
-> WHERE revemail LIKE ’moc.ffatsalikas%’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: staff
type: range
possible_keys: revemail
key: revemail
key_len: 203
ref: NULL
rows: 1
Extra: Using index condition; Using MRR
1 row in set (0.00 sec)
You have optimized the query to have a data access strategy (type)ofrange.Youalsohavethe
desirable
Extra values Using index condition and Using MRR — see Table 18-2 for details.
Note that having an extra field also means upkeep of the field. The best way to upkeep the field
would be to have any code that inserts or updates the
email field also update the new reve-

mail
field. Another option is to create triggers to update the revemail field when the email
field is changed. See Chapter 7 for more information about triggers.
Batching expensive operations
INSERT and DELETE operations need to update indexes, and DELETE operations can cause frag-
mentation. These operations are particularly expensive on MyISAM tables, as the table blocks
all other reads and writes until the
INSERT and DELETE operations are complete. One way to
optimize
INSERT queries on MyISAM is to use the system variable concurrent_insert,which
allows some nonblocking
INSERT operations. Another way to optimize these queries, regardless
of storage engine, is to batch expensive
INSERT and DELETE operations.
INSERT operations can be batched by using the INSERT DELAYED syntax. For more information
on
INSERT DELAYED, see Chapter 4.
To batch
DELETE operations and reduce fragmentation, use a table for deleted records:
CREATE TABLE deleted_customer (
customer_id smallint(5) unsigned NOT NULL PRIMARY KEY
ENGINE=InnoDB DEFAULT CHARSET=utf8;
When a customer is deleted, they are flagged for deletion by inserting their customer_id into
the
deleted_customer table. When a query looks for nondeleted customers, it can use a very
fast join with the desirable
Not exists value for Extra:
mysql> EXPLAIN SELECT first_name, last_name
-> FROM customer LEFT JOIN deleted_customer USING (customer_id)
-> WHERE deleted_customer.customer_id IS NULL\G

628
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 579
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: deleted_customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.customer.customer_id
rows: 1
Extra: Using where; Using index; Not exists
2 rows in set (0.02 sec)
Because DELETE operations are expensive, a batch DELETE should be scheduled for nonpeak
times. After deleting rows from the customer table, defragment the table. Table defragmentation
will lock the table with a write lock, blocking all reads and writes, but that is another reason to
perform batch deletions during nonpeak times. The batch deletion is done by running:

DELETE customer.* FROM customer
INNER JOIN deleted_customer USING (customer_id);
TRUNCATE TABLE deleted_customer;
OPTIMIZE TABLE;
Even though the table join when retrieving nondeleted customer information is fast, it is more
overhead. However, because expensive
DELETE operations are batched, overall the application
will run much faster if problems are caused by having many
DELETE operations.
Optimizing frequent operations
There may be a database query that is run extremely frequently. Even if it is not a particularly
slow or expensive query, it is contributing to load on your servers. Example queries retrieve
information such as:
■ The number of currently online customers
■ The total number of active customers
629
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
■ Rankings
■ Tag clouds
■ How many articles or other resources appear on the site
■ How many comments an article has
■ Up-to-the-second calculations of account balances
These queries can often be batched. For example, do your customers need the exact
up-to-the-second number of logged-in customers? This may be a number displayed on every
web page, including pre-login pages, to get people to log in and stay logged in. It may be
sufficient in these cases to calculate the number of logged-in customers once every 5 minutes.
Depending on the usage of your application, calculating rankings, number of comments on an
article, and account balances may make more sense to be denormalized instead of calculated on
the fly. For example, the following query lists customers in order from those who have paid the

most to those who have paid the least:
mysql> EXPLAIN SELECT first_name, last_name, SUM(amount) AS
total_paid
-> FROM customer INNER JOIN payment USING (customer_id)
-> GROUP BY customer_id ORDER BY total_paid DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 591
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.customer.customer_id
rows: 14
Extra:
2 rows in set (0.00 sec)
If this query is run every time a report is accessed, it may be running quite often, depending on
who can access the reports! Rankings, number of comments per article, and tag cloud calcula-

tions are frequently run at a rate higher than the data actually changes.
630
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Query Analysis and Index Tuning 18
One way to optimize reporting queries and other calculations that are run more frequently than
the data changes is to denormalize the data, using new field(s) or table(s) to store the relevant
calculations. When the data is changed, the denormalized field(s) must also be updated. This
can be done by changing your application code or by adding a trigger. For more on triggers, see
Chapter 7.
Another way to optimize these types of queries is to run them periodically, storing the results
in the database, file, or globally accessible application-wide variable. This can pose problems
if users are expected to seeing up-to-the-second current results, so try to train your customers
to expect recent, but not necessarily up-to-the-second, results. If there is resistance to changing
user expectations, calculating features such as rankings periodically can be a great marketing
tool. Customers will likely log in more often if their rankings change every 4–6 hours, instead
of immediately after each action they take.
Summary
In this chapter, you have learned how to use EXPLAIN to generate a query plan. How data and
data changes over time can affect the query plans has been discussed. You have explored how to
make a query plan better by:
■ Adding indexes
■ Changing the size and type of data fields
■ Adding new data fields
■ Moving indexed fields out of functions
■ Limiting the use of temporary tables
■ Batching expensive and/or frequent queries
■ Periodically calculating frequent queries
631
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Monitoring Your Systems
IN THIS CHAPTER
Deciding what to monitor
Examining open source
monitoring
Examining commercial
monitoring
I
n Chapter 17, we discussed how to measure database performance.
By taking regular measurements of the health of your systems, you
can learn about database problems before they are happening, send a
message as they are happening, and have information to help debug after
they happen. This type of automated monitoring can make your job eas-
ier, whether monitoring alerts you when a system is down, or gives you
the information necessary for capacity planning.
It is surprising how many organizations do not have any automated
alerting, because ‘‘when the database is down, we know because users call
us.’’ Alerting frameworks are easy to set up and use and can let you know
not only about current problems, but alert you to what may be a problem
in the future. For example, a full disk will cause
mysqld to hang and
may cause corrupt data and log files. Alerting when a disk is approaching
capacity can save a lot of problems — you can clear out some space and
make sure the disk does not become full.
Sending an alert is as easy as sending an e-mail; most pagers and cell
phones have e-mail addresses that can be set up to send the device a
message. It may be tempting to schedule scripts that, when they fail, send
an e-mail. However, there is a lot more flexibility in alerting systems that
do not take much time to set up. Alerting systems come bundled with
basic checks, and the best ones are ones where you can write your own

checks and easily integrate them into the alerting framework.
Even in organizations that have alerting, graphing trends and patterns ends
up being a low priority. However, organizations that graph data are better
able to handle alerts, find the root causes of problems, and predict when
future problems may occur. Though graphs are not necessarily examined
daily, they are an immense help when they are examined.
633
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
It should be pointed out when examining the output of whatever program that you
use for monitoring that, as an example, just because your systems are operating at an
average of 10% of resource utilization that you expect the systems to be able to operate with 10
times that load. The reason why is that systems scaling is never a linear issue. When you hit 20%
utilization you might uncover a bottleneck that keeps your systems from working well until you
resolve the problem.
We will discuss some of the widely-used open source monitoring tools, as well as the MySQL
Enterprise Monitor that is part of paid support for MySQL, and MONyog, another commercially
available monitoring tool. While all of the solutions presented in this chapter are suitable for
production use in an enterprise environment, you may find that some are more suitable than
others for your environment. For example, many of the tools depend heavily on SNMP (the
Simple Network Management Protocol), which some organizations deem unsecure. Other
organizations may require an agent-less protocol, or that the monitoring tool(s) run on the
Windows operating system. The many solutions provided in this chapter reflect the needs of
different organizations.
Deciding What to Monitor
Monitoring, like documentation, is an ongoing process. New features in mysqld and changes in
database use may require changing what is monitored. Monitoring adds overhead to your sys-
tems and takes time to implement and maintain, so monitor everything is not always desirable.
Exactly what you should monitor is different for different environments. Each item you monitor
must have a purpose for monitoring — for example, you may monitor replication lag to be able

to see trends in how far behind replication lags. You may also monitor replication lag to alert
you when replication lags beyond a certain point, as that may indicate a bigger problem.
Thinking of the reasons for monitoring can help you figure out what you need to monitor. Some
reasons for monitoring are:
■ Alerting when:
■ The database is unavailable
■ A database error occurs
■ InnoDB tablespaces are running out of space
■ The file system is running out of space (
mysqld hangs when it cannot write to a log
or data file)
■ The file system is running out of memory
■ A query has been running for a long time
■ There are many connections, approaching max_connections
■ The database response time is unacceptable
634
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Your Systems 19
■ Replication is lagging behind
■ Temporary disk tables get very large
■ Slow query rate is high
■ Number of sleeping queries is high
■ Graphing and seeing trends/patterns for:
■ Database/system uptime
■ InnoDB buffer pool efficiency
■ Query cache efficiency
■ How much disk space the database uses
■ How many users your application has
■ Table locks
■ How often tables in a database are changed

■ File I/O caused by the database
■ Number and size of reads/writes/transactions
■ Replication lag
■ Table cache efficiency
■ Number of connections and their states (sleeping, copying to temp table, query)
■ Frequency of query joins, sorts, full table scans, range scans
■ Temporary table and temporary disk table usage
This list is not exhaustive, but it is a good start to thinking about what you might want to moni-
tor. Note that the items on the list above point to more than simply monitoring database param-
eters. The best monitoring solutions take into account the database, the server, the application,
and other architectural pieces of the system such as load balancers and switches.
Many organizations run an excellent alerting solution (such as Nagios) and an excellent graphing
solution (such as Cacti), though this has the overhead and duplication that information about
what hosts and services to monitor is in more than one place. However, the alternative is an
all-in-one solution that is not as strong in both alerting and graphing.
When deciding what to use for your monitoring needs, consider how the organization as a
whole can benefit from your choices. It is not uncommon for a DBA to install monitoring
on their systems, and have a systems administrator say ‘‘can you just monitor Apache on the
webservers, too?’’ Small projects can grow, and with the right technologies in place, you can
start the ball rolling on a monitoring system that will be able to see details such as whether or
not a server is running, as well as overall pictures such as how many concurrent users your
application can withstand.
Monitoring systems can be divided into two groups: agent-based and agentless. With an
agent-based system a program (agent) is installed on the host server being monitored that is
635
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
continually running as a service (on Windows) or a daemon (on Unix-based servers). This agent
monitors the local system and communicates back to a central server. Agentless monitoring,
on the other hand, does not require an additional agent to be running on the local server.

Monitoring may be done remotely through protocols such as SSH and SNMP, or locally via a
scheduled task or cronjob.
An agent can often give more visibility of the inner workings of the monitored server. However,
an agent requires maintenance of a distributed program, and may use more system resources on
the monitored server than agentless monitoring. If there are hundreds of servers, installation and
upgrades can be quite time consuming. With agentless monitoring, you can have a very quick
rollout of monitoring services for even large numbers of servers.
Examining Open Source Monitoring
The most popular monitoring systems are open source frameworks. Though Nagios is the most
popular alerting system and Cacti is the most popular graphing system, there are other moni-
toring systems that are good enough to deserve mention. This section will discuss the following
monitoring frameworks:
■ Nagios
■ Cacti
■ Hyperic HQ
■ OpenNMS
■ Zenoss core
■ Munin
■ Monit
Nagios
One of the most widely used open source alerting systems is Nagios (pronounced
‘‘NAH-ghee-ose’’). Nagios was originally developed for use under Linux, though it can be
used under other variants of Unix, including BSD systems, Solaris and Mac OS X. Nagios can
monitor Windows servers, but the centralized Nagios server does not run natively on Windows.
Nagios does not do graphing or trending very well. The web interface is written in PHP, and the
checks are mostly Perl and shell scripts, though checks can be a script in any language that can
run from the command line.
Many operating system distributions include Nagios; if yours does not, it can be downloaded
at
www.nagios.org, where there is also extensive documentation. The main features of

Nagios are:
■ Graphical web-based user interface
■ Convenient dashboard overview for overall health at-a-glance
■ Supports host, service and network monitoring
636
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Your Systems 19
■ Supports agent-less checks and client-server agents (a daemon running on each client
machine checked sends information back to the centralized server)
■ Supports agent-less checking of cloud computing environments. Supports agent-based
checking if the agent is installed in the cloud computing environment.
■ Text file based configuration (there is a separate nagiosql package that makes the configu-
ration web-based)
■ Extensive user community, including many free third-party plugins (
www.
nagiosexchange.org
)
■ Define custom alerting scripts, with four possible statuses
■ Define custom actions based on status
■ Extremely customizable user permissions
■ Web interface authorization for security
■ Servers can be easily grouped
■ History and basic reporting of alert status
■ Which machines are checked, when they are checked, who is notified and what actions
are taken are all configurable in almost any way possible — you can specify that a machine
is not checked Sundays from 3 pm to 4 pm, or a group of machines should e-mail alerts
from 6 pm to 8 am and send pages from 8 am to 6 pm.
■ One-off downtimes can be scheduled
■ Custom paging escalations
■ Acknowledgement of a problem can be optional or required

■ Dependencies can be set up, reducing pager storms when a parent service goes down.
■ Very basic network topology map
■ Screenshots of Nagios are available at
www.nagios.org/about/screenshots.php
■ Virtual appliance for VMWare are available at www.vmware.com/appliances/
directory/372
All this flexibility and customizability comes with a learning curve. However, it is not a
large learning curve, and there is a ‘‘quickstart’’ guide to setting up Nagios in 15 minutes at
0/quickstart.html. There is also a large user
community available to help.
Cacti
Cacti is a widely-used PHP time-series graphing tool with a centralized server that can be
installed on Unix, Mac OS X and Windows. Data is gathered via SNMP. Cacti is available from
www.cacti.net.
The main features of Cacti are:
■ Graphical web-based user interface
■ Convenient dashboard overview for overall health at-a-glance
637
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
■ Supports host, service and network monitoring
■ Agent-less monitoring
■ Due to agent-less monitoring, implicitly supports checking of cloud computing environ-
ments
■ Web-based configuration management
■ Extensive user community, including many free templates (
ti.
net/about15067.html
)
■ Define custom data-gathering scripts

■ Define custom graphs
■ Extremely customizable user permissions
■ Web interface authorization for security
■ User-defined preferences
■ History and extensive reporting of data gathered
■ Servers can be easily grouped
■ View many graphs at once
■ View multiple servers on a graph
■ View multiple services on a graph
■ Customizable graph type (line, area, stack, etc) and graph order
■ Easy drag-and-drop to zoom on any graph
■ View/edit raw data
■ Screenshots of cacti are available at
www.cacti.net/screenshots.php
■ Virtual appliances for VMWare, Parallels, Qemu, KVM, VirtualIron and Microsoft Virtual
PC are available at
/>There are plugins to Cacti which give basic alerting functionality, but nowhere near the flexibil-
ity of Nagios. However, Cacti is the most comprehensive open source graphing tool available.
There is also no way to define a custom set of times for when to gather data for graphing
with Cacti.
Baron Schwartz, MySQL community member and co-author of High Performance MySQL, 2
nd
edition, developed a set of Cacti templates for monitoring MySQL (www.xaprb.com/blog/
tag/cacti-templates/
).
Hyperic HQ
Hyperic HQ is a Java-based comprehensive graphing solution, with some alerting features. It is
available for download from
www.hyperic.com. Binaries for Windows, Solaris and Linux are
638

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Your Systems 19
available, and there is also a platform-independent version, which uses the Java Runtime Envi-
ronment (JRE) already installed on your system. Hyperic HQ can run SNMP checks, but is not
dependent on SNMP.
The main features of Hyperic HQ are:
■ Graphical web-based user interface
■ Convenient dashboard overview for overall health at-a-glance
■ Supports host, service and network monitoring
■ Supports client-server agents (a daemon running on each client machine checked sends
information back to the centralized server)
■ Explicitly supports checking of cloud computing environments including Amazon Web
Services and Google App Engine.
■ Auto-discovery of services on a host (once an agent is deployed)
■ Web-based configuration management
■ Many built-in checks, including hundreds of MySQL checks. See
www.hyperic.com/
products/managed/mysql-management.htm
for the impressive list of metrics on
reliability, transactions, connections, SQL commands, configuration changes, resource
utilization, queries, I/O, and tables.
■ Define custom alerting scripts
■ Manually perform
ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE and REPAIR TABLE
from the Hyperic HQ interface
■ History and extensive reporting of data gathered
■ Define custom data-gathering scripts
■ Define custom graphs
■ Servers can be easily grouped
■ View multiple servers on a graph

■ View multiple services on a graph
■ Custom paging escalations
■ Acknowledgement of a problem can be optional or required
■ Dependencies can be set up, reducing pager storms in the event that a parent service goes
down
■ Screenshots available at
www.hyperic.com/demo/gallery.html
■ VMWare virtual appliance version
Hyperic does have a user community and user-contributed plugins, though the user community
is not as large as that of Nagios. There are not many user-contributed plugins, but that is likely
639
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part IV Extending Your Skills
due to the comprehensive nature of Hyperic’s built-in checks. There is an Enterprise edition for
purchase that adds in many features including automated failover, more flexible alerting, auto-
mated corrective actions, scheduled maintenance, and role-based access control.
Hyperic HQ is an appropriate solution for an organization that requires graphing and alerting
to be a part of the same application. Although the alerting functionality in the non-Enterprise
version is similar to that of Cacti’s capabilities, Hyperic HQ has more out-of-the-box graphs for
MySQL.
OpenNMS
The open network management system, or OpenNMS, is a distributed graphing and alerting sys-
tem. Written in Java, XML and XSL, it is available from
www.opennms.org for Linux, Solaris,
BSD, Mac OS X, and Windows. OpenNMS checks a number of protocols, though it is depen-
dent on SNMP for many checks.
The main features of OpenNMS are:
■ Graphical web-based user interface
■ Convenient dashboard overview for overall health at-a-glance
■ Supports host, service and network monitoring

■ Auto-discovery of services on a host or IP address range
■ Web-based configuration management
■ Agent-less monitoring
■ Define custom data-gathering scripts
■ Define custom graphs
■ Define custom alerting scripts
■ Define custom actions based on status
■ Extremely customizable user permissions
■ Web interface authorization for security
■ User-defined preferences
■ History and extensive reporting of data gathered
■ Servers can be easily grouped
■ View many graphs at once
■ Zoom by time-period on any graph
■ Active user community
■ Custom paging escalations
■ Acknowledgement of a problem can be optional or required
640
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Monitoring Your Systems 19
■ Dependencies can be set up, reducing pager storms in the event that a parent service
goes down
■ Extensive network topology map
■ Official demo available from
(username:
demo, password: demo)
OpenNMS does not seem to have many custom checks, and it is unclear whether that is because
SNMP can handle most of the checks, or if there is no easy way such as a script, plug-in or tem-
plate to share a check.
Zenoss Core

Zenoss Core is a Python-based monitoring system that runs on Linux, Solaris, BSD
and Mac OS X environments. Data can be gathered via SNMP or via the Zenoss Plu-
gins, platform-specific python monitoring scripts. Zenoss Core can be downloaded at
www.zenoss.com/download/links.
The main features of Zenoss Core are:
■ Graphical web-based user interface
■ Convenient dashboard overview for overall health at-a-glance
■ Supports host, service and network monitoring
■ Supports agentless checks and client-server agents (a daemon running on each client
machine checked sends information back to the centralized server)
■ Supports agent-less checking of cloud computing environments. Supports agent-based
checking if the agent is installed in the cloud computing environment.
■ Auto-discovery of services on a host
■ Web-based configuration management
■ Very active user community, with many user-contributed customizations
(
www.zenoss.com/community/wiki/tips-and-tricks/)
■ Define custom data-gathering scripts
■ Define custom graphs
■ Define custom alerting scripts
■ Define custom actions based on status
■ Extremely customizable user permissions
■ Web interface authorization for security
■ History and extensive reporting of data gathered
■ Servers can be easily grouped
■ View many graphs at once
641
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.