深度优化LNMP之MySQL
MySQL數據庫優化框架體系
1.硬件層面優化?
2.操作系統層面優化?
3.MySQL數據庫層面優化?
4.MySQL安全優化?
5.網站集群架構上的優化?
6.MySQL流程、制度控制優化
硬件層面優化
1、數據庫物理機采購
CPU:?64位CPU,一臺機器2-16顆CPU。至少2-4顆,L2(緩存)越大越好?
內存:?96-128G,MySQL 3-4個實例。32-64G,1-2實例?
硬盤:機械:選SAS,數量越多越好,轉速越高越好15k?
性能:SSD(高并發) >?SAS(普通業務線上) >SATA(線下)?
選SSD:使用SSD或者PCIe SSD設備,可提升上千倍的IOPS效率。?
隨機IO:SAS單盤能力300IOPS?SSD隨機IO:單盤能力可達35000IOPS Flashcache HBA卡?
raid磁盤陣列:?4快盤:RAID0>RAID1(推薦)>RAID5(少用)>RAID1?
主庫選擇raid10,從庫可選raid5/raid0/raid10,從庫配置等于或大于主庫?
網卡:使用多塊網卡bond,以及buffer,tcp優化?
千兆網卡及千兆、萬兆交換機?
提示:?
數據庫屬于IO密集型服務,硬件盡量不要使用虛擬化。?
Slave硬件要等于或大于Master的性能
2、企業案例:
百度:某部門IBM服務器為48核CPU,內存96GB,一臺服務器跑3~4個實例:?
sina:服務器是DELL R510巨多,CPU是E5210,48GB內存,硬盤12*300G SAS,做RAID10
3、服務器硬件配置調整
(1)服務器BIOS調整:?
提升CPU效率參考設置:?
a.打開Perfirmance Per Watt Optimeized(DAPC)模式,發揮CPU最大性能,數據庫通常需要高運算量?
b.打開CIE和C States等選項,目的也是為了提升CPU效率?
c.?Memory Frequency(內存頻率)選擇Maximum Performance(最佳性能)?
d.內存設置菜單中,啟動Node Interleaving,避免NUMA問題?
(2)陣列卡調整:?
a.購置陣列卡同時配備CACHE及BBU模塊(機械盤)?
b.設置陣列寫策略為WEB,甚至OFRCE WB?(對數據安全要求高)(wb指raid卡的寫策略:會寫(write back))?
c.嚴禁使用WT策略,并且關閉陣列預讀策略
操作系統層面優化
1.操作系統及MySQL實例選擇
1.一定要選擇x86_64系統,推薦使用CentOS6.8?linux,關閉NUMA特性?
2.將操作系統和數據分開,不僅僅是邏輯上,還包括物理上?
3.避免使用Swap交換分區?
4.避免使用軟件磁盤陣列?
5.避免使用LVM邏輯卷?
6.刪除服務器上未使用的安裝包和守護進程
2.文件系統層優化
(1)調整磁盤Cache mode
(2)采用Linux I/O scheduler算法deadline?
deadline調度參數?
對于Centos Linux建議?read_expire = 1/2 write_expire
Linux I/O調度方法?Linux deadline io 調度算法
(3)采用xfs文件系統?
業務量不是很大也可采用ext4,業務量很大,推薦使用xfs:調整XFS文件系統日志和緩沖變量?
XFS高性能設置?
(4)mount掛載文件系統?
增加:async,noatime,nodiratime,nobarrier等
noatime
訪問文件時不更新inode的時間戳,高并發環境下,推線顯示應用該選項,可以提高系統I/O性能
async
寫入時數據會先寫到內存緩沖區,只到硬盤有空檔才會寫入磁盤,這樣可以提升寫入效率!風險為若服務器宕機或不正常,會損失緩沖區中未寫入磁盤的數據 解決辦法:服務器主板電池或加UPS不間斷電源
nodiratime
不更新系統上的directory?inode時間戳,高并發環境,推薦顯示該應用,可以提高系統I/O性能?
?
nobarrier
不使用raid卡上電池?
(5)Linux 內核參數優化?
1.將vm,swappiness設置為0-10?
2.將vm,dirty_background_ratio設置為5-10,將vm,dirty_ratio設置為它的兩倍左右,以確保能持續將臟數據刷新到磁盤,避免瞬間I/O寫,產生嚴重等待?
3.優化TCP協議棧
4.網絡優化?
#優化系統套接字緩沖區
#優化TCP接收/發送緩沖區
#優化網絡設備接收隊列
5.其他優化
提示:面試的時候說框架,然后說一兩個小的優化參數即可?
更多內核可以參考“跟老男孩學運維書的第三章”以及我們的博客,近期將會更新
MySQL數據庫層面優化
my.cnf參數優化
此優化主要針對innodb引擎
更多內核參數:my-innodb-heavy-4G.cnf 配置文件參數介紹?
MySQL工具mysqlreport?我們可以使用工具來分析MySQL的性能?
如何才能做到網站高并發訪問??生產環境中對于防范DDOS攻擊的討論?
面試可能會問到DOOS 攻擊防護
關于庫表的設計規范
SQL語句的優化
索引優化?
1)白名單機制一百度,項目開發啊,DBA參與,減少上線后的慢SQL數據?
抓出慢SQL,配置my.cnf
2)慢查詢的日志分析工具——mysqlsla或pt-query-digest(推薦)
3)每天晚上0點定時分析慢查詢,發到核心開發,DBA分析,及高級運維,CTO的郵箱里?
DBA分析給出優化建議–>核心開發確認更新–>DBA線上操作處理?
4)定期使用pt-duplicate-key-checker檢查并刪除重復的索引?
定期使用pt-index-usage工具檢查并刪除使用頻率很低的索引?
5)使用pt-online-schema-change來完成大表的ONLINE DDL需求?
6)有時候MySQL會使用錯誤的索引,對于這種情況使用USE INDEX?
7)使用explain及set profile優化SQL語句
大的復雜的SQL語句拆分成多個小的SQL語句?
子查詢,JOIN連表查詢,某個表4000萬條記錄?
數據庫是存儲數據的地方,但不是計算數據的地方?
對數據計算,應用類處理,都要拿到前端應用解決。禁止在數據庫上處理?
搜索功能,like ‘%oldboy%’ 一般不要用MySQL數據庫?
使用連接(JOIN)來代替子查詢(Sub_Queries)?
避免在整個表上使用cout(*),它可能鎖住整張表?
多表聯接查詢時,關聯字段類型盡量一致,并且都要有索引。?
在WHERE子句中使用UNION代替子查詢?
多表連接查詢時,把結果集小的表(注意,這里是指過濾后的結果集,不一樣是全表數據量小的)作為驅動表
爬蟲獲取數據的過程
**網站集群架構上的優化
1.服務器上跑多實例,2-4個(具體需要看服務器的硬件信息) 2.主從復制一主無從,采用mixed模式,盡量不要跨機房同步(進程遠程讀本地寫) 3.定期使用pt-table-checksum、pt-table-sync來檢查并修復mysql主從復制的數據差異 4.業務拆分:搜索功能,like '%oldboy% ' 一般不要用MySQL數據庫 5.業務拆分:某些業務應用使用nosql持久化存儲,例如:memcached、redis、ttserver例如粉絲關注,好友關系等 6.數據庫前端必須要加cache,例如:memcached,用戶登錄,商品查詢 7.動態的數據庫靜態化,整個文件靜態化,頁面片段靜態化 8.數據庫集群與讀寫分離。一主多從,通過程序或dbproxy進行集群讀寫分離 9.單表超過800萬,拆庫拆表。人工拆表拆庫(登錄、商品、訂單) 10.百度、阿里國內前三公司,會選擇從庫進行備份,對數據庫進行分庫分表MySQL 流程、制度控制優化
任何一次人為數據庫記錄的更新,都要走一個流程:
a.人的流程:開發-->核心開發-->運維或DBA b.測試流程:內網測試-->IDC測試-->線上執行 c.客戶端管理,phpmyadminMySQL基礎安全
1.啟動程序700,屬主和用戶組為MySQL 2.為MySQL超級用戶root設置密碼 3.如果要求嚴格可以刪除root用戶,創建其他管理用戶,例如admin 4.登錄時盡量不要在命令行暴露密碼,備份腳本中如果有密碼,給設置700,屬主和密碼組為mysql或root 5.刪除默認存在的test庫 6.初始刪除無用的用戶,只保留 | root | 127.0.0.1 | | root | localhost | 7.不要一個用戶管理所有的庫,盡量專庫專用戶 8.清理mysql操作日志文件~/.mysql_history(權限600,可以不刪) 9.禁止開發獲得到web連接的密碼,禁止開發連接操作生產對外的庫 10.phpmyadmin安全 11.服務器禁止設置外網IP 12.防SQL注入(WEB)php.ini或web開發插件監控,waf控制總結
以上是生活随笔為你收集整理的深度优化LNMP之MySQL的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 【译】 Stealing the fun
- 下一篇: 实现页面适配_微信公众号文章页面适配深色