Mysql 性能優化 ( my.cnf )

来源:http://www.cnblogs.com/wangxiaoqiangs/archive/2016/05/17/5500675.html
-Advertisement-
Play Games

簡介： Mysql 參數優化 一、Mysql 源碼編譯參數 ## -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 指定 Mysql 安裝路徑## -DMYSQL_DATADIR=/usr/local/mysql/data 指定 Mysql 數據目錄## -DTMPDI ...



簡介：

Mysql 參數優化

一、Mysql 源碼編譯參數

shell > yum -y install gcc gcc-c++ make cmake ncurses-devel zlib-devel bison

shell > cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DTMPDIR=/usr/local/mysql/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \
-DSYSCONFDIR=/etc/my.cnf \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci

##  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql      指定 Mysql 安裝路徑
##  -DMYSQL_DATADIR=/usr/local/mysql/data          指定 Mysql 數據目錄
##  -DTMPDIR=/usr/local/mysql/data                        指定 Mysql 臨時目錄
##  -DMYSQL_UNIX_ADDR                                      指定 Mysql socket
##  -DSYSCONFDIR=/etc/my.cnf                              指定 Mysql 配置文件
##  -DWITH_MYISAM_STORAGE_ENGINE=1             安裝 MyISAM 存儲引擎
##  -DWITH_INNOBASE_STORAGE_ENGINE=1          安裝 Innodb 存儲引擎
##  -DWITH_FEDERATED_STORAGE_ENGINE=1        安裝 Federated 存儲引擎
##  -DDEFAULT_CHARSET=utf8                               指定預設字元集為 utf8
##  -DEXTRA_CHARSETS=all                                   安裝擴展字元集
##  -DDEFAULT_COLLATION=utf8_general_ci            預設排序規則 utf8_general_ci

Cmake 參數：http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html

---------------------------------------------------------------------------------------------

二、my.cnf 配置文件

## global_buffers 在記憶體中緩存從數據文件中檢索出來的數據塊，可以大大提高查詢和更新數據的性能

## 計算公式：Innodb_buffer_pool_size + Innodb_additional_mem_pool_size + Innodb_log_buffer_size + key_buffer_size + query_cache_size

## per_thread_buffers 線程獨享記憶體大小

## 計算公式（ read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size ）* max_connections

## 註意：global_buffers + per_thread_buffers 不能大於實際物理記憶體，否者併發量大時會造成記憶體溢出、系統死機 ！

[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

## 定義客戶端連接信息，埠號、socket 存放位置

[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
temdir = /usr/local/mysql/data

## Mysql 基本信息，埠號、socket、安裝目錄、數據存放目錄、臨時目錄

# skip-name-resolve

## 禁止 Mysql 對外部連接進行 DNS 解析，加快連接速度。開啟後所有遠程連接主機只能使用 IP 的方式

skip_external_locking

## 避免 Mysql 外部鎖定，減少出錯幾率、增強穩定性

local-infile = 0

## 禁止 SQL 讀取本地文件

character-set-server = utf8

## 預設字元集 utf8

default-storage-engine = innodb

## 預設存儲引擎

# general_log = on

## 開啟查詢日誌，一般選擇不開啟，因為查詢日誌記錄很詳細，會增大磁碟 IO 開銷，影響性能

# general_log_file = /usr/local/mysql/data/mysql.log

## 查詢日誌存放位置及文件名

log-error = /usr/local/mysql/data/error.log

## 錯誤日誌位置跟文件名

# slow_query_log = on

## 開啟慢查詢日誌，開啟後將會記錄執行時間超過 long_query_time 參數值的 SQL 語句（ 一般臨時開啟即可 ）

# long_query_time = 2

## 定義執行時間超過多少秒為慢查詢，預設 10s

# slow_query_log_file = /usr/local/mysql/data/slow.log

## 定義慢查詢日誌存放位置

# server-id = 1

## Mysql Server 唯一標識，用來做主同同步（ 主從時開啟 ）

log-bin = mysql-bin

## 開啟 binlog ( 二進位 ) 日誌，主要用來做增量備份跟主從同步

binlog_format = mixed

## Mysql binlog 的日誌格式，Statement、ROW 跟 Mixed（ 混合模式 ）

binlog_cache_size = 2M

## 二進位日誌緩衝大小，此參數是為每 Session 單獨分配的，當一個線程開始一個事務時，Mysql 就會為此 Session 分配一個 binlog cache，當這個事務提交時，binlog cache 中的數據被寫入 binlog 文件
## 通過 show status like 'binlog_cache%'; 來查看使用 binlog cache 的次數及使用磁碟的次數

sync_binlog = 0

## 這個參數對 Mysql 系統來說很重要，不僅影響到 binlog 對 Mysql 所帶來的性能損耗，還影響到 Mysql 中數據的完整性。
## 值為 0 時代表事務提交後，Mysql 不做 fsync 之類的磁碟同步指令刷新 binlog_cache 中的信息到磁碟，而讓 Filesystem 自行決定什麼時候同步，或者 cache 滿了之後才同步磁碟。
## 值為 n 時代表進行 n 次事務提交後，Mysql 將進行一次 fsync 之類的磁碟同步指令來將 binlog_cache 中的數據強制寫入磁碟。
## 系統預設將此參數設置為 0 ，即不做任何強制性的磁碟刷新指令，性能最好，但是風險也最大。當系統崩潰時 binlog_cache 中的所有 binlog 信息都會丟失。
## 而設置為 1 時，是最安全但是性能損耗最大。當系統崩潰時，最多丟失 binlog_cache 中未完成的一個事務，對實際數據沒有實質性的影響。

expire_logs_days = 30

## 保留 30 天的 binlog 日誌，系統重啟、執行 flush logs 或 binlog 日誌文件大小達到上限時刪除 binlog 日誌

back_log = 500

## Mysql 連接請求隊列存放數，當某一時刻客戶端連接請求過多，造成 Mysql Server 無法為其建立連接時存放的數量，最大 65535（ 增大時需同時調整 OS 級別的網路監聽隊列限制 ）

max_connections = 1000

## Mysql 最大連接數，直接影響 Mysql 應用的併發處理能力（ 500~1000 是個比較合適的值，註意每建立的連接都會占用一定的記憶體空間，直到連接被關閉才釋放記憶體 ）

wait_timeout = 100
interactive_timeout = 100

## 伺服器關閉非交換連接之前等待活動的秒數，預設 28800 秒（ 註意：在 my.cnf 中修改這個參數需要配合 interactive_timeout (超時間隔)參數一起修改，否則不生效）
##（ Mysql 處理完一條連接後所等待釋放的時間，如果併發很高，預設設置會導致最大連接被占滿，出現 "too many connections" 錯誤 ）
##（ 如果這個值很低，比如 5 ，可能會導致出現 "ERROR 2006 (HY000) MySQL server has gone away" 的錯誤，出現這個錯誤還有可能是 max_allowed_packet 設置過小 ）
## 真實案例

max_connect_errors = 1000

## 最大連接失敗次數，跟性能沒有太大關係，主要跟安全方面有關（ 達到此上限後會無條件阻止其連接資料庫 ，預設 100 ）

connect_timeout = 20

## 連接超時時間為 20 秒

max_allowed_packet = 16M

## 網路傳輸中一次消息量的最大值，預設 4M ，必須設為 1024 的整倍數

table_open_cache = 2000

## 打開文件描述符的緩存個數，防止系統頻繁打開、關閉描述符而浪費資源（ 對性能有影響，預設 2000 ）

read_buffer_size = 512K

## Mysql 讀入緩衝區大小，對錶進行順序掃描的請求將分配一個讀入緩衝區，Mysql 會為其分配一段記憶體緩衝區（ 預設 128K ，此參數為每線程分配 ）

read_rnd_buffer_size = 512K

## Mysql 隨機 Query 緩衝區大小，當按任意順序讀取行時，將分配一個隨機讀取緩衝區。如進行排序查詢時，Mysql 會首先掃描該緩衝，避免磁碟搜索，提高查詢速度（ 預設 256K ，該緩衝也是為每線程分配 ）

sort_buffer_size = 512K

## 系統中對數據進行排序時使用的 buffer ，如果系統中排序比較大，且記憶體充足、併發不大時，可以適當增大此值（ 預設 256K ，此參數為每線程分配獨立的 buffer ）

join_buffer_size = 512K

## join 為 ALL、index、rang 或 index_merge 時使用的 buffer（ 預設 256K ，每 Thread 都會建立自己獨立的 buffer ）

thread_stack = 256K

## 參數表示每線程的堆棧大小

thread_cache_size = 64

## Thread Cache 池中存放的連接線程數（ 此池中的線程不是啟動服務時就創建的，而是隨著連接線程的創建和使用，逐漸將用完的線程存入其中，達到此值後將不再緩存連接線程 ）
## 緩存命中率計算公式：Thread_Cache_Hit = ( Connections - Thread_created ) / Connections * 100%
## 系統運行一段時間後，Thread Cache 命中率應該保持在 90% 以上

explicit_defaults_for_timestamp = 1

## 如果此參數不開啟，error_log 中會有警告信息

query_cache_type = 1

## 是否啟用 query_cache ，0 為不使用（ 若要關閉 query_cache 時，需同時將 query_cache_size 、query_cache_limit 設為 0 ）

query_cache_size = 32M

## 查詢緩衝大小，當重覆查詢時會直接從該緩衝中獲取，但是當所查詢表有改變時，緩衝的查詢將失效（ 頻繁寫入、更新、高併發的環境下建議關閉此緩衝 ）

query_cache_limit = 1M

## 單個查詢所能夠使用的緩衝區大小

ft_min_word_len = 1

## 使用全文索引最小長度

transaction_isolation = REPEATABLE-READ

## 事務隔離級別，為了有效保證併發讀取數據的正確性（ 預設 Repeatables Read 即：可重覆讀 ）
## Innodb 有四種隔離級別：Read Uncommitted（ 未提交讀 ）、Read Committed（ 已提交讀 ）、Repeatable Read（ 可重覆讀 ）、Serializable（ 可序列化 ）

tmp_table_size = 32M

## 臨時表大小

key_buffer_size = 32M

## 用來緩存 MyISAM 存儲引擎的索引（ 預設 8M ，如果使用 Innodb 存儲引擎，此值設為 64M 或更小 ）
## 計算公式：key_reads / key_read_requests * 100% 的值小於 0.1%

## Innodb 存儲引擎相關參數

innodb_file_per_table = 0

## 關閉獨享表空間，使用共用表空間

innodb_buffer_pool_size = 256M

## Innodb 存儲引擎核心參數，用於緩存 Innodb 表的索引、數據（ 預設 128M ，單獨使用 Innodb 存儲引擎且單一 Mysql 服務時建議設為物理記憶體的 70% - 80 % ）
## 可以通過 show status like 'innodb_buffer_pool_%'; 來獲取 innodb buffer pool 的實時狀態信息
## Innodb_buffer_pool_pages_total 總共的 pages（ Innodb 存儲引擎中所有數據存放最小物理單位 page ，每個 page 預設為 16KB ）
## Innodb_buffer_pool_pages_free  空閑的 pages
## Innodb_buffer_pool_pages_data  有數據的 pages
## Innodb_buffer_pool_read_requests  總共的 read 請求次數
## Innodb_buffer_pool_reads  讀取物理磁碟讀取數據的次數，即：在 buffer pool 中沒有找到
## Innodb_buffer_pool_wait_free  因 buffer 空間不足而產生的 wait_free
## Innodb_buffer_pool_read_ahead_rnd  記錄進行隨機讀的時候產生的預讀次數
## Innodb_buffer_pool_read_ahead_seq  記錄連續讀的時候產生的預讀次數
## Innodb_buffer_pool_size  使用率 = innodb_buffer_pool_pages_data / innodb_buffer_pool_pages_total * 100%
## Innodb_buffer_pool_read  命中率 = （ innodb_buffer_pool_read_requests - innodb_buffer_pool_reads ）/ innodb_buffer_pool_read_requests * 100%

innodb_use_sys_malloc = 1

## 使用系統自帶的記憶體分配器，替代 innodb_additional_mem_pool_size 參數

innodb_data_file_path = ibdata1:512M:autoextend

## 指定一個大小為 512M 的、可擴展的 ibdata1 數據文件

innodb_read_io_threads = 4
innodb_write_io_threads = 4

## Innodb 使用後臺線程處理數據頁上的 IO 請求，根據 CPU 核數修改，預設 4

innodb_thread_concurrency = 0

## Innodb 線程併發數，0 為不限制，預設 0

innodb_flush_log_at_trx_commit = 2

## Innodb 事務日誌刷新方式，0 為每隔一秒 log thread 會將 log buffer 中的數據寫入到文件，並通知文件系統進行文件同步 flush 操作，極端情況下會丟失一秒的數據
## 1 為每次事務結束都會觸發 log thread 將 log buffer 中的數據寫入文件並通知文件系統同步文件，數據最安全、不會丟失任何已經提交的數據
## 2 為每次事務結束後 log thread 會將數據寫入事務日誌，但只是調用了文件系統的文件寫入操作，並沒有同步到物理磁碟，因為文件系統都是有緩存機制的，各文件系統的緩存刷新機制不同
## 當設為 1 時是最為安全的，但性能也是最差的。0 為每秒同步一次，性能相對高一些。設為 2 性能是最好的，但故障後丟失數據也最多（ OS 跟主機硬體、供電足夠安全可以選擇，或對數據少量丟失可以接受 ）。

innodb_log_buffer_size = 8M

## 事務日誌所使用的緩衝區。Innodb 在寫事務日誌時，為了提高寫 Log 的 IO 性能，先將信息寫入 Innodb Log Buffer 中，當滿足 Innodb_flush_log_trx_commit 參數或日誌緩衝區寫滿時，再將日誌同步到磁碟中。
## 預設 8M ，一般設為 16~64M 即可，可以通過 show status like 'innodb_log%'; 查看狀態

innodb_buffer_pool_dump_at_shutdown = 1

## 關閉資料庫時把熱數據 dump 到本地磁碟。

innodb_buffer_pool_dump_now = 1

## 採用手工方式把熱數據 dump 到本地磁碟。

innodb_buffer_pool_load_at_startup = 1

## 啟動時把熱數據載入到記憶體。

innodb_buffer_pool_load_now = 1

## 採用手工方式把熱數據載入到記憶體。

## 以上四條參數會快速預熱 Buffer_pool 緩衝池，當機器正常重啟後，熱數據還保留在記憶體中，避免瞬間連接數爆滿導致機器死機。

[mysqldump]
quick
max_allowed_packet = 4M

## 使用 mysqldump 工具備份資料庫時，當某張表過大時備份會報錯，需要增大該值（ 增大到大於表大小的值 ）

 


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

-Advertisement-
Play Games
更多相關文章
  • MyEclipse+Mysql （一）
    MyEclipse連接Mysql資料庫 準備工作：MyEclipse使用的是2013版，mysql Ver 14.14 Distrib 5.6.28 1.jar包的下載（jdbc驅動） 我下載的是：mysql-connector-java-5.1.7-bin.jar 2.打開MyEclipse >選 ...
  • 分區表的查詢
    前一篇文章提到了在2014下分區表引起的奇葩問題，最近也在苦苦研究問題的原因。這篇文章主要講述在分區表下，查詢是怎麼樣的一個情況。由於2014的新功能“參數估計”下是如何運轉的目前無從得知，所以只能分享下2008下的原理了... 在 SQL Server 2008 中，已分區表的內部表示形式已發生變 ...
  • 資料庫知識點③
    1.DeadLocks 死鎖 Cycle of transactions waiting for locks to be released by each other. 2.Handle: (1) DeadLocks prevention Based on timestamps; Wait-Die ...
  • Mysql的簡單使用（三）
    接上文Mysql的簡單使用（二） mysql中結構相同的兩個表進行合併：（註意需要兩個表的結構是一樣的） 有如下結構的兩個表father和person。 合併的步驟為： 1.把person表和father表兩個表進行聯合輸出到臨時表tmp中。 命令為：>create temporary table ...
  • Logstash mutate 插件【翻譯+實踐】
    地址：https://www.elastic.co/guide/en/logstash/2.2/plugins-filters-mutate.html 本文內容 語法 mutate 插件可以在欄位上執行變換，包括重命名、刪除、替換和修改。這個插件相當常用。 比如： 你已經根據 Grok 表達式將 T ...
  • 筆記整理之 Bulk Insert
    之前2篇日誌整理了BCP大致的用法，這次整理一下它的兄弟 Bulk Insert 的寫法以及和bcp那邊的結合的用法。 首先，Bulk Insert 語句要在連接了Sql Server 伺服器之後才執行的，和bcp 不一樣，bcp 是以命令來執行，而Bulk Insert 相當於一個執行一個操作語句 ...
  • 安裝Hadoop2.7和hive2.0以及redis
    安裝過程很簡單，主要記錄期間碰到的問題： 安裝過程： 下載安裝包： hadoop:http://mirrors.hust.edu.cn/apache/hadoop/common/hadoop-2.7.2/hadoop-2.7.2.tar.gz hive:http://mirror.bit.edu.c ...
  • Mysql的簡單使用（二）
    接上文Mysql的簡單使用（一） 欄位參數以“（欄位名1 數據類型1，欄位名2 數據類型2，......）”的形式構建。 關於mysql常用的數據類型，一下是比較常用的幾種，想查閱比較詳細的資料可以自尋去網上搜尋。 —————————————————————————————————————————— ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類