大型網站MySQL深度最佳化揭秘 2

標籤：

學無止境，老男孩教育成就你人生的起點！

相信自己相信老男孩！！！

老男孩-51cto-公開課-大型網站MySQL深度最佳化揭秘部分整理有沒跟上的抱歉

?

目錄

大型網站MySQL深度最佳化揭秘????2

第1章最佳化的思路和線路????2

1.1 網站最佳化的思路????2

1.2 MySQL最佳化，nginx這樣的東西怎麼最佳化？????2

第2章硬體層面最佳化????3

2.1 資料庫物理機????3

2.1.1 CPU????3

2.1.2 Memory????3

2.1.3 disk(磁碟IO)????3

2.1.4 RAID陣列????3

2.1.5 網卡????3

2.1.6 案例????4

2.2 硬體調整????4

2.2.1 BIOS調整提高CPU效能????4

2.2.2 陣列卡調整????4

第3章軟體層面最佳化????4

3.1 作業系統????4

3.2 檔案系統層最佳化????5

3.3 核心層面最佳化????5

第4章 MySQL層面最佳化????5

4.1 my.cnf參數的最佳化????6

4.2 庫和表的設計最佳化????6

4.3 SQL語句的最佳化????7

4.3.1 抓出來慢查詢????7

4.3.2 每天產生slow.log????7

4.3.3 盡量不用子查詢,用join替代????8

第5章網站集合群架構上來最佳化資料庫????8

5.1 伺服器跑多執行個體2-4個????8

5.2 業務拆分:????8

5.3 資料庫前端加緩衝????8

5.4 備份策略????9

第6章流程制度安全????9

?

?

大型網站MySQL深度最佳化揭秘

1. 最佳化的思路和線路
2. 網站最佳化的思路

解答：從使用者開啟瀏覽器，到返回網頁內容這個過程最佳化每一個細節

1. MySQL最佳化，nginx這樣的東西怎麼最佳化？

建議：根據OSI 7層模型，從下到上。。。以這個為線路

1. 硬體層面最佳化
2. 資料庫物理機
   1. CPU

64位的CPU,伺服器2-16,CPU 一般2-4顆 L1,L2越大越好

1. Memory

48G-96G-128G-256G

48G 2-3個執行個體

96G 3-4個執行個體

1. disk(磁碟IO)

資料庫是IO密集應用

1. 機械盤

SAS(不選擇SATA),300G*12塊,磁碟數量越多IO越高,SAS 15k的硬碟

1. SSD????

測試對比:SAS單盤隨機IO,3000IOPS, SSD單盤隨機IO達到上萬

1. RAID陣列

選硬體 RAID (0>10>5>1)

1. 網卡

至少千兆(bond), 萬兆交換器

資料庫伺服器盡量不用虛擬化

SLAVE伺服器配置最好是大於等於Master

從庫會接替主庫,從庫配置太低，導致延遲

1. 案例

百度:IBM伺服器,記憶體96G-128G, CPU48核心

SINA:dell r510 記憶體48G,磁碟300*12塊, RAID10

1. 硬體調整
   1. BIOS調整提高CPU效能

開啟(DAPC)模式,發揮CPU效能

啟動Node Interleaving避免NUMA問題

關閉C1E和State等

1. 陣列卡調整

配置CACHE和BBU模組(機械盤)

寫策略(always write back)

不要用(wt)策略

關閉陣列預讀策略

1. 軟體層面最佳化
2. 作業系統

選擇x86_64位系統

系統硬碟和資料盤分開

極端情況下不分swap分區

避免使用作業系統的軟raid

避免使用LVM

專庫專用不要跑(LNMP,TOMCAT)

1. 檔案系統層最佳化

調整Cache mode

啟動wce=1(Write Cache Enable)

RCD=0(Read Cache Disable)

系統調度演算法預設cfq(比較中庸),資料庫選擇noop,deadline.針對deadline可以調節參數(核心參數)

Centos 6.8 預設ext4可以作為資料庫的檔案系統,房屋呢量大的話,XFS就更好

Centos7預設也選擇了XFS,調整XFS日誌,緩衝參數.

mount參數很重要 –o async,noatime,nodirname,nobarrier等

1. 核心層面最佳化

參考連結:http://oldboy.blog.51cto.com/2561410/1336488

vm.swappiness設定為0,或者0-5,讓資料庫盡量不使用swap

vm.dirty_background_ratio設定5-10,vm.dirty_ratio設定前面的2倍.持續將系統資料刷到磁碟.

參考連結:http://blog.sina.com.cn/s/blog_448574810101k1va.html

減少time_wait

net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_tw_reuse=1,

net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600

1. MySQL層面最佳化

參考連結:http://oldboy.blog.51cto.com/2561410/1726517

1. my.cnf參數的最佳化

如果我們採用myisam引擎,key_buffer_size加大.採用innodb

推薦使用innodb, 5.5.5以後預設都是innodb引擎

innodb_buffer_pool_size, 調整為記憶體的50%,單一實例.多執行個體各25%

innodb_flush_log_at_trx_commit, sync_binlog, 設定為1, 資料可以丟失的話(不重要),可以設定為0, 從庫都設定為0,事物的log,多長時間刷入到硬碟裡

使用獨立資料表空間. innodb_file_per_table=1. 預設共用表檔案效率低

innodb_log_file_size=256M 不要給過大

log_query_time=1 ,log的日誌查詢,超過1秒的SQL語句,記錄到日誌裡,回頭看這個日誌,進行最佳化.

一些session參數,不要設定過大,一個串連就會佔用參數設定的大小.不要給過大

Sort_buffer_size, join_buffer_size,read_buffer_size,tmp_table_size,max_heap_table_size這類參數都是session,層級參數.2M6M8M就可以了.

查詢快取參數要設定小一些:query_cache_size = 64M,想要緩衝,前端加mc,redis

1. 庫和表的設計最佳化

字元集UTF-8

固定字串的內容,可以選擇char

資料庫都要給一個自增的首頁,沒什麼用途.

欄位長度,在滿足要求前提下,最短的.Varchar(16)

省份,性別,這樣內容欄位可以設定ENUM類型,mysql系統資料表(char,ENUM)

儘可能不用text/blob比較大的欄位類型(博文文章),如果使用的話,可以放到子表裡

一般針對欄位索引,盡量採用欄位的前N個字元索引,不要整個欄位索引,效率低

多用聯合索引,有首碼特性,少用獨立索引,性別列,不要建立索引了.效果差

1. SQL語句的最佳化

索引最佳化(營運最常用的)

1. 抓出來慢查詢

百度:白名單的方法,設計程式時參與設計,程式上線串連資料庫,有個控制查庫的東西,請示放我庫裡,才能查詢,資料庫沒有或者減少慢查詢

經常給開發培訓,DB水平更高

現在網站慢了,show full processlist; 抓慢查詢,連續執行兩下,間隔1-2秒,如果還有,懷疑他是慢查詢.

日常:把慢查詢語句記錄到log裡面

my.cof

long_query_time=2 超過兩秒的查詢

log_queryies_not_using_indexes沒有超過兩秒沒有走索引

log_slow_queries=/data/3306/slow.log

1. 每天產生slow.log

按天切割slow.log,切割後分析軟體分析(mysqlsla,-pt-query-digest)

mysqldumpslow,myprofi. 最佳化的語句,不一定是單條佔用時間長的,頻率搞,單條不長,但是總時間很長的,這些可能也是最佳化的重點.

對於營運來講,慢查詢SQL發給開發.

有能力和開發一起搞

用explain測試語句是否走索引,set profile深度查看語句執行情況.

檢查重複資料刪除的索引,工具pt-duplicate-key-checker

效率很低的索引,檢查刪除,pt-index-usage工具

1. 盡量不用子查詢,用join替代

資料庫是存放資料的地方,不是計算資料的地方,計算放在web

搜尋功能,like "%daf%",不用資料庫搜尋

在語句中盡量去掉in or <> 字元

1. 網站集合群架構上來最佳化資料庫

參考連結:http://oldboy.blog.51cto.com/2561410/775056

1. 伺服器跑多執行個體2-4個

   主從複製最多9個,建議1主5從, 主庫採用mixed模式,不要跨機房複製(如果是,遠程寫,本地讀

2. 業務拆分:

   搜尋功能,like "%daf%",不用資料庫搜尋

   搜尋軟體：Sphinx,Xapian,Solr

   粉絲關注,好關係,統計這類應用比較簡單,不用資料庫,放到redis(想要持久haunted)

3. 資料庫前端加緩衝

   動態內容轉靜態化(資料庫的資料,轉成html檔案,放到儲存上),好處就是可以使用CDN緩衝

   資料庫採用讀寫分離,讀從庫,寫主庫.

   相關軟體:MyCat,atlas,cobar,amoeba,MySQL-proxy

   單表超過800W,拆庫拆表,自動擴容,自動收縮.

4. 備份策略

   選擇從庫備份,鎖表,備份時間長,影響資料訪問

   備份時採用分表分庫,不分非常費勁.

5. 流程 制度 安全

   50%的故障都是人為造成的.

   操作流程:開放-->核心開發-->營運或DBA

   測試流程:辦公室測試-->IDC測試環境測試-->生產環境

大型網站MySQL深度最佳化揭秘2