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

Around 09:30 in the morning, the business staff reported that their pages opened slowly, and the subsequent pages appeared at 502.

Then I received an alarm and logged in to the database server (4-core cpu) to check cpu 400% load 30 or so.

Go to the database and find a lot of slow queries

It was thought that these slow queries came from the scheduled tasks of the system every day (the system is equivalent to an olap system, which carries out batch data query and extraction every day. ) so crontab-e stops all scheduled tasks first. But slow queries still exist.

All the slow queries were based on the same template, and later asked the developer's colleague to launch the new version yesterday.

SELECT (plan.due_amount + IF (plan.overdue_day > 0,0)-plan.reduce_amount) due_amount_total, plan.repay_status FROM mostop_xiaodai_supplement_loan_repay_plan plan LEFT JOIN mostop_xiaodai_loan_info_extend extend ON extend.loan_id = plan.loan_id WHERE plan.base_plan_id = 11124546 AND plan.step_no = 2

View execution table structure

Mysql > show create table mostop_xiaodai_loan_info_extend\ gateway * 1. Row * * Table: mostop_xiaodai_loan_info_extendCreate Table: CREATE TABLE `mostop_xiaodai_loan_info_ extend` (`id`bigint (20) unsigned NOT NULL COMMENT 'number', `agentid` int (10) unsigned NOT NULL COMMENT 'channel ID' `update_ id`bigint (20) unsigned NOT NULL COMMENT 'loan number', `create_ time`datetime NOT NULL COMMENT 'creation time', `update_ time`datetime NOT NULL COMMENT 'update time', `total_ Rate` decimal (10L6) unsigned DEFAULT NULL COMMENT 'Total interest rate', `service_ Rate` decimal (10L6) unsigned DEFAULT NULL COMMENT 'Service rate', `intrest_ Rate` decimal (10jue 6) unsigned DEFAULT NULL COMMENT 'interest rate', `overdue_ Rate` decimal (10L6) unsigned DEFAULT NULL COMMENT 'overdue Total interest rate' `overdue_service_ rate` decimal (10jue 6) unsigned DEFAULT NULL COMMENT 'overdue service rate', `penalty_ rate` decimal (10jue 6) unsigned DEFAULT NULL COMMENT 'liquidated damages rate', `is_ split` tinyint (4) DEFAULT'0' COMMENT 'interest fee leveling Do you need to open an order', 'desired_repay_ type' varchar (9) DEFAULT NULL COMMENT', ideal repayment method', 'desired_total_ rate` decimal (10L6) DEFAULT NULL COMMENT', 'supplement_overdue_ rate`decimal (10L6) DEFAULT NULL COMMENT', 'overdue total interest rate', `supplement_penalty_ rate`decimal (10L6) DEFAULT NULL COMMENT' Default penalty rate of white note order', 'investor_ rate` decimal (10pc6) DEFAULT NULL COMMENT' investor interest rate', `investor_repay_ type` varchar (9) DEFAULT NULL COMMENT 'investor interest rate', PRIMARY KEY (`id`, `agentid`), UNIQUE KEY `agentid` (`agentid`, `agentid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' loan information expansion table'

View the execution plan

Mysql > explain SELECT (plan.due_amount + IF (plan.overdue_day > 0, (plan.due_amount * extend.supplement_penalty_rate / 100) 0) | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | plan | NULL | const | idx_base_planid | idx_base_planid | 8 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | extend | | NULL | ALL | NULL | 4690305 | 100.00 | Using where | + -- +

Although there is a loan_id column on the federated index in the table

UNIQUE KEY `agentid` (`agentid`, `agentid`)

However, according to the leftmost prefix principle of the index, loan_id is directly out of the where condition, and the composite index is broken, so the index is invalid. The R & D students thought that the indexes in the table could be used and went online without audit, which led to the overload of the server caused by the full table scan.

Solution.

Add Index

Alter table mostop_xiaodai_loan_info_extend add index IDX_loan_id (loan_id)

Execute the plan after adding the index

Explain SELECT (plan.due_amount + IF (plan.overdue_day > 0,0)-plan.reduce_amount) due_amount_total, plan.repay_status-> FROM mostop_xiaodai_supplement_loan_repay_plan plan LEFT JOIN mostop_xiaodai_loan_info_extend extend ON extend.loan_id = plan.loan_id WHERE plan.base_plan_id = 11124546 AND plan.step_no = 2 +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | plan | NULL | const | idx_base_planid | idx_base_planid | 8 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | extend | NULL | ref | IDX_loan_id | IDX_loan_id | 8 | const | 1 | 100.00 | Using where | +-+ -+

The server load immediately returned to normal

Through this accident

Conduct SQL audit before launch

Applications and databases are deployed separately on different servers

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.