background

Summarize the process of dealing with online problems. A large number of customers reported that the official website could not be accessed. After logging in to the database server, the database cpu soared to 1000%, and the business website could not be accessed. In the initial positioning, due to the database, it soared to 1000% soon after restart. There is no way but to locate the statement causing the database problem by monitoring the database performance and find the interface using the statement. After the monitoring operation, it was found that the database could not process requests normally because the efficiency of sql statements was too low.

After the project goes online, mysql will occupy a large CPU or the business page will get stuck due to the large amount of data, resulting in poor user experience. You need to monitor the mysql database. By running the monitoring mysql script on the database server, you can effectively locate the problem function point. Optimize the statements running in mysql. At this time, the importance of database monitoring scripts is reflected.

prepare

Write the following script files in the linux server. Pay attention to modifying the IP port user password:

       [root@localhost ~]# vi mysql-detail-status.sh 

mysqladmin -uroot -proot  -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ++count == 1){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}

After saving, grant executable permissions:

        [root@localhost ~]# chomd 777 mysql-detail-status.sh 

function

        [root@localhost ~]# ./mysql-detail-status.sh 

analysis

       1. Analyze whether the QPS is too large. When there are few users, the QPS is generally not very high. In case of ultra-high, check the statement problem first

        2. Analyze whether innodb row operation is too large. This is the number of physical rows scanned, which is returned as needed

        3. You can locate when a slow interface appears according to the interface log. Judge whether the interface change is caused by the statement through the log of the above script.

        4. After finding the business that can be reproduced, run "show processlist" in mysql Find the statement that is currently running slowly. In this way, the basic cpu occupation problem can be solved by optimizing the business and statements.