Tài liệu MySQL Administrator''''s Bible- P13 pdf

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 find 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

other. We may find that from 1–2 PM, there are only two slow queries, but from 2-3 PM, there

are ten slow queries.

With about 300 status variables, manual analysis is tedious. However, no automated tool can

take into consideration the specifics of your system, and what is acceptable to your users. There

is a tradeoff to using automated tools, which may be acceptable. Even if you use an automated

tool or tools, knowing how to use SHOW GLOBAL STATUS is a key skill for a database administra￾tor working with mysqld.

mysqltuner

The open source program mysqltuner is a Perl script that is a part of the default package

distribution for some operating systems. If it is not part of your operating system, you

can download it at www.mysqltuner.com. It can be run with no options — by default,

mysqltuner.pl connects to mysqld on localhost port 3306, and prompts for a username and

password:

shell> ./mysqltuner.pl

>> MySQLTuner 0.9.9 - Major Hayden <[email protected]>

>> Bug reports, feature requests, and downloads at http://

mysqltuner.com/

>> Run with ’--help’ for additional options and output

filtering

Please enter your MySQL administrative login: username

Please enter your MySQL administrative password:

You do not need SUPER privileges in order to run the script. After entering your password,

mysqltuner analyzes mysqld and outputs four sections:

■ General Statistics

■ Storage Engine Statistics

■ Performance Metrics

■ Recommendations

Each line of information is prefixed with a code that indicates whether the check is positive,

neutral, or negative:

■ Check neutral or skipped [--]

■ Check OK [OK]

■ Warning, check not OK [!!]

Before the first section, mysqltuner will output a problem if the password provided is blank:

[!!] Successfully authenticated with no password - SECURITY RISK!

568

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Measuring Performance 17

General Statistics

There are three checks in the General Statistics section. The first 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 flag 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

final 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 figure out how much space the database is using.

The Data_free field 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

than zero, mysqltuner considers the table fragmented. This may lead to false warnings when

using global InnoDB data files (i.e., not using innodb_file_per_table), as Data_free

shows the amount of data left in the global InnoDB data files.

If a storage engine is enabled, but there are no tables that are defined 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 first 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 first 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 five 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.