最近一直在進行mysql的學習，主要是學習mysql的一些配置參數，了解參數的意義，考慮其對mysql性能的影響，向同僚要了一份正式伺服器上的mysql配置，目前的配置能夠滿足正式生産環境中的性能要求，在學習mysql的過程中，對該配置中的一些參數進行了解和學習，這裡總結和整理一下，友善以後回顧。
其中涉及的配置參數主要有以下幾個，接下來會依次進行學習和了解：
skip-external-locking
在某些系統中，lockd鎖管理器可能不能正常工作，可以使用skip-external-locking告訴mysqld不使用外部鎖定。（這意味着你不能在同一個資料目錄運作兩個mysqld伺服器，而且如果你使用相同記憶體配置，也需要特别注意）
在Mysql4.0版本之後，外部鎖定預設是禁用的，而啟用外部鎖定（系統鎖定），如果系統上的lockd不能完全工作（比如Linux系統），很容易造成mysqld死鎖。
明确的禁用外部鎖定，可以通過配置skip-external-locking。 外部鎖定隻影響存儲引擎為MyISAM表的通路。
key_buffer_size = 16M
這個參數的含義是：MyISAM表用于索引塊的緩沖區大小，可以被所有線程所共享，預設值是8388608（即8M），它是對MyISAM表性能影響最大的一個參數，如果資料庫存儲引擎為InnoDB類型，這個參數是無效的。可以通過增大這個值，以便索引更好的處理所有讀和多寫操作。
這裡有兩篇相關的文章值得參閱： mysql優化：Key_buffer_size MySQL的key_buffer_size參數
max_allowed_packet = 1M
表示一個資料包的最大大小，或任何生成的/中間字元串，或任何mysql_stmt_send_long_data() API 函數傳遞的參數。資料包消息的緩沖區被初始化為net_buffer_length的位元組（net_buffer_length預設值為16384：16KB，最大可以設定為1048576：1M），但是在需要的時候可以增長到max_allowed_packet位元組，這個參數的預設值是4M，在接收一些大的資料包時可能會出現錯誤，最大可以設定為1GB。當你通過修改這個變量的值更改消息緩沖區的大小時，如果用戶端程式允許的話，建議在用戶端也修改緩沖區的大小。在用戶端庫，預設的max_allowed_packet是1GB，但是個人的用戶端可能會重寫這個值，例如，mysql和mysqldump分别是16 MB和24 MB。可以通過在指令行設定或在配置檔案中修改max_allowed_packet參數來更改用戶端的值，需要注意的是：在session會話級别中，這個變量是隻讀的。
table_open_cache = 64
表示所有線程打開表的數量，增加該值會增加mysqld要求的檔案描述符的數量？你可以通過檢查Opened_tables狀态變量來确定是否需要增加表緩存數量（Opened_tables表示已打開的表的數量）。如果這個值很大，你又不經常使用FLUSH TABLES（這個指令會強制關閉并重新打開所有表），可以增加table_open_cache的值。
table_open_cache和max_connections系統變量影響檔案伺服器保持打開的最大數量。如果你增加了其中一個或兩個值，可能會超過作業系統上每個程序打開的檔案描述符的數量限制。許多的作業系統允許你增加這個限制，但是也需要确定作業系統是否有可能增加打開檔案的限制，以及如何去做。
table_open_cache的值與max_connections的值存在關系。例如，對于200個并發運作的連接配接，指定table_open_cache的值 至少為200 * N，其中 N是 任何參與 執行 查詢的連接配接中關聯 表的最大 數量。你還必須為臨時表和檔案保留一些額外的檔案描述符。
請確定你的作業系統能夠處理由table_open_cache設定的隐含打開的檔案描述符的數量。如果table_open_cache值設定的太高，MYSQL可能使用完檔案描述符而拒絕連接配接，不能執行查詢，并會變的不可靠。還必須需要考慮的是，MyISAM存儲引擎中，每個唯一打開的表都需要兩個檔案描述符。你可以通過在mysqld的啟動選項中配置open-files-limit參數，來增加MySQL可用的檔案描述符的數量。
sort_buffer_size = 512K
表示為每一個需要排序的會話配置設定一個指定的緩存區大小。sort_buffer_size的值不特定于任何存儲引擎，它适用于對優化的一般方式。通過SHOW GLOBAL STATUS指令，如果你發現每秒鐘有許多的Sort_merge_passes輸出（Sort_merge_passes表示不得不做合并排序算法的數量），我們就需要考慮增加sort_buffer_size的值，以加快order by 或 group by 等操作的查詢性能（order by和group by的查詢效率很難通過優化查詢和改善索引提供效率）。
優化器會嘗試找出有多少空間是必要的，但可以配置設定更多，直到達到極限。值得注意的是：如果全局的設定，大于系統所需要的值，将減緩大部分涉及排序的查詢效率。最好在session會話級别增加它的值，而且值針對那些需要增加sort_buffer_size的session會話。在Linux系統中，有256KB和2MB的門檻值，其中較大的值可能顯著減慢記憶體配置設定，是以你應該考慮那些較小的值。
net_buffer_length = 8K
每個用戶端線程都與一個connection連接配接緩沖區和結果緩沖區相關聯，預設值是16K。兩者最初的大小都是net_buffer_length，但是根據需要會動态的擴大到max_allowed_packet設定的大小，結果緩沖區在每一個sql語句執行後都會縮小到設定的net_buffer_length。
這個變量通常不應該改變，但是如果你的系統記憶體很小，你可以将其設定為用戶端語句的期望長度。如果語句的長度超過這個值，connection連接配接緩沖區會自動的擴大。net_buffer_length參數的最大值可以設定到1M。需要注意的是：在session會話級别中，這個變量是隻讀的。
read_buffer_size = 256K
進行順序掃描的MyISAM表的 每個線程，都為它掃描的每個表配置設定一個指定大小的緩沖區。如果需要做很多的順序掃描，你可能會增大這個值，預設值為131072（128K）。這個變量的值應該是4KB的倍數。如果它被設定為不是4KB的倍數，它的值将被舍入為4KB的最近倍數。
該參數在以下情況時适用于所有的搜尋引擎：
緩存索引在一個臨時檔案（而不是臨時表），使用ORDER BY進行行排序時。
進行分區批量插入操作時。
對于嵌套查詢緩存結果。
如果使用 另外一個存儲引擎，需要為MEMORY表确定記憶體塊大小。 read_buffer_size 最大允許 設定為 2GB 。
read_rnd_buffer_size = 512K
此變量用于多範圍讀取優化，包括MyISAM表以及任何存儲引擎。當從一個有排序操作的MyISAM表的一個關鍵分揀操作中讀取行，該行通過該緩沖區讀取，以避免磁盤尋道。設定這個值為較大的值可以顯著的提高ORDER BY操作的性能， 然而，這是配置設定給每一個用戶端的緩沖區，是以不應該在全局級别将其設定為一個較大的值。相反，隻有在需要進行大量查詢操作的用戶端才建議在session會話級别增大這個變量值。read_rnd_buffer_size最大允許設定為2GB。
myisam_sort_buffer_size = 8M
表示在REPAIR TABLE上進行MyISAM索引排序時，或通過CREATE INDEX、ALTER TABLE建立索引時，配置設定的緩沖區大小。相應的，對于InnoDB引擎，有InnoDB_sort_buffer_size的設定。
query_cache_size= 8M
表示配置設定給高速緩存查詢結果的記憶體量。預設情況下，查詢緩存是禁用的。這是通過使用預設的query_cache_size為 1M，query_cache_type為0（為0表示不啟用查詢緩存）使用的，這樣做會顯著降低開銷，因為如果你設定了query_cache_size為0，你也需要在啟動時設定query_cache_type為0。
允許設定的值為1024的倍數，其他設定的值會四舍五入到最近的那個值。 需要 注意的是，即使query_cache_type設定為0，query_cache_size位元組的記憶體也會被預設的配置設定。
查詢緩存需要一個最小大小約40 kb的配置設定結構（具體的值取決于系統結構）。如果設定的query_cache_size太小，還可能會産生一些問題。
query_cache_type的值有0、1、2三種，0表示不進行任何查詢緩存；1表示 緩存所有可緩存的查詢結果除了那些以SELECT SQL_NO_CACHE開頭的查詢；2表示隻緩存以SELECT SQL_CACHE開頭的查詢結果。mysql官方doc建議設定為2。
thread_cache_size = 20
表示伺服器将會緩存重用的線程數量，當一個用戶端斷開連接配接，如果緩存中線程的數量小于設定的thread_cache_size，那麼這個用戶端的線程會變放入到緩存中。請求的線程如果可能的話，會從高速緩存中去的線程，當緩存為空時，才會建立新的線程。如果系統中存在許多新的連接配接的話，增加這個變量值可以提高性能。通常情況下，如果你的代碼中很好的做了線程實作，這種性能改進并不顯著。然而，如果你的伺服器每秒有數百個連接配接，你通常應該設定thread_cache_size足夠高，大多數新連接配接都會使用緩存的線程。通過比較這個變量與Connections（表示嘗試連接配接到Mysql伺服器的數量（無論是否連接配接成功））和Threads_created（表示處理connection連接配接所建立的線程的數量）狀态變量之間的差別，你可以看到線程緩存的高效。
這個變量的預設值是根據以下公式計算的，封頂為100：8 + (max_connections / 100)，但是在嵌入式伺服器（libmysqld）這個變量是沒有效果的，在MySQL 5.7.2版本之後，這個參數也不再可見。
log-bin=mysql-bin
表示啟用二進制日志記錄，伺服器記錄了所有改變資料語句的二進制日志，用于備份和複制。
binlog_format=mixed 之前 MySQL中事務隔離級别與binlog_format的一點了解中學習，這裡不再贅述。
innodb_flush_log_at_trx_commit = 2
這個變量的官方定義是：Controls the balance between strict ACID compliance for commit operations，and higher performance that is possible when commit-related I/O operations are rearranged and done in batches。我自己的了解是用于控制兩種關系之間的平衡，這兩種關系：送出操作嚴格的ACID特性，送出相關的IO操作被分批的重新排列和完成時可能帶來的高性能。你可以通過修改這個變量的預設值達到更好的性能，但是你可能會在意外崩潰時丢失一秒的事務。
預設值為 1 完全符合資料庫ACID特性，這個值表示，在每次事務送出的時候，InnoDB日志緩沖區的内容都會被寫入到日志檔案，并且日志檔案會被重新整理到磁盤。
如果變量值為0，則表示InnoDB日志緩沖區的内容大約每秒被寫入日志檔案一次，并且日志檔案會被重新整理到磁盤。那些日志緩沖區中沒有寫入的内容會在事務送出的時候被寫入日志檔案。由于程序排程的問題，每秒的重新整理并不能100%保證每一秒都發生。由于重新整理磁盤的操作隻有大約每秒才發生一次，是以在任何mysqld程序崩潰的時候，你都會喪失一秒的事務。
如果變量值為2，則表示InnoDB日志緩沖區的内容會在事務送出的時候寫入到日志檔案，并且日志檔案會大約每秒重新整理一次磁盤。同樣的，由于程序排程的問題，每秒的重新整理并不能100%保證每一秒都發生。由于重新整理磁盤的操作隻有大約每秒才發生一次，是以在作業系統崩潰或突然斷電的時候，你都會喪失一秒的事務資料。
在MySQL 5.6.6版本中，InnoDB日志重新整理頻率由變量innodb_flush_log_at_timeout,控制，它允許你将日志重新整理頻率設定為N秒（預設值是1，可以設定1到2700之間的整數值），但是任何mysqld程序的崩潰都會清除高達N秒的事務資料。
DDL變化和其他内部InnoDB的活動，則是獨立的innodb_flush_log_at_trx_commit設定進行InnoDB日志重新整理。
InnoDB的崩潰恢複機制是不管變量innodb_flush_log_at_trx_commit的設定的，事務要麼全部應用，要麼全部删除。
根據資料庫應用設定的持久性和一緻性，建議參考如下方式進行InnoDB事務設定：
如果啟用了二進制日志，設定sync_binlog=1。
總是設定innodb_flush_log_at_trx_commit=1。
這麼建議的原因是：許多作業系統和一些磁盤硬體愚弄重新整理到磁盤的操作，他們可能會告訴mysqld：重新整理操作已經發生，但是事實上并沒有發生。然後事務的持久性即使設定為1，也不能得到保證。在最糟糕的情況突然斷電甚至會造成InnoDB資料的損壞。在SCSI磁盤控制器或本身加速檔案重新整理的磁盤上使用電池支援的磁盤緩存，會使操作更安全。你也可以嘗試使用Unix指令hdparm禁用磁盤寫入緩存的硬體高速緩存，或使用特定的硬體供應商提供的其他一些指令。
sync_binlog
如果這個變量的值大于0，MySQL伺服器會在sync_binlog送出組被寫入到二進制日志之後，使用fdatasync()指令同步二進制日志到磁盤。預設的sync_binlog變量值為0，表示不同步到磁盤。mysql伺服器依賴于作業系統不時的重新整理二進制檔案的内容，用于任何其他檔案。值為1是最安全的選擇，因為在崩潰的情況下你最多從二進制日志丢失一個送出組。然而，它也是最慢的選擇（除非你你的磁盤具有電池備份緩存，這會使得同步非常快）。
innodb_lock_wait_timeout = 20
表示InnoDB事務從等待擷取行鎖到放棄的時間長度，預設的值為50秒。一個事務試圖擷取被另一個InnoDB事務鎖定的行所等待的最大時間，逾時時會發出以下錯誤資訊：
ERROR 1205 (HY000)： Lock wait timeout exceeded； try restarting transaction（鎖等待逾時，試圖重新開機事務）
當鎖等待逾時後，目前的語句會復原（并不是整個事務復原）。如果需要整個事務都復原，需要在伺服器啟動時通過innodb_rollback_on_timeout參數設定。
在高度互動的應用程式或OTLP系統中，為了更好的使用者回報或将更新放入一個隊列等待後續處理，你可能會減小該變量值。對于長期運作的後端操作，比如在一個資料倉庫中存在大批量的插入或更新操作等待完成時，你可能會增加該值。
innodb_lock_wait_timeout僅适用于InnoDB的行級鎖。一個MySQL表鎖不會發生在InnoDB，這個參數并不适用于等待表鎖。
鎖等待逾時值不适用于死鎖，因為在事務死鎖時，InnoDB會立即檢測到它們并事務復原。
innodb_lock_wait_timeout可以在運作時，通過SET GLOBAL或SET SESSION聲明進行設定。修改全局的設定需要SUPER權限，并會影響接下來所有連接配接用戶端的操作。任何用戶端都可以在SESSION會話級别設定innodb_lock_wait_timeout，它隻會影響到該用戶端。
至此，線上mysql伺服器上的配置檔案參數已經全部做了整理，對于這些參數也有了一定的認識和了解，接下來，對于經常使用到的connections變量做一下整理，為接下來與同僚讨論mysql的優化及設定做些準備，主要是以下幾個connections變量：
max_connections
系統變量，它表示最大允許的并發用戶端連接配接數，會影響在伺服器上運作的線程數量，預設值是151，增加該值會增加mysqld請求的檔案描述符的數量。如果所請求的描述符的數量不可用，伺服器會減少max_connections的值。連接配接拒絕是因為，max_connections的最大值，達到了Connection_errors_max_connections狀态變量的增量。
thread_cache_size 變量的預設值就與max_connections有關。
max_user_connections
表示允許任何給定的MySQL使用者帳戶同時連接配接的最大數目。預設值為0表示不限制。此變量可以在伺服器啟動時或運作時設定一個全局值。它也有一個隻讀會話值，表示與目前會話相關聯的帳戶的有效同時連接配接的限制值。會話級别的max_user_connections初始化如下：
如果使用者帳戶具有非零的MAX_USER_CONNECTIONS資源限制（帳戶的資源限制通過GRANT語句指定），會話級别的MAX_USER_CONNECTIONS值就設為該限制。
否則的話，會話級别的MAX_USER_CONNECTIONS的值會被設定為全局值。
Connection_errors_max_connections
表示當伺服器中連接配接數達到max_connections的限制後，連接配接數被拒絕的數量。
Connections
表示嘗試連接配接到mysql伺服器的數量，無論成功或失敗。
Max_used_connections
從伺服器啟動開始，已同時被使用的最大連接配接數。
skip-external-locking作用
在Mysql Linux 的發行版中，預設存在一行skip-external-locking，它表示跳過外部鎖定，與之相對，External-locking變量表示啟用外 部鎖定，用于多線程條件下對MyISAM資料表進行鎖定，預設情況下mysql是禁用外部鎖定的，在現實生産中，我們的業務環境是單伺服器環境，不需要外 部鎖定，是以将其禁用。
key_buffer_size 在InnoDB引擎時無效
在 現實的生産環境中，我們曾對業務庫進行過更新，雖然我們的業務庫使用的是InnoDB引擎，但是其中仍然存在幾張遺留的使用MyISAM存儲引擎的表，設 置這個參數也是為了提供對于這幾張表的通路性能，用于這幾張表的索引更好的處理讀和多寫操作。另外，在之前windows上安裝mysql 5.5時，會預設産生幾個不同生産環境的my.ini檔案，這個參數的配置也參閱了其中的一些配置。
table_open_cache=64 設定是否過小
這個參數的設定最好根據現實生産環境進行設定，在mysql指令行通過show global status like 'open%_tables%'，可以查到兩個重要的參數，如下：
+---------------+-------+
| Variable_name | Value |
| Open_tables | 81 |
| Opened_tables | 88 |
對與大多數的伺服器設定，建議參考一下公式：Open_tables / Opened_tables >= 0.85；Open_tables / table_open_cache <= 0.95
但是并不是設定table_open_cache越大越好，因為table_cache加大後，使得mysql對 SQL響應的速度更快了，不可避免的會産生更多的死鎖（dead lock），這樣反而使得資料庫整個一套操作慢了下來，嚴重影響性能。是以平時維護中還是要根據庫的實際情況去作出判斷，找到最适合你維護的庫的 table_open_cache值。
詳請參閱：參數table_open_cache。
myisam_sort_buffer_size參數隻對MyISAM引擎有效
在現實的生産環境中，雖然我們的業務庫使用的是InnoDB引擎，但有多張業務表使用的是MyIsam引擎，需要這個參數，用于進行表恢複時使用的緩沖區的大小，也是參考了mysql 5.5的配置。
net_buffer_length預設為16K,設定為8K？
這個參數根據用戶端connection 語句的長度有關，現實的業務并不繁雜，語句也比較簡單，參考mysql 5.5的配置，設定net_buffer_length=8k，不過經過與同僚讨論，決定使用預設值16K。
thread_cache_size設定為20的依據
這個參數的設定與max_connections有關，max_connections表示最大允許的并發用戶端連接配接數，會影響在伺服器上運作的線程數量，預設值是151，thread_cache_size 的設定，官方doc建議公式：8+（max_connections / 100）,這與現實的業務也存在關系，當伺服器并發很大時，需要修改max_connections的值以滿足業務需要，在我們的現實業務 中，thread_cache_size設定為20較為合适。
innodb_flush_log_at_trx_commit 官方doc建設不要修改預設值
雖然mysql官方doc建議将innodb_flush_log_at_trx_commit設定為1，但在現實的業務中，客戶對于業務性能的速度很高，預設為1表示，在每次事務送出的時候，InnoDB日志緩沖區的内容都會被寫入到日志檔案，并且日志檔案會被重新整理到磁盤。設定為2減少了重新整理磁盤的操作，雖然在突然斷點或系統崩潰時可能丢失事務資料，但是在業務允許範圍内，相反修改此參數對于業務速度有很大的提升。
read_rnd_buffer_size增加order by查詢效率
在What exactly is read_rnd_buffer_size中有了一點了解，其中提到了read_buffer_size，在三個方法優化MySQL資料庫查詢中大概的了解了這個參數的作用，當一個查詢不斷地掃描某一個表，MySQL會為它配置設定一段記憶體緩沖區。read_buffer_size變 量控制這一緩沖區的大小。如果你認為連續掃描進行得太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能。不過貌似這兩個參數都是值針對于 MyIsam表的，在mysql安裝目錄my.ini中看到這樣一句注釋：Size of the buffer used for doing full table scans of MyISAM tables。對于這個參數的配置還需要再讨論。
本文出自http://blog.csdn.net/jeffreylau7/article/details/53317113