mysql 優(yōu)化調(diào)試命令
 
1、mysqld --verbose --help
這個命令生成所有mysqld選項(xiàng)和可配置變量的列表
2、通過連接它并執(zhí)行這個命令，可以看到實(shí)際上使用的變量的值：
mysql> SHOW VARIABLES;
還可以通過下面的語句看到運(yùn)行服務(wù)器的統(tǒng)計和狀態(tài)指標(biāo)：
mysql>SHOW STATUS；
使用mysqladmin還可以獲得系統(tǒng)變量和狀態(tài)信息：
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table 命令可以立即關(guān)閉所有不使用的表并將所有使用中的表標(biāo)記為已經(jīng)關(guān)閉，這樣可以有效釋放大多數(shù)使用中的內(nèi)存。FLUSH TABLE在關(guān)閉所有表之前不返回結(jié)果。
 
swap -s檢查可用交換區(qū)
 
mysql內(nèi)存計算公式
 
mysql used mem = key_buffer_size + query_cache_size + tmp_table_size
+ innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections * (
read_buffer_size + read_rnd_buffer_size
+ sort_buffer_size+ join_buffer_size
+ binlog_cache_size + thread_stack
)
 
在mysql 中輸入如下命令，可自動計算自己的當(dāng)前配置最大的內(nèi)存消耗
 
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
 
 
mysql關(guān)鍵參數(shù)設(shè)置
Mysqld 數(shù)據(jù)庫的參數(shù)設(shè)置有兩種類型，
 
一種是全局參數(shù)，影響服務(wù)器的全局操作；
另一種是會話級參數(shù)，只影響當(dāng)前的客戶端連接的相關(guān)操作。
 
服務(wù)器啟動時，所有全局參數(shù)都初始化為默認(rèn)值。可以在初始化文件或命令行中指定的選項(xiàng)來更改這些默認(rèn)值。服務(wù)器啟動后，通過連接服務(wù)器并執(zhí)行 SET GLOBAL var_name 語句可以更改動態(tài)全局參數(shù)。要想更改全局參數(shù)，必須具有 SUPER 權(quán)限。全局參數(shù)的修改只對新的連接生效，已有的客戶端連接并不會生效。
 
服務(wù)器還可以為每個客戶端連接維護(hù)會話級參數(shù)，客戶端連接時使用相應(yīng)全局參數(shù)的當(dāng)前值對客戶端會話參數(shù)進(jìn)行初始化?？蛻艨梢酝ㄟ^ SET SESSION var_name 語句來更改動態(tài)會話參數(shù)。設(shè)置會話級參數(shù)不需要特殊權(quán)限，但每個客戶端可以只更改自己的會話級參數(shù)，不能更改其它客戶的會話級參數(shù)。
不指定設(shè)置的參數(shù)類型時，默認(rèn)設(shè)置的是會話級參數(shù)。
 

(1)、max_connections：
允許的同時客戶的數(shù)量。增加該值增加 mysqld 要求的文件描述符的數(shù)量。這個數(shù)字應(yīng)該增加，否則，你將經(jīng)?？吹?too many connections 錯誤。 默認(rèn)數(shù)值是100，我把它改為1024 。

(2)、record_buffer：
每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。默認(rèn)數(shù)值是131072(128k)，我把它改為16773120 (16m)

(3)、key_buffer_size：
為了最小化磁盤的 I/O ， MyISAM 存儲引擎的表使用鍵高速緩存來緩存索引，這個鍵高速緩存的大小則通過 key-buffer-size 參數(shù)來設(shè)置。如果應(yīng)用系統(tǒng)中使用的表以 MyISAM 存儲引擎為主，則應(yīng)該適當(dāng)增加該參數(shù)的值，以便盡可能的緩存索引，提高訪問的速度。

索引塊是緩沖的并且被所有的線程共享。key_buffer_size是用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負(fù)擔(dān)得起那樣多。如果你使它太大，系統(tǒng)將開始換頁并且真的變慢了。默認(rèn)數(shù)值是8388600(8m)，我的mysql主機(jī)有2gb內(nèi)存，所以我把它改為 402649088(400mb)。
默認(rèn)情況下，所有的索引都使用相同的鍵高速緩存，當(dāng)訪問的索引不在緩存中時，使用 LRU （ Least Recently Used 最近最少使用）算法來替換緩存中最近最少使用的索引塊。為了進(jìn)一步避免對鍵高速緩存的爭用，從 MySQL5.1 開始，可以設(shè)置多個鍵高速緩存，并為不同的索引鍵指定使用的鍵高速緩存。下面的例子演示如何修改高速鍵緩存的值，如何設(shè)置多個鍵高速緩存，以及如何為不同的索引指定不同的緩存：
顯示當(dāng)前的參數(shù)大小，為16M：
mysql> show variables like 'key_buffer_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
修改參數(shù)值到200M：
mysql> set global key_buffer_size=204800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
上面介紹的是默認(rèn)的鍵緩存，下面介紹如何設(shè)置多個鍵緩存：
設(shè)置 hot_cache 的鍵緩存 100M ， cold_cache 的鍵緩存 100M ，另外還有 200M 的默認(rèn)的鍵緩存。如果索引不指定鍵緩存，則會放在默認(rèn)的鍵緩存中。
mysql> set global hot_cache.key_buffer_size=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> set global cold_cache.key_buffer_size= 1024 00;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
如果要顯示設(shè)置的多鍵緩存的值，可以使用：
mysql> SELECT @@global.hot_cache.key_buffer_size;
+------------------------------------+
| @@global.hot_cache.key_buffer_size |
+------------------------------------+
| 102400 |
+------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT @@global.cold_cache.key_buffer_size;
+-------------------------------------+
| @@global.cold_cache.key_buffer_size |
+-------------------------------------+
| 102400 |
+-------------------------------------+
1 row in set (0.00 sec)
指定不同的索引使用不同的鍵緩存：
mysql> CACHE INDEX test1 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test1 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
mysql> CACHE INDEX test2 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test2 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
通常在數(shù)據(jù)庫剛剛啟動的時候，需要等待數(shù)據(jù)庫熱起來，也就是等待數(shù)據(jù)被緩存到緩存區(qū)中，這段時間數(shù)據(jù)庫會因?yàn)?buffer 的命中率低而導(dǎo)致應(yīng)用的訪問效率不高。使用鍵高速緩存的時候，可以通過命令將索引預(yù)加載到緩存區(qū)中，大大縮短了數(shù)據(jù)庫預(yù)熱的時間。具體的操作方式是：
mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;
+------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------+----------+----------+
| test .test1 | preload_keys | status | OK |
| test .test2 | preload_keys | status | OK |
+------------+--------------+----------+----------+
2 rows in set (3.89 sec)
如果已經(jīng)使用 CACHE INDEX 語句為索引分配了一個鍵高速緩沖，預(yù)加載可以將索引塊放入該緩存，否則，索引塊將被加載到默認(rèn)的鍵高速緩沖。
 

4)、back_log：
要求 mysql 能有的連接數(shù)量。當(dāng)主要mysql線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。
back_log 值指出在mysql暫時停止回答新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的tcp/ip連接的偵聽隊(duì)列的大小。你的操作系統(tǒng)在這個隊(duì)列大小上有它自己的限制。試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效的。
當(dāng)你觀察你的主機(jī)進(jìn)程列表，發(fā)現(xiàn)大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待連接進(jìn)程時，就要加大 back_log 的值了。默認(rèn)數(shù)值是50，我把它改為500。

(5)、interactive_timeout：
服務(wù)器在關(guān)閉它前在一個交互連接上等待行動的秒數(shù)。一個交互的客戶被定義為對 mysql_real_connect()使用 client_interactive 選項(xiàng)的客戶。 默認(rèn)數(shù)值是28800，我把它改為7200。

(6)、sort_buffer：
每個需要進(jìn)行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速order by或group by操作。默認(rèn)數(shù)值是2097144(2m)，我把它改為 16777208 (16m)。

(7)、table_cache：
為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。mysql對每個唯一打開的表需要2個文件描述符。默認(rèn)數(shù)值是64，我把它改為512。

(8)、thread_cache_size：
可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當(dāng)斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。通過比較 connections 和 threads_created 狀態(tài)的變量，可以看到這個變量的作用。我把它設(shè)置為 80。

(9)mysql的搜索功能
用mysql進(jìn)行搜索，目的是能不分大小寫，又能用中文進(jìn)行搜索
只需起動mysqld時指定 --default-character-set=gb2312

(10)、wait_timeout：
服務(wù)器在關(guān)閉它之前在一個連接上等待行動的秒數(shù)。 默認(rèn)數(shù)值是28800，我把它改為7200。
 
(11)、innodb_thread_concurrency：
你的服務(wù)器CPU有幾個就設(shè)置為幾,默認(rèn)為8。
 
(12)、query_cache_size  與 query_cache_limit
QueryCache 之后所帶來的負(fù)面影響：
a) Query 語句的hash 運(yùn)算以及hash 查找資源消耗。當(dāng)我們使用Query Cache 之后，每條SELECT
類型的Query 在到達(dá)MySQL 之后，都需要進(jìn)行一個hash 運(yùn)算然后查找是否存在該Query 的
Cache，雖然這個hash 運(yùn)算的算法可能已經(jīng)非常高效了，hash 查找的過程也已經(jīng)足夠的優(yōu)化
了，對于一條Query 來說消耗的資源確實(shí)是非常非常的少，但是當(dāng)我們每秒都有上千甚至幾千
條Query 的時候，我們就不能對產(chǎn)生的CPU 的消耗完全忽視了。
b) Query Cache 的失效問題。如果我們的表變更比較頻繁，則會造成Query Cache 的失效率非常
高。這里的表變更不僅僅指表中數(shù)據(jù)的變更，還包括結(jié)構(gòu)或者索引等的任何變更。也就是說我
們每次緩存到Query Cache 中的Cache 數(shù)據(jù)可能在剛存入后很快就會因?yàn)楸碇械臄?shù)據(jù)被改變而被
清除，然后新的相同Query 進(jìn)來之后無法使用到之前的Cache。
c) Query Cache 中緩存的是Result Set ，而不是數(shù)據(jù)頁，也就是說，存在同一條記錄被Cache 多
次的可能性存在。從而造成內(nèi)存資源的過渡消耗。當(dāng)然，可能有人會說我們可以限定Query
Cache 的大小啊。是的，我們確實(shí)可以限定Query Cache 的大小，但是這樣，Query Cache 就很
容易造成因?yàn)閮?nèi)存不足而被換出，造成命中率的下降。
 
QueryCache 的正確使用：
雖然Query Cache 的使用會存在一些負(fù)面影響，但是我們也應(yīng)該相信其存在是必定有一定價值。我
們完全不用因?yàn)镼uery Cache 的上面三個負(fù)面影響就完全失去對Query Cache 的信心。只要我們理解了
Query Cache 的實(shí)現(xiàn)原理，那么我們就完全可以通過一定的手段在使用Query Cache 的時候揚(yáng)長避短，重
發(fā)發(fā)揮其優(yōu)勢，并有效的避開其劣勢。
首先，我們需要根據(jù)Query Cache 失效機(jī)制來判斷哪些表適合使用Query 哪些表不適合。由于Query
Cache 的失效主要是因?yàn)镼uery 所依賴的Table 的數(shù)據(jù)發(fā)生了變化，造成Query 的Result Set 可能已經(jīng)
有所改變而造成相關(guān)的Query Cache 全部失效，那么我們就應(yīng)該避免在查詢變化頻繁的Table 的Query 上
使用，而應(yīng)該在那些查詢變化頻率較小的Table 的Query 上面使用。MySQL 中針對Query Cache 有兩個專
用的SQL Hint（提示）：SQL_NO_CACHE 和SQL_CACHE，分別代表強(qiáng)制不使用Query Cache 和強(qiáng)制使用
Query Cache。我們完全可以利用這兩個SQL Hint，讓MySQL 知道我們希望哪些SQL 使用Query Cache 而
哪些SQL 就不要使用了。這樣不僅可以讓變化頻繁Table 的Query 浪費(fèi)Query Cache 的內(nèi)存，同時還可以
減少Q(mào)uery Cache 的檢測量。
其次，對于那些變化非常小，大部分時候都是靜態(tài)的數(shù)據(jù)，我們可以添加SQL_CACHE 的SQL Hint，
強(qiáng)制MySQL 使用Query Cache，從而提高該表的查詢性能。
最后，有些SQL 的Result Set 很大，如果使用Query Cache 很容易造成Cache 內(nèi)存的不足，或者將
之前一些老的Cache 沖刷出去。對于這一類Query 我們有兩種方法可以解決，一是使用SQL_NO_CACHE 參
數(shù)來強(qiáng)制他不使用Query Cache 而每次都直接從實(shí)際數(shù)據(jù)中去查找， 另一種方法是通過設(shè)定
“query_cache_limit”參數(shù)值來控制Query Cache 中所Cache 的最大Result Set ，系統(tǒng)默認(rèn)為
1M（1048576）。當(dāng)某個Query 的Result Set 大于“query_cache_limit”所設(shè)定的值的時候，Query
Cache 是不會Cache 這個Query 的。

(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定義了 InnoDB 存儲引擎的表數(shù)據(jù)和索引數(shù)據(jù)的最大內(nèi)存緩沖區(qū)大小。和 MyISAM 存儲引擎不同， MyISAM 的 key_buffer_size 只能緩存索引鍵，而 innodb_buffer_pool_size 卻可以緩存數(shù)據(jù)塊和索引鍵。適當(dāng)?shù)脑黾舆@個參數(shù)的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O 。在一個以 InnoDB 為主的專用數(shù)據(jù)庫服務(wù)器上，可以考慮把該參數(shù)設(shè)置為物理內(nèi)存大小的 60%-80%
 
InnoDB占用的內(nèi)存，除innodb_buffer_pool_size用于存儲頁面緩存數(shù)據(jù)外，另外正常情況下還有大約8%的開銷，主要用在每個緩存頁幀的描述、adaptive hash等數(shù)據(jù)結(jié)構(gòu)，如果不是安全關(guān)閉，啟動時還要恢復(fù)的話，還要另開大約12%的內(nèi)存用于恢復(fù)，兩者相加就有差不多21%的開銷。

這樣，12G的innodb_buffer_pool_size，最多的時候InnoDB就可能占用到14.5G(12G X 21%)的內(nèi)存，再加上操作系統(tǒng)用的幾百M(fèi)，近千個線程堆棧，就差不多16G了。
 
MAX_QUERIES_PER_HOUR 用來限制用戶每小時運(yùn)行的查詢數(shù)量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
（db用戶在dbname的數(shù)據(jù)庫上控制用戶每小時打開新連接的數(shù)量為5個）
 
MAX_USER_CONNECTIONS 限制有多少用戶連接MYSQL服務(wù)器：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
（db用戶在dbname的數(shù)據(jù)庫賬戶一次可以同時連接的最大連接數(shù)為2個）

MAX_UPDATES_PER_HOUR 用來限制用戶每小時的修改數(shù)據(jù)庫數(shù)據(jù)的數(shù)量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
（db用戶在dbname的數(shù)據(jù)庫上控制用戶每小時修改更新數(shù)據(jù)庫的次數(shù)為5次）
MAX_USER_CONNECTIONS 用來限制用戶每小時的修改數(shù)據(jù)庫數(shù)據(jù)的數(shù)量：
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql單個用戶的最大連接數(shù)
（db用戶在dbname的數(shù)據(jù)庫上控制用戶每小時的連接數(shù)為20個）
 
調(diào)優(yōu)舉例
針對my.cnf文件進(jìn)行優(yōu)化：
[mysqld]
skip-locking（取消文件系統(tǒng)的外部鎖）
skip-name-resolve（不進(jìn)行域名反解析,注意由此帶來的權(quán)限/授權(quán)問題）
key_buffer_size = 256M（分配給MyISAM索引緩存的內(nèi)存總數(shù)）對于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。
  注意：該參數(shù)值設(shè)置的過大反而會是服務(wù)器整體效率降低！
  max_allowed_packet = 4M（允許最大的包大?。?br />   thread_stack = 256K（每個線程的大小）
  table_cache = 128K（緩存可重用的線程數(shù)）
  back_log = 384（臨時停止響應(yīng)新請求前在短時間內(nèi)可以堆起多少請求，如果你需要在短時間內(nèi)允許大量連接，可以增加該數(shù)值）
  sort_buffer_size = 2M(分配給每個線程中處理排序)
  read_buffer_size = 2M（讀取的索引緩沖區(qū)大?。?br />   join_buffer_size = 2M（分配給每個線程中處理掃描表連接及索引的內(nèi)存）
  myisam_sort_buffer_size = 64M（myisam引擎排序緩沖區(qū)的大?。?br />   table_cache = 512（緩存數(shù)據(jù)表的數(shù)量，避免重復(fù)打開表的開銷）
  thread_cache_size = 64（緩存可重用線程數(shù)，見笑創(chuàng)建新線程的開銷）
  query_cache_size = 64M（控制分配給查詢緩存的內(nèi)存總量）
  tmp_table_size = 256M(指定mysql緩存的內(nèi)存大小)
  max_connections = 768（最大連接數(shù)）指mysql整個的最大連接數(shù)
max_connect_errors = 10000(最大連接錯誤數(shù)據(jù))
  wait_timeout = 10（超時時間，可以避免攻擊）
  thread_concurrency = 8（根據(jù)cpu數(shù)量來設(shè)置）
  skip-bdb 禁用不必要的引擎
  skip-networking（關(guān)閉mysql tcp/ip連接方式）
  Log-slow-queries = /var/log/mysqlslowqueries.log
  long_query_time = 4（設(shè)定慢查詢的時間）
  skip-host-cache(提高mysql速度的)
  open_files_limit = 4096(打開文件數(shù)）
interactive_timeout = 10(服務(wù)器在關(guān)閉它前在一個交互連接上等待行動的秒數(shù))
max_user_connections = 500（最大用戶連接數(shù)）
 
key_buffer_size                 默認(rèn)為218       調(diào)到128最佳
query_cache_size 
tmp_table_size                  默認(rèn)為16M        調(diào)到64-256最掛

innodb_thread_concurrency=8       你的服務(wù)器CPU有幾個就設(shè)置為幾,默認(rèn)為8
 
table_cache=1024 物理內(nèi)存越大,設(shè)置就越大.默認(rèn)為2402,調(diào)到512-1024最佳
innodb_additional_mem_pool_size=8M   默認(rèn)為2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列隊(duì)滿后再統(tǒng)一儲存,默認(rèn)為1
innodb_log_buffer_size=4M          默認(rèn)為1M

read_buffer_size=4M                   默認(rèn)為64K
read_rnd_buffer_size   隨機(jī)讀 緩存區(qū)  默認(rèn)為256K
sort_buffer_size=32M                   默認(rèn)為256K
max_connections=1024                 默認(rèn)為1210
thread_cache_size=120             默認(rèn)為60
 
性能測試
1、mysql 自帶測試工具
shell> perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
server_name是一個支持的服務(wù)器。要獲得所有選項(xiàng)和支持的服務(wù)器，調(diào)用命令：
shell> perl run-all-tests --help
2、mysqlreport
 http://hackmysql.com/mysqlreport
 
參考文檔
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
http://hackmysql.com/tools
http://www.imysql.cn/