监控和剖析数据库操作P6Spy,SQL Profiler,SQL 使用简介
新一篇:?關于java類的動態裝載
|
在我們 Java 開發應用程序的過程中，難免會碰到系統的性能問題，特別在企業應用的開發過程中，都會與數據庫進行打交道。當我們碰到數據庫性能時，最有效的就是直接跟蹤每一個 SQL 語句的執行情況，SQL 語句的優化、索引的優化往往也是最容易取得最直接的效果的。
下面，我們首先開始介紹 P6Spy 這個剖析工具，看它是如何無侵入性地進行數據庫操作的監控與剖析。
P6Spy
P6Spy 是一個可以用來在應用程序中攔截和修改數據操作語句的開源框架。通過 P6Spy 我們可以對 SQL 語句進行攔截，相當于一個 SQL 語句的記錄器，這樣我們可以用它來作相關的分析，比如性能分析。P6Spy 用 Log4J 來記錄 JDBC 調用的日記信息。
自從 2003 年 11 月 30 日 P6Spy 版本 1.3 發布后，已經被下載超過 19,000 次了。P6Spy 支持 WebSphere、WebLogic、JBoss、Resin 和 Tomcat 等絕大多數的應用服務器。
使用步驟
首先，你的應用系統應當是基于數據庫的，然后你需要去獲取 P6Spy 相關的文件（在 參考資源 中可以找到下載鏈接，您可以直接下載軟件包）。下面介紹 P6Spy 的安裝與使用的詳細操作過程：
P6Log 與 P6Outage
在 P6Spy 發布包中，它包含 P6Log 和 P6Outage 兩個模塊：
P6Log 是用來攔截和記錄任務應用程序的 JDBC 語句的。這個功能對于開發者監控 EJB 服務器上的 SQL 語句執行情況尤其有用，可以讓開發者完成盡可能高效的代碼。同時 P6Spy 的部署是極其簡單的，而且根本不需要更改任何一行代碼，即對現有的應用是無侵入性的。
| realdriver= (your driver) (你的實際 JDBC 驅動程序名稱) executionthreshold=整數時間 (以毫秒為單位) |
P6Outage 專門用來檢測和記錄執行時間比較長的 SQL 語句，P6Outage 只記錄超過配置條件里時間的那些信息，并對可能影響到數據庫的運行效率減小到最低。
| #outagedetection=true|false (是否記錄較長時間運行的語句) #outagedetectioninterval=整數時間 (以秒為單位) |
架構原理
簡單地講，我們可以認為 P6Spy 就是一個代理（Proxy），它只做了一層對 JDBC 驅動的攔截，然后轉發出去，這樣的設計與實際的應用程序沒有任何的耦合性，除了在配置中將驅動程序改成 P6Spy 的攔截驅動外，程序其他地方并不需要做任何的改變。這層攔截器除了可能會給系統帶來略微的性能下降外，對程序其他方面沒有任何的影響。而相對于這一點點的性能下降，在開發環境中對于開發人員來說是無法感覺到，相比它所帶來的好處，完全可以忽略不計。
圖 1. P6Spy 對數據庫進行攔截監控的處理過程
問題與解決
如果在你的應用程序啟動后，卻在 spy.log 文件中發現了如下的提示信息，那就是驅動程序加載先后的問題了。
| <你的程序的數據庫驅動名稱> is a real driver in spy.properties, but it has been loaded before p6spy. p6spy will not wrap these connections. Either prevent the driver from loading, or try setting'deregisterdrivers' to true in spy.properties |
請把 spy.properties 配置文件里的 deregisterdrivers=false 改為 deregisterdrivers=true，重新運行即可。
這是因為有些應用系統中會先于 P6Spy 加載了真正的數據庫的驅動程序，導致 P6Spy 無法監控到，設置 deregisterdrivers 為 true，是顯式地把真正的數據庫的驅動程序進行反注冊掉，而采用 P6Spy 的驅動程序。
配置參數及相關意義
下表列出了 spy.properties 配置文件中的各配置項的名稱、默認值及其意義和相關注意事項：
| module.log | com.p6spy.engine.logging. P6LogFactory | 用來攔截和記錄任務應用程序的 JDBC 語句。若無配置或注釋掉則無此功能。 |
| module.outage | com.p6spy.engine.outage. P6OutageFactory | 檢測和記錄執行時間比較長的 SQL 語句。若無配置或注釋掉則無此功能。 |
| realdriver | 真正的應用系統使用的數據庫驅動程序名稱。 | |
| realdriver2 | 真正的應用系統使用的第二種備用數據庫驅動程序名稱。 | |
| realdriver3 | 真正的應用系統使用的第三種備用數據庫驅動程序名稱。 | |
| deregisterdrivers | false | 顯示地把真正的數據庫的驅動程序進行反注冊掉。取值 true| false |
| executionthreshold | P6Log 模塊執行時間設置，整數值 (以毫秒為單位)，只有當超過這個時間才進行記錄 Log。 | |
| outagedetection | false | P6Outage 模塊是否記錄較長時間運行的語句。取值 true| false |
| outagedetectioninterval | P6Outage 模塊執行時間設置，整數值 （以秒為單位)），只有當超過這個時間才進行記錄 Log。 | |
| filter | false | 是否過濾 Log，取值 true| false |
| include | 過濾 Log 時所包含的表名列表，以逗號分隔。 | |
| exclude | 過濾 Log 時所排除的表名列表，以逗號分隔。 | |
| sqlexpression | 過濾 Log 時的 SQL 表達式名稱 | |
| autoflush | true | 是否自動刷新。取值 true| false |
| dateformat | 設置時間的格式，也就是用 Java 的 SimpleDateFormat 程序。 | |
| includecategories | 顯示指定過濾 Log 時包含的分類列表，取值為 error，info，batch，debug，statement，commit，rollback，result 的各種組合。 | |
| excludecategories | 顯示指定過濾 Log 時排隊的分類列表，取值同上。 | |
| stringmatcher | 使用正則表達式來過濾 Log，取值為 com.p6spy.engine.common.GnuRegexMatcher 和 com.p6spy.engine.common.JakartaRegexMatcher | |
| stacktrace | false | 打印堆棧跟蹤信息。取值 true| false |
| stacktraceclass | 如果 stacktrace=true，則可以指定具體的類名來進行過濾。 | |
| reloadproperties | false | 監測屬性配置文件是否進行重新加載。取值 true| false |
| reloadpropertiesinterval | 60 | 屬性配置文件重新加載的時間間隔，以秒為單位。 |
| useprefix | false | 是否加上前綴，設置為 true，會加上 p6spy: 作為前綴。取值 true| false |
| appender | com.p6spy.engine.logging. appender.FileLogger | 指定 Log 的 appender，與 Log4J 有點同義，取值：com.p6spy.engine.logging.appender.Log4jLogger、com.p6spy.engine.logging.appender.StdoutLogger 和 com.p6spy.engine.logging.appender.FileLogger |
| logfile | spy.log | 指定 Log 的文件名，任何適于操作系統的文件。 |
| append | true | 指定是否每次是增加 Log，設置為 false 則每次都會先進行清空。取值 true| false |
| log4j.appender.STDOUT | org.apache.log4j.ConsoleAppender | 當 appender 為 log4j 時采用的配置，配置如同 Log4J 的相關配置。 |
| log4j.appender.STDOUT.layout | org.apache.log4j.PatternLayout | 同上 |
| log4j.appender.STDOUT. layout.ConversionPattern | p6spy - %m%n | 同上 |
| log4j.logger.p6spy | INFO,STDOUT | Log 級別的設置，取值同 Log4J 的配置 |
| realdatasource | 設置數據源 DataSource 的配置名稱。 | |
| realdatasourceclass | 設置數據源 DataSource 的類的全稱。 | |
| realdatasourceproperties | 設置數據源 DataSource 的屬性，以分號分隔。 | |
| jndicontextfactory | 設置 JNDI 數據源的 NamingContextFactory。 | |
| jndicontextproviderurl | 設置 JNDI 數據源的提供者的 URL。 | |
| jndicontextcustom | 設置 JNDI 數據源的一些定制信息，以分號分隔。 |
|
SQL Profiler
SQL Profiler 是一個由 Jahia.org 提供的基于 P6Spy 引擎的快速剖析工具，用來統計 SQL 查詢語句以便了解哪里是性能瓶頸，在哪里創建索引或者采取相應的辦法才能提高效率，并且能根據 SQL 查詢語句的情況幫你生成合適的索引腳本。
這個小工具可以實時地顯示數據庫查詢的情況，通過集成的 SQL 解析器，在訪問大多數表與列上面建立統計分析，并生成索引腳本。當然，其它的信息也會進行收集和顯示，比如：單個數據庫請求的時間、一類請求的時間以及所有請求的時間。因此，可以有效地通過視圖的排序來檢測數據的性能問題所在。這個工具對于大量的需要進行分析的請求是非常有用的，而不是人工一個個地去做分析。當你需要知道比如對相同的表和列進行訪問但是采用不同的查詢值時，這種分組的查詢可以用建立在 ANTLR 上的 SQL 解析器進行分析。
使用步驟
首先，你的應用系統同樣也應當是基于數據庫的，然后你需要去獲取 SQL Profiler 相關的文件（在 參考資源 中可以找到下載鏈接，您可以直接下載軟件包）。下面介紹 SQL Profiler 的安裝與使用的詳細操作過程：
分析結果
經過一段時間的系統運行后，點擊 Pause 按鈕停止攔截，可以得到分析結果如下圖：
圖 2. SQL Profiler 的分析結果 Profiler 視圖
接著，可以切換到 Loggers 視圖，這是 Lgger 視圖的信息：
圖 3. SQL Profiler 的分析結果 Logger 視圖
當然，也可以切換到 Analysis 視圖，這是 Analysis 視圖的分析結果信息：
圖 4. SQL Profiler 的分析結果 Analysis 視圖
在經過分析后，我們可以直接通過 SQLProfiler 提交的保存按鈕，直接導出應當進行數據庫優化的建議的索引腳本，通過查看索引腳本，我們可以看到創建索引的詳細 SQL 腳本，這樣，我們就可以非常方便地進行數據庫調優了。
問題與解決
最后一個需要注意的問題就是需要先啟動 SQLProfiler，然后再啟動應用程序或者 Tomcat 等應用服務器。這是因為 SQLProfiler 默認使用的是 Log4j 的 SocketAppender，所以要先啟動。否則，會因你的應用程序或應用服務器中的 Web 應用之類的因連接不到 Socket 的服務器（SQLProfiler 相當于 Socket 的服務器）而發生錯誤，可以通過 SQL Profiler 控制界面最下面的連接狀態就可以知道是否有程序連接上來。
圖 5. SQL Profiler 處于非連接狀態
圖 6. SQL Profiler 處于連接狀態
|
IronTrack SQL
IronEye，一個專注于 JDBC 性能的監控和測試的開源項目，它包含有三個工具：IronEye SQL，IronEye Cache，IronTrack SQL。其中，IronEye SQL 用于監測 Java 應用和數據庫服務器之間查詢開銷的時間，診斷在性能方面是否存在著相關問題，讓開發人員在測試之前就能發現問題。IronEye 于 2003 年 10 月 1 日開始基于 Apache Software License 發布。
IronEye SQL 這個輕量級的 Java 工具提供所有流動在數據庫與應用程序之間的 SQL 統計信息并用多張圖表展現，可以快速優化程序的性能。
IronGrid 相對于 Continuous Integration 提出了 Continuous Performance 的概念，即在項目開發過程中隨時關注性能問題，而不是傳統的出了問題再解決的方案。
IronGrid 在應用程序對數據庫的操作上的 Continuous Performance 是通過 IronTrack SQL 進行體現的。IronTrack SQL 能通過對 JDBC 的包裝來攔截應用程序對數據庫的請求，完成性能監控。IronTrack SQL 的好處在于不需要修改任何代碼或者在數據庫端安裝任何程序，只需要在測試時把依賴的 JDBC 替換就可以了。
使用步驟
首先，你的應用系統同樣也應當是基于數據庫的，然后你需要去獲取 IronTrack SQL 相關的文件（在 參考資源 中可以找到下載鏈接，您可以直接下載軟件包）。下面介紹 IronTrack SQL 的安裝與使用的詳細操作過程：
連接設置
點擊“Config”按鈕就可以設置主機名、端口與刷新的時間（毫秒為單位）。根據你的服務器與端口的不同而進行相應地改變，下面以本地和 2000 端口，刷新時間為 500 毫秒為示例。設置完成后，確定，點擊“Connect”就可以連接應用系統并進行監測與分析了，當要停止分析時，只要點擊“Disconnect”按扭即可立刻停止分析了。
在分析的過程中，我們可以根據需要點擊“Purge”按鈕，它可以清除目前所監測到的內容，然后重新進行記錄監測信息，很方便地進行重新開始。
圖 7. IronTrack SQL 連接示例
分析結果
經過一段時間的系統運行后，我們可以直接得到分析的結果與相應的圖形分析示例。相關的信息顯示如下：
圖 8. IronTrack SQL 分析結果
- Count 列顯示 SQL 語句的調用次數；
- Avg Time 列顯示 SQL 語句的執行平均時間；
- Max Time 列顯示 SQL 語句花費的最高時間；
- SQL 列顯示真正執行的 SQL 語句內容。
同時也可以通過設置過濾條件來顯示指定條件的結果，比如：只關注平均調用次數大于 100 次 的結果。點擊“Filtering”左邊的小三角圖標，可以顯示如下的過濾條件設置欄目：
圖 9. IronTrack SQL 設置相關的過濾條件
設置完成后，點擊“Apply Filter”按鈕即可以獲取所需要的相關結果了。這樣可以更加方便地集中精力進行所需要的內容分析，可以更加方便快速地定位到問題的所在之處，然后進行解決。
|
總結
通過使用 P6Spy、SQL Profiler、IronTrack SQL 工具，我們可以無侵入已有的應用系統而有效地進行數據庫操作的監控與剖析，為發現系統的性能瓶頸，尋找系統的性能調優提供了相當便利的方法。
轉載于:https://www.cnblogs.com/dainiao01/archive/2008/05/17/2250315.html
總結
以上是生活随笔為你收集整理的监控和剖析数据库操作P6Spy,SQL Profiler,SQL 使用简介的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： DirectShow 在VS2005中环
- 下一篇： mysql 字符串拼接的几种方式