A part-time DBA's database operation and maintenance experience Xiaomi Technology Xx@xiaomi.com 2011

A part-time DBA's database operation and maintenance experience Xiaomi technology [email protected] 2011

Alarm Monitoring System particle size is too large, not good to use (Current status of our company)
Database status: 10 servers, HP Hp380g7 Dell R710, have done a master-slave
All SAS disks 15K RAID10
Server memory 24G
database mixed with business, not specifically for database use causing problems (Current status of our company)
Xtrabackup for backup

Database is small: 160G 70G 30G
Program Support sub-database sub-table

--------------------------
Problem

io util% 100%(Learn)
Normal IO util% should be stable in 20%~30%

disk AWAIT/SVCTM high value, often in milliseconds ( learn)
Problem:

RAID Card Battery no power (Learn)
After buying the battery, io util% drops to 10%,AWAIT/SVCTM value in 0.x milliseconds

Data data and Binlog files are divided into different disks (not done)

Kernel IO deadline scheduling algorithm
Memory swappiness=0(Learn)
attach importance to DMESG(Learn)

After architecture optimization, QPS stabilized in 1500~2000

Source compiled MySQL

permissions minimized, only CRUD permissions assigned (Already done)

Memory expansion 16g->64g, after the increase in BP, the early hours to monitor the amount of memory, open to eat swap
Solution 1: timed echo 1>/proc/sys/vm/drop_caches (Learn)
Workaround 2: During the next instance restart, Numactl-interleave all (Learn)

Bad execution plan, direct Force index

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

F

A part-time DBA's database operation and maintenance experience Xiaomi Technology [email protected] 2011