Shulou(Shulou.com)06/01 Report--

I. description

The performance pressure of the database in the production system is relatively high, and the cpu iowait is 40% 50%. It is difficult to log in to the database system. We need to find out what process caused it, and it is best to find out what file caused it.

II. Operation process

1. Because it is a database system, it is easy to know that it is caused by mysqld, using glances system tools.

2. The principle of pt-ioprofile is to attach a strace process to a pid for IO analysis. Use ps aux | grep mysqld to find the process number corresponding to the mysqld process, and use pt-ioprofile to see which file takes up the most IO time. For positioning problems, it is more useful to locate through the throughput of IO. Use the parameter-- cell=sizes, which displays the result in the way that the result has been shown in BUnip s:

# ps-ef | grep mysql

# pt-ioprofile-profile-pid=113106-cell=sizes

Mon May 15 13:48:36 CST 2017

Tracing process ID 113106

Total pread read pwrite write fsync open close lseek ftruncate filename

2032296082 0 3424256 0 146 00 0 2028871680 0 / data/disk1/mysqllogs/relay-bin.002231

486457344 482263040 0 4194304 00 00 00 / data/disk1/mysqldata/dcs@002dpartition/t_warn_info_history#P#p201705.ibd

148111360 540672 0 147570688 0 0 0 / data/disk1/mysqldata/ibdata1

19590656 0 0 19590656 0 0 0 / data/disk1/mysqldata/ib_logfile0

6725632 0 162765 00 00 0 6562867 0 / data/mysqllogs/relay-bin.002231

3429905 000 3429905 000 00 / data/disk1/mysqllogs/mysql-bin.000431

35824 0 0 0 17912 0 0 0 17912 0 / data/disk1/mysqldata/innodb_status.113106

32768 32768 0 0 0 / data/disk1/mysqldata/mysql/slave_master_info.ibd

561 0 66 0 0 0 495 0 / data/mysqllogs/relay-bin.index

448 00 0 448 00 00 0 / data/mysqllogs/relay-bin.002232

314 0 0 0 314 0 0 0 / data/disk1/mysqllogs/error.log

0 0 0 / data/mysqllogs/relay-bin.~rec~

0 0 0 / data/mysqllogs/relay-bin.index_crash_safe

0 0 0 / data/mysqllogs/

0 0 0 / data/disk1/mysqllogs/relay-bin.index

#

Through the above, it is easy to find the performance problems caused by replication, you can further check the replication status, combined with business analysis of why there are periodic insertion peaks.

The final processing only modifies individual parameters of the database, mainly memory-related parameters, which need to be observed tomorrow. Originally planned to upgrade the system configuration, if through analysis and modification of the database, system parameters to solve this problem. Only then can the value of DBA be reflected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.