MySQL Administrator's Bible- P14

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

101
lượt xem 21
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P14: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P14

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 ﬁeld, 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) of 9. 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 ﬁeld. Therefore, there is no way to avoid accessing every value in the return_date index. mysqld needs to 617
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 ﬁeld. Index consequences n Chapter 6, we explained how indexes work. Indexes can make data retrieval faster because I 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, and DELETE queries run more slowly. It helps to be familiar with the nature of all the queries against the database. If you ﬁnd that selecting a ﬁeld 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 ﬁlesort 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 shows up in a 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, and GROUP BY. More information on how to create and use subqueries can be found on the ON the WEBSITE accompanying website for this book at www.wiley.com/go/mysqladminbible. For example, the following EXPLAIN plan is for a query to ﬁnd 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=’barbara.jones@sakilacustomer.org’ -> ORDER BY last_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE 618
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 ﬁlter of WHERE email=’barbara.jones@sakilacustomer.org’: 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=’barbara.jones@sakilacustomer.org’ -> 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) of full table scan (ALL) to one of looking up a nonunique index value (ref). Often, ﬁrst instincts may not fully optimize a query. For example, your ﬁrst instinct in opti- mizing this query might have been to add an index on only the email ﬁeld. 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 ﬁelds allows mysqld to use that index to optimize the data access strategy and the ﬁlesort. It is always a good idea to test as many optimization solutions as possible see the sidebar ‘‘Testing ideas.’’ 619
Part IV Extending Your Skills Testing ideas n the example from the section ‘‘Optimizing away Using ﬁlesort,’’ you might have tried to see if I mysqld would use an index on last_name only; if that was your ﬁrst 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=’barbara.jones@sakilacustomer.org’ ORDER BY last_name\G Sometimes, the ﬁrst 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 ﬁlter for the WHERE clause ﬁrst, before ordering. If mysqld was to use the index on last_name, it would have to go through the entire index, and for each row in the index, look up the email ﬁeld 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 ﬁeld 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) and the EXPLAIN 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 beneﬁcial to use an index with a shorter length, even if it means mysqld has to do a filesort. 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) of range, 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 ﬁelds and/or ordering, the way to optimize this is to get rid of either the ORDER BY or the GROUP BY. This may 620
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. ■ The presence of 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 keyword is used in a SELECT statement with DISTINCT or GROUP BY. The SQL_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 ﬁlesort. 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 ﬁlesort, 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 ﬁlesort is better for your particular situation. ■ ORDER BY or GROUP BY is used on a ﬁeld that is not the ﬁrst table in the join queue (the ﬁrst 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 ﬁlter so that the table order changes. For example, the following query uses the customer table ﬁrst in the join queue, but is sorting based on rental_date, a ﬁeld 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
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 ﬁeld in the customer table, or we could change the query to use the rental table ﬁrst 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
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 beneﬁcial 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 ﬁelds that use more than 512 bytes, and when any ﬁeld 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
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, but the WHERE clause is ﬁltering based on a function of the title ﬁeld. 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 deﬁne 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
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 field BETWEEN ’2005-05-30 LAST_DAY(field)=’2005-05-30’ --or 00:00:00’ AND ’2005-05-30 LEFT(datefield, 10) = ’2005-05-30’ 23:59:59’ SUBSTRING_INDEX(datefield,’ ’) = ’2005-05-30’ ABS(field) > 20 field > 20 or field < -20 field + 1 > 20 field > 19 FLOOR(field)=1 ﬁeld >= 1 AND ﬁeld < 2 CONCAT(field,’day’)=’Saturday’ ﬁeld=’Satur’ FROM_UNIXTIME(field)=’2005-05-30 ﬁeld= 1117425600 00:00:00’ LEFT(INET_NTOA(field),10)=’192.168.1.’ ﬁeld 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 ‘‘ﬁnd sales on May 30’’ or to ‘‘ﬁnd 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
Part IV Extending Your Skills and DBAs optimize queries — if the developer writes a suboptimal query, in many organizations the DBA will ﬁnd 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 ﬁnd 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 ﬁrst have to ﬁgure out what the query is looking for. This ﬁlter ﬁnds 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 ﬁl- ter is that it ﬁnds 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 ﬁnd 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 difﬁcult to eliminate WHERE clauses such as MOD(field,10)=2 and LENGTH(field) EXPLAIN SELECT first_name, last_name, email -> FROM staff -> WHERE email LIKE ’%sakilastaff.com’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE 626
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 ﬁeld 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 | +------------------------------+------------------------------+ | Mike.Hillyer@sakilastaff.com | moc.ffatsalikas@reylliH.ekiM | | Jon.Stephens@sakilastaff.com | moc.ffatsalikas@snehpetS.noJ | +------------------------------+------------------------------+ 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 ﬁeld and index help: mysql> SELECT REVERSE(’%sakilastaff.com’); +-----------------------------+ | REVERSE(’%sakilastaff.com’) | +-----------------------------+ | moc.ffatsalikas% | +-----------------------------+ 1 row in set (0.00 sec) 627
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) of range. You also have the desirable Extra values Using index condition and Using MRR — see Table 18-2 for details. Note that having an extra ﬁeld also means upkeep of the ﬁeld. The best way to upkeep the ﬁeld would be to have any code that inserts or updates the email ﬁeld also update the new reve- mail ﬁeld. Another option is to create triggers to update the revemail ﬁeld when the email ﬁeld 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 ﬂagged 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
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
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 sufﬁcient 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 ﬂy. 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
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 ﬁeld(s) or table(s) to store the relevant calculations. When the data is changed, the denormalized ﬁeld(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, ﬁle, 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 ﬁelds ■ Adding new data ﬁelds ■ Moving indexed ﬁelds out of functions ■ Limiting the use of temporary tables ■ Batching expensive and/or frequent queries ■ Periodically calculating frequent queries 631
Monitoring Your Systems I n Chapter 17, we discussed how to measure database performance. By taking regular measurements of the health of your systems, you IN THIS CHAPTER can learn about database problems before they are happening, send a Deciding what to monitor message as they are happening, and have information to help debug after they happen. This type of automated monitoring can make your job eas- Examining open source ier, whether monitoring alerts you when a system is down, or gives you monitoring the information necessary for capacity planning. Examining commercial It is surprising how many organizations do not have any automated monitoring 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 ﬁles. 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 ﬂexibility 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, ﬁnd 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
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 ﬁnd 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 reﬂect 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 ﬁgure 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 ﬁle system is running out of space (mysqld hangs when it cannot write to a log or data ﬁle) ■ The ﬁle 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
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 efﬁciency ■ Query cache efﬁciency ■ 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 efﬁciency ■ 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 beneﬁt 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
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