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

0
70
lượt xem
20

Mô tả tài liệu

MySQL Administrator's Bible- P13: 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)

Lưu

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

1. Measuring Performance 17 For some status variables, such as Slave_running, the current output is enough information — either the slave is running or it is not. The Threads_connected status variable shows how many threads are currently connected. However, for many status variables, there is more to be done than simply looking at the value of each variable. For example, the Slow_queries status variable provides a count of how many slow queries the system has logged: mysql> SHOW GLOBAL STATUS LIKE ’Slow_queries’; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 1073 | +---------------+-------+ 1 row in set (0.00 sec) Is it good or bad that there have been 1073 slow queries? You should investigate and optimize all the slow queries that are logged — see the mysqldumpslow and mysqlsla tools discussed later in this chapter for how to ﬁnd slow queries, and see Chapter 18 for how to analyze queries. When determining the health of a system, the important data is how frequently slow queries are happening. The Uptime status variable shows how long, in seconds, that particular mysqld has been running: mysql> SHOW GLOBAL STATUS WHERE Variable_name=’Slow_queries’ -> OR Variable_name=’uptime’; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 1073 | | Uptime | 10906 | +---------------+-------+ 2 rows in set (0.08 sec) The server has been up for 10906 seconds (or roughly seven and a half days); The rate of slow queries is an average of about one slow query every 10 seconds. Ideally, you would like to be able to see the rate of change over time. For example, the slow query infor- mation you saw earlier would indicate a problem in a database that usually has one slow query every hour; the database administrator would be celebrated in a database that usually has one slow query every second. Establishing a baseline for a system’s status and comparing over time will make patterns evident and shows where problems may lurk. One way to establish a baseline is to compare the status variables over a short period of time. To get an average of status variables in an hour, you can compare the output of SHOW GLOBAL STATUS taken from a server at 1 pm to the output of SHOW GLOBAL STATUS taken from the same server at 2 pm. Instead of comparing variables to Uptime, variables are compared to each 567
3. Measuring Performance 17 General Statistics There are three checks in the General Statistics section. The ﬁrst is whether or not there is a new version of mysqltuner. This is skipped by default, but can be turned on by giving the --checkversion ﬂag to mysqltuner. The second check determines which version of mysqld you are running, and whether or not that version is supported by mysqld. If you are running a version that has been marked as end of life by Sun Microsystems, a warning will be issued. The ﬁnal check is whether or not the operating system is 64 bit. -------- General Statistics -------------------------------------- [--] Skipped version check for MySQLTuner script [!!] Currently running unsupported MySQL version 6.0.6-alpha- community-log [OK] Operating on 64-bit architecture If the system is running a 32-bit architecture with 2 GB of RAM or less, mysqltuner notes: [OK] Operating on 32-bit architecture with less than 2GB RAM Otherwise, you get a warning: [!!] Switch to 64-bit OS - MySQL cannot currenty use all of your RAM Storage engine statistics This section analyzes the sizes and storage engines of tables, except for tables in the mysql and information_schema databases. At the time of this writing, mysqltuner does not give any details about the Falcon or Maria storage engines. mysqltuner uses SHOW TABLE STATUS in pre-5.0 database servers to determine the size of each table and whether or not the table is frag- mented. With MySQL 5.0 and above, it uses the information_schema database to gather the same information. It prints out a list of the total data stored in each table type and ends with a count of fragmented tables. -------- Storage Engine Statistics ----------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 6G (Tables: 128) [--] Data in InnoDB tables: 21G (Tables: 44) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 7 It is important to note that the size of Data_length in SHOW TABLE STATUS or the informa- tion_schema database is not always accurate. For storage engines that estimate the size of their data the size shown will be an approximation. Also, the size of indexes is not taken into consid- eration, so this information cannot be used to ﬁgure out how much space the database is using. The Data_free ﬁeld of either SHOW TABLE STATUS or the information schema. The TABLES database is used to determine whether a table is fragmented or not. If Data_free is greater 569
6. Part III Core MySQL Administration [OK] Open file limit used: 1% (310/25K) [OK] Table locks acquired immediately: 100% (236M immediate / 236M locks) [!!] InnoDB data size / buffer pool: 21.3G/1.5G Recommendations -------- Recommendations ---------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Variables to adjust: *** MySQL’s maximum memory usage exceeds your installed memory *** *** Add more RAM before increasing any MySQL buffer variables *** query_cache_limit (> 2M, or use smaller result sets) query_cache_size (> 64M) innodb_buffer_pool_size (>= 21G) Performance and Reporting Options --skipsize Don’t enumerate tables and their types/sizes --checkversion Check for updates to MySQLTuner --forcemem Amount of RAM installed in megabytes --forceswap Amount of swap memory configured in MB Output Options: --nogood Remove OK responses --nobad Remove negative/suggestion responses --noinfo Remove informational responses --nocolor Don’t print output in color As you can see, the information provided by mysqltuner can be quite valuable. However, any recommendations from this (or other) proﬁling programs should be taken with some caution. It is very easy to make changes just based on the recommendations of mysqltuner, without understanding what is really happening, and have a system that does not perform as optimally as possible. mysqlreport The mysqlreport program is similar in scope to mysqltuner. Like mysqltuner it is a Perl program that uses the SHOW STATUS command to gather an overall picture of a server’s health. Unlike mysqltuner, the mysqlreport program does not provide any recommendations. However, it does provide a more in-depth analysis of your system that you can use to determine where changes need to be made. The program is available at http://hackmysql.com/mysqlreport. Running the program is not difﬁcult: shell> ./mysqlreport --user qa_user --password 572
9. Measuring Performance 17 The Total line shows how many total questions were processed by the server. It is simply a summation of the two ﬁelds of data. While it is somewhat disingenuous, you can say that the second ﬁeld of the Total line is your server’s query per second average. In the case of the server being proﬁled, it doesn’t execute very many queries per second. After the Total line, all of the lines following are sorted based upon frequency. In the case of the server being proﬁled, the DMS statements were the majority of the total questions executed by the server. The DMS line shows statistics about Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE queries). The majority of the server processing should be DML statements, and if it is not, it probably indicates a problem. The Com_ line displays the server communication commands, and the QC Hits line shows how many query result sets were served from the query cache. In the case of the proﬁle server, it is not a signiﬁcant percentage (2.21%). There is a signiﬁcant amount of data about the query cache later in the report, so it will be examined more closely at that point. The Unknown line should be fairly small. Unknown questions are the questions that MySQL handles and increments the total questions counter but does not have a separate status value to increment. The Slow line shows how many queries took longer than the server variable long_query_time to return a result. With the server being proﬁled the long_query_time is 10 s (seconds). In addition to these lines showing general information, the Questions section provides a sepa- rate subsection for each line. With the server being proﬁled for the example, the most activity occurred with data manipulation statements, so it is the ﬁrst subsection. DMS 8.20M 3.4/s 57.73 INSERT 7.17M 3.0/s 50.51 87.49 UPDATE 752.84k 0.3/s 5.30 9.18 DELETE 219.20k 0.1/s 1.54 2.67 SELECT 53.88k 0.0/s 0.38 0.66 REPLACE 0 0/s 0.00 0.00 This subsection can tell you at a glance how read or write heavy the application is. In this case, it is almost entirely writes (99.34%). This is very unusual. This also explains why the earlier per- centage for queries served out of the query cache is so low. For the proﬁled server, the next subsection is the Com_ subsection: Com_ 5.68M 2.4/s 40.02 begin 3.86M 1.6/s 27.16 show_status 517.09k 0.2/s 3.64 set_option 352.17k 0.1/s 2.48 The Com_ subsection shows the values for the most used Com_ commands on the proﬁled server. If you have some very unusual activity, it might show up here. __ SELECT and Sort ____________________________________________ Scan 758.38k 0.3/s %SELECT: 1407.6 Range 559 0.0/s 1.04 575
18. Part III Core MySQL Administration With the -s (sort order) option there six possible values: t, at, l, r, c, and s. ■ The t value sorts the query output by total execution time. Total execution time is the average query execution time × the number of times the query was executed. ■ The at value sorts the query output by the average query execution time. It is the default sorting method. ■ The l value sorts the query output by lock time. ■ The al value sorts the query output by lock time average. ■ The r value sorts the query output by total rows. ■ The ar value sorts the query output by average rows sent. ■ The c value sorts the query output by count. Here is an example of performing the sort by total execution time of the top ﬁve queries: \$ mysqldumpslow -s t -t 5 /mnt/mysql/logs/slowquery.log Reading mysql slow query log from /mnt/mysql/logs/slowquery.log Count: 3324 Time=9.79s (32533s) Lock=0.00s (0s) Rows=1.0 (3324), username@hostname SELECT count(*) AS count_all FROM table_one WHERE (sweepstake_id = N and user_id is null) Count: 1957 Time=11.26s (22028s) Lock=0.00s (0s) Rows=1.0 (1955), username@hostname SELECT * FROM table_one WHERE (sweepstake_id = N and user_id is null) LIMIT N Count: 279 Time=30.27s (8446s) Lock=0.00s (0s) Rows=0.6 (163), username@hostname SELECT * FROM table_two WHERE (table_two.id = N) FOR UPDATE Count: 132 Time=43.59s (5754s) Lock=0.00s (0s) Rows=0.2 (23), username@hostname SELECT * FROM table_one WHERE (table_one.id = N) FOR UPDATE Count: 901 Time=5.41s (4872s) Lock=0.02s (14s) Rows=1.0 (897), username@hostname SELECT * FROM table_three WHERE (table_three.value=’S’) LIMIT N Knowing that these ﬁve queries spent more time executing (total execution time) than all the other queries, you can concentrate your time on optimizing these queries. Query optimization is covered in detail in Chapter 18. 584