mysql产线性能报告,现场MySQL性能问题分析报告

1、首先把现场环境数据拷贝到本地MySQL

2、本地测试环境机器配置：16G内存，I5-6500 3.2GHz 四核心四线程

3、导入现场数据

host表：217条

interface表：30153条

4、开启profiling

set profiling =1;

5、执行原SQL

select h.* from spider.host h where exists (select i.host_Id from spider.interface i where h.id = i.host_Id and i.mac = '3C:E5:A6:83:C2:18' and tenant_Id='e10adc3949ba59abbe56e057f20f88dd');

6、查看结果

show profiles;

7、查询profile结果(MySQL 5.7之后profiling被移动到了information_schema下，所以show profile for query 2;已经无效)

select

query_id, -- 查询id 它用于标识一个查询

seq, -- 显示序号

(select sum(duration) from information_schema.profiling as innert where innert.query_id = outert.query_id) as total_cost , -- 总用时in seconds

state,-- 状态

duration, -- 持续时间

cpu_user, -- 用户空间的cpu 使用量

cpu_system, -- 内核空间的cpu 使用量

-- context_voluntary, -- 自愿上下文切换

-- context_involuntary, -- 非自愿上下文切换

block_ops_in, -- 块调入次数

block_ops_out, -- 块调出次数

swaps -- 发生swap 的次数

from

information_schema.profiling as outert

where query_id =2

order by

seq;

query_id

seq

total_cost

state

duration

cpu_user

cpu_system

block_ops_in

block_ops_out

swaps

2

355

0.482394

Sending data

0.010085

0

0

null

null

null

2

356

0.482394

executing

0.000004

0

0

null

null

null

2

357

0.482394

Sending data

0.010532

0.0156

0

null

null

null

2

358

0.482394

executing

0.000004

0

0

null

null

null

2

359

0.482394

Sending data

0.010249

0.0156

0

null

null

null

2

360

0.482394

executing

0.000005

0

0

null

null

null

2

361

0.482394

Sending data

0.009931

0

0

null

null

null

2

362

0.482394

executing

0.000005

0

0

null

null

null

2

363

0.482394

Sending data

0.010161

0.0156

0

null

null

null

2

364

0.482394

executing

0.000003

0

0

null

null

null

2

365

0.482394

Sending data

0.009964

0.0156

0

null

null

null

2

366

0.482394

executing

0.000003

0

0

null

null

null

2

367

0.482394

Sending data

0.00998

0

0

null

null

null

2

368

0.482394

executing

0.000005

0

0

null

null

null

2

369

0.482394

Sending data

0.010206

0.0156

0

null

null

null

2

370

0.482394

executing

0.000006

0

0

null

null

null

2

371

0.482394

Sending data

0.01072

0.0156

0

null

null

null

2

372

0.482394

executing

0.000005

0

0

null

null

null

2

373

0.482394

Sending data

0.010745

0

0

null

null

null

2

374

0.482394

executing

0.000006

0

0

null

null

null

2

375

0.482394

Sending data

0.010069

0.0156

0

null

null

null

2

376

0.482394

executing

0.000004

0

0

null

null

null

2

377

0.482394

Sending data

0.009987

0.0156

0

null

null

null

2

378

0.482394

executing

0.000004

0

0

null

null

null

2

379

0.482394

Sending data

0.010143

0

0

null

null

null

2

380

0.482394

executing

0.000005

0

0

null

null

null

2

381

0.482394

Sending data

0.010385

0.0156

0

null

null

null

2

382

0.482394

executing

0.000003

0

0

null

null

null

2

383

0.482394

Sending data

0.009962

0.0156

0

null

null

null

2

384

0.482394

executing

0.000004

0

0

null

null

null

2

385

0.482394

Sending data

0.009858

0

0

null

null

null

2

386

0.482394

executing

0.000003

0

0

null

null

null

2

387

0.482394

Sending data

0.009794

0.0156

0

null

null

null

2

388

0.482394

executing

0.000003

0

0

null

null

null

2

389

0.482394

Sending data

0.009844

0.0156

0

null

null

null

2

390

0.482394

executing

0.000004

0

0

null

null

null

2

391

0.482394

Sending data

0.011117

0

0

null

null

null

2

392

0.482394

executing

0.000005

0

0

null

null

null

2

393

0.482394

Sending data

0.009924

0.0156

0

null

null

null

2

394

0.482394

executing

0.000004

0

0

null

null

null

2

395

0.482394

Sending data

0.009747

0

0

null

null

null

2

396

0.482394

executing

0.000004

0

0

null

null

null

2

397

0.482394

Sending data

0.010594

0.0156

0

null

null

null

2

398

0.482394

executing

0.000005

0

0

null

null

null

2

399

0.482394

Sending data

0.00978

0.0156

0

null

null

null

2

400

0.482394

executing

0.000005

0

0

null

null

null

2

401

0.482394

Sending data

0.009816

0

0

null

null

null

2

402

0.482394

executing

0.000006

0

0

null

null

null

2

403

0.482394

Sending data

0.010401

0.0156

0

null

null

null

2

404

0.482394

executing

0.000005

0

0

null

null

null

2

405

0.482394

Sending data

0.009956

0.0156

0

null

null

null

2

406

0.482394

executing

0.000003

0

0

null

null

null

2

407

0.482394

Sending data

0.009643

0

0

null

null

null

2

408

0.482394

executing

0.000005

0

0

null

null

null

2

409

0.482394

Sending data

0.009805

0.0156

0

null

null

null

2

410

0.482394

executing

0.000003

0

0

null

null

null

2

411

0.482394

Sending data

0.009743

0.0156

0

null

null

null

2

412

0.482394

executing

0.000004

0

0

null

null

null

2

413

0.482394

Sending data

0.009934

0

0

null

null

null

2

414

0.482394

executing

0.000005

0

0

null

null

null

2

415

0.482394

Sending data

0.010076

0.0156

0

null

null

null

2

416

0.482394

executing

0.000005

0

0

null

null

null

2

417

0.482394

Sending data

0.00997

0.0156

0

null

null

null

2

418

0.482394

executing

0.000004

0

0

null

null

null

2

419

0.482394

Sending data

0.009954

0

0

null

null

null

2

420

0.482394

executing

0.000002

0

0

null

null

null

2

421

0.482394

Sending data

0.009765

0.0156

0

null

null

null

2

422

0.482394

executing

0.000002

0

0

null

null

null

2

423

0.482394

Sending data

0.009669

0

0

null

null

null

2

424

0.482394

executing

0.000002

0

0

null

null

null

2

425

0.482394

Sending data

0.010328

0.0156

0

null

null

null

2

426

0.482394

executing

0.000003

0

0

null

null

null

2

427

0.482394

Sending data

0.009812

0.0156

0

null

null

null

2

428

0.482394

executing

0.000006

0

0

null

null

null

2

429

0.482394

Sending data

0.010008

0

0

null

null

null

2

430

0.482394

executing

0.000019

0

0

null

null

null

2

431

0.482394

Sending data

0.009829

0.0156

0

null

null

null

2

432

0.482394

executing

0.000004

0

0

null

null

null

2

433

0.482394

Sending data

0.009756

0.0156

0

null

null

null

2

434

0.482394

executing

0.000003

0

0

null

null

null

2

435

0.482394

Sending data

0.009751

0

0

null

null

null

2

436

0.482394

executing

0.000002

0

0

null

null

null

2

437

0.482394

Sending data

0.009769

0.0156

0

null

null

null

2

438

0.482394

executing

0.000003

0

0

null

null

null

2

439

0.482394

Sending data

0.009678

0

0

null

null

null

2

440

0.482394

executing

0.000002

0

0

null

null

null

2

441

0.482394

Sending data

0.009909

0.0156

0

null

null

null

2

442

0.482394

executing

0.000005

0

0

null

null

null

2

443

0.482394

Sending data

0.01087

0.0156

0

null

null

null

2

444

0.482394

executing

0.000006

0

0

null

null

null

2

445

0.482394

Sending data

0.009896

0

0

null

null

null

2

446

0.482394

executing

0.000006

0

0

null

null

null

2

447

0.482394

Sending data

0.009799

0.0156

0

null

null

null

2

448

0.482394

executing

0.000005

0

0

null

null

null

2

449

0.482394

Sending data

0.010074

0.0156

0

null

null

null

2

450

0.482394

end

0.000007

0

0

null

null

null

2

451

0.482394

query end

0.000005

0

0

null

null

null

2

452

0.482394

closing tables

0.000009

0

0

null

null

null

2

453

0.482394

freeing items

0.000146

0

0

null

null

null

2

454

0.482394

cleaning up

0.00003

0

0

null

null

null

可以看到每条查询占用CPU为1.5%

8、正常的一条普通查询，可以看到没有那么多的sending data和executing，因为query_id=2的查询用了组合查询，会产生很多临时表(这个我猜的)，造成大量CPU占用

解决方案

修改sql，换种查询方式

select h.* from spider.host h , spider.interface i where h.id = i.host_Id and i.mac = '3C:E5:A6:83:C2:18' and h.tenant_Id='e10adc3949ba59abbe56e057f20f88dd';