mysql的監(jiān)控方法大致分為兩類：

1.連接到mysql數(shù)據(jù)庫(kù)內(nèi)部,，使用show status,，show variables，flush status 來(lái)查看mysql的各種性能指標(biāo),。

2. 直接使用mysqladmin查看其性能指標(biāo),，例如：

UserParameter=mysql.uptime,mysqladmin -uroot status|cut -f2 -d":"|cut -f1 -d"T"

mysqladmin兩個(gè)參數(shù)，status,，extended-status

shell > mysqladmin -uroot -ppassword variables status

可得到以下信息（后面詳解）

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

Uptime: 4557887 #mysql運(yùn)行的秒數(shù)

Threads: 1 #連接數(shù)

Questions: 1684130 #The number of questions (queries) from clients since the server was started.

Slow queries: 0 #The number of queries that have taken more than long_query_time seconds

Opens: 221872 #The number of tables the server has opened.

Flush tables: 1 #The number of flush-*, refresh, and reload commands the server has executed.

Open tables: 64 #The number of tables that currently are open.

Queries per second avg: 0.369 #從上次運(yùn)行開(kāi)始計(jì)算,，每秒鐘平均查詢次數(shù)

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

Questions = Com_* + Qcache_hits

最完整的信息

shell > mysqladmin -uroot -ppassword variables extended-status

其他的信息

shell > /usr/libexec/mysqld --verbose --help （這個(gè)命令生成所有mysqld選項(xiàng)和可配置變量的列表 ）

mysql>SHOW STATUS; （服務(wù)器狀態(tài)變量，運(yùn)行服務(wù)器的統(tǒng)計(jì)和狀態(tài)指標(biāo)）

mysql> SHOW VARIABLES;（服務(wù)器系統(tǒng)變量,，實(shí)際上使用的變量的值）

或者

mysql>SHOW STATUS LIKE '%變量名% ' ;

對(duì)配置參數(shù)的說(shuō)明：

配置參數(shù)的格式如下：（shell > mysqladmin -uroot -ppassword variables extended-status）

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

+-----------------------------------------+------------------------------------------------------------+

| Variable_name | Value |

+-----------------------------------------+------------------------------------------------------------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| automatic_sp_privileges | ON |

.........

注：value 值的單位是byte ,，要得到M ，需除以2次1024

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

Uptime 4405546

MySQL服務(wù)器已經(jīng)運(yùn)行的秒數(shù)

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

auto_increment_increment 1

auto_increment_offset 1

兩個(gè)變量值都只能為1到65,535之間的整數(shù)值,。設(shè)置為非整數(shù)值,，則會(huì)給出錯(cuò)誤。

這兩個(gè)變量影響AUTO_INCREMENT列。

auto_increment_increment控制列中的值的增量值（步進(jìn)量）,。

auto_increment_offset確定AUTO_INCREMENT列值的初始值,。

一般不去更改。更改方法：mysql> SET @auto_increment_offset=5;

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

max_connections 100

table_cache 64

open_files_limit 1024

Open_tables 64

Opened_tables 187690

幾個(gè)參數(shù)的關(guān)系：

table_cache * 2 + max_connections＝max_open_files

max_connections

默認(rèn)為100

mysql>show processlist;

mysql>show full processlist;

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

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

max_open_files 由 open_files_limit 參數(shù)決定,。

mysql打開(kāi)的最大文件數(shù),，受兩個(gè)參數(shù)的影響：系統(tǒng)打開(kāi)的最大文件數(shù)（ulimit -n）和 open_files_limit 。

加大max_open_files的值

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

在/etc/my.cnf加入open_files_limit=8192

在/etc/security/limits.conf添加

* soft nofile 8192

* hard nofile 8192

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

最好用sysctl或者修改/etc/sysctl.conf文件,，同時(shí)還要在配置文件中把open_files_limit這個(gè)參數(shù)增大,，對(duì)于4G內(nèi)存服務(wù)器，open_files_limit至少要增大到4096,，非特殊情況,，設(shè)置成8192就可以了。

table_cache

MySQL 5.0升級(jí)到5.1,，table_cache 改名table_open_cache

設(shè)置表高速緩存的數(shù)目,。

表緩存的說(shuō)明：

當(dāng) Mysql 訪問(wèn)一個(gè)表時(shí)，如果該表在緩存中已經(jīng)被打開(kāi),，則可以直接訪問(wèn)緩存,；如果還沒(méi)有被緩存，但是在 Mysql 表緩沖區(qū)中還有空間,，那么這個(gè)表就被打開(kāi)并放入表緩沖區(qū),；如果表緩存滿了，則會(huì)按照一定的規(guī)則將當(dāng)前未用的表釋放,，或者臨時(shí)擴(kuò)大表緩存來(lái)存放,，使用表緩存的好處是可以更快速地訪問(wèn)表中的內(nèi)容。

每個(gè)連接進(jìn)來(lái),，都會(huì)至少打開(kāi)一個(gè)表緩存,。因此， table_cache 的大小應(yīng)與 max_connections 的設(shè)置有關(guān),。例如,，對(duì)于 200 個(gè)并行運(yùn)行的連接，應(yīng)該讓表的緩存至少有 200 × N ,，這里 N 是網(wǎng)站程序一次查詢所用到的表的最大值,。

每個(gè)線程會(huì)獨(dú)自持有一個(gè)數(shù)據(jù)文件的文件描述符，而索引文件的文件描述符是公用的,。當(dāng)table cache不夠用的時(shí)候,，MySQL會(huì)采用LRU算法踢掉最長(zhǎng)時(shí)間沒(méi)有使用的表。如果table_cache設(shè)置過(guò)小,，MySQL就會(huì)反復(fù)打開(kāi),、關(guān)閉 frm文件,，造成一定的性能損失。如果table_cache設(shè)置過(guò)大,，MySQL將會(huì)消耗很多CPU去做 table cache的算法運(yùn)算,。

而InnoDB的元數(shù)據(jù)管理是放在共享表空間里面做的，所以獲取表的結(jié)構(gòu)不需要去反復(fù)解析frm文件,，這是比MyISAM強(qiáng)的地方,。即使 table_cache設(shè)置過(guò)小，對(duì)于InnoDB的影響也是很小的,，因?yàn)樗静恍枰磸?fù)打開(kāi),、關(guān)閉frm文件去獲取元數(shù)據(jù)。

合理設(shè)置table_cache的大?。和ㄟ^(guò)查看open_tables,，Opened_tables，F(xiàn)lush tables 的值來(lái)比較,。

察看當(dāng)前的表緩存情況：

shell > mysqladmin -uroot -ppassword variables status

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

Opens: 221872 則是已經(jīng)打開(kāi)的表的數(shù)量,。

Flush tables: 1

Open tables: 64 是當(dāng)前打開(kāi)的表的數(shù)量

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

mysql> show global status like 'open%_tables';

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

open_tables 是當(dāng)前打開(kāi)的表的數(shù)量，

Opened_tables 表示打開(kāi)過(guò)的表數(shù)量

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

清空表緩存

mysql> flush tables;

如果發(fā)現(xiàn) open_tables 接近 table_cache 的時(shí)候,，如果 Opened_tables 隨著重新運(yùn)行 SHOW STATUS 命令快速增加,，就說(shuō)明緩存命中率不夠,。并且多次執(zhí)行FLUSH TABLES(通過(guò)shell > mysqladmin -uroot -ppassword variables status ),，那就說(shuō)明可能 table_cache 設(shè)置的偏小，經(jīng)常需要將緩存的表清出,，將新的表放入緩存,，這時(shí)可以考慮增加這個(gè)參數(shù)的大小來(lái)改善訪問(wèn)的效率。

如果 Open_tables 比 table_cache 設(shè)置小很多,，就說(shuō)明table_cache 設(shè)的太大了,。

table_cache的值在2G內(nèi)存以下的機(jī)器中的值默認(rèn)時(shí)256到512，如果機(jī)器有4G內(nèi)存,則默認(rèn)這個(gè)值是2048,，但這決意味著機(jī)器內(nèi)存越大,，這個(gè)值應(yīng)該越大，因?yàn)閠able_cache加大后,，使得mysql對(duì)SQL響應(yīng)的速度更快了,，不可避免的會(huì)產(chǎn)生更多的死鎖（dead lock），這樣反而使得數(shù)據(jù)庫(kù)整個(gè)一套操作慢了下來(lái),，嚴(yán)重影響性能,。

注意，不能盲目地把table_cache設(shè)置成很大的值,。如果設(shè)置得太高,，可能會(huì)造成文件描述符不足,，從而造成性能不穩(wěn)定或者連接失敗。

對(duì)于有1G內(nèi)存的機(jī)器,，推薦值是128－256,。

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

key_buffer_size 67108864（/1024/1024=64M）

Key_read_requests 40944 從緩存讀鍵的數(shù)據(jù)塊的請(qǐng)求數(shù)。

Key_reads 2711 從硬盤(pán)讀取鍵的數(shù)據(jù)塊的次數(shù),。

Key_write_requests 將鍵的數(shù)據(jù)塊寫(xiě)入緩存的請(qǐng)求數(shù),。

Key_writes 向硬盤(pán)寫(xiě)入將鍵的數(shù)據(jù)塊的物理寫(xiě)操作的次數(shù)。

（獲得信息：

shell > mysqladmin -uroot -ppassword variables extended-status

shell>mysqladmin -uroot -ppassword variable status

mysql> show status like '%key_read%';

）

key_buffer_size設(shè)置索引塊（index blocks）緩存的大小,，保存了 MyISAM 表的索引塊,。它被所有線程共享，決定了數(shù)據(jù)庫(kù)索引處理的速度,，尤其是索引讀的速度,。理想情況下，對(duì)于這些塊的請(qǐng)求應(yīng)該來(lái)自于內(nèi)存,，而不是來(lái)自于磁盤(pán),。

只對(duì)MyISAM表起作用。即使你不使用MyISAM表,，但是內(nèi)部的臨時(shí)磁盤(pán)表是MyISAM表,，也要使用該值。

key_buffer_size: 如果不使用MyISAM存儲(chǔ)引擎,，16MB足以,，用來(lái)緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲(chǔ)引擎,，在內(nèi)存允許的情況下,，盡可能將所有索引放入內(nèi)存，簡(jiǎn)單來(lái)說(shuō)就是“越大越好”

合理設(shè)置key_buffer_size的方法：

查看Key_read_requests和Key_reads的比例,，

Key_reads 代表命中磁盤(pán)的請(qǐng)求個(gè)數(shù),， Key_read_requests 是總數(shù)。命中磁盤(pán)的讀請(qǐng)求數(shù)除以讀請(qǐng)求總數(shù)就是不中比率,。如果每 1,000 個(gè)請(qǐng)求中命中磁盤(pán)的數(shù)目超過(guò) 1 個(gè),，就應(yīng)該考慮增大關(guān)鍵字緩沖區(qū)了。

key_reads / key_read_requests的值應(yīng)該盡可能的低,，比如1:100,，1:1000 ，1:10000,。

對(duì)于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M,。

注意：該參數(shù)值設(shè)置的過(guò)大反而會(huì)是服務(wù)器整體效率降低！

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

256MB內(nèi)存和許多表,，想要在中等數(shù)量的客戶時(shí)獲得最大性能,，應(yīng)使用：

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 --sort_buffer_size=4M --read_buffer_size=1M &

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

每個(gè)連接到MySQL服務(wù)器的線程都需要有自己的緩沖,，默認(rèn)為其分配256K。事務(wù)開(kāi)始之后,，則需要增加更多的空間,。運(yùn)行較小的查詢可能僅給指定的線程增加少量的內(nèi)存消耗，例如存儲(chǔ)查詢語(yǔ)句的空間等,。但如果對(duì)數(shù)據(jù)表做復(fù)雜的操作比較復(fù)雜,，例如排序則需要使用臨時(shí)表，此時(shí)會(huì)分配大約read_buffer_size,，sort_buffer_size,，read_rnd_buffer_size，tmp_table_size大小的內(nèi)存空間,。不過(guò)它們只是在需要的時(shí)候才分配,，并且在那些操作做完之后就釋放了。

myisam_sort_buffer_size 8388608

當(dāng)在REPAIR TABLE或用CREATE INDEX創(chuàng)建索引或ALTER TABLE過(guò)程中排序 MyISAM索引分配之緩沖區(qū),。

sort_buffer_size 2097144

每個(gè)排序線程分配的緩沖區(qū)的大小,。增加該值可以加快ORDER BY或GROUP BY操作。

注意：該參數(shù)對(duì)應(yīng)的分配內(nèi)存是每個(gè)連接獨(dú)享,，如果有100個(gè)連接,，那么實(shí)際分配的總共排序緩沖區(qū)大小為100 × 6 ＝ 600MB。所以,，對(duì)于內(nèi)存在4GB左右的服務(wù)器推薦設(shè)置為6-8M,。

mysql> SHOW STATUS LIKE "sort%";

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

Sort_merge_passes 1

Sort_range 79192

Sort_rows 2066532

Sort_scan 44006

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

如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size,。

當(dāng) MySQL 必須要進(jìn)行排序時(shí),，就會(huì)在從磁盤(pán)上讀取數(shù)據(jù)時(shí)分配一個(gè)排序緩沖區(qū)來(lái)存放這些數(shù)據(jù)行,。如果要排序的數(shù)據(jù)太大,，那么數(shù)據(jù)就必須保存到磁盤(pán)上的臨時(shí)文件中，并再次進(jìn)行排序,。如果 sort_merge_passes 狀態(tài)變量很大,，這就指示了磁盤(pán)的活動(dòng)情況。

read_buffer_size 131072

（show variables like 'read%';）

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

read_buffer_size 1048576

read_rnd_buffer_size 524288

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

read_buffer_size是MySql讀入緩沖區(qū)大小,。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū),，MySql會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小,。

每個(gè)線程連續(xù)掃描時(shí)為掃描的每個(gè)表分配的緩沖區(qū)的大小(字節(jié)),。如果進(jìn)行多次連續(xù)掃描，可能需要增加該值,， 默認(rèn)值為131072,。和sort_buffer_size一樣,，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)享。

read_rnd_buffer_size

read_rnd_buffer_size是MySql的隨機(jī)讀緩沖區(qū)大小,。當(dāng)按任意順序讀取行時(shí)(例如,，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū),。進(jìn)行排序查詢時(shí),，MySql會(huì)首先掃描一遍該緩沖,，以避免磁盤(pán)搜索,，提高查詢速度，如果需要排序大量數(shù)據(jù),，可適當(dāng)調(diào)高該值,。該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)享,。

join_buffer_size 131072

聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣,，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享,。

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

max_allowed_packet 1048576

net_buffer_length 16384

包消息緩沖區(qū)初始化為net_buffer_length字節(jié)，但需要時(shí)可以增長(zhǎng)到max_allowed_packet字節(jié),。該值默認(rèn)很小,，以捕獲大的(可能是錯(cuò)誤的)數(shù)據(jù)包。

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

thread_stack 196608

每個(gè)線程的堆棧大小,。用crash-me測(cè)試檢測(cè)出的許多限制取決于該值,。 默認(rèn)值足夠大，可以滿足普通操作,。

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

thread_cache_size 0

query_cache_size 0

tmp_table_size 33554432

innodb_thread_concurrency 8

max_connections 100

max_connect_errors 10

（獲得信息：

shell > mysqladmin -uroot -ppassword variables extended-status

shell>mysqladmin -uroot -ppassword variable status

）

thread_cache_size

（

mysql> show status LIKE 'threads%';

）

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

Threads_cached 27

Threads_connected 15

Threads_created 838610

Threads_running 3

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

線程緩存,。mysqld 在接收連接時(shí)會(huì)根據(jù)需要生成線程。在一個(gè)連接變化很快的繁忙服務(wù)器上,，對(duì)線程進(jìn)行緩存便于以后使用可以加快最初的連接,。

此處重要的值是 Threads_created，每次 mysqld 需要?jiǎng)?chuàng)建一個(gè)新線程時(shí),，這個(gè)值都會(huì)增加,。如果這個(gè)數(shù)字在連續(xù)執(zhí)行 SHOW STATUS 命令時(shí)快速增加，就應(yīng)該嘗試增大線程緩存,。

query_cache_size

mysql> SHOW VARIABLES LIKE 'have_query_cache';

mysql> show variables like '%query%';

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

ft_query_expansion_limit 20

have_query_cache YES

long_query_time 10.000000

query_alloc_block_size 8192

query_cache_limit 1048576

query_cache_min_res_unit 4096

query_cache_size 0

query_cache_type ON

query_cache_wlock_invalidate OFF

query_prealloc_size 8192

slow_query_log OFF

slow_query_log_file /var/run/mysqld/mysqld-slow.log

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

have_query_cache 是否有查詢緩存

query_cache_limit 指定單個(gè)查詢能夠使用的緩沖區(qū)大小,，缺省為1M

query_cache_type 變量影響其工作方式。這個(gè)變量可以設(shè)置為下面的值：

0 或OFF 將阻止緩存或查詢緩存結(jié)果,。

1 或ON 將允許緩存,，以SELECT SQL_NO_CACHE 開(kāi)始的查詢語(yǔ)句除外。

2 或DEMAND ,， 僅對(duì)以SELECT SQL_CACHE 開(kāi)始的那些查詢語(yǔ)句啟用緩存,。

如果全部使用innodb存儲(chǔ)引擎,，建議為0，如果使用MyISAM 存儲(chǔ)引擎,，建議為2

query_cache_min_res_unit 是在4.1版本以后引入的,，它指定分配緩沖區(qū)空間的最小單位，缺省為4K,。檢查狀態(tài)值Qcache_free_blocks,，如果該值非常大，則表明緩沖區(qū)中碎片很多,，這就表明查詢結(jié)果都比較小,，此時(shí)需要減小 query_cache_min_res_unit。

query_cache_size 為了存儲(chǔ)老的查詢結(jié)果而分配的內(nèi)存數(shù)量 (以字節(jié)指定) ,。如果設(shè)置它為 0 ,，查詢緩沖將被禁止(缺省值為 0 )。 根據(jù) 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進(jìn)行調(diào)整,，一般不建議太大,，256MB可能已經(jīng)差不多了，大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大

mysql> SHOW STATUS LIKE 'qcache%';

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

Qcache_free_blocks 5216

Qcache_free_memory 14640664

Qcache_hits 2581646882

Qcache_inserts 360210964

Qcache_lowmem_prunes 281680433

Qcache_not_cached 79740667

Qcache_queries_in_cache 16927

Qcache_total_blocks 47042

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

Qcache_free_blocks 緩存中相鄰內(nèi)存塊的個(gè)數(shù),。數(shù)目大說(shuō)明可能有碎片,。FLUSH QUERY CACHE 會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊,。

Qcache_free_memory 緩存中的空閑內(nèi)存,。

Qcache_hits 每次查詢?cè)诰彺嬷忻袝r(shí)就增大。

Qcache_inserts 每次插入一個(gè)查詢時(shí)就增大,。 未命中然后插入,。

Qcache_lowmem_prunes 的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況,同時(shí)Qcache_hits的值非常大,，則表明查詢緩沖使用非常頻繁,，此時(shí)需要增加緩沖大小，Qcache_hits的值不大,，則表明你的查詢重復(fù)率很低,，這種情況下使用查詢緩沖反而會(huì)影響效率，那么可以考慮不用查詢緩沖,。這個(gè)數(shù)字最好長(zhǎng)時(shí)間來(lái)看；如果這個(gè)數(shù)字在不斷增長(zhǎng),，就表示可能碎片非常嚴(yán)重,，或者內(nèi)存很少。（上面的 free_blocks 和 free_memory 可以告訴您屬于哪種情況）,。

Qcache_not_cached 不適合進(jìn)行緩存的查詢的數(shù)量,，通常是由于這些查詢不是 SELECT 語(yǔ)句,。

Qcache_queries_in_cache 當(dāng)前緩存的查詢（和響應(yīng)）的數(shù)量。

Qcache_total_blocks 緩存中塊的數(shù)量,。

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

查詢命中率=Qcache_hits -Qcache_inserts /Qcache_hits

查詢插入率=Qcache_inserts / Com_select;

未插入率 = Qcache_not_cached / Com_select;

很多 LAMP 應(yīng)用程序都嚴(yán)重依賴于數(shù)據(jù)庫(kù),，但卻會(huì)反復(fù)執(zhí)行相同的查詢。每次執(zhí)行查詢時(shí),，數(shù)據(jù)庫(kù)都必須要執(zhí)行相同的工作 —— 對(duì)查詢進(jìn)行分析,，確定如何執(zhí)行查詢，從磁盤(pán)中加載信息,，然后將結(jié)果返回給客戶機(jī),。MySQL 有一個(gè)特性稱為查詢緩存，查詢緩存會(huì)存儲(chǔ)一個(gè) SELECT 查詢的文本與被傳送到客戶端的相應(yīng)結(jié)果,。如果之后接收到一個(gè)同樣的查詢,，服務(wù)器將從查詢緩存中檢索結(jié)果，而不是再次分析和執(zhí)行這個(gè)同樣的查詢,。在很多情況下,，這會(huì)極大地提高性能。不過(guò),，問(wèn)題是查詢緩存在默認(rèn)情況下是禁用的,。

通常，間隔幾秒顯示這些變量就可以看出區(qū)別,，這可以幫助確定緩存是否正在有效地使用,。運(yùn)行 FLUSH STATUS 可以重置一些計(jì)數(shù)器，如果服務(wù)器已經(jīng)運(yùn)行了一段時(shí)間,，這會(huì)非常有幫助,。

使用非常大的查詢緩存，期望可以緩存所有東西,，這種想法非常誘人,。但如果表有變動(dòng)時(shí)，首先要把Query_cache和該表相關(guān)的語(yǔ)句全部置為失效，然后在寫(xiě)入更新,。

那么如果Query_cache非常大，該表的查詢結(jié)構(gòu)又比較多,，查詢語(yǔ)句失效也慢,，一個(gè)更新或是Insert就會(huì)很慢，這樣看到的就是Update或是Insert怎么這么慢了,。

所以在數(shù)據(jù)庫(kù)寫(xiě)入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過(guò)大,。而且在高并發(fā),，寫(xiě)入量大的系統(tǒng)，建系把該功能禁掉,。

作為一條規(guī)則,，如果 FLUSH QUERY CACHE 占用了很長(zhǎng)時(shí)間，那就說(shuō)明緩存太大了,。

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

wait_timeout 28800

服務(wù)器關(guān)閉非交互連接之前等待活動(dòng)的秒數(shù)。

在線程啟動(dòng)時(shí),，根據(jù)全局wait_timeout值或全局interactive_timeout值初始化會(huì)話wait_timeout值,，取決于客戶端類型

connect_timeout 10

mysqld服務(wù)器用Bad handshake響應(yīng)前等待連接包的秒數(shù),。

interactive_timeout 28800

服務(wù)器關(guān)閉交互式連接前等待活動(dòng)的秒數(shù),。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項(xiàng)的客戶端。

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

mysql> SHOW STATUS LIKE "com_select";

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

Com_select 318243

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

mysql> SHOW STATUS LIKE "handler_read_rnd_next";

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

Handler_read_rnd_next 165959471

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

MySQL 也會(huì)分配一些內(nèi)存來(lái)讀取表,。理想情況下,，索引提供了足夠多的信息，可以只讀入所需要的行,，但是有時(shí)候查詢（設(shè)計(jì)不佳或數(shù)據(jù)本性使然）需要讀取表中大量數(shù)據(jù),。要理解這種行為,，需要知道運(yùn)行了多少個(gè) SELECT 語(yǔ)句，以及需要讀取表中的下一行數(shù)據(jù)的次數(shù)（而不是通過(guò)索引直接訪問(wèn)）,。

Handler_read_rnd_next / Com_select 得出了表掃描比率 —— 在本例中是 521:1,。如果該值超過(guò) 4000,，就應(yīng)該查看 read_buffer_size,，例如 read_buffer_size = 4M,。如果這個(gè)數(shù)字超過(guò)了 8M,，就應(yīng)該與開(kāi)發(fā)人員討論一下對(duì)這些查詢進(jìn)行調(diào)優(yōu)了,！

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

mysql> SHOW STATUS LIKE 'created_tmp%';

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

Created_tmp_disk_tables 30660

Created_tmp_files 2

Created_tmp_tables 32912

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

臨時(shí)表可以在更高級(jí)的查詢中使用，其中數(shù)據(jù)在進(jìn)一步進(jìn)行處理（例如 GROUP BY 字句）之前,，都必須先保存到臨時(shí)表中,；理想情況下,，在內(nèi)存中創(chuàng)建臨時(shí)表,。但是如果臨時(shí)表變得太大,，就需要寫(xiě)入磁盤(pán)中。

每次使用臨時(shí)表都會(huì)增大 Created_tmp_tables,；基于磁盤(pán)的表也會(huì)增大 Created_tmp_disk_tables,。對(duì)于這個(gè)比率,，并沒(méi)有什么嚴(yán)格的規(guī)則，因?yàn)檫@依賴于所涉及的查詢,。長(zhǎng)時(shí)間觀察 Created_tmp_disk_tables 會(huì)顯示所創(chuàng)建的磁盤(pán)表的比率,，您可以確定設(shè)置的效率。 tmp_table_size 和 max_heap_table_size 都可以控制臨時(shí)表的最大大小,，因此請(qǐng)確保在 my.cnf 中對(duì)這兩個(gè)值都進(jìn)行了設(shè)置,。

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

日志相關(guān)

log-bin=mysql-bin

binlog_format=mixed

mysql-bin.000001、mysql-bin.000002等文件是數(shù)據(jù)庫(kù)的操作日志,，例如UPDATE一個(gè)表,，或者DELETE一些數(shù)據(jù),，即使該語(yǔ)句沒(méi)有匹配的數(shù)據(jù)，這個(gè)命令也會(huì)存儲(chǔ)到日志文件中,，還包括每個(gè)語(yǔ)句執(zhí)行的時(shí)間,，也會(huì)記錄進(jìn)去的。

=================================================================================================

InnoDB

innodb_buffer_pool_size

　　innodb_buffer_pool_size 定義了 InnoDB 存儲(chǔ)引擎的表數(shù)據(jù)和索引數(shù)據(jù)的最大內(nèi)存緩沖區(qū)大小,。和 MyISAM 存儲(chǔ)引擎不同,， MyISAM 的 key_buffer_size 只能緩存索引鍵，而 innodb_buffer_pool_size 卻可以緩存數(shù)據(jù)塊和索引鍵,。適當(dāng)?shù)脑黾舆@個(gè)參數(shù)的大小,，可以有效的減少 InnoDB 類型的表的磁盤(pán) I/O 。為Innodb加速優(yōu)化首要參數(shù),。默認(rèn)值8M

這個(gè)參數(shù)不能動(dòng)態(tài)更改,，所以分配需多考慮。分配過(guò)大,，會(huì)使Swap占用過(guò)多，致使Mysql的查詢特慢,。如果你的數(shù)據(jù)量不大,，并且不會(huì)暴增，那么可分配是你的數(shù)據(jù)大?。保埃プ笥易鰹檫@個(gè)參數(shù)的值,。例如：數(shù)據(jù)大小為50M,那么給這個(gè)值分配innodb_buffer_pool_size＝64M

mysql>show variables like 'innodb%';

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

innodb_adaptive_hash_index ON

innodb_additional_mem_pool_size 2097152

innodb_autoextend_increment 8

innodb_autoinc_lock_mode 1

innodb_buffer_pool_size 67108864

innodb_checksums ON

innodb_commit_concurrency 0

innodb_concurrency_tickets 500

innodb_data_file_path ibdata1:10M:autoextend

innodb_data_home_dir /var/lib/mysql

innodb_doublewrite ON

innodb_fast_shutdown 1

innodb_file_io_threads 4

innodb_file_per_table OFF

innodb_flush_log_at_trx_commit 1

innodb_flush_method

innodb_force_recovery 0

innodb_lock_wait_timeout 50

innodb_locks_unsafe_for_binlog OFF

innodb_log_buffer_size 8388608

innodb_log_file_size 16777216

innodb_log_files_in_group 2

innodb_log_group_home_dir /var/lib/mysql

innodb_max_dirty_pages_pct 90

innodb_max_purge_lag 0

innodb_mirrored_log_groups 1

innodb_open_files 300

innodb_rollback_on_timeout OFF

innodb_stats_method nulls_equal

innodb_stats_on_metadata ON

innodb_support_xa ON

innodb_sync_spin_loops 20

innodb_table_locks ON

innodb_thread_concurrency 8

innodb_thread_sleep_delay 10000

innodb_use_legacy_cardinality_algorithm ON

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

mysql>show status like 'innodb%';

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

Innodb_buffer_pool_pages_data 2559 分配出去， 正在被使用頁(yè)的數(shù)量,，包括臟頁(yè),。單位是page

Innodb_buffer_pool_pages_dirty 0 臟頁(yè)但沒(méi)有被flush除去的頁(yè)面數(shù)。單位是page

Innodb_buffer_pool_pages_flushed 795 已經(jīng)flush的頁(yè)面數(shù),。單位是page

Innodb_buffer_pool_pages_free 1473 當(dāng)前空閑頁(yè)面數(shù),。單位是page

Innodb_buffer_pool_pages_misc 64 緩存池中當(dāng)前已經(jīng)被用作管理用途或hash index而不能用作為普通數(shù)據(jù)頁(yè)的數(shù)目。單位是page

Innodb_buffer_pool_pages_total 4096 緩沖區(qū)總共的頁(yè)面數(shù),。單位是page

Innodb_buffer_pool_read_ahead_rnd 8 隨機(jī)預(yù)讀的次數(shù)

Innodb_buffer_pool_read_ahead_seq 1 順序預(yù)讀的次數(shù)

Innodb_buffer_pool_read_requests 1725871 從緩沖池中讀取頁(yè)的次數(shù)

Innodb_buffer_pool_reads 2108 從磁盤(pán)讀取頁(yè)的次數(shù),。緩沖池里面沒(méi)有， 就會(huì)從磁盤(pán)讀取

Innodb_buffer_pool_wait_free 0 緩沖池等待空閑頁(yè)的次數(shù),，當(dāng)需要空閑塊而系統(tǒng)中沒(méi)有時(shí),，就會(huì)等待空閑頁(yè)面

Innodb_buffer_pool_write_requests 2296 緩沖池總共發(fā)出的寫(xiě)請(qǐng)求次數(shù)

Innodb_data_fsyncs 695 總共完成的fsync次數(shù)

Innodb_data_pending_fsyncs 0 innodb當(dāng)前等待的fsync次數(shù)

Innodb_data_pending_reads 0 innodb當(dāng)前等待的讀的次數(shù)

Innodb_data_pending_writes 0 innodb當(dāng)前等待的寫(xiě)的次數(shù)

Innodb_data_read 44044288 總共讀入的字節(jié)數(shù)

Innodb_data_reads 2191 innodb完成的讀的次數(shù)

Innodb_data_writes 1296 innodb完成的寫(xiě)的次數(shù)

Innodb_data_written 26440192 總共寫(xiě)出的字節(jié)數(shù)

Innodb_dblwr_pages_written 795

Innodb_dblwr_writes 90

Innodb_log_waits 0 因日志緩存太小而必須等待其被寫(xiě)入所造成的等待數(shù)。單位是次,。

Innodb_log_write_requests 263

Innodb_log_writes 410

Innodb_os_log_fsyncs 500

Innodb_os_log_pending_fsyncs 0

Innodb_os_log_pending_writes 0

Innodb_os_log_written 343552

Innodb_page_size 16384

Innodb_pages_created 4

Innodb_pages_read 2555

Innodb_pages_written 795

Innodb_row_lock_current_waits 0

Innodb_row_lock_time 0

Innodb_row_lock_time_avg 0

Innodb_row_lock_time_max 0

Innodb_row_lock_waits 0

Innodb_rows_deleted 0

Innodb_rows_inserted 352

Innodb_rows_read 818617

Innodb_rows_updated 88

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

命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)

innodb_buffer_pool_size: 如果不使用InnoDB存儲(chǔ)引擎,，可以不用調(diào)整這個(gè)參數(shù)，如果需要使用,，在內(nèi)存允許的情況下,，盡可能將所有的InnoDB數(shù)據(jù)文件存放如內(nèi)存中,，同樣將但來(lái)說(shuō)也是“越大越好”

innodb_additional_pool_size

這個(gè)值不用分配太大，系統(tǒng)可以自動(dòng)調(diào),。不用設(shè)置太高,。通常比較大數(shù)據(jù)設(shè)置16Ｍ夠用了，如果表比較多,，可以適當(dāng)?shù)脑龃?。如果這個(gè)值自動(dòng)增加，會(huì)在error log有中顯示的,。20M足夠了,。

innodb_log_file_size

作用：指定日志的大小

分配原則：幾個(gè)日志成員大小加起來(lái)差不多和你的innodb_buffer_pool_size相等。在高寫(xiě)入負(fù)載尤其是大數(shù)據(jù)集的情況下很重要,。這個(gè)值越大則性能相對(duì)越高,，但是要注意到可能會(huì)增加恢復(fù)時(shí)間。

說(shuō)明：這個(gè)值分配的大小和數(shù)據(jù)庫(kù)的寫(xiě)入速度,，事務(wù)大小,，異常重啟后的恢復(fù)有很大的關(guān)系。

innodb_log_buffer_size：

作用：事務(wù)在內(nèi)存中的緩沖,。

分配原則：控制在2-8M.這個(gè)值不用太多的,。他里面的內(nèi)存一般一秒鐘寫(xiě)到磁盤(pán)一次。具體寫(xiě)入方式和你的事務(wù)提交方式有關(guān),。一般最大指定為3M比較合適,。

參考：Innodb_os_log_written(show global status 可以拿到)

如果這個(gè)值增長(zhǎng)過(guò)快，可以適當(dāng)?shù)脑黾觟nnodb_log_buffer_size

另外如果你需要處理大理的text,，或是blog字段,，可以考慮增加這個(gè)參數(shù)的值。

默認(rèn)的設(shè)置在中等強(qiáng)度寫(xiě)入負(fù)載以及較短事務(wù)的情況下,，服務(wù)器性能還可以,。如果存在更新操作峰值或者負(fù)載較大，就應(yīng)該考慮加大它的值了,。如果它的值設(shè)置太高了,，可能會(huì)浪費(fèi)內(nèi)存 -- 它每秒都會(huì)刷新一次，因此無(wú)需設(shè)置超過(guò)1秒所需的內(nèi)存空間,。通常 8-16MB 就足夠了,。越小的系統(tǒng)它的值越小。

innodb_flush_logs_at_trx_commit

作用：控制事務(wù)的提交方式

分配原則：這個(gè)參數(shù)只有3個(gè)值,，0,，1，2請(qǐng)確認(rèn)一下自已能接受的級(jí)別,。默認(rèn)為1,，主庫(kù)請(qǐng)不要更改了,。性能更高的可以設(shè)置為0或是2，但會(huì)丟失一秒鐘的事務(wù),。

值為1時(shí)：innodb 的事務(wù)LOG在每次提交后寫(xiě)入日志文件,，并對(duì)日志做刷新到磁盤(pán)。這個(gè)可以做到不丟任何一個(gè)事務(wù),。

值為2事,，也就是不把日志刷新到磁盤(pán)上，而只刷新到操作系統(tǒng)的緩存上,。日志仍然會(huì)每秒刷新到磁盤(pán)中去,，因此通常不會(huì)丟失每秒1-2次更新的消耗。如果設(shè)置為 0 就快很多了,，不過(guò)也相對(duì)不安全了 -- MySQL服務(wù)器崩潰時(shí)就會(huì)丟失一些事務(wù),。設(shè)置為 2 只會(huì)丟失刷新到操作系統(tǒng)緩存的那部分事務(wù)。

innodb_file_per_table

作用：使每個(gè)Innodb的表,，有自已獨(dú)立的表空間,。如刪除文件后可以回收那部分空間。

分配原則：只有使用不使用,。但DB還需要有一個(gè)公共的表空間,。

InnoDB 默認(rèn)會(huì)將所有的數(shù)據(jù)庫(kù)InnoDB引擎的表數(shù)據(jù)存儲(chǔ)在一個(gè)共享空間中：ibdata1，增刪數(shù)據(jù)庫(kù)的時(shí)候,，ibdata1文件不會(huì)自動(dòng)收縮，單個(gè)數(shù)據(jù)庫(kù)的備份也將成為問(wèn)題,。通常只能將數(shù)據(jù)使用mysqldump 導(dǎo)出,，然后再導(dǎo)入解決這個(gè)問(wèn)題。

查看是否開(kāi)啟：

mysql> show variables like ‘%per_table%’;

開(kāi)啟

innodb_file_per_table=1

請(qǐng)適當(dāng)?shù)脑黾觟nnodb_open_files

innodb_open_files

作用：限制Innodb能打開(kāi)的表的數(shù)據(jù),。

分配原則：如果庫(kù)里的表特別多的情況,，請(qǐng)?jiān)黾舆@個(gè)。這個(gè)值默認(rèn)是300,。這個(gè)值必須超過(guò)你配置的innodb_data_file_path個(gè)數(shù),。

請(qǐng)適當(dāng)?shù)脑黾觮able_cache

innodb_flush_method

作用：Innodb和系統(tǒng)打交道的一個(gè)IO模型

分配原則：Windows不用設(shè)置。UNIX可以設(shè)置：fdatasync (默認(rèn)設(shè)置),，O_DIRECT,，和O_DSYNC

O_DIRECT跳過(guò)了操作系統(tǒng)的文件系統(tǒng)Disk Cache，讓MySQL直接讀寫(xiě)磁盤(pán),。 有數(shù)據(jù)表明,，如果是大量隨機(jī)寫(xiě)入操作，O_DIRECT會(huì)提升效率,。但是順序?qū)懭牒妥x取效率都會(huì)降低,。

innodb_max_dirty_pages_pct

作用：控制Innodb的臟頁(yè)在緩沖中在那個(gè)百分比之下,，值在范圍1-100,默認(rèn)為90.

O_DIRECT的flush_method更適合于操作系統(tǒng)內(nèi)存有限的情況下（可以避免不必要的對(duì)交換空間的讀寫(xiě)操作），否則,，它會(huì)由于禁用了os的緩沖降低對(duì)數(shù)據(jù)的讀寫(xiě)操作的效能,。

使用memlock可以避免MySQL內(nèi)存進(jìn)入swap