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
4. Part III Core MySQL Administration than zero, mysqltuner considers the table fragmented. This may lead to false warnings when using global InnoDB data ﬁles (i.e., not using innodb_file_per_table), as Data_free shows the amount of data left in the global InnoDB data ﬁles. If a storage engine is enabled, but there are no tables that are deﬁned with that storage engine, mysqltuner will issue a warning such as: [!!] InnoDB is enabled but isn’t being used A false positive may arise if you run mysqltuner with a user that cannot see all the tables within mysqld, as the storage engine may actually be in use by a table that the user does not have permissions to see. Performance Metrics The Performance Metrics section uses the output from SHOW GLOBAL STATUS and performs the tedious calculations you would ordinarily do by hand. The ﬁrst line gives a general overview of mysqld: -------- Performance Metrics ------------------------------------ Up for:116d 21h 10m 14s (338M q[33.501 qps],39M conn,TX:174B,RX: 28B) The values in the ﬁrst line are simply the status variables from SHOW GLOBAL STATUS with some formatting for better readability, as shown in Table 17-10: TABLE 17-10 Relationships between Variables in Performance Metrics and SHOW GLOBAL STATUS Performance Metrics Variable Status Variable from SHOW GLOBAL STATUS Up for Uptime q Questions qps qps (queries per second) conn Connections TX Bytes Sent RX Bytes Received The next line gives the percentage of reads and writes, using the Com_select status variable as the number of reads, and the sum of the Com_delete, Com_insert, Com_update, Com_replace status variables as the writes. The percentage given is a percentage of the total reads and writes (all ﬁve Com variables added together) and does not include administrative commands like SHOW. Because of this, these percentages may be misleading. [--] Reads / Writes: 32% / 68% 570
5. Measuring Performance 17 The next two lines relate to memory usage: [--] Total buffers: 1.9G global + 12.2M per thread (300 max threads) [!!] Maximum possible memory usage: 5.5G (91% of installed RAM) Information for these lines comes from the system variables that are the output of SHOW GLOBAL VARIABLES. The global buffer formula that mysqltuner uses is: key_buffer_size + max_tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size The per thread buffer formula that mysqltuner uses is: read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size The max_threads comes from the system variable max_connections. The Maximum possible memory usage is calculated by: global + max_connections * (per thread) The global and per thread buffers in mysqltuner are not a complete picture of how much memory is allocated for global use; they do not take into account any of the memory settings for the BDB, Falcon, and Maria storage engines. Thus, the Maximum possible memory usage is inaccurate. The Maximum possible memory usage in our example is a large percentage of available mem- ory. In some cases, it may exceed the memory available. This may or may not be a problem; in many cases, there will not be max_connections number of connections that are all using the maximum per thread memory allocation. In fact, there may be a few queries that require high values for some of the per thread memory variables. The max_connections variable is use- ful to reduce the number of connections, so that mysqld does not crash by trying to allocate more memory than is available. However, there are many cases in which both a high number of max_connections and a high number of per thread memory variables are needed. This is one of the reasons that automated tuning is not always useful. The values in the rest of the Performance Metrics section are simple calculations involving system and status variables from SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS: [OK] Slow queries: 0% (4K/338M) [OK] Highest usage of available connections: 34% (102/300) [OK] Key buffer size / total MyISAM indexes: 350.0M/13.7G [OK] Key buffer hit rate: 97.2% (368M cached / 10M reads) [!!] Query cache efficiency: 14.1% (12M cached / 90M selects) [!!] Query cache prunes per day: 246 [OK] Sorts requiring temporary tables: 8% (1M temp sorts / 19M sorts) [OK] Temporary tables created on disk: 12% (162K on disk / 1M total) [OK] Thread cache hit rate: 99% (102 created / 39M connections) [OK] Table cache hit rate: 53% (358 open / 675 opened) 571
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
7. Measuring Performance 17 After you are prompted for the password, the report is generated. While this is the simplest way to run mysqlreport, there are a number of options used for connecting to mysqld and manag- ing the mysqlreport program run. Table 17-11 lists the available options. TABLE 17-11 Available Options For mysqlreport Option Description --user username Speciﬁes the username used by mysqlreport to for connection to mysqld. --password password Speciﬁes the password used by mysqlreport to connect to mysqld. --host address Speciﬁes an address of mysqld to connect and gather data from. --port tcpip_port The TCP/IP port used for connection to mysqld. --socket socket_file_ Speciﬁes the socket ﬁle used for local connections on a location Unix-based server. --infile file_name Reads status information from file_name instead of connecting to a server and running SHOW STATUS and SHOW VARIABLES commands. --outfile file_name Writes report to both the ﬁle named file_name and the screen. --email email_address On Unix-based systems emails report to email_address. --flush-status After gathering the current values issues a FLUSH STATUS command. --relative value By default, mysqlreport generates a report based on the status of the server since it began operation. The --relative option can be used to generate reports that are based on the values from previous reports. If value is an integer the reports are generated live from mysqld every num seconds. The option value can also be a list of input ﬁles (generated by running mysqlreport with the --report-count option), and the relative report is generated from these input ﬁles in the order speciﬁed. --report-count num Collects num number of reports for use as input ﬁles for the --relative option. --detach Runs the mysqlreport program in the background. --help Prints help information and exits. --debug Prints debugging information and exits. 573
8. Part III Core MySQL Administration As with mysqltuner, the mysqlreport program generates a report with sections devoted to various aspects of mysqld being analyzed. The header section provides some general informa- tion about what version of MySQL is running, how long the server has been running and the time the report was generated. shell> ./mysqlreport --user qa_user --password Password for database user qa_user: MySQL 5.0.45-Debian_1ub uptime 27 22:47:2 Tue Sep 23 23:56:20 2008 The next section is the Key section and covers information about the key buffer usage. The key buffer is the buffer used to store MyISAM indexes. __ Key _________________________________________________________ Buffer used 13.08M of 16.00M %Used: 81.76 Current 16.00M %Usage: 100.00 Write hit 96.88% Read hit 99.22% The ﬁrst line of the Key section should be ignored. Buffer used is suppose to show the highest ever level of buffer usage. However, it is very often inaccurate. In this example, it shows a max- imum of 13.08 megabytes used. The Current line shows the buffer amount currently being uti- lized. In this case, the entire 16 MB is being utilized. The Write hit value can vary quite a lot, depending on your overall server usage. If mysqld has a lot of write activity that primarily executes INSERT and UPDATE statements, then Write hit may be very low. If your server has a high percentage of SELECT statement execution, then the key Write hit may be close to 100 percent. However, a negative key Write hit indicates that MySQL is writing keys to hard disk more frequently than the key buffer in RAM. This is going to be slow. The Read hit value shows the ratio of key reads from hard disk to key reads from memory. This percentage should be very high — near 100 percent. Having your MyISAM table indexes stored in the key buffer is going to provide for much faster updating than having the indexes stored on disk. If this value is not very close to 100 percent, you should see a performance increase by allocating more memory to the key buffer. The next section, Questions, includes information about both SQL queries being executed and the MySQL protocol communications: __ Questions ____________________________________________________ Total 14.20M 5.9/s DMS 8.20M 3.4/s %Total: 57.73 Com_ 5.68M 2.4/s 40.02 COM_QUIT 346.13k 0.1/s 2.44 -Unknown 340.18k 0.1/s 2.39 QC Hits 313.62k 0.1/s 2.21 Slow 10 s 492 0.0/s 0.00 %DMS: 0.01 Log: ON 574
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
10. Part III Core MySQL Administration Full join 4 0.0/s 0.01 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 182 0.0/s Sort range 6 0.0/s Sort mrg pass 152 0.0/s The SELECT and Sort subsection provides information about the Select_status values. These values can help you pinpoint issues with selects. For example, the Scan line indicates how full table scans were performed. This could indicate that indexes might be needed to use these tables effectively. A Full join is when full table scans are performed on tables being joined in a multi-table queries. Both of these values should be as low as possible. The other values tend not to impact performance. If you want more information about them, complete documentation is available online at http://hackmysql.com. Notice that the Scan line has a percentage value of 1407.6. Since the total for all these values should add up to 100 percent, this is clearly incorrect. Be careful when going through this report, as there are occasional glitches. __ Query Cache __________________________________________________ Memory usage 361.34k of 32.00M %Used: 1.10 Block Fragmnt 11.36% Hits 313.62k 0.1/s Inserts 42.09k 0.0/s Insrt:Prune 42.09k:1 0.0/s Hit:Insert 7.45:1 As stated earlier, this server is very heavy on writes. Because of this the query cache is not used very much. The Memory usage line shows the amount of memory actually being used out of the total memory allocated to the Query Cache. In this case, it is 361.34k out of 32 MB. The Block Fragment percentage should be somewhere between 10 and 20 percent. It indicates the amount of fragmentation in the query cache. The Hits line indicates the number of query result data sets actually served from the query cache. This should be as high as possible. For additional details one query cache optimization, see Chapter 12. The next two lines are ratios that indicate the general effectiveness of your query cache. The ﬁrst line, Insert:Prune, is the ratio of inserts (into the query cache) to prunes. A prune is when a query is removed from the query cache. In this case, the ratio is very heavy on inserts because prunes are not really happening. If the amount of prunes is very large, it might be beneﬁcial to increase the size of the query cache. The Hit:Insert ratio shows the number of hits (results) returned from the query cache versus the number of inserts into the query cache. The higher this ratio is the better your server performance. For additional details on query cache optimiza- tion, see Chapter 12. __ Table Locks ________________________________________________ Waited 6 0.0/s %Total: 0.00 Immediate 8.33M 3.4/s 576
11. Measuring Performance 17 The Table Locks subsection shows how often the database had to wait to obtain a lock on a table (Waited) and how often the lock was granted immediately (Immediate). If the database had to wait for table locks very often, this indicates there often a problem with MyISAM contention. __ Tables ________________________________________________________ Open 467 of 1024 %Cache: 45.61 Opened 13.23k 0.0/s The Tables subsection Open line indicates the number of tables that were open when the report was ran, the total number of open tables allowed in the table cache, and the percentage of table cache used. The Opened line indicates the total number of tables opened since mysqld started up and the average number opened per second. If the number of open tables is equal to the table cache, it could possibly indicate a problem. You might want to increase the size of the table cache. __ Connections ___________________________________________________ Max used 19 of 500 %Max: 3.80 Total 346.36k 0.1/s The connections subsection is straightforward. The Max used line indicates the maximum num- ber of connections every used out of the total possible. If the Max used is approaching the max- imum number you might need to raise the max_connections variable. The Total line indi- cates the total number of connections and the average number being made per second. __ Created Temp _________________________________________________ Disk table 159.56k 0.1/s Table 745.02k 0.3/s Size: 128.0M File 45 0.0/s The Created Temp subsection is very important. The Disk table line shows the total number of temporary tables that were created on disk. When temporary tables are created on disk instead of remaining in memory, it is a big performance hit. It happens because some of the temporary tables created in memory exceed the maximum size conﬁgured with the max_tmp_tables variable. If you have a signiﬁcant quantity of these, it would be best to increase the size of max_tmp_tables. Assuming, of course, that your server has enough RAM to increase this value. The next line shows the number of temporary tables created in memory, the average per second, and the maximum allowable size before they are converted to disk-based temporary tables. __ Threads ____________________________________________________ Running 1 of 2 Cached 7 of 8 %Hit: 99.99 Created 37 0.0/s Slow 0 0/s 577
12. Part III Core MySQL Administration The Threads subsection details information about the threads used for each connection to the server. The most important line in this subsection is Cached. It shows the number of threads that are stored in the thread cache ready for reuse. This keeps the server from having to create a new thread for a new connection. The cache hit rate of 99.99 percent is excellent. If it is not very high, it indicates that the server has to continually create and destroy new threads, which can impact performance. If this is the case, you should probably increase the size of the thread cache. __ Aborted _____________________________________________ Clients 451 0.0/s Connects 50 0.0/s The Aborted subsection show statistics about connections to the server that have been dropped. If this number is very high in relation to the amount of uptime, it can indicated a problem. The Clients line shows connections that are closed because of three possibilities: ■ The client program did not call mysql_close() before exiting. ■ The client has been sleeping more seconds than the values of either of the two system vari- ables wait_timeout or interactive_time. ■ The client program ended in the middle of a data transfer. The Connects line is incremented when one of four things happens: ■ The client does not the required privileges to connect to the database. ■ The client used an incorrect password. ■ A connection packed is malformed or doesn’t contain the proper information. ■ It takes the server more than connect_timeout seconds to get a connection packet form the client. __ Bytes __________________________________________________ Sent 121.68G 50.4k/s Received 8.78G 3.6k/s The Bytes subsection shows the number of bytes sent from and received by the server: __ InnoDB Buffer Pool _____________________________________ Usage 511.98M of 512.00M %Used: 100.00 Read hit 99.94% Pages Free 1 %Total: 0.00 Data 32.28k 98.51 %Drty: 0.03 Misc 486 1.48 Latched 0 0.00 Reads 501.12M 207.5/s From file 320.24k 0.1/s 578
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