MySQL優化聊兩句

来源:http://www.cnblogs.com/verrion/archive/2016/09/24/mysql_optimised.html
-Advertisement-
Play Games

原文地址:http://www.cnblogs.com/verrion/p/mysql_optimised.html MySQL優化聊兩句 MySQL不多介紹，今天聊兩句該如何優化以及從哪些方面入手，很多運維從業者一說起優化就不知所措，當運營過程中某個參數值到達一定閥值之後，就會出現各種問題，很多運 ...


原文地址:http://www.cnblogs.com/verrion/p/mysql_optimised.html

MySQL優化聊兩句

 

MySQL不多介紹，今天聊兩句該如何優化以及從哪些方面入手，很多運維從業者一說起優化就不知所措，當運營過程中某個參數值到達一定閥值之後，就會出現各種問題，很多運維工程師這時不知所措，第一可能也從來沒有處理過類似情況，另一方面業務又緊張，系統不正常，首要任務是解決問題，那沒辦法只能重啟了，我們先不說重啟是否可行，比如有些應用可以重啟並且解決了問題，但如沒有解決問題，或者爆發出新的問題怎麼辦，比如血崩情況。所以我們應該從問題本身出發，第一查看日誌，然後再具體問題具體分析等。

 

上面說了故障處理的一方面解決辦法，同樣適合於優化處理之道，根據本人學習和經驗，這裡說一下我的優化思路：首先要有這點認同感，任何系統應用都一定能達到瓶頸的時候，那我們為什麼不事先去想想瓶頸在哪裡，而不是當問題出現了，才去臨時抱佛腳。尤其對於運維來說，任何事情都要具有可把控性，預先想好大部分情況的預案和解決措施。

 

為什麼要優化：

系統在現有的環境中達到處理能力的最大限制，可能多一個請求或者連接都能對業務造成影響

 

如何優化：

問題關鍵在於：硬體資源（CPU、記憶體、磁碟、網路）等已經負荷啦。這樣我們不很明瞭了，找出是哪個部分使硬體資源超負荷了，然後採取相應的措辭去調整，優化是個過程，直至系統已經優化足夠好了，還是沒有解決問題，只有橫向擴展加硬體資源啦。

 

根據上面所說的，我總結一下，mysql(其他的也一樣)優化應該從以下幾點出發，從體系架構層面一層一層的往上講解：

1、底層硬體層：最基礎的也是最重要的，下麵幾層的優化最終是解決這層瓶頸；

2、操作系統層：搭載怎樣的操作系統、文件系統、網路參數等選型和調優；

3、集群架構層：業務發展架構也要隨之發展；

4、數據應用層：mysql安裝、配置文件參數設置等；

5、SQL調優層：SQL語句優化，好的sql語句成功案例能提高70%的性能；

6、行為模式層：安全、流程、制度等優化；

 

下麵一一說道，由於本人水平有限，有錯誤之處，請批評指正

 

一：底層硬體層

1、硬體資源列表

硬體資源

採購或購買雲伺服器配置參考

CPU

64位，2-16顆粒，L2越大越好

記憶體

96/128G跑3/4個實例；32/64G跑1-2個實例為佳

硬碟

機械硬碟(SAS)，數量越多越好，轉速越高越好

單盤能力對比：SAS(300IOPS)>SSD(35000IOPS)

性能：SSD>SAS>SATA

磁碟陣列

性能：RAID0>RAID10(推薦)>RAID5(少用)>RAID1

註意：主庫選擇raid10，從庫可選raid0/raid5/raid10，從庫配置等於或大於主庫 

網卡

至少千兆網卡及千兆萬兆交換機 
多塊網卡bond設置

註意：資料庫屬於IO密集型服務，硬體儘量避免使用虛擬化。 
      Slave硬體資源要等於或大於Master的

 

2、硬體配置列表(雲服務跳過這一步)

設置選項

具體操作

BIOS系統

CPU優化設置（以DELL系列伺服器為例）：

1.打開Perfirmance Per Watt Optimeized（DAPC）模式，提高CPU運算能力

2.打開CIE和C States等選項，減少cpu調度演算法時間，提升效率

記憶體優化設置

1. Memory Frequency（記憶體頻率）選擇Maximum Performance（最佳性能）

2.記憶體設置菜單中，啟動Node Interleaving，避免NUMA問題

磁碟陣列卡

1.陣列卡要具有CACHE及BBU模塊

2.設置寫策略為write back(感興趣可以瞭解Cache兩種寫策略：write-through與write-back )，並且關閉陣列預讀策略

 

二：操作系統層

1、文件系統相關

優化層級

具體操作

操作系統

無疑選擇x86_64架構，（RedHat>CentOS）基於6.8穩定版

數據規劃

基於物理塊層面分別存放操作系統文件、mysql應用和數據文件

交換分區

不要使用swap空間，酌情使用共用記憶體/dev/shm

軟體磁碟陣列

不要使用

LVM邏輯捲

不要使用

註意：下麵幾點設置目的是為了提升I/O性能

I/O調度演算法

使用deadline調度參數，建議 read_expire = 1/2 write_expire

echo 500 > /sys/block/sdc/queue/iosched/read_expire

echo 1000 > /sys/block/sdc/queue/iosched/write_expire

xfs文件系統

業務量不是很大可採用ext4，業務量很大推薦使用xfs：並且調整XFS文件系統日誌和緩衝變數

數據目錄掛載

掛載設置以下參數：

sync:往硬碟寫數據時先寫入記憶體緩衝區,待硬碟空閑時再同步下來，大大提升效率，缺點就是如果伺服器宕機或不正常，會損失緩衝區中未寫入磁碟的數據，解決辦法：利用主板電池或UPS不間斷電源供電；

noatime:access文件時不更新inode的時間戳，高併發環境下，可以提高系統I/O性能，對select操作尤為重要；

nodiratime:不更新系統上的directory inode時間戳，可以提高系統I/O性能

nobarrier:不建議使用raid卡電池

 

2、網路參數相關

內核參數

具體操作

swappiness

1.cat /proc/sys/vm/swappiness 預設為60

2.echo “vm.swappiness=10” >> /etc/sysctl.conf(一般設置0-10)

3.sysctl -p

ratio

vm.dirty_background_ratio設置為5-10

vm.dirty_ratio設置為它的兩倍左右，

目的：確保能持續將臟數據刷新到磁碟，避免瞬間I/O寫，產生嚴重等待 ，方法如上

TCP相關參數

註意：熟悉TCP11種狀態轉換原理機制

1、減少TIME_WAIT(基本所有應用都要配置)

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

2、減少FIN-WAIT-2狀態時間

net.ipv4.tcp_fin_timeout=10

3、減少TCP KeepAlived連接偵測的時間

net.ipv4.tcp_keepalived_time=600

4、提高系統最大SYN半連接數（預設1024）排隊長度

net.ipv4.tcp_max_syn_backlog = 16384

5、減少系統SYN連接重試次數(預設5)

net.ipv4.tcp_synack_retries = 1

6、拋棄在內核里建立的連接之前發送SYN包的數量

net.ipv4.tcp_sync_retries = 1

7、允許系統打開的埠範圍

net.ipv4.ip_local_prot_range = 4500 65535

網路相關參數

註意：熟悉網路相關知識

1、調整socket套接字緩衝區

net.core.rmem_max=16777216 #最大socket讀buffer

net.core.wmem_max=16777216 #最大socket寫buffer

net.core.wmem_default = 8388608

net.core.rmem_default = 8388608

2、調整TCP接收/發送緩衝區

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

3、調整網路設備接收隊列

net.core.netdev_max_backlog=3000

其他優化

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

 

三：集群架構層

架構設計

註意事項

實例

根據伺服器硬體資源和業務需求，一般跑2-4個

方案

Mysql架構多種多樣，擴展性極強，這裡不詳細介紹

如：主從複製一主多從架構，採用mixed模式

業務量數據量大的優化架構：

1、讀寫分離：通過程式或者dbproxy，主寫從讀；

2、垂直分庫，水平分表（一般建議單表不超過4000萬）

數據掃描

周期性使用pt-table-checksum、pt-table-sync來檢查並修複主從複製的數據差異

緩存機制

DB層前端添加cache層，比如memcached/redis，用來存儲session、token、好友任務排行榜等某些業務

靜態化

動態的資料庫靜態化：比如整個文件靜態化，頁面欄位靜態化

規避選項

比如：SQL有大量模糊查詢業務，儘量避免使用mysql資料庫

註意：資料庫架構是一門很大的學問，水平有限只是簡單聊兩句

 

四：數據應用層

註意：下麵參數值的優化主要針對innodb引擎，如果要使用MyISAM引擎，需要調整key_buffer_size值

參數項

設置參考

innodb_buffer_pool_size

物理記憶體的50-70%左右

inno_flush_log_at_trx_commit

不丟失數據設置為1，根據業務需求來

sync_binlog

不丟失數據設置為1，根據業務需求來

innodb_file_per_table

設置為1：使用獨立表空間

innodb_data_file_path

ibdata1:1G:autoextend

innodb_log_file_size

256M，這個參數和下麵那個參數搭配使用

innodb_log_files_in_group

2

innodb_log_file_size

不要設置過大，第一保證記錄日誌更快，第二保障增量恢複數據庫時間更短

long_query_time

設置慢查詢sql的時間

max_connection

最大連接數，根據業務場景選擇

max_connection_error

最大鏈接錯誤數，官方建議10萬以上

open_files_limit

10倍max_connection

innodb_open_files

同上

table_open_cache

同上

table_definition_cache

同上

tmp_table_szie

Session分配情況，根據業務來設置

max_heap_table_size

同上

sort_buffer_size

同上

join_buffer_size

同上

read_buffer_size

同上

read_rnd_buffer_size

同上

query cache

官方建議關閉，要設置不要超過512M

提示：更多內核參數有時間詳細介紹，一切來源於官方文檔，我只是千萬運維的一枚搬運工而已。

 

五：SQL調優層

1、資料庫表設計的一些想法，DBA必須參數開發

設計思路

具體操作

字元集

建議使用utf-8(中文影響)，官方預設用Latin1，因後者快

字元串

1.固定字元串使用定長char，儘量避免varchar

2.變長字元串varchar，不要用char（因UTF8忽略此影響）

上面兩者如果能預測業務存儲長度，長度能短則短

約束

欄位屬性添加not、null，並且表結構設計無關的自增列做主鍵

特殊類型

1.文本欄位(備註，博文內容等)設置為enum類型

2.text/blob類型儘量不用，對select查詢性能影響極大

隨機I/O

嚴禁使用select *語句，查詢特定需要的列即可

索引

1.多用複合索引(除特殊建獨立索引)，尤其cardinality很小時候

  如：該列唯一值總數少於255個，就不要建立獨立索引

2.對於varchar類型，通常取其50%或更少長度創建首碼索引就能滿足90%左右查詢需求，沒必要整個長度創建反而性能降低

 

2、語句優化

設計思路

具體操作

前期準備

項目開發階段，DBA必須參與設計SQL，並查找慢sql

配置my.cnf

long_query_time = 2

log-slow-queries=/data/mysql/slow-log.log

log_queries_not_using_indexs = true

工具使用

日誌慢sql分析工具mysqlsla或pt-query-digest，還有很多工具

  1. 根據報告結果自己寫腳本在某個固定時間抓取當天慢sql
  2. 計劃任務某個固定時間點把上面結果發郵件至相關人事分析

索引檢測工具

1.定期使用pt-duplicate-key-checker檢查並刪除重覆的索引 

2.定期使用pt-index-usage檢查並刪除使用頻率很低的索引 
3.使用pt-online-schema-change來實現大表的online ddl需求 

SQL設計

1.搜索業務，比如like查詢，不建議使用MySQL

2.不要使用count(*)，可能鎖表

3.多表連接查詢，關聯欄位使用索引而且儘量一致

4.多表連接查詢，結果集小的表作為驅動表

5. where子句儘量使用union代替子查詢 

SQL拆分

大的複雜的sql語句拆分為各個功能性小sql

比如：join連表查詢，子查詢，單表超過4000萬條記錄等

瓶頸問題

慢sql解決方法：

  1. show processlist:找出特定SQL占用的資源影響性能
  2. 使用explain或者set profile列印報表
  3. 具體問題具體分析，有時研發參與

 

六：行為模式層

標準規範

具體操作

啟動程式

文件許可權700，屬主和用戶組都為mysql

超級用戶

MySQL超級用戶root設置複雜的密碼，比如：我生成環境幹掉root用戶，創建其他管理用戶並且名字不是大眾類型

登錄行為
  1. 命令行不要暴露密碼
  2. 備份腳本設置密碼保護比如加密(轉換成MD5值)

運維思想

運維有種約定：有種配置叫預設，導致很多運維人員不去關註該參數的作用和來源

  1. 刪除預設存在的test庫
  2. 刪除無用的用戶，只保留[email protected]和root@localhost

責任問題

禁止一個用戶管理所有的DB，建立起一一服務關係

許可權問題
  1. 禁止開發人員得到生成環境程式連接的密碼並且限制連接
  2. 禁止開發程式過程中使用不帶子句的DDL語句
  3. Phpmyadmin管理工具安全問題

環境問題
  1. 伺服器禁止設置外網IP
  2. 定期清理MySQL操作記錄文件(.mysql_history)
  3. 防SQL註入攻擊：比如nginx+lua編寫WAF控制等

上線行為
  1. 必須制定上線變更流程制度（開發—測試—運維）
  2. 測試流程：開發環境—測試環境—線上執行—驗證

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android 手機衛士--彈出對話框
    在《Android 手機衛士--解析json與消息機制發送不同類型消息》一文中，消息機制發送不同類型的信息還沒有完全實現，在出現異常的時候，應該彈出吐司提示異常，代碼如下： 因為彈出吐司操作比較常見，於是將其寫成工具類進行簡單的封裝，方便以後調用。 本文地址：http://www.cnblogs.c ...
  • 視頻屏幕左半部分上下滑動改變亮度，右半部分上下滑動改變聲音
    說明：實現功能：（1）屏幕右半部分上滑，聲音變大，下滑，聲音變小 屏幕左半部分上滑，亮度變大，下滑，亮度變小（2）如果亮度>1或者小於0.2時，手機震動 private float startY;//記錄手指按下時的Y坐標 private float startX;//記錄手指按下時的Y坐標 pri ...
  • Android 手機衛士--解析json與消息機制發送不同類型消息
    本文地址：http://www.cnblogs.com/wuyudong/p/5900800.html，轉載請註明源地址。 1、解析json數據 解析json的代碼很簡單 2、使用消息機制發送不同類型消息 在獲取伺服器端json數據後，得到app的版本號，通過和客戶端的版本號進行對比 可以使用and ...
  • iOS自帶TTS技術的實現即語音播報
    文本轉語音技術, 也叫TTS, 是Text To Speech的縮寫. iOS如果想做有聲書等功能的時候, 會用到這門技術. 一，使用iOS自帶TTS需要註意的幾點： 二，代碼示例, 播放語音 三，AVSpeechSynthesizer介紹 這個類就像一個會說話的人, 可以”說話”, 可以”暫停”說 ...
  • iOS10 App適配許可權 Push Notifications 字體Frame 遇到的坑!!!!
    添加配置許可權 添加Push Notifications支持 開關開啟後會自動生成xxxx.entitlements文件 這裡需要註意幾點 生成的該文件是否包含到你的打包工程中Bundle Resources中 如果沒有手動添加進去 如果工程有多個Target 且多個證書在一起建議不要使用 Autom ...
  • Android Handler消息傳遞機制
    handler通俗一點講就是用來在各個線程之間發送數據的處理對象。在任何線程中，只要獲得了另一個線程的handler，則可以通過 handler.sendMessage(message)方法向那個線程發送數據。基於這個機制，我們在處理多線程的時候可以新建一個thread，這個thread擁有UI線程... ...
  • iOS --- UIWebView的載入本地數據的三種方式
    UIWebView是IOS內置的瀏覽器，可以瀏覽網頁，打開文檔 html/htm pdf docx txt等格式的文件。 safari瀏覽器就是通過UIWebView做的。 伺服器將MIME的標識符等放入傳送的數據中告訴瀏覽器使用那種插件讀取相關文件。 uiwebview載入各種本地文件（通過loa ...
  • [SQL] SQL 基礎知識梳理（一）- 資料庫與 SQL
    SQL 基礎知識梳理（一）- 資料庫與 SQL 序 目錄 What's 資料庫 資料庫結構 SQL 概要 創建表 刪除和更新表 1-1 What's 資料庫 1.資料庫（Database，DB）：將大量數據保存起來，通過電腦加工而成的可以進行高效訪問的數據集合。如：大型-銀行存儲的信息，小型-電話 ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類