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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類