如何監(jiān)控MySQL

首先介紹下 pt-stalk，它是 Percona-Toolkit 工具包中的一個(gè)工具，說(shuō)起 PT 工具包大家都不陌生，平時(shí)常用的 pt-query-digest、 pt-online-schema-change 等工具都是出自于這個(gè)工具包，這里就不多介紹了。

成都創(chuàng)新互聯(lián)專注于清河網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠(chéng)為您提供清河營(yíng)銷型網(wǎng)站建設(shè)，清河網(wǎng)站制作、清河網(wǎng)頁(yè)設(shè)計(jì)、清河網(wǎng)站官網(wǎng)定制、微信小程序服務(wù)，打造清河網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供清河網(wǎng)站排名全網(wǎng)營(yíng)銷落地服務(wù)。

pt-stalk 的主要功能是在出現(xiàn)問(wèn)題時(shí)收集 OS 及 MySQL 的診斷信息，這其中包括：

1. OS 層面的 CPU、IO、內(nèi)存、磁盤(pán)、網(wǎng)絡(luò)等信息；

2. MySQL 層面的行鎖等待、會(huì)話連接、主從復(fù)制，狀態(tài)參數(shù)等信息。

而且 pt-stalk 是一個(gè) Shell腳本，對(duì)于我這種看不懂 perl 的人來(lái)說(shuō)比較友好，腳本里面的監(jiān)控邏輯與監(jiān)控命令也可以拿來(lái)參考，用于構(gòu)建自己的監(jiān)控體系。

三、使用

接著我們來(lái)看下如何使用這個(gè)工具。

pt-stalk 通常以后臺(tái)服務(wù)形式監(jiān)控 MySQL 并等待觸發(fā)條件，當(dāng)觸發(fā)條件時(shí)收集相關(guān)診斷數(shù)據(jù)。

觸發(fā)條件相關(guān)的參數(shù)有以下幾個(gè)：

function：

°?默認(rèn)為 status，代表監(jiān)控 SHOW GLOBAL STATUS 的輸出；

°?也可以設(shè)置為 processlist，代表監(jiān)控 show processlist 的輸出；

variable：

°?默認(rèn)為 Threads_running，代表 監(jiān)控參數(shù)，根據(jù)上述監(jiān)控輸出指定具體的監(jiān)控項(xiàng)；

threshold：

°?默認(rèn)為 25，代表 監(jiān)控閾值，監(jiān)控參數(shù)超過(guò)閾值，則滿足觸發(fā)條件；

°?監(jiān)控參數(shù)的值非數(shù)字時(shí)，需要配合 match 參數(shù)一起使用，如 processlist 的 state 列；

cycles：

°?默認(rèn)為 5，表示連續(xù)觀察到五次滿足觸發(fā)條件時(shí)，才觸發(fā)收集；

連接參數(shù)：host、password、port、socket。

其他一些重要參數(shù)：

iterations：該參數(shù)指定 pt-stalk 在觸發(fā)收集幾次后退出，默認(rèn)會(huì)一直運(yùn)行。

run-time：觸發(fā)收集后，該參數(shù)指定收集多長(zhǎng)時(shí)間的數(shù)據(jù)，默認(rèn) 30 秒。

sleep：該參數(shù)指定在觸發(fā)收集后，sleep 多久后繼續(xù)監(jiān)控，默認(rèn) 300 秒。

interval：指定狀態(tài)參數(shù)的檢查頻率，判斷是否需要觸發(fā)收集，默認(rèn) 1 秒。

dest：監(jiān)控?cái)?shù)據(jù)存放路徑，默認(rèn)為 /var/lib/pt-stalk。

retention-time ：監(jiān)控?cái)?shù)據(jù)保留時(shí)長(zhǎng)，默認(rèn) 30 天。

daemonize：以后臺(tái)服務(wù)運(yùn)行，默認(rèn)不開(kāi)啟。

log：后臺(tái)運(yùn)行日志，默認(rèn)為 /var/log/pt-stalk.log。

collect：觸發(fā)發(fā)生時(shí)收集診斷數(shù)據(jù)，默認(rèn)開(kāi)啟。

°?collect-gdb：收集 GDB 堆棧跟蹤，需要 gdb 工具。

°?collect-strace：收集跟蹤數(shù)據(jù)，需要 strace 工具。

°?collect-tcpdump：收集 tcpdump 數(shù)據(jù)，需要 tcpdump 工具。

Linux中ps -ef |grep mysql命令里面出現(xiàn)大量MySQL進(jìn)程,是什么原因

ps -ef 的意思顯示系統(tǒng)執(zhí)行進(jìn)程

-e 顯示所有終端機(jī)下執(zhí)行的進(jìn)程

-f 指的是顯示UID,PPIP,C與STIME欄位

grep 是搜索過(guò)濾

ps -ef | grep mysql的含義就是顯示系統(tǒng)執(zhí)行進(jìn)程，從顯示的全部進(jìn)程信息中搜索包含mysql字符串的信息，并顯示出來(lái)

如何查mysql死鎖進(jìn)程

查詢死鎖進(jìn)程

采用如下存儲(chǔ)過(guò)程來(lái)查詢數(shù)據(jù)中當(dāng)前造成死鎖的進(jìn)程。

drop procedure sp_who_lock

go

CREATE procedure sp_who_lock

as

begin

declare @spid int

declare @blk int

declare @count int

declare @index int

declare @lock tinyint

set @lock=0

create table #temp_who_lock

(

id int identity(1,1),

spid int,

blk int

)

if @@error0 return @@error

insert into #temp_who_lock(spid,blk)

select 0 ,blocked

from (select * from master..sysprocesses where blocked0)a

where not exists(select * from master..sysprocesses where a.blocked =spid and blocked0)

union select spid,blocked from master..sysprocesses where blocked0

if @@error0 return @@error

select @count=count(*),@index=1 from #temp_who_lock

if @@error0 return @@error

if @count=0

begin

select '沒(méi)有阻塞和死鎖信息'

return 0

end

while @indexA href="mailto:=@count"=@count

begin

if exists(select 1 from #temp_who_lock a where id@index and exists(select 1 from #temp_who_lock where idA href="mailto:=@index"=@index and a.blk=spid))

begin

set @lock=1

select @spid=spid,@blk=blk from #temp_who_lock where id=@index

select '引起數(shù)據(jù)庫(kù)死鎖的是: '+ CAST(@spid AS VARCHAR(10)) + '進(jìn)程號(hào),其執(zhí)行的SQL語(yǔ)法如下'

select @spid, @blk

dbcc inputbuffer(@spid)

dbcc inputbuffer(@blk)

end

set @index=@index+1

end

if @lock=0

begin

set @index=1

while @indexA href="mailto:=@count"=@count

begin

select @spid=spid,@blk=blk from #temp_who_lock where id=@index

if @spid=0

select '引起阻塞的是:'+cast(@blk as varchar(10))+ '進(jìn)程號(hào),其執(zhí)行的SQL語(yǔ)法如下'

else

select '進(jìn)程號(hào)SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進(jìn)程號(hào)SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其當(dāng)前進(jìn)程執(zhí)行的SQL語(yǔ)法如下'

dbcc inputbuffer(@spid)

dbcc inputbuffer(@blk)

set @index=@index+1

end

end

drop table #temp_who_lock

return 0

end

GO

--執(zhí)行該存儲(chǔ)過(guò)程

exec sp_who_lock

補(bǔ)充：

一、產(chǎn)生死鎖的原因

在SQL Server中，阻塞更多的是產(chǎn)生于實(shí)現(xiàn)并發(fā)之間的隔離性。為了使得并發(fā)連接所做的操作之間的影響到達(dá)某一期望值而對(duì)資源人為的進(jìn)行加鎖(鎖本質(zhì)其實(shí)可以看作是一個(gè)標(biāo)志位)。當(dāng)一個(gè)連接對(duì)特定的資源進(jìn)行操作時(shí)，另一個(gè)連接同時(shí)對(duì)同樣的資源進(jìn)行操作就會(huì)被阻塞，阻塞是死鎖產(chǎn)生的必要條件。

二、如何避免死鎖

1.使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過(guò)程，及早提交或回滾事務(wù)；

2.設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3分鐘-10分種；超過(guò)時(shí)間，自動(dòng)放棄本次操作，避免進(jìn)程懸掛；

3.優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；

4.對(duì)所有的腳本和SP都要仔細(xì)測(cè)試，在正是版本之前；

5.所有的SP都要有錯(cuò)誤處理（通過(guò)@error）；

6.一般不要修改SQL SERVER事務(wù)的默認(rèn)級(jí)別。不推薦強(qiáng)行加鎖。

三、處理死鎖

1、最簡(jiǎn)單的處理死鎖的方法就是重啟服務(wù)。

2、根據(jù)指定的死鎖進(jìn)程ID進(jìn)行處理

根據(jù)第二步查詢到的死鎖進(jìn)行，大致分析造成死鎖的原因，并通過(guò)如下語(yǔ)句釋放該死鎖進(jìn)程

kill pid --pid為查詢出來(lái)的死鎖進(jìn)程號(hào)

3、通過(guò)存儲(chǔ)過(guò)程殺掉某個(gè)庫(kù)下面的所有死鎖進(jìn)程和鎖

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_killspid]

GO

create proc sp_killspid

@dbname varchar(200) --要關(guān)閉進(jìn)程的數(shù)據(jù)庫(kù)名

as

declare @sql nvarchar(500)

declare @spid nvarchar(20)

declare #tb cursor for

select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)

open #tb

fetch next from #tb into @spid

while @@fetch_status=0

begin

exec('kill '+@spid)

fetch next from #tb into @spid

end

close #tb

deallocate #tb

go

--使用方法，“db_name”為處理的數(shù)據(jù)庫(kù)名稱

exec sp_killspid 'db_name'

怎樣查看 mysql buffer pool hit

系統(tǒng)mysql的進(jìn)程數(shù)

查看 mysql buffer pool hit

ps -ef | grep "mysql" | grep -v "grep" | wc –l

2.Slave_running

mysql show status like 'Slave_running';

如果系統(tǒng)有一個(gè)從復(fù)***務(wù)器，這個(gè)值指明了從服務(wù)器的健康度

3.Threads_connected

mysql show status like 'Threads_connected';

當(dāng)前客戶端已連接的數(shù)量。這個(gè)值會(huì)少于預(yù)設(shè)的值，但你也能監(jiān)視到這個(gè)值較大，這可保證客戶端是處在活躍狀態(tài)。

4.Threads_running

mysql show status like 'Threads_running';

如果數(shù)據(jù)庫(kù)超負(fù)荷了，你將會(huì)得到一個(gè)正在（查詢的語(yǔ)句持續(xù)）增長(zhǎng)的數(shù)值。這個(gè)值也可以少于預(yù)先設(shè)定的值。這個(gè)值在很短的時(shí)間內(nèi)超過(guò)限定值是沒(méi)問(wèn)題的。當(dāng)Threads_running值超過(guò)預(yù)設(shè)值時(shí)并且該值在5秒內(nèi)沒(méi)有回落時(shí)， 要同時(shí)監(jiān)視其他的一些值。

5.Aborted_clients

mysql show status like 'Aborted_clients';

客戶端被異常中斷的數(shù)值，即連接到mysql服務(wù)器的客戶端沒(méi)有正常地?cái)嚅_(kāi)或關(guān)閉。對(duì)于一些應(yīng)用程序是沒(méi)有影響的，但對(duì)于另一些應(yīng)用程序可能你要跟蹤該值，因?yàn)楫惓Ｖ袛噙B接可能表明了一些應(yīng)用程序有問(wèn)題。

6.Questions

mysql show status like 'Questions';

每秒鐘獲得的查詢數(shù)量，也可以是全部查詢的數(shù)量，根據(jù)你輸入不同的命令會(huì)得到你想要的不同的值。

7.Handler_*

mysql show status like 'Handler_%';

如果你想監(jiān)視底層（low-level）數(shù)據(jù)庫(kù)負(fù)載，這些值是值得去跟蹤的。

如果Handler_read_rnd_next值相對(duì)于你認(rèn)為是正常值相差懸殊，可能會(huì)告訴你需要優(yōu)化或索引出問(wèn)題了。Handler_rollback表明事務(wù)被回滾的查詢數(shù)量。你可能想調(diào)查一下原因。

8.Opened_tables

mysql show status like 'Opened_tables';

表緩存沒(méi)有命中的數(shù)量。如果該值很大，你可能需要增加table_cache的數(shù)值。典型地，你可能想要這個(gè)值每秒打開(kāi)的表數(shù)量少于1或2。

9.Select_full_join

mysql show status like 'Select_full_join';

沒(méi)有主鍵（key）聯(lián)合（Join）的執(zhí)行。該值可能是零。這是捕獲開(kāi)發(fā)錯(cuò)誤的好方法，因?yàn)橐恍┻@樣的查詢可能降低系統(tǒng)的性能。

10.Select_scan

mysql show status like 'Select_scan';

執(zhí)行全表搜索查詢的數(shù)量。在某些情況下是沒(méi)問(wèn)題的，但占總查詢數(shù)量該比值應(yīng)該是常量（即Select_scan/總查詢數(shù)量商應(yīng)該是常數(shù)）。如果你發(fā)現(xiàn)該值持續(xù)增長(zhǎng)，說(shuō)明需要優(yōu)化，缺乏必要的索引或其他問(wèn)題。

11.Slow_queries

mysql show status like 'Slow_queries';

超過(guò)該值（--long-query-time）的查詢數(shù)量，或沒(méi)有使用索引查詢數(shù)量。對(duì)于全部查詢會(huì)有小的沖突。如果該值增長(zhǎng)，表明系統(tǒng)有性能問(wèn)題。

12.Threads_created

mysql show status like 'Threads_created';

該值應(yīng)該是低的。較高的值可能意味著你需要增加thread_cache的數(shù)值，或你遇到了持續(xù)增加的連接，表明了潛在的問(wèn)題。

13.客戶端連接進(jìn)程數(shù)

shell mysqladmin processlist

mysql show processlist;

你可以通過(guò)使用其他的統(tǒng)計(jì)信息得到已連接線程數(shù)量和正在運(yùn)行線程的數(shù)量，檢查正在運(yùn)行的查詢花了多長(zhǎng)時(shí)間是一個(gè)好主意。如果有一些長(zhǎng)時(shí)間的查詢，管理員可以被通知。你可能也想了解多少個(gè)查詢是在"Locked"的狀態(tài)—---該值作為正在運(yùn)行的查詢不被計(jì)算在內(nèi)而是作為非活躍的。一個(gè)用戶正在等待一個(gè)數(shù)據(jù)庫(kù)響應(yīng)。

14.innodb狀態(tài)

mysql show engine innodb status\G;

該語(yǔ)句產(chǎn)生很多信息，從中你可以得到你感興趣的。首先你要檢查的就是“從最近的XX秒計(jì)算出來(lái)的每秒的平均負(fù)載”。

(1)Pending normal aio reads:?該值是innodb io請(qǐng)求查詢的大小（size）。如果該值大到超過(guò)了10—20，你可能有一些瓶頸。

(2)reads/s, avg bytes/read, writes/s, fsyncs/s:這些值是io統(tǒng)計(jì)。對(duì)于reads/writes大值意味著io子系統(tǒng)正在被裝載。適當(dāng)?shù)闹等Q于你系統(tǒng)的配置。

(3)Buffer pool hit rate:這個(gè)命中率非常依賴于你的應(yīng)用程序。當(dāng)你覺(jué)得有問(wèn)題時(shí)請(qǐng)檢查你的命中率

(4)inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底層操作。你可以用這些值檢查你的負(fù)載情況查看是否是期待的數(shù)值范圍。