MySQL 優化大全：設定 my.cnf 優化指南

Linux 下的 my.cnf 文件位置：Redhat是在 /etc/my.cnf； Debian是在 etc/mysql/my.cnf；Windows是在 my.ini 文件中

Memory 在 1GB 以上：
[client]
port = 3306
socket = /var/tmp/mysql.sock

[mysqld]
port = 3306
socket = /var/tmp/mysql.sock
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = join_buffer_size=4M
set-variable = thread_cache=8
#thread_concurrency = 主機cpu數量*2
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=64M
set-variable = max_connections=750
log-bin
server-id = 1

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout
----------------------------------------------------------------
注意事項
key_buffer_size + (record_buffer + sort_buffer)*max_connections <= 2G
如果不熟悉 my.cnf 內的設定也沒關係，可以直接使用 MySQL 的範例檔，在 /usr/share/mysql/ 下會有 my-huge.cnf, my-large.cnf, my-medium.cnf 及 my-small.cnf 幾個檔案，根據伺服器的硬件而選擇適合的檔案:

    * my-huge.cnf: 適合 1GB - 2GB RAM的主機使用。
    * my-large.cnf: 適合 512MB RAM的主機使用。
    * my-medium.cnf: 只有 32MB - 64MB RAM 的主機使用，或者有 128MB RAM 但需要運行其他伺服器，例如 web server。
    * my-small.cnf: 記憶體少於 64MB 時適用這個，MySQL 會佔用較少資源。

我選用了 my-large.cnf，只要執行以下指令便完成了，但如果你原先已經有 my.cnf，請先備份起來及自行調整:
shell> cp /usr/share/mysql/my-large.cnf /etc/my.cnf
shell> /etc/init.d/mysqld restart

以上的第二句是重新啟動 MySQL，請根據你的系統設定修改。
開啟 MySQL query cache
如果 MySQL server 是非常繁忙，可以開啟 query cache 以加速回應時間，開啟方法可以在 my.cnf 裡面加入以下項目:
(Redhat 下面是 /etc/my.cnf；Debian 是在 etc/mysql/my.cnf)
query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576

以上語法的設定裡面，query_cache_size 是分配 256MB 記憶體空間給 query cache；query_cache_type 設定成 1，是給所有的查詢做 cache；query_cache_limit 是指定個別的查詢語句 1KB 的記憶體空間。

這些數值可根據自己的需要作出更改，設定及存檔後，重新啟動 MySQL 即可。
--------------------------------------------------------------
my.ini與mysql優化指南(mysql4.0 myslq5.0 or 5.x)mysql優化了：
其實這個很複雜，對於不同的網站，其在線，訪問量，帖子數量，網絡情況，以及機器配置都有關係，優化不是一次可以完成的，需要不斷的觀察和調試，以取得最佳效果，下面我引用 Asiwish和老高的兩篇優化措施，給大家看看，這些設置只要通過修改win2000的c:/winnt/my.ini 或者 win2003的 c:/windows/my.ini 來實現：
首先，我們打開這個文件，先將最底部的密碼項改成其它的，這個對數據庫本身沒影響，主要是防止一些有心人獲取這個密碼，對我們不利。然後大家看看兩個例子，其中一些設置，略懂的英文的，即明白是什麼意思，不懂得可以去查單詞，我懶得一個個對照寫說明了，一個是千人在線，1G內存的：
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-2-23 16:28:14

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=D:/mysql
#bind-address=210.5.*.*
datadir=D:/mysql/data
#language=D:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
set-variable   = max_connections=1500
skip-locking
#skip-networking
set-variable     = key_buffer=384M
set-variable     = max_allowed_packet=1M
set-variable     = table_cache=512
set-variable     = sort_buffer=2M
set-variable     = record_buffer=2M
set-variable     = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable     = thread_concurrency=8
set-variable     = myisam_sort_buffer_size=64M
#set-variable     = connect_timeout=5
#set-variable     = wait_timeout=5
server-id     = 1
[isamchk]
set-variable     = key_buffer=128M
set-variable     = sort_buffer=128M
set-variable     = read_buffer=2M
set-variable     = write_buffer=2M

[myisamchk]
set-variable     = key_buffer=128M
set-variable     = sort_buffer=128M
set-variable     = read_buffer=2M
set-variable     = write_buffer=2M
[WinMySQLadmin]
Server=D:/mysql/bin/mysqld-nt.exe
這個方案，整體夠用了，但是在pconnect和最大連接數上，需要研究max_connections沒必要那麼大，我個人認為幾百就夠，否則給服務器加大了不少負擔，經常會當機，連接超時的設置也要根據實際情況調整，大家可以自由調整，然後觀察效果如何。下面是老高兩年前的一些建議，大家參考一下：
　　7、MYSQL 的優化（/etc/my.cnf）
    1)確認在「[mysqld]」部分加入了「skip-innodb」和「skip-bdb」參數；
    2)確認在「[mysqld]」部分加入了「skip-name-resolve」和「skip-locking」參數；
    3)如果不需要的話，可以將二進制日誌（binlog）停掉，方法是將「log-bin」註釋掉；
    4)在內存允許的情況下，對一些參數進行重新配置，目標在於將大部分操作集中於內存中，儘量不進行磁盤操作，對於我的 MYSQL 服務器我是如下修改的，基於 2G 內存情況：
    [mysqld]
    set-variable   = key_buffer=512M
    set-variable   = max_allowed_packet=4M
    set-variable   = table_cache=1024
    set-variable   = thread_cache=64
    set-variable   = join_buffer_size=32M
    set-variable   = sort_buffer=32M
    set-variable   = record_buffer=32M
    set-variable   = max_connections=512
    set-variable   = wait_timeout=120
    set-variable   = interactive_timeout=120
    set-variable   = max_connect_errors=30000
    set-variable   = long_query_time=1
    set-variable   = max_heap_table_size=256M
    set-variable   = tmp_table_size=128M
    set-variable   = thread_concurrency=8
    set-variable   = myisam_sort_buffer_size=128M

    你可以根據「show status」命令返回的狀態進行微調。我主要注意以下變量的數值，越小越好，最好為零：）
    Created_tmp_disk_tables
    Created_tmp_tables
    Created_tmp_files
    Slow_queries

另外，基於 8G 內存情況：mysql wait_timeout 那個值設置大了沒用 做10左右就可了 (大C說得)wait_timeout是使用長久連線時 空閒進程的控制 只要數據庫在連接狀態 他是不進行干預的 不管是否有查詢或更新操作 把這個設置小一點 再使用pconnect就比較理想了)，timeout的時間﹐我的經驗值是5-20﹐看你的SERVER的訪問量了~~(夢飛說的)訪問量越大 這個值就應該越小 否則留出的空閒進程太多 會佔用不必要的內存
在一個15分鐘在線3000人的論壇上 設置為3比較合適 同時打開pconnect，另外，我再引用一篇文章：
數據庫連接過多的錯誤，可能的原因分析及解決辦法：
分析：
系統不能連接數據庫，關鍵要看兩個數據：
1、數據庫系統允許的最大可連接數max_connections。這個參數是可以設置的。如果不設置，默認是100。最大是16384。
2、數據庫當前的連接線程數threads_connected。這是動態變化的。
查看max_connections、max_connections的辦法見後。
如果 threads_connected == max_connections 時，數據庫系統就不能提供更多的連接數了，這時，如果程序還想新建連接線程，數據庫系統就會拒絕，如果程序沒做太多的錯誤處理，就會出現類似強壇的報錯信息。因為創建和銷毀數據庫的連接，都會消耗系統的資源。而且為了避免在同一時間同時打開過多的連接線程，現在編程一般都使用所謂數據庫連接池技術。但數據庫連接池技術，並不能避免程序錯誤導致連接資源消耗殆盡。這種情況通常發生在程序未能及時釋放數據庫連接資源或其他原因造成數據庫連接資源不能釋放，但強壇系統估計不會發生這種低級的編程錯誤。該錯誤的簡便的檢查辦法是，在刷新強壇頁面時，不斷監視threads_connected的變化。如果max_connections足夠大，而 threads_connected值不斷增加以至達到max_connections，那麼，就應該檢查程序了。當然，如果採用數據庫連接池技術，threads_connected增長到數據庫連接池的最大連接線程數時，就不再增長了。從強壇出錯的情況看，更大的可能性是數據庫系統沒能進行適當地配置。

下面提出一點建議。供參考：
讓你們的工程師把MySQL的最大允許連接數從默認的100調成32000。這就不會老出現連接過多的問題了。
查看max_connections
進入MySQL，用命令：show variables
查看數據庫最大可連接數的變量值：max_connections
查看threads_connected

進入MySQL，用命令：show status
查看當前活動的連接線程變量值：threads_connected

設置max_connections
設置辦法是在my.cnf文件中，添加下面的最後紅色的一行：
------------------------------------------------------
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
set-variable = max_connections=32000
-------------------------------------------------------------------------
修改完畢後，重啟MySQL即可。當然，為了確保設置正確，應該查看一下max_connections。
注意：
1、雖然這裡寫的32000。但實際MySQL服務器允許的最大連接數16384；
2、除max_connections外，上述其他配置應該根據你們系統自身需要進行配置，不必拘泥；
3、添加了最大允許連接數，對系統消耗增加不大。
4、如果你的mysql用的是my.ini作配置文件，設置類似，但設置的格式要稍作變通。

mysql連接過多

問：怎樣解決mysql連接過多的錯誤？

答：系統不能連接數據庫，關鍵要看兩個數據：

1、數據庫系統允許的最大可連接數max_connections。這個參數是可以設置的。如果不設置，默認是100。最大是16384。

2、數據庫當前的連接線程數threads_connected。這是動態變化的。

查看max_connections、max_connections的辦法見後。

如果 threads_connected == max_connections 時，數據庫系統就不能提供更多的連接數了，這時，如果程序還想新建連接線程，數據庫系統就會拒絕，如果程序沒做太多的錯誤處理，就會出現類似強壇的報錯信息。

因為創建和銷毀數據庫的連接，都會消耗系統的資源。而且為了避免在同一時間同時打開過多的連接線程，現在編程一般都使用所謂數據庫連接池技術。

但數據庫連接池技術，並不能避免程序錯誤導致連接資源消耗殆盡。

這種情況通常發生在程序未能及時釋放數據庫連接資源或其他原因造成數據庫連接資源不能釋放，但強壇系統估計不會發生這種低級的編程錯誤。

該錯誤的簡便的檢查辦法是，在刷新強壇頁面時，不斷監視threads_connected的變化。如果max_connections足夠大，而 threads_connected值不斷增加以至達到max_connections，那麼，就應該檢查程序了。當然，如果採用數據庫連接池技術， threads_connected增長到數據庫連接池的最大連接線程數時，就不再增長了。

從強壇出錯的情況看，更大的可能性是數據庫系統沒能進行適當地配置。下面提出一點建議。供參考

讓你們的工程師把MySQL的最大允許連接數從默認的100調成32000。這就不會老出現連接過多的問題了。

查看max_connections

進入MySQL，用命令：

show variables

查看數據庫最大可連接數的變量值：

max_connections

查看threads_connected

進入MySQL，用命令：

show status查看當前活動的連接線程變量值：

threads_connected

設置max_connections

設置辦法是在my.cnf文件中，添加下面的最後紅色的一行：

[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
set-variable = max_connections=32000

修改完畢後，重啟MySQL即可。當然，為了確保設置正確，應該查看一下max_connections。

注意：
1、雖然這裡寫的32000。但實際MySQL服務器允許的最大連接數16384；
2、除max_connections外，上述其他配置應該根據你們系統自身需要進行配置，不必拘泥；
3、添加了最大允許連接數，對系統消耗增加不大。
4、如果你的mysql用的是my.ini作配置文件，設置類似，但設置的格式要稍作變通。

1. 系統項目經驗
   1.1 項目特點：
       1.1.1 論壇活躍度高 （ 1.5 - 2 貼/秒 ）；
       1.1.2 在線瀏覽人數多 （ 1000 萬 PV/日、6 萬人在線 ）；
       1.1.3 數據庫查詢頻繁，高並發量 （ 3000+ 次/秒/台 ）；
       1.1.4 業務要求高可用性，在線生產數據庫要求 7 * 24；
   1.2 實施經驗：
       1.2.1 在著手進行實施改造之前做大量的前期分析工作，儘量確保收集到準確全面的性能問題點。
             在論壇社區應用領域中，尤其對於大型論壇而言，幾乎每個論壇都有自己獨特的問題類型；
       1.2.2 使用了大量數據庫緩存技術：文件緩存、帖子緩存、memcached 緩存；
       1.2.3 數據庫使用分佈式部署，將一些耗費資源的功能分佈到不同的數據庫服務器中，同時保證
             各個數據庫服務器中的數據高度一致；
       1.2.4 使用 MySQL 分區技術有效解決數據表體積過大帶來的性能降低；
       1.2.5 全面仔細的評估所需的硬件資源，避免造成不必要的硬件成本浪費；

            
2. 實現高可用性的技術途徑以及系統性能優化方法

   2.1 硬件資源以及操作系統的準備
       2.1.1 MySQL 是一個存儲 I/O 密集型應用；
       2.1.2 充足的內存是保證 MySQL 穩定高性能的重要條件；
       2.1.3 在 MySQL 集群體系的規劃中，儘量保證各服務器的硬件配置一致性；
       2.1.4 在 MySQL 集群體系的規劃中，儘量使用高速專用網絡作為傳輸介質，降低延時；
       2.1.5 儘量選擇 64 位 Unix/Linux 操作系統作為應用平台；

   2.2 MySQL 版本選擇
       2.2.1 在保證穩定的前提下儘量選擇高版本 MySQL；
       2.2.2 可以選擇 ICC 編譯器構建的 MySQL 版本，極端情況下可根據需要自行從源代碼編譯 MySQL；
       2.2.3 避免激進，如果 ChangeLog 中未列出關鍵性更新，則儘量選擇保持現有版本，規避可能存在的風險；

   2.3 應用程序（PHP）對於 MySQL 的優化
       2.3.1 MySQL - 數據存儲引擎，明確數據庫應用的角色，不要將其作為數據處理運算中心；
       2.3.2 根據需求靈活適當的選擇數據表類型（MyISAM、HEAP、InnoDB）；
       2.3.3 合理規劃數據庫結構，以及數據索引；
       2.3.4 儘量不要讓 MySQL 處理數據運算操作，運算操作可交由前端程序進行處理；
       2.3.5 評估每一條查詢語句的執行效能，尤其是在重負載下可能帶來的性能降低（leftjoin 拆分）；
       2.3.6 程序設計當中，注重緩存的使用，儘量將需要頻繁查詢的數據緩存在外部（文件、BDB、Memcached）；

   2.4 MySQL 自身優化
       2.4.1 MySQL 應用服務優化的依據不是人云亦云，而是對當前數據庫進行長期不斷的運行狀態分析
             得出的數據，這才是最真實，最準確，最有用的優化依據，要明白為什麼優化，優化什麼；
       2.4.2 使用 MySQL 狀態監測工具以及監控平台對 MySQL 運行狀態進行長期監測，一方面發現問題，
             另一方面能夠得到寶貴的變化趨勢分析；
       2.4.3 經常查看 MySQL 日誌，發現隱含的問題，修複數據庫、索引，優化數據表；
       2.4.4 數據庫體積日益增大的情況下，提早準備好應對方案（分表、分區、歸檔等等）；
       2.4.5 my.cnf 中各個參數的優化，熟讀手冊，明確各個參數的含義，不要人云亦云（query_cache）；
       2.4.6 MySQL 的優化是個不斷進行的過程，不存在一勞永逸的優化方案；

   2.5 MySQL 數據安全性保障
       2.5.1 數據庫/服務器的損壞是個必然的結果，只是時間問題；
       2.5.2 在數據庫建立初期，就要設計並部署好一套優良的備份方案，最大程度保障數據的安全性；
             2.5.2.1 非關鍵性應用？可以考慮用短暫的停機時間換取安全可靠的備份數據；
             2.5.2.2 關鍵性應用？可以通過增加設備的方式換取 7 * 24 的運行狀態，同時安全的備份數據；
       2.5.3 儘量對數據庫備份保留 3 天甚至更長的備份；
       2.5.4 定期驗證備份的有效性；
       2.5.5 提前制訂當災難發生時的恢復/回滾策略；