<%-- - $Revision: 27964 $ - $Date: 2006-03-02 09:27:01 -0800 (Thu, 02 Mar 2006) $ - - Copyright (C) 1999-2004 Jive Software. All rights reserved. - This software is the proprietary information of Jive Software. Use is subject to license terms. --%> <%@ page import="java.text.*" errorPage="error.jsp" %> <%@ page import="org.jivesoftware.database.DbConnectionManager"%> <%@ page import="org.jivesoftware.util.JiveGlobals"%> <%@ page import="org.jivesoftware.database.ProfiledConnection"%> <%@ page import="org.jivesoftware.database.ProfiledConnectionEntry"%> <%@ page import="org.jivesoftware.util.ParamUtils"%> <%! // Global methods, vars // Default refresh values static final int[] REFRESHES = {10,30,60,90}; %> <% // Get parameters boolean doClear = request.getParameter("doClear") != null; String enableStats = ParamUtils.getParameter(request,"enableStats"); int refresh = ParamUtils.getIntParameter(request,"refresh", -1); boolean doSortByTime = ParamUtils.getBooleanParameter(request,"doSortByTime"); // Var for the alternating colors int rowColor = 0; // Clear the statistics if (doClear) { ProfiledConnection.resetStatistics(); // Reload the page without params. response.sendRedirect("system-db-stats.jsp"); } // Enable/disable stats if ("true".equals(enableStats)) { DbConnectionManager.setProfilingEnabled(true); } else if ("false".equals(enableStats)) { DbConnectionManager.setProfilingEnabled(false); } boolean showQueryStats = DbConnectionManager.isProfilingEnabled(); // Number intFormat for pretty printing of large number values and decimals: NumberFormat intFormat = NumberFormat.getInstance(JiveGlobals.getLocale()); DecimalFormat decFormat = new DecimalFormat("#,##0.00"); %> <html> <head> <title>Database Query Statistics</title> <meta name="pageID" content="system-db-stats"/> <% // Enable refreshing if specified if (refresh >= 10) { %> <meta http-equiv="refresh" content="<%= refresh %>;URL=system-db-stats.jsp?refresh=<%= refresh %>"> <% } %> <meta name="helpPage" content="enable_or_disable_database_query_statistics.html" /> </head> <body> <p> Enable database query statistics to trace all database queries made by Wildfire. This can be useful to debug issues and monitor database performance. However, it's not recommended that you leave query statistics permanently running, as they will cause performance to degrade slightly. </p> <p><b>Query Statistics Status</b></p> <form action="system-db-stats.jsp"> <ul> <table cellpadding="3" cellspacing="1" border="0"> <tr> <td> <input type="radio" name="enableStats" value="true" id="rb01" <%= ((showQueryStats) ? "checked":"") %>> <label for="rb01"><%= ((showQueryStats) ? "<b>Enabled</b>":"Enabled") %></label> </td> <td> <input type="radio" name="enableStats" value="false" id="rb02" <%= ((!showQueryStats) ? "checked":"") %>> <label for="rb02"><%= ((!showQueryStats) ? "<b>Disabled</b>":"Disabled") %></label> </td> <td> <input type="submit" name="" value="Update"> </td> </tr> </table> </ul> </form> <% if (showQueryStats) { %> <p><b>Query Statistics Settings</b></p> <form action="system-db-stats.jsp"> <ul> <table cellpadding="3" cellspacing="1" border="0"> <tr> <td> Refresh: <select size="1" name="refresh" onchange="this.form.submit();"> <option value="none">None <% for (int j=0; j<REFRESHES.length; j++) { String selected = ((REFRESHES[j] == refresh) ? " selected" : ""); %> <option value="<%= REFRESHES[j] %>"<%= selected %> ><%= REFRESHES[j] %> seconds <% } %> </select> </td> <td> <input type="submit" name="" value="Set"> </td> <td>|</td> <td> <input type="submit" name="" value="Update Now"> </td> <td>|</td> <td> <input type="submit" name="doClear" value="Clear All Stats"> </td> </tr> </table> </ul> </form> <br /> <p> <b>SELECT Query Statistics</b> </p> <ul> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="1" border="0" width="100%"> <tr bgcolor="#ffffff"> <td>Total # of selects</td> <td><%= intFormat.format(ProfiledConnection.getQueryCount(ProfiledConnection.SELECT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Total time for all selects (ms)</td> <td><%= intFormat.format(ProfiledConnection.getTotalQueryTime(ProfiledConnection.SELECT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Average time for all selects (ms)</td> <td><%= decFormat.format(ProfiledConnection.getAverageQueryTime(ProfiledConnection.SELECT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Selects per second</td> <td><%= decFormat.format(ProfiledConnection.getQueriesPerSecond(ProfiledConnection.SELECT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>20 Most common selects</td> <td bgcolor="#ffffff"><% ProfiledConnectionEntry[] list = ProfiledConnection.getSortedQueries(ProfiledConnection.SELECT, doSortByTime); if (list == null || list.length < 1) { out.println("No queries"); } else { %> </td> </tr> </table> </td></tr> </table> <br /> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="0" border="0" width="100%"> <tr bgcolor="#ffffff"><td> <% out.println("<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"0\" bgcolor=\"#aaaaaa\"><tr><td bgcolor=\"#ffffff\" align=\"middle\"><b>Query</b></td>"); out.println("<td bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=false&refresh=" + refresh + ";\">Count</a></b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b>Total Time</b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=true&refresh=" + refresh + ";\">Avg. Time</a></b></td></tr>"); for (int i = 0; i < ((list.length > 20) ? 20 : list.length); i++) { ProfiledConnectionEntry pce = list[i]; out.println("<tr><td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + pce.sql + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.count) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor++%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime/pce.count) + "</td></tr>"); } out.println("</table>"); } %></td> </tr> </table> </td></tr> </table> </ul> <b>INSERT Query Statistics</b> <ul> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="1" border="0" width="100%"> <tr bgcolor="#ffffff"> <td>Total # of inserts</td> <td><%= ProfiledConnection.getQueryCount(ProfiledConnection.INSERT) %></td> </tr> <tr bgcolor="#ffffff"> <td>Total time for all inserts (ms)</td> <td><%= ProfiledConnection.getTotalQueryTime(ProfiledConnection.INSERT) %></td> </tr> <tr bgcolor="#ffffff"> <td>Average time for all inserts (ms)</td> <td><%= decFormat.format(ProfiledConnection.getAverageQueryTime(ProfiledConnection.INSERT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Inserts per second</td> <td><%= decFormat.format(ProfiledConnection.getQueriesPerSecond(ProfiledConnection.INSERT)) %></td> </tr> <tr bgcolor="#ffffff"> <td>10 Most common inserts</td> <td bgcolor="#ffffff"><% list = ProfiledConnection.getSortedQueries(ProfiledConnection.INSERT, doSortByTime); if (list == null || list.length < 1) { out.println("No queries"); } else { %> </td> </tr> </table> </td></tr> </table> <br /> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="0" border="0" width="100%"> <tr bgcolor="#ffffff"><td> <% out.println("<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"0\" bgcolor=\"#aaaaaa\"><tr><td bgcolor=\"#ffffff\" align=\"middle\"><b>Query</b></td>"); out.println("<td bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=false&refresh=" + refresh + ";\">Count</a></b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b>Total Time</b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=true&refresh=" + refresh + ";\">Avg. Time</a></b></td></tr>"); rowColor = 0; for (int i = 0; i < ((list.length > 10) ? 10 : list.length); i++) { ProfiledConnectionEntry pce = list[i]; out.println("<tr><td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + pce.sql + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.count) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor++%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime/pce.count) + "</td></tr>"); } out.println("</table>"); } %></td> </tr> </table> </td></tr> </table> </ul> <b>UPDATE Query Statistics</b> <ul> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="1" border="0" width="100%"> <tr bgcolor="#ffffff"> <td>Total # of updates</td> <td><%= ProfiledConnection.getQueryCount(ProfiledConnection.UPDATE) %></td> </tr> <tr bgcolor="#ffffff"> <td>Total time for all updates (ms)</td> <td><%= ProfiledConnection.getTotalQueryTime(ProfiledConnection.UPDATE) %></td> </tr> <tr bgcolor="#ffffff"> <td>Average time for all updates (ms)</td> <td><%= decFormat.format(ProfiledConnection.getAverageQueryTime(ProfiledConnection.UPDATE)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Updates per second</td> <td><%= decFormat.format(ProfiledConnection.getQueriesPerSecond(ProfiledConnection.UPDATE)) %></td> </tr> <tr bgcolor="#ffffff"> <td>10 Most common updates</td> <td bgcolor="#ffffff"><% list = ProfiledConnection.getSortedQueries(ProfiledConnection.UPDATE, doSortByTime); if (list == null || list.length < 1) { out.println("No queries"); } else { %> </td> </tr> </table> </td></tr> </table> <br /> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="0" border="0" width="100%"> <tr bgcolor="#ffffff"><td> <% out.println("<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"0\" bgcolor=\"#aaaaaa\"><tr><td bgcolor=\"#ffffff\" align=\"middle\"><b>Query</b></td>"); out.println("<td bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=false&refresh=" + refresh + ";\">Count</a></b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b>Total Time</b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=true&refresh=" + refresh + ";\">Avg. Time</a></b></td></tr>"); rowColor = 0; for (int i = 0; i < ((list.length > 10) ? 10 : list.length); i++) { ProfiledConnectionEntry pce = list[i]; out.println("<tr><td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + pce.sql + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.count) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor++%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime/pce.count) + "</td></tr>"); } out.println("</table>"); } %></td> </tr> </table> </td></tr> </table> </ul> <b>DELETE Query Statistics</b> <ul> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="1" border="0" width="100%"> <tr bgcolor="#ffffff"> <td>Total # of deletes</td> <td><%= ProfiledConnection.getQueryCount(ProfiledConnection.DELETE) %></td> </tr> <tr bgcolor="#ffffff"> <td>Total time for all deletes (ms)</td> <td><%= ProfiledConnection.getTotalQueryTime(ProfiledConnection.DELETE) %></td> </tr> <tr bgcolor="#ffffff"> <td>Average time for all deletes (ms)</td> <td><%= decFormat.format(ProfiledConnection.getAverageQueryTime(ProfiledConnection.DELETE)) %></td> </tr> <tr bgcolor="#ffffff"> <td>Deletes per second</td> <td><%= decFormat.format(ProfiledConnection.getQueriesPerSecond(ProfiledConnection.DELETE)) %></td> </tr> <tr bgcolor="#ffffff"> <td>10 Most common deletes</td> <td bgcolor="#ffffff"><% list = ProfiledConnection.getSortedQueries(ProfiledConnection.DELETE, doSortByTime); if (list == null || list.length < 1) { out.println("No queries"); } else { %> </td> </tr> </table> </td></tr> </table> <br /> <table bgcolor="#aaaaaa" cellpadding="0" cellspacing="0" border="0" width="600"> <tr><td> <table bgcolor="#aaaaaa" cellpadding="3" cellspacing="0" border="0" width="100%"> <tr bgcolor="#ffffff"><td> <% out.println("<table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"0\" bgcolor=\"#aaaaaa\"><tr><td bgcolor=\"#ffffff\" align=\"middle\"><b>Query</b></td>"); out.println("<td bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=false&refresh=" + refresh + ";\">Count</a></b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b>Total Time</b></td>"); out.println("<td nowrap bgcolor=\"#ffffff\"><b><a href=\"javascript:location.href='system-db-stats.jsp?doSortByTime=true&refresh=" + refresh + ";\">Avg. Time</a></b></td></tr>"); rowColor = 0; for (int i = 0; i < ((list.length > 10) ? 10 : list.length); i++) { ProfiledConnectionEntry pce = list[i]; out.println("<tr><td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + pce.sql + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.count) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime) + "</td>"); out.println("<td bgcolor=\"" + ((rowColor++%2 == 0) ? "#efefef" : "#ffffff") + "\">" + intFormat.format(pce.totalTime/pce.count) + "</td></tr>"); } out.println("</table>"); } %></td> </tr> </table> </td></tr> </table> </ul> <% } %> </body></html>