全面優(yōu)化

MySQL全面優(yōu)化

1.優(yōu)化哲學

1.1 為什么優(yōu)化？

為了獲得成就感?
為了證實比系統(tǒng)設計者更懂數(shù)據(jù)庫?
為了從優(yōu)化成果來證實優(yōu)化者更有價值?

但通常事實證實的結果往往會和您期待相反！
優(yōu)化有風險，涉足需謹慎！

1.2 優(yōu)化風險

優(yōu)化不總是對一個單純的環(huán)境進行！還很可能是一個復雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風險，只不過你沒能力意識到和預見到！
任何的技術可以解決一個問題，但必然存在帶來一個問題的風險！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失敗！

穩(wěn)定性和業(yè)務可持續(xù)性通常比性能更重要！
優(yōu)化不可避免涉及到變更，變更就有風險！
優(yōu)化使性能變好，維持和變差是等概率事件！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔當風險，但會所有的人分享優(yōu)化成果！
所以優(yōu)化工作是由業(yè)務需要驅使的！?。?

1.3 誰參與優(yōu)化

數(shù)據(jù)庫管理員
業(yè)務部門代表
應用程序架構師
應用程序設計人員
應用程序開發(fā)人員
硬件及系統(tǒng)管理員
存儲管理員

1.4 優(yōu)化方向

安全優(yōu)化（業(yè)務持續(xù)性）
性能優(yōu)化（業(yè)務高效性）

1.5 優(yōu)化的范圍及思路

優(yōu)化范圍：
存儲、主機和操作系統(tǒng):
    主機架構穩(wěn)定性
    I/O規(guī)劃及配置
    Swap
    OS內(nèi)核參數(shù)
        網(wǎng)絡問題
應用程序:（Index，lock，session）
        應用程序穩(wěn)定性和性能
        SQL語句性能
    串行訪問資源
    性能欠佳會話管理
數(shù)據(jù)庫優(yōu)化:（內(nèi)存、數(shù)據(jù)庫設計、參數(shù)）
    內(nèi)存
    數(shù)據(jù)庫結構(物理&邏輯)
    實例配置
優(yōu)化效果和成本的評估：

2. 優(yōu)化工具的使用

2.1 系統(tǒng)層面的

2.1.1 CPU
top  
top -H
top -H -p pid
top -H -p 9618

CPU使用情況:
%Cpu(s):  
0.1 us    : 用戶進程占用CPU時間占比
我們覺得越高越好,不要滿負荷

0.2 sy    : 系統(tǒng)本身和內(nèi)核工作CPU時間占比
資源管理,維護,分配,回收等等.

并發(fā)連接過高 .
鎖爭用
99.8 id   : 空閑

0.0 wa    : 等待時CPU時間占比
IO吞吐 有問題   : 硬件,鏈路,RAID 
IO/PS  峰值     :   大量隨機IO ,索引設計不合理,多表連接優(yōu)化不到位,大量子查詢,大事務.  
鎖爭用
2.1.2 MEM
KiB Mem :  4028432 total,  3774464 free,   116240 used,   137728 buff/cache
1.2.1 名稱介紹
total :總內(nèi)存大小
free  ：空閑的
used  ：在使用的
buff/cache ：緩沖區(qū) 和 緩存
2.1.3 內(nèi)存管理子系統(tǒng)：
slab Allocator
buddy system 
程序=指令+數(shù)據(jù)
對于page cache來講(OS buffer)
1. 內(nèi)存的可用空間的計算   free +buffer cache 
2. 內(nèi)存回收(buffer)的方式:
        (1) 寫入磁盤
        (2) swap  
對于數(shù)據(jù)庫來講：需要將swap屏蔽掉
2.1.4 swap
KiB Swap:  2097148 total,  2097148 free,        0 used.  3701464 avail Mem 
Linux 6操作系統(tǒng)，默認回收策略（buffer cache），不立即回收策略
內(nèi)存使用達到100%-60%時候，40% 會使用swap
Linux 7操作系統(tǒng)
內(nèi)存使用達到100%-30%（70%）時候，才會時候swap
cat /proc/sys/vm/swappiness 
30  
echo 0 >/proc/sys/vm/swappiness    的內(nèi)容改成0（臨時）
vim /etc/sysctl.conf
添加:
vm.swappiness=0
sysctl -p

2.2. iostat 命令

dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
現(xiàn)象說明

1. IO 高 cpu us 也高,屬于正?，F(xiàn)象
2. CPU  us高  IO很低  ,MySQL 不在做增刪改查,有可能是存儲過程,函數(shù),排序,分組,多表連接
3. Wait,SYS 高  , IO低:  IO出問題了,鎖等待過多的幾率比較大. 
IOPS：每秒磁盤最多能夠發(fā)生的IO次數(shù)，這是個定值 
頻繁小事務,IOPS很高,達到閾值,可能IO吞吐量沒超過IO最大吞吐量.無法新的IO了
存儲規(guī)劃有問題.

2.3 數(shù)據(jù)庫優(yōu)化工具

    show status  
    show variables 
    show index  
    show processlist 
    show slave status
    show engine innodb status 
    desc /explain 
    slowlog
        
    擴展類深度優(yōu)化:
    pt系列
    mysqlslap 
    sysbench 
    information_schema 
    performance_schema
    sys

3. 優(yōu)化思路分解

3.1 硬件優(yōu)化

3.1.1 主機
(1) 真實的硬件（PC Server）: 
    DELL  R系列 ，華為，浪潮，HP，聯(lián)想 ,曙光
        
(2)云產(chǎn)品：
    ECS、
    數(shù)據(jù)庫RDS、DRDS\Polardb  , TDSQL

(3) IBM 小型機: 
 P6  570  595  
 P7 720  750 780     
 P8
3.1.2 CPU根據(jù)數(shù)據(jù)庫類型
OLTP  在線事務處理系統(tǒng).
OLAP  數(shù)據(jù)分析,處理 

IO密集型：線上系統(tǒng)，OLTP主要是IO密集型的業(yè)務，高并發(fā)
CPU密集型：數(shù)據(jù)分析數(shù)據(jù)處理，OLAP，cpu密集型的，需要CPU高計算能力（i系列，IBM power系列）

CPU密集型： I 系列的，主頻很高，核心少 
IO密集型：  E系列（至強），主頻相對低，核心數(shù)量多
3.1.3 內(nèi)存
建議2-8倍cpu核心數(shù)量 （ECC）
3.1.4 磁盤選擇
SATA-III   SAS    Fc    SSD（sata） pci-e  ssd  Flash
主機 RAID卡的BBU(Battery Backup Unit)關閉
3.1.3 存儲設備
根據(jù)存儲數(shù)據(jù)種類的不同，選擇不同的存儲設備
配置合理的RAID級別(raid5、raid10、熱備盤)   
r0 :條帶化 ,性能高
r1 :鏡像，安全
r5 :校驗+條帶化，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高，最少四塊盤，浪費一半的空間（高IO要求）
3.1.4 網(wǎng)絡
1、硬件買好的（單卡單口）
2、網(wǎng)卡綁定（bonding），交換機堆疊
以上問題，提前規(guī)避掉。

3.2 操作系統(tǒng)優(yōu)化

3.2.1 Swap調(diào)整
echo 0 >/proc/sys/vm/swappiness的內(nèi)容改成0（臨時），
vim  /etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p

擴展:內(nèi)存管理

1. 三個區(qū)域 :
    常駐內(nèi)存集: 程序運行服務的
    頁緩存page cache: 
        FREE list 
        LRU  list
    匿名頁:

2. Slab Allocator
    將 page cache 劃分成了好多條鏈狀結構.
類型一: 
    Free list(空閑的)
    ==================================================
     |    |
     o    o
    
類型二 :
    LRU list (正在被使用的)
    
    冷================================================熱
      i    i 
      0    0
    
3. buddy system (內(nèi)存伙伴系統(tǒng))
    內(nèi)存回收和重利用
    
cat /proc/sys/vm/swappiness   ====> 0

free list 上為 0;

buddy system 進行內(nèi)存回收和重利用 
1. 優(yōu)先釋放 Cache(負責查詢類的內(nèi)存結構),從冷到熱進行釋放. 
2. Cache沒法釋放時,會根據(jù)buffer從冷導熱,進行回收和重用內(nèi)存
3. 所有可被釋放 buffer 或cache,已經(jīng)全部被回收重用了,還是內(nèi)存緊缺的話
此時,swap還是會被使用. 


內(nèi)存泄露問題:     
8G   使用率達到了 95%以上
innodb_buffer_pool_size=2G 
redo_buffer_size=256M 
其他內(nèi)存總共: 1G 左右
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
3.2.2 IO調(diào)度策略
centos 7 默認是 deadline
cat   /sys/block/sda/queue/scheduler
cat   /sys/block/vda/queue/scheduler


# 臨時修改為deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler 
vi /boot/grub/grub.conf

更改到如下內(nèi)容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet


IO ：
    raid
    no lvm
    ext4或xfs
    ssd
    IO調(diào)度策略

提前規(guī)劃好以上所有問題，減輕MySQL優(yōu)化的難度。

3.3 應用層

1. 開發(fā)過程規(guī)范,標準
2. 減少慢SQL:不走索引,復雜邏輯,切割大事務.
    like '%aa%'
    != not in 
    limit >500w 
    DDL  ---> show processlist ;  ---> kill  ---> Online DDL ,pt-osc
    delete 大量數(shù)據(jù). ----> pt-archive
    update : 索引  ,   鎖.

3. 避免業(yè)務邏輯錯誤,避免鎖爭用.
    a :   update  id=10 ; update  id=100;
    b :  update   id=100; update id=10;


這個階段,需要我們DBA深入業(yè)務,或者要和開發(fā)人員\業(yè)務人員配合實現(xiàn)

優(yōu)化,最根本的是"優(yōu)化"人.
                    ----oldguo
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容