How to locate mysql Performance problem _ MySQL

How to locate mysql performance problems bitsCN.com

1. First, use the system tool to locate the problem [top | nmon | dstat-dcnml | orzdba]:

The top command is used to view the resource usage of each process. Each metric is interpreted as follows [top f | 1 -- help ]:

Line 1: Current system time, system running duration, current number of logged-on users, system load (average length of task queue, three values: 1, 5, 15 minutes) [the system load should not exceed the number of cpu cores]

Row 2: Total processes, running process data, number of sleeping processes, number of stopped processes, and number of zombie processes

Row 3: Percentage of cpu used by user space, percentage of cpu used by kernel space, percentage of cpu used by processes whose user process space has changed its priority, percentage of idle cpu, and percentage of cpu waiting for input/output ,,,

Row 4: Total physical memory, total physical memory used, and total idle memory used as the kernel cache [70%]

Fifth line: total number of swap areas, Total Number of swap areas used, total number of idle swap areas, and total number of buffered swap areas [do not use swap partitions]

Pid process id

Ppid parent process id

Ruser real user name

Uid process owner user ID

User process owner username

Group process owner group name

Tty terminal for starting the process

Pr priority

NI priority (negative value indicates high priority, positive value indicates low priority)

P: The final cpu used, only when there are many CPUs

% CPU usage percentage since last cpu update

Total cpu TIME used by the TIME system (seconds)

TIME ++ ~ (1/100 seconds)

% MEM percentage of physical memory used by the process

Total virtual memory used by the VIRT process VIRT = SWAP + RES

Total virtual memory used by the SWAP process VIRT = SWAP + RES

In the virtual memory used by the RES process, the physical memory used by the replaced process and not swapped out RES = CODE + DATA

Size of physical memory occupied by CODE executable CODE

Physical memory size occupied by parts other than the DATA executable code (DATA segment + stack)

SHR shared memory size

Number of nFLT page errors

The number of modified pages that the last time the data is written to the present.

S process status D = non-disruptive sleep status R = Running S = sleep T = tracking/stopping Z = Zombie process
COMMAND line, COMMAND

If the process is sleep, WCHAN displays the system function name in sleep.

FLAGS task flag

2. View network problems

Whether netstat-I 1 has packet loss or network errors

Iftop [d s]: Check whether the port information of the local machine is displayed when the process traffic flow is switched by S; whether the port information of the remote target host is displayed when switched by D;

3. View io flow

Iostat-x 2

Iotop View process io trend

Dd if =/dev/zero of =/data1/test bs = 64 k count = 16 k conv = fsync current production environment EMC normal write speed 565 MB/second
Dd if =/data1/test bs = 64 k | dd of =/dev/null current production environment EMC normal read speed 675 MB/second

4. query pressure
Mysqladmin-uroot-P3309 ext-ri 1 | grep-I queries

5. check for lock wait

select r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_queryfrom information_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx bON b.trx_id = w.blocking_trx_idINNER JOIN information_schema.innodb_trx rON r.trx_id = w.requesting_trx_id/G;

6. View The innodb status

Show engine innodb status/G;

7. view the history from the monitoring tool

The above is how to locate the mysql Performance question _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!