لطفا منتظر باشید ...
15.9 Tuning MySQL
To
conclude this chapter, we show you selected techniques for improving
the performance of your databases, queries, and MySQL server. We
focus on how to choose and design indexes, tips for querying and
database design, how to tune the server parameters, and how to use
MySQL''s query cache.
15.9.1 Index Design
As discussed in Chapter 5, each table should have a PRIMARY
KEY definition as part of its CREATE
TABLE statement. A primary key is an attribute (or set of
attributes) that uniquely identifies a row in a table. Storing two
rows with the same primary key isn''t permitted and
an attempt to INSERT duplicate primary keys
produces an error (unless you use the IGNORE
modifier).
The attribute values of the primary key are stored in an
index to allow fast access to a row using the
primary key values. The default index type for a MyISAM table type is
fast for queries that find a specific row, a range of rows, for joins
between tables, grouping data, ordering data, and finding minimum and
maximum values. Indexes don''t provide any speed
improvement for retrieving all the rows in a table or for other query
types.
As discussed briefly in Chapter 5, indexes are
also useful for fast access to rows by values other than those in the
primary key. For example, in the customer table,
you might define an index by adding it using:
ALTER TABLE customer ADD INDEX namecity (surname,firstname,city);
After you define this index, some queries that select a particular
customer through a WHERE clause automatically use
it. Consider an example:
SELECT * FROM customer WHERE surname = ''Marzalla''
AND firstname = ''Dimitria'' AND city = ''St Albans'';
This query can use the new index to quickly locate the row that
matches the search criteria. Without the index, the server must scan
all the rows in the customer table and compare
each row to the WHERE clause. This might be quite
slow and certainly requires significantly more disk activity than the
index-based approach (assuming the table has more than a few rows).
A particular feature of database servers is that they develop a query
evaluation strategy and optimize it without any interaction from the
user or programmer. If an index is available, and it makes sense to
use it in the context of a query, the server does this automatically.
All you need to do is identify which
queries are common, and make an index
available for those common queries by adding the
KEY
clause to the
CREATE
TABLE statement or using ALTER
TABLE on an existing table.
If you''ve created the namecity
index, and you want to check that MySQL will use it for the previous
query, you can do so with the EXPLAIN statement:
EXPLAIN SELECT * FROM customer WHERE surname = ''Marzalla''
AND firstname = ''Dimitria'' AND city = ''St Albans'';
This reports that:
+----+-------------+----------+------+---------------+----------+---------+----
---------------+------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+----------+------+---------------+----------+---------+----
---------------+------+--------+
| 1 | SIMPLE | customer | ref | namecity | namecity | 153 | const,
const,const | 1 | Using where |
+----+-------------+----------+------+---------------+----------+---------+----
---------------+------+--------+
1 row in set (0.06 sec)
You can see that the namecity index is listed as a
possible choice in the possible_keys column and,
as expected, it''s the index that''ll
be used to evaluate the query as shown in the key
column. The EXPLAIN
statement is a useful diagnostic tool
for understanding and optimizing complex queries but we
don''t discuss it in detail here; you can find out
more about it in Section 5.2.1 of the MySQL manual.
Careful index design is important. The namecity
index we have defined can also speed queries other than those that
supply a complete surname,
firstname, and city. For
example, consider a query:
SELECT * FROM customer WHERE surname = ''LaTrobe'' AND
firstname = ''Anthony'';
This query can also use the index namecity,
because the index permits access to rows in sorted order first by
surname, then firstname, and
then city. With this sorting, all
"LaTrobe, Anthony" index entries
are clustered together in the index. Indeed, the index can also be
used for the query:
SELECT * FROM customer WHERE surname LIKE ''Mar%'';
Similarly, all surnames beginning with
"Mar" are clustered together in the
index. You can use EXPLAIN to check that the index
is being used.
However, the index can''t be used for a query such
as:
SELECT * FROM customer WHERE firstname = ''Dimitria'' AND
city = ''St Albans'';
The index can''t be used because the leftmost
attribute named in the index, surname,
isn''t part of the WHERE clause.
In this case, all rows in the customer table
must be scanned and the query is much slower (again assuming there
are more than a few rows in the customer table,
and assuming there is no other index that could be used).
|
There are other cases in which an index can''t be
used, such as when a query contains an OR that
isn''t on an indexed attribute:
SELECT * FROM customer WHERE surname = ''Marzalla'' OR zipcode = "3001";
Again, the customer table must be completely
scanned, because the second condition,
zipcode="3001", requires all rows to be retrieved
as there is no index available on the attribute
zipcode. Also, the attributes that are combined
together with OR must be the leftmost attributes
in the index; otherwise the query requires a complete scan of the
customer table. The following example requires a
complete scan:
SELECT * FROM customer WHERE firstname = ''Dimitria'' OR
surname = ''Marzalla'';
If all the attributes in the index are used in all the queries, to
optimize index size, the leftmost attribute in the
KEY clause should be the attribute with the
highest number of duplicate entries.
Because indexes speed up queries, why wouldn''t you
create indexes on all the attributes you can possibly search on? The
answer is that while indexes are fast for searching, they consume
space and require updates each time rows are added or deleted, or key
attributes are changed. So, if a database is largely static,
additional indexes have low overheads, but if a database changes
frequently, each additional index slows down the update process
significantly. In either case, indexes consume additional space on
disk and in memory, and unnecessary indexes should be avoided.
One way to reduce the size of an index and speed updates is to create
an index on a prefix of an attribute. Our namecity
index uses considerable space: for each row in the
customer table, an index entry is up to 150
characters in length because it is created from the combined values
of the surname, firstname, and
city attributes.[2]
[2] This
isn''t the space actually required by an index entry,
because the data is compressed for storage. However, even with
compression, the fewer characters that are indexed, the more compact
is the representation, the more space is saved, and (depending on the
usability of the index) the faster searching and updates are.
To reduce space, you can define the index as:
ALTER TABLE customer ADD INDEX namecity
(surname(10),firstname(3),city(2));
This uses only the first 10 characters of surname,
3 of firstname, and 2 of city
to distinguish index entries. This is quite reasonable, because 10
characters from a surname distinguishes between most surnames, and
the addition of a few characters from a first name and the prefix of
their city should be sufficient to uniquely identify almost all
customers. Having a smaller index with less information can also mean
that queries are actually faster, because more index information can
be retrieved from disk per second, more of the index can fit into
spare memory, and disk retrieval speed is almost always the
bottleneck in query performance.
The space saving is significant with a reduced index. A new index
entry requires only 15 characters, a savings of up to 135 characters,
so index insertions, deletions, and modifications are now likely to
be much faster. Note that for TEXT and
BLOB attribute types, a prefix must be taken when
indexing, because indexing the entire attribute is impractical and
isn''t permitted by the MySQL server.
15.9.2 Design Tips
Careful
index design is one technique that improves speed and reduces
resource requirements. However, design of your database, tables,
attributes, and queries is also important. As discussed previously,
accessing a hard disk is slow and is usually the bottleneck in
database server performance. Therefore, most techniques described in
this section improve performance by minimizing disk space and disk
use.
Reducing disk space requirements improves both disk seek and read
performance. Disk read performance is improved because less data is
required to be transferred, while seek performance is improved
because the disk head has to move less on average when randomly
accessing a smaller file than when accessing a larger file.
Here are some simple ways to improve database server performance:
Carefully choose attribute types and lengths. Where possible, use
small variants such as SMALLINT or
MEDIUMINT rather than the regular choice
INT. When using fixed-length attributes, such as
CHAR, specify a length that is as short as
possible.
Use fixed-length attributes: try to avoid the types
VARCHAR, BLOB, and
TEXT. While fixed-length text attributes may waste
space, scanning fixed-length rows is much faster than scanning
variable-length rows (and, as discussed in Section 15.6. the MyISAM table type
adjusts its structure for speed when variable-length types
aren''t used).
MySQL can''t join tables using an index if the
attributes are different types or (in some cases) have different
lengths. You should use ALTER TABLE to change the
attribute types and lengths so that they match. However, MySQL can
join char and varchar
attributes, as long as they have the same declared length. You can
check what''s happening with the
EXPLAIN statement.
Create a statistics table if aggregate functions such as
COUNT( ) or SUM( ) are
frequently used in queries that contain WHERE
clauses and are on large tables. A statistics table usually stores
only one row that is manually updated with the aggregate values of
another table.
For example, suppose you want to create a statistics table that
tracks the number of rows in the customer table.
You would create it with the following statement:
CREATE TABLE custCount (custCount int(5));
It doesn''t need a primary key because
there''s only going to be one row of data in the
table. You would then initialize the statistics table to the current
count of customers using:
INSERT INTO custCount SELECT count(*) FROM customer;
From then on, you''d use the new table to check the
count of customers. For example:
SELECT custCount from custCount;
If a row is deleted from the customer table, you
need to update the statistics table:
UPDATE custCount SET custCount = custCount - 1;
Similarly, if a new row is added, you add one to the counter. For
large tables, this technique is often faster than calculating
aggregate functions with the slow built-in functions that require
complete processing of all rows.
If you''re inserting large numbers of rows, list the
values in one (or few) insert statements as this is much faster to
process. For example, convert:
INSERT INTO table (1, "cat");
INSERT INTO table (2, "dog");
into:
INSERT INTO table (1, "cat"), (2, "dog");
If large numbers of rows are deleted from a table, or a table
containing variable-length attributes is frequently modified, disk
space may be wasted. MySQL doesn''t usually remove
deleted or modified data; it only marks the location as being no
longer in use. Wasted space can affect access speed.
To reorganize a table, use the OPTIMIZE
TABLE command discussed in Section 15.3.3. It should be used
periodically (perhaps once per month).
MySQL uses statistics about a table to make decisions about how to
optimize each query. You can update these statistics by running:
ANALYZE TABLE customer;
You don''t need to do this often.
Use the Heap table type discussed in "Table
Types" for small tables that are searched only for
exact matches using = or
<=>.
Section 5.2.13 of the MySQL manual includes other excellent ideas for
simple performance improvement.
15.9.3 Server Tuning Tips
Comprehensive database tuning is a complex topic that fills many
books. We include in this section only a few practical ideas to help
you to begin to improve the performance of a database system. You can
refer to the books in Appendix G for more
information and also read Section 5.5 of the MySQL manual.
MySQL includes is the
mysqladmin
tool for database administration. Details
of the system setup can be found by running the following command in
a Unix shell:
% /usr/local/mysql/bin/mysqladmin -uroot -ppassword variables
In Microsoft Windows, type the following into the Run dialog
that''s accessible through the Start menu:
"C:\Program Files\EasyPHP1-7\mysql\bin\mysqladmin.exe" -uroot
-ppassword variables
Both commands assume you''ve followed our
installation instructions in Appendix A
through Appendix C.
This shows, in part, the following selected system parameters:
join_buffer current value: 131072
key_buffer current value: 8388600
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097144
table_cache current value: 64
Some of the important parameters are those that impact disk use.
MySQL has several main-memory buffer parameters that control how much
data is kept in memory for processing. These include:
The record_buffer for scanning all rows in a table
The sort_buffer for ORDER BY
and GROUP BY operations
The key_buffer for storing indexes in main memory
The join_buffer for joins that
don''t use indexes
In general, the larger these buffers, the more data from disk is
cached or stored in memory and the fewer disk
accesses are required. However, if the sum of these parameters is
near to exceeding the size of the memory installed in the server, the
operating system will start to swap data between disk and memory, and
the MySQL server will be slow. In any case, careful experimentation
based on the application is likely to improve server performance.
Section 5.5.2 of the MySQL manual suggests parameter settings when
starting the MySQL server. First, for machines with more than 256 MB
of free memory, large tables in the database, and a moderate number
of users, start your MySQL in Unix with:
% /usr/local/mysql/bin/mysqld_safe -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O read_buffer_size=1M &
The following setting is appropriate for an application such as the
online winestore, because many users are expected, the queries are
largely index-based, and the database is small:
mysqld_safe -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K &
There are two other parameters used in this example that
we''ve not discussed. The
table_cache parameter manages the maximum number
of open tables per user connection, while the
net_buffer parameter sets the minimum size of the
network query buffer in which incoming queries are kept before they
are executed.
The SHOW STATUS and SHOW
VARIABLES commands that are described at the beginning of
this chapter can be used to report on MySQL''s use
and behavior. SHOW VARIABLES does the same thing
as the mysqladmin command at the beginning of
this section. SHOW STATUS gives a brief
point-in-time summary of the server status and can help find more
about the number of user connections, queries, and table use. This is
useful input into changing the startup parameters
we''ve just described.
15.9.4 Query Caching
MySQL 4 features an optional query
cache. When you activate it, the results of queries are stored in a
memory buffer. If an identical query arrives later, the results are
returned from the cache rather than the query being rerun. This is an
excellent feature if your application runs many identical queries and
your database doesn''t change too often. Typically,
this makes it an ideal tool for web database applications, and we
recommend you use it for your applications; if you followed our Unix
installation instructions in Appendix A
through Appendix C, you''ve already
enabled the query cache with default parameters.
Consider an example. In our online winestore, the following query is
executed every time any user visits the homepage. The query finds the
latest three wines that have been stocked at the winestore and have
been reviewed by a wine writer:
SELECT wi.winery_name, w.year, w.wine_name, w.wine_id, w.description
FROM wine w, winery wi, inventory i WHERE w.winery_id = wi.winery_id
AND w.wine_id = i.wine_id AND w.description IS NOT NULL
GROUP BY w.wine_id ORDER BY i.date_added DESC LIMIT 3;
The query isn''t fast to run: it uses three tables,
two join conditions, a conditional check for a description, a
GROUP BY clause, an ORDER BY
clause, and the LIMIT modifier. However,
it''s an ideal candidate for query caching: the
homepage is popular and new wines, wine reviews, and inventory are
infrequently added. From simple experiments with our online winestore
from the first edition of this book, we''ve found
that adding query caching makes visiting the homepage almost three
times faster.
Query caching has several features:
It''s configurable so that you can control which
queries are cached when you write the queries (more later in this
section).
It can be tuned: you can control how much memory is used in total,
the maximum size of a result set in the cache, and the size of memory
blocks that are allocated in the cache (more on this next).
It''s sensitive to data or table structure changes:
if they change, any query that uses them is automatically flushed
from the cache.
However, there are some situations in which it isn''t
useful and you need to be careful:
If you rarely execute the same query, most queries are first checked
against the cache and then executed anyway. This adds an overhead to
most queries.
If your data changes frequently, the cached queries have to be
frequently flushed from the cache, and this slows querying down. If
any row changes in a table, a query using that table is flushed (even
if it doesn''t access the row).
To use the cache, a query must be identical byte for byte with a
query that''s in the cache. So, for example,
SELECT * FROM customer and SELECT * FROM
CUSTOMER are treated as different queries, and the latter
won''t find the former in the cache. Even additional
whitespace renders two queries different.
Queries that use functions that should return different results
don''t use the cache. Most of these are obvious:
system time functions, system date functions, locking functions, and
so on. A complete list is in Section 6.9.1 of the MySQL manual.
15.9.4.1 Configuring query caching
Query caching is off by default when you install MySQL 4.1. To turn
it on, you need to edit your MySQL configuration file that you
installed when following the installation procedure in Appendix A through Appendix C. In
a Unix environment, edit the file /etc/my.cnf,
and in Microsoft Windows edit the my.ini file in
C:\winnt for Windows 2000/2003/NT or
C:\windows for Windows XP. Find the section that
beings with the heading:
# The MySQL server
[mysqld]
Add the following statements to the end of that section:
query_cache_size = 16M
query_cache_type = 1
query_cache_min_res_unit = 4K
query_cache_limit = 1M
You need to add the first parameter
query_cache_size that defines how much memory the
cache uses (we''ve decided on 16 MB): the default is
0, which disables caching. The remaining three statements are
optional, but we''ve included them with their default
settings anyway. The query_cache_type parameter
defines whether caching is off (0), on (1), or only used when you ask
for it (2); we discuss how to control which queries are cached in the
next section. The third parameter
query_cache_min_res_unit defines the minimum
memory block size for a cached result set, and the default of 4 KB.
works well. The last parameter query_cache_limit
defines the maximum size of a result set that can be cached, and the
1 MB default is a sensible choice.
After you''ve made the changes, save the file, and
restart your MySQL using the method described for your platform in
Appendix A through Appendix C. Alternatively, reboot your machine.
You now have caching enabled. If you repeat a query such as
SELECT * FROM customer twice or
more, you can check that the cache is in action by using the
SHOW STATUS command from the
command interpreter. This outputs, in part, the following:
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 1 |
| Qcache_hits | 3 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 16709128 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
In our example, you can see that our system has just started up:
there''s one query in the cache, only one query has
ever been inserted, it''s been re-run three times,
and no queries that couldn''t be cached have ever
been run. There''s also plenty of memory free.
15.9.4.2 Controlling query caching
After you''ve got caching turned on, you can control
whether an individual query is cached or not. If you
don''t prevent caching, and the
query_cache_type parameter is set to its default
of 1, all queries are cached (with the exception of those queries
that it doesn''t make sense to cache, as discussed
previously). If the query_cache_type parameter is
set to 2, only those queries you ask to be cached will be cached.
Here''s an example of how to explicitly cache a query:
SELECT SQL_CACHE * FROM customer;
Here''s an example of how not to cache a query:
SELECT SQL_NO_CACHE * FROM customer;
Caching only works with SELECT queries. It
doesn''t make sense to cache dynamic
DELETE, INSERT, or
UPDATE queries (or their variants such as
TRUNCATE).
