mysql 優化調試命令
1、mysqld --verbose --help
這個命令生成所有mysqld選項和可配置變量的列表
2、通過連接它並執行這個命令，可以看到實際上使用的變量的值：
mysql> SHOW VARIABLES;
還可以通過下面的語句看到運行服務器的統計和狀態指標：
mysql>SHOW STATUS；
使用mysqladmin還可以獲得系統變量和狀態信息：
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table 命令可以立即關閉所有不使用的表並將所有使用中的表標記為已經關閉，這樣可以有效釋放大多數使用中的內存。FLUSH TABLE在關閉所有表之前不返回結果。
swap -s檢查可用交換區
mysql內存計算公式
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 中輸入如下命令，可自動計算自己的當前配置最大的內存消耗
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關鍵參數設置
Mysqld 數據庫的參數設置有兩種類型，
一種是全局參數，影響服務器的全局操作；
另一種是會話級參數，只影響當前的客戶端連接的相關操作。
服務器啟動時，所有全局參數都初始化為默認值。可以在初始化文件或命令行中指定的選項來更改這些默認值。服務器啟動後，通過連接服務器並執行 SET GLOBAL var_name 語句可以更改動態全局參數。要想更改全局參數，必須具有 SUPER 權限。全局參數的修改只對新的連接生效，已有的客戶端連接並不會生效。
服務器還可以為每個客戶端連接維護會話級參數，客戶端連接時使用相應全局參數的當前值對客戶端會話參數進行初始化。客戶可以通過 SET SESSION var_name 語句來更改動態會話參數。設置會話級參數不需要特殊權限，但每個客戶端可以只更改自己的會話級參數，不能更改其它客戶的會話級參數。
不指定設置的參數類型時，默認設置的是會話級參數。
(1)、max_connections：
允許的同時客戶的數量。增加該值增加 mysqld 要求的文件描述符的數量。這個數字應該增加，否則，你將經常看到 too many connections 錯誤。 默認數值是100，我把它改為1024 。
(2)、record_buffer：
每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區。如果你做很多順序掃描，你可能想要增加該值。默認數值是131072(128k)，我把它改為16773120 (16m)
(3)、key_buffer_size：
為了最小化磁盤的 I/O ， MyISAM 存儲引擎的表使用鍵高速緩存來緩存索引，這個鍵高速緩存的大小則通過 key-buffer-size 參數來設置。如果應用系統中使用的表以 MyISAM 存儲引擎為主，則應該適當增加該參數的值，以便盡可能的緩存索引，提高訪問的速度。
索引塊是緩沖的並且被所有的線程共享。key_buffer_size是用於索引塊的緩沖區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，系統將開始換頁並且真的變慢了。默認數值是8388600(8m)，我的mysql主機有2gb內存，所以我把它改為 402649088(400mb)。
默認情況下，所有的索引都使用相同的鍵高速緩存，當訪問的索引不在緩存中時，使用 LRU （ Least Recently Used 最近最少使用）算法來替換緩存中最近最少使用的索引塊。為了進一步避免對鍵高速緩存的爭用，從 MySQL5.1 開始，可以設置多個鍵高速緩存，並為不同的索引鍵指定使用的鍵高速緩存。下面的例子演示如何修改高速鍵緩存的值，如何設置多個鍵高速緩存，以及如何為不同的索引指定不同的緩存：
顯示當前的參數大小，為16M：
mysql> show variables like 'key_buffer_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
修改參數值到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)
上面介紹的是默認的鍵緩存，下面介紹如何設置多個鍵緩存：
設置 hot_cache 的鍵緩存 100M ， cold_cache 的鍵緩存 100M ，另外還有 200M 的默認的鍵緩存。如果索引不指定鍵緩存，則會放在默認的鍵緩存中。
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)
如果要顯示設置的多鍵緩存的值，可以使用：
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)
通常在數據庫剛剛啟動的時候，需要等待數據庫熱起來，也就是等待數據被緩存到緩存區中，這段時間數據庫會因為 buffer 的命中率低而導致應用的訪問效率不高。使用鍵高速緩存的時候，可以通過命令將索引預加載到緩存區中，大大縮短了數據庫預熱的時間。具體的操作方式是：
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)
如果已經使用 CACHE INDEX 語句為索引分配了一個鍵高速緩沖，預加載可以將索引塊放入該緩存，否則，索引塊將被加載到默認的鍵高速緩沖。
4)、back_log：
要求 mysql 能有的連接數量。當主要mysql線程在一個很短時間內得到非常多的連接請求，這就起作用，然後主線程花些時間(盡管很短)檢查連接並且啟動一個新線程。
back_log 值指出在mysql暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的tcp/ip連接的偵聽隊列的大小。你的操作系統在這個隊列大小上有它自己的限制。試圖設定back_log高於你的操作系統的限制將是無效的。
當你觀察你的主機進程列表，發現大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待連接進程時，就要加大 back_log 的值了。默認數值是50，我把它改為500。
(5)、interactive_timeout：
服務器在關閉它前在一個交互連接上等待行動的秒數。一個交互的客戶被定義為對 mysql_real_connect()使用 client_interactive 選項的客戶。 默認數值是28800，我把它改為7200。
(6)、sort_buffer：
每個需要進行排序的線程分配該大小的一個緩沖區。增加這值加速order by或group by操作。默認數值是2097144(2m)，我把它改為 16777208 (16m)。
(7)、table_cache：
為所有線程打開表的數量。增加該值能增加mysqld要求的文件描述符的數量。mysql對每個唯一打開的表需要2個文件描述符。默認數值是64，我把它改為512。
(8)、thread_cache_size：
可以復用的保存在中的線程的數量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。通過比較 connections 和 threads_created 狀態的變量，可以看到這個變量的作用。我把它設置為 80。
(9)mysql的搜索功能
用mysql進行搜索，目的是能不分大小寫，又能用中文進行搜索
只需起動mysqld時指定 --default-character-set=gb2312
(10)、wait_timeout：
服務器在關閉它之前在一個連接上等待行動的秒數。 默認數值是28800，我把它改為7200。
(11)、innodb_thread_concurrency：
你的服務器CPU有幾個就設置為幾,默認為8。
(12)、query_cache_size 與 query_cache_limit
QueryCache 之後所帶來的負面影響：
a) Query 語句的hash 運算以及hash 查找資源消耗。當我們使用Query Cache 之後，每條SELECT
類型的Query 在到達MySQL 之後，都需要進行一個hash 運算然後查找是否存在該Query 的
Cache，雖然這個hash 運算的算法可能已經非常高效了，hash 查找的過程也已經足夠的優化
了，對於一條Query 來說消耗的資源確實是非常非常的少，但是當我們每秒都有上千甚至幾千
條Query 的時候，我們就不能對產生的CPU 的消耗完全忽視了。
b) Query Cache 的失效問題。如果我們的表變更比較頻繁，則會造成Query Cache 的失效率非常
高。這裡的表變更不僅僅指表中數據的變更，還包括結構或者索引等的任何變更。也就是說我
們每次緩存到Query Cache 中的Cache 數據可能在剛存入後很快就會因為表中的數據被改變而被
清除，然後新的相同Query 進來之後無法使用到之前的Cache。
c) Query Cache 中緩存的是Result Set ，而不是數據頁，也就是說，存在同一條記錄被Cache 多
次的可能性存在。從而造成內存資源的過渡消耗。當然，可能有人會說我們可以限定Query
Cache 的大小啊。是的，我們確實可以限定Query Cache 的大小，但是這樣，Query Cache 就很
容易造成因為內存不足而被換出，造成命中率的下降。
QueryCache 的正確使用：
雖然Query Cache 的使用會存在一些負面影響，但是我們也應該相信其存在是必定有一定價值。我
們完全不用因為Query Cache 的上面三個負面影響就完全失去對Query Cache 的信心。只要我們理解了
Query Cache 的實現原理，那麼我們就完全可以通過一定的手段在使用Query Cache 的時候揚長避短，重
發發揮其優勢，並有效的避開其劣勢。
首先，我們需要根據Query Cache 失效機制來判斷哪些表適合使用Query 哪些表不適合。由於Query
Cache 的失效主要是因為Query 所依賴的Table 的數據發生了變化，造成Query 的Result Set 可能已經
有所改變而造成相關的Query Cache 全部失效，那麼我們就應該避免在查詢變化頻繁的Table 的Query 上
使用，而應該在那些查詢變化頻率較小的Table 的Query 上面使用。MySQL 中針對Query Cache 有兩個專
用的SQL Hint（提示）：SQL_NO_CACHE 和SQL_CACHE，分別代表強制不使用Query Cache 和強制使用
Query Cache。我們完全可以利用這兩個SQL Hint，讓MySQL 知道我們希望哪些SQL 使用Query Cache 而
哪些SQL 就不要使用了。這樣不僅可以讓變化頻繁Table 的Query 浪費Query Cache 的內存，同時還可以
減少Query Cache 的檢測量。
其次，對於那些變化非常小，大部分時候都是靜態的數據，我們可以添加SQL_CACHE 的SQL Hint，
強制MySQL 使用Query Cache，從而提高該表的查詢性能。
最後，有些SQL 的Result Set 很大，如果使用Query Cache 很容易造成Cache 內存的不足，或者將
之前一些老的Cache 沖刷出去。對於這一類Query 我們有兩種方法可以解決，一是使用SQL_NO_CACHE 參
數來強制他不使用Query Cache 而每次都直接從實際數據中去查找， 另一種方法是通過設定
“query_cache_limit”參數值來控制Query Cache 中所Cache 的最大Result Set ，系統默認為
1M（1048576）。當某個Query 的Result Set 大於“query_cache_limit”所設定的值的時候，Query
Cache 是不會Cache 這個Query 的。
(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定義了 InnoDB 存儲引擎的表數據和索引數據的最大內存緩沖區大小。和 MyISAM 存儲引擎不同， MyISAM 的 key_buffer_size 只能緩存索引鍵，而 innodb_buffer_pool_size 卻可以緩存數據塊和索引鍵。適當的增加這個參數的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O 。在一個以 InnoDB 為主的專用數據庫服務器上，可以考慮把該參數設置為物理內存大小的 60%-80%
InnoDB占用的內存，除innodb_buffer_pool_size用於存儲頁面緩存數據外，另外正常情況下還有大約8%的開銷，主要用在每個緩存頁幀的描述、adaptive hash等數據結構，如果不是安全關閉，啟動時還要恢復的話，還要另開大約12%的內存用於恢復，兩者相加就有差不多21%的開銷。
這樣，12G的innodb_buffer_pool_size，最多的時候InnoDB就可能占用到14.5G(12G X 21%)的內存，再加上操作系統用的幾百M，近千個線程堆棧，就差不多16G了。
MAX_QUERIES_PER_HOUR 用來限制用戶每小時運行的查詢數量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
（db用戶在dbname的數據庫上控制用戶每小時打開新連接的數量為5個）
MAX_USER_CONNECTIONS 限制有多少用戶連接MYSQL服務器：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
（db用戶在dbname的數據庫賬戶一次可以同時連接的最大連接數為2個）
MAX_UPDATES_PER_HOUR 用來限制用戶每小時的修改數據庫數據的數量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
（db用戶在dbname的數據庫上控制用戶每小時修改更新數據庫的次數為5次）
MAX_USER_CONNECTIONS 用來限制用戶每小時的修改數據庫數據的數量：
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql單個用戶的最大連接數
（db用戶在dbname的數據庫上控制用戶每小時的連接數為20個）
調優舉例
針對my.cnf文件進行優化：
[mysqld]
skip-locking（取消文件系統的外部鎖）
skip-name-resolve（不進行域名反解析,注意由此帶來的權限/授權問題）
key_buffer_size = 256M（分配給MyISAM索引緩存的內存總數）對於內存在4GB左右的服務器該參數可設置為256M或384M。
注意：該參數值設置的過大反而會是服務器整體效率降低！
max_allowed_packet = 4M（允許最大的包大小）
thread_stack = 256K（每個線程的大小）
table_cache = 128K（緩存可重用的線程數）
back_log = 384（臨時停止響應新請求前在短時間內可以堆起多少請求，如果你需要在短時間內允許大量連接，可以增加該數值）
sort_buffer_size = 2M(分配給每個線程中處理排序)
read_buffer_size = 2M（讀取的索引緩沖區大小）
join_buffer_size = 2M（分配給每個線程中處理掃描表連接及索引的內存）
myisam_sort_buffer_size = 64M（myisam引擎排序緩沖區的大小）
table_cache = 512（緩存數據表的數量，避免重復打開表的開銷）
thread_cache_size = 64（緩存可重用線程數，見笑創建新線程的開銷）
query_cache_size = 64M（控制分配給查詢緩存的內存總量）
tmp_table_size = 256M(指定mysql緩存的內存大小)
max_connections = 768（最大連接數）指mysql整個的最大連接數
max_connect_errors = 10000(最大連接錯誤數據)
wait_timeout = 10（超時時間，可以避免攻擊）
thread_concurrency = 8（根據cpu數量來設置）
skip-bdb 禁用不必要的引擎
skip-networking（關閉mysql tcp/ip連接方式）
Log-slow-queries = /var/log/mysqlslowqueries.log
long_query_time = 4（設定慢查詢的時間）
skip-host-cache(提高mysql速度的)
open_files_limit = 4096(打開文件數）
interactive_timeout = 10(服務器在關閉它前在一個交互連接上等待行動的秒數)
max_user_connections = 500（最大用戶連接數）
key_buffer_size 默認為218 調到128最佳
query_cache_size
tmp_table_size 默認為16M 調到64-256最掛
innodb_thread_concurrency=8 你的服務器CPU有幾個就設置為幾,默認為8
table_cache=1024 物理內存越大,設置就越大.默認為2402,調到512-1024最佳
innodb_additional_mem_pool_size=8M 默認為2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列隊滿後再統一儲存,默認為1
innodb_log_buffer_size=4M 默認為1M
read_buffer_size=4M 默認為64K
read_rnd_buffer_size 隨機讀 緩存區 默認為256K
sort_buffer_size=32M 默認為256K
max_connections=1024 默認為1210
thread_cache_size=120 默認為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是一個支持的服務器。要獲得所有選項和支持的服務器，調用命令：
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/
