標籤: tuning

FreeBSD上mysql的效能追蹤

最近因為有台主機的 mysql cpu 異常變高許多, 所以要來進行查詢問題所在.

因為 mysql 沒有像 mssql 的 sql profiler 那麼方便的工具, 所以利用 mysql 本身的 log 來進行, 也就是 slow query log 這個功能, 步驟如下:

1. 先將 /etc/my.cnf 中的 [mysqld] 內多加入下面資料:

log-slow-queries = /var/log/slow-query.log #slow query記錄檔的位置
long_query_time = 2 #query執行超過2秒時才記錄

2. 重啟 mysql 服務, 指令如下:

/usr/local/etc/rc.d/mysql-server restart

3. 執行一陣子後, 就可以看看該 log 檔內的 query , 接下來就是針對這些 query 來調整效能

以上是在 FreeBSD 環境下的作法. (其他環境其實也類似)

其實執行時間長不一定是效率不好, 不過若是常常發生的查詢是需要長時間的, 就有改善的必要, 簡單地說, 就是若一個查詢需要 5秒, 但一天跑不到 10次, 那根本不用管他, 不過若一個查詢需要 0.02 秒, 但一天要用到數萬次, 即使從 0.02 改善到 0.015 就會有很明顯的效能改善, 所以要看發生的頻率及所花費執行的時間, 平衡來看.

另外, 若是該 log 沒有產出, 記得權限要給對, 因為是 mysql service account 去執行寫入的動作, 就算沒有任何 log 也會有 mysql 啟動的資訊, 不會沒有任何產出的 log.

參考資料:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://blog.lansea-chu.com/index.php/archives/238
http://homeserver.com.tw/mysql/mysql%E7%9A%84%E6%9F%A5%E8%A9%A2%E6%99%82%E9%96%93log-slow-queries/
http://ezkuan.blogspot.com/2010/05/freebsd-mysql.html

SQL Server的遺失索引統計

先說好, 這篇必須是 SQL Server 2005 以上的用戶才能用到的, 因為用到的資料是 DMV 的系統 view, 也就是 Dynamic Management Views.

這裡會用到的 DMVs 是用來查詢所謂遺失的索引, 白話一點, 就是應該要建立的索引, 而沒有建立的索引, 稱之為所謂的”遺失的索引”. 資料庫在查詢時, 若是發現有這樣的狀況, 會記錄下來, 在 DMVs 內的這幾個表:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

在這些表內可以利用這篇文章 (揭露隱藏的資料以最佳化應用程式效能)的一個計算方式(當然也可以再調整), 來將影響較為嚴重而又沒有加上索引的 table 找出來, 查詢如下:

SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Columns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

這樣表列出來的資料簡單說明一下:
第一個Total Cost為總成本, 該作者是使用了一個計算的方式, 並以此為排序條件, 找出總成本最高的資料, 第二個 avg_user_impact 為對使用者的影響, 後面三個最重要了, [EqualityUsage]是指等於的條件, 例如 newsid=235 這種條件, 而 [InequalityUsage] 就是指不等的使用, 例如: newsid < 235, 而 [Include Columns]是指查詢時的涵蓋欄位, 也就是指 select newsid, newstitle, newsdesc …. from 前面的欄位.

再來談談有關索引欄位的建立, 上述的後三個欄位就都是要件, 基本上, Equality, Inequality 是指 where 使用的比較欄位, 而 Include Columns 是查詢出來的覆蓋欄位, 至於要如何下這個索引, 成本最粗的下法是將 Equality + Include Columns 加入或 Inequality + Include Columns 加入, 這樣就會有查詢較佳的效能, 但會不會是最好的, 也還是得看異動的頻繁度來考量, 而該索引有沒有價值, 也是必須要評估的, 這裡介紹的方法是將沒有建上的索引資料整理出來列表給管理員來參考用的, 可以節省許多追蹤上的時間. 希望對於效能管理上對各位能有所幫助!!

參考資料:
http://msdn.microsoft.com/zh-tw/magazine/cc135978.aspx