query counters in DB library; internal server parameters for DB library counters
authorVictor Kirhenshtein <victor@netxms.org>
Sat, 14 Nov 2015 12:16:45 +0000 (14:16 +0200)
committerVictor Kirhenshtein <victor@netxms.org>
Sat, 14 Nov 2015 12:16:45 +0000 (14:16 +0200)
include/nxdbapi.h
src/db/libnxdb/session.cpp
src/java/netxms-eclipse/DataCollection/src/org/netxms/ui/eclipse/datacollection/dialogs/SelectInternalParamDlg.java
src/server/core/main.cpp
src/server/core/node.cpp
webui/webapp/DataCollection/src/org/netxms/ui/eclipse/datacollection/dialogs/SelectInternalParamDlg.java

index 7b02bdf..ab90bc6 100644 (file)
@@ -98,6 +98,18 @@ struct PoolConnectionInfo
    int srcLine;
 };
 
+/**
+ * DB library performance counters
+ */
+struct LIBNXDB_PERF_COUNTERS
+{
+   UINT64 selectQueries;
+   UINT64 nonSelectQueries;
+   UINT64 totalQueries;
+   UINT64 longRunningQueries;
+   UINT64 failedQueries;
+};
+
 /**
  * Functions
  */
@@ -213,6 +225,7 @@ int LIBNXDB_EXPORTABLE DBConnectionPoolGetAcquiredCount();
 void LIBNXDB_EXPORTABLE DBSetLongRunningThreshold(UINT32 threshold);
 void LIBNXDB_EXPORTABLE DBSetDebugPrintCallback(void (*cb)(int, const TCHAR *, va_list));
 ObjectArray<PoolConnectionInfo> LIBNXDB_EXPORTABLE *DBConnectionPoolGetConnectionList();
+void LIBNXDB_EXPORTABLE DBGetPerfCounters(LIBNXDB_PERF_COUNTERS *counters);
 
 bool LIBNXDB_EXPORTABLE IsDatabaseRecordExist(DB_HANDLE hdb, const TCHAR *table, const TCHAR *idColumn, UINT32 id);
 bool LIBNXDB_EXPORTABLE IsDatabaseRecordExist(DB_HANDLE hdb, const TCHAR *table, const TCHAR *idColumn, const uuid& id);
index 6b06b73..2c95d3a 100644 (file)
  */
 #define IS_VALID_STATEMENT_HANDLE(s) ((s != NULL) && (s->m_connection != NULL))
 
+/**
+ * Performance counters
+ */
+static UINT64 s_perfSelectQueries = 0;
+static UINT64 s_perfNonSelectQueries = 0;
+static UINT64 s_perfTotalQueries = 0;
+static UINT64 s_perfLongRunningQueries = 0;
+static UINT64 s_perfFailedQueries = 0;
+
 /**
  * Session init callback
  */
@@ -249,6 +258,9 @@ bool LIBNXDB_EXPORTABLE DBQueryEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *
       dwResult = hConn->m_driver->m_fpDrvQuery(hConn->m_connection, pwszQuery, wcErrorText);
    }
 
+   s_perfNonSelectQueries++;
+   s_perfTotalQueries++;
+
    ms = GetCurrentTimeMs() - ms;
    if (hConn->m_driver->m_dumpSql)
    {
@@ -257,6 +269,7 @@ bool LIBNXDB_EXPORTABLE DBQueryEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *
    if ((dwResult == DBERR_SUCCESS) && ((UINT32)ms > g_sqlQueryExecTimeThreshold))
    {
       __DBDbgPrintf(3, _T("Long running query: \"%s\" [%d ms]"), szQuery, (int)ms);
+      s_perfLongRunningQueries++;
    }
    
    MutexUnlock(hConn->m_mutexTransLock);
@@ -268,6 +281,7 @@ bool LIBNXDB_EXPORTABLE DBQueryEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *
 
    if (dwResult != DBERR_SUCCESS)
        {       
+      s_perfFailedQueries++;
                if (hConn->m_driver->m_logSqlErrors)
                        nxlog_write(g_sqlErrorMsgCode, EVENTLOG_ERROR_TYPE, "ss", szQuery, errorText);
                if (hConn->m_driver->m_fpEventHandler != NULL)
@@ -308,6 +322,10 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, T
    
    MutexLock(hConn->m_mutexTransLock);
    INT64 ms = GetCurrentTimeMs();
+
+   s_perfSelectQueries++;
+   s_perfTotalQueries++;
+
    hResult = hConn->m_driver->m_fpDrvSelect(hConn->m_connection, pwszQuery, &dwError, wcErrorText);
    if ((hResult == NULL) && (dwError == DBERR_CONNECTION_LOST) && hConn->m_reconnectEnabled)
    {
@@ -323,6 +341,7 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, T
    if ((hResult != NULL) && ((UINT32)ms > g_sqlQueryExecTimeThreshold))
    {
       __DBDbgPrintf(3, _T("Long running query: \"%s\" [%d ms]"), szQuery, (int)ms);
+      s_perfLongRunningQueries++;
    }
    MutexUnlock(hConn->m_mutexTransLock);
 
@@ -333,6 +352,7 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, T
 
        if (hResult == NULL)
        {
+          s_perfFailedQueries++;
                if (hConn->m_driver->m_logSqlErrors)
                        nxlog_write(g_sqlErrorMsgCode, EVENTLOG_ERROR_TYPE, "ss", szQuery, errorText);
                if (hConn->m_driver->m_fpEventHandler != NULL)
@@ -772,6 +792,10 @@ DB_ASYNC_RESULT LIBNXDB_EXPORTABLE DBAsyncSelectEx(DB_HANDLE hConn, const TCHAR
    
    MutexLock(hConn->m_mutexTransLock);
    INT64 ms = GetCurrentTimeMs();
+
+   s_perfSelectQueries++;
+   s_perfTotalQueries++;
+
    hResult = hConn->m_driver->m_fpDrvAsyncSelect(hConn->m_connection, pwszQuery, &dwError, wcErrorText);
    if ((hResult == NULL) && (dwError == DBERR_CONNECTION_LOST) && hConn->m_reconnectEnabled)
    {
@@ -787,9 +811,11 @@ DB_ASYNC_RESULT LIBNXDB_EXPORTABLE DBAsyncSelectEx(DB_HANDLE hConn, const TCHAR
    if ((hResult != NULL) && ((UINT32)ms > g_sqlQueryExecTimeThreshold))
    {
       __DBDbgPrintf(3, _T("Long running query: \"%s\" [%d ms]"), szQuery, (int)ms);
+      s_perfLongRunningQueries++;
    }
    if (hResult == NULL)
    {
+      s_perfFailedQueries++;
       MutexUnlock(hConn->m_mutexTransLock);
 
 #ifndef UNICODE
@@ -1070,6 +1096,9 @@ DB_STATEMENT LIBNXDB_EXPORTABLE DBPrepareEx(DB_HANDLE hConn, const TCHAR *query,
         nxlog_write(g_sqlErrorMsgCode, EVENTLOG_ERROR_TYPE, "ss", query, errorText);
                if (hConn->m_driver->m_fpEventHandler != NULL)
                        hConn->m_driver->m_fpEventHandler(DBEVENT_QUERY_FAILED, pwszQuery, wcErrorText, errorCode == DBERR_CONNECTION_LOST, hConn->m_driver->m_userArg);
+
+               s_perfFailedQueries++;
+               s_perfTotalQueries++;
        }
 
    if (hConn->m_driver->m_dumpSql)
@@ -1323,6 +1352,9 @@ bool LIBNXDB_EXPORTABLE DBExecuteEx(DB_STATEMENT hStmt, TCHAR *errorText)
        MutexLock(hConn->m_mutexTransLock);
    UINT64 ms = GetCurrentTimeMs();
 
+   s_perfNonSelectQueries++;
+   s_perfTotalQueries++;
+
        DWORD dwResult = hConn->m_driver->m_fpDrvExecute(hConn->m_connection, hStmt->m_statement, wcErrorText);
    ms = GetCurrentTimeMs() - ms;
    if (hConn->m_driver->m_dumpSql)
@@ -1332,6 +1364,7 @@ bool LIBNXDB_EXPORTABLE DBExecuteEx(DB_STATEMENT hStmt, TCHAR *errorText)
    if ((dwResult == DBERR_SUCCESS) && ((UINT32)ms > g_sqlQueryExecTimeThreshold))
    {
       __DBDbgPrintf(3, _T("Long running query: \"%s\" [%d ms]"), hStmt->m_query, (int)ms);
+      s_perfLongRunningQueries++;
    }
 
    // Do reconnect if needed, but don't retry statement execution
@@ -1362,6 +1395,7 @@ bool LIBNXDB_EXPORTABLE DBExecuteEx(DB_STATEMENT hStmt, TCHAR *errorText)
                        free(query);
 #endif
                }
+               s_perfFailedQueries++;
        }
 
    return dwResult == DBERR_SUCCESS;
@@ -1398,6 +1432,9 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectPreparedEx(DB_STATEMENT hStmt, TCHAR *error
        DB_HANDLE hConn = hStmt->m_connection;
    MutexLock(hConn->m_mutexTransLock);
 
+   s_perfSelectQueries++;
+   s_perfTotalQueries++;
+
    INT64 ms = GetCurrentTimeMs();
    DWORD dwError = DBERR_OTHER_ERROR;
        DBDRV_RESULT hResult = hConn->m_driver->m_fpDrvSelectPrepared(hConn->m_connection, hStmt->m_statement, &dwError, wcErrorText);
@@ -1411,6 +1448,7 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectPreparedEx(DB_STATEMENT hStmt, TCHAR *error
    if ((hResult != NULL) && ((UINT32)ms > g_sqlQueryExecTimeThreshold))
    {
       __DBDbgPrintf(3, _T("Long running query: \"%s\" [%d ms]"), hStmt->m_query, (int)ms);
+      s_perfLongRunningQueries++;
    }
 
    // Do reconnect if needed, but don't retry statement execution
@@ -1441,6 +1479,7 @@ DB_RESULT LIBNXDB_EXPORTABLE DBSelectPreparedEx(DB_STATEMENT hStmt, TCHAR *error
                        free(query);
 #endif
                }
+               s_perfFailedQueries++;
        }
 
        if (hResult != NULL)
@@ -1618,3 +1657,15 @@ int LIBNXDB_EXPORTABLE DBIsTableExist(DB_HANDLE conn, const TCHAR *table)
    return conn->m_driver->m_fpDrvIsTableExist(conn->m_connection, wname);
 #endif
 }
+
+/**
+ * Get performance counters
+ */
+void LIBNXDB_EXPORTABLE DBGetPerfCounters(LIBNXDB_PERF_COUNTERS *counters)
+{
+   counters->failedQueries = s_perfFailedQueries;
+   counters->longRunningQueries = s_perfLongRunningQueries;
+   counters->nonSelectQueries = s_perfNonSelectQueries;
+   counters->selectQueries = s_perfSelectQueries;
+   counters->totalQueries = s_perfTotalQueries;
+}
index a844204..971b91b 100644 (file)
@@ -73,6 +73,11 @@ public class SelectInternalParamDlg extends AbstractSelectParamDlg
                        list.add(new AgentParameter("Server.AverageDCPollerQueueSize", Messages.get().SelectInternalParamDlg_DCI_AvgDCQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.AverageSyslogProcessingQueueSize", Messages.get().SelectInternalParamDlg_SyslogProcessingQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.AverageSyslogWriterQueueSize", Messages.get().SelectInternalParamDlg_SyslogWriterQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Failed", "Failed DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.LongRunning", "Long running DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.NonSelect", "Non-SELECT DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Select", "SELECT DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Total", "Total DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.IData", "DB writer requests (DCI data)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.Other", "DB writer requests (other queries)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.RawData", "DB writer requests (raw DCI data)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
index 3668d33..a5ee468 100644 (file)
@@ -1377,6 +1377,15 @@ int ProcessConsoleCommand(const TCHAR *pszCmdLine, CONSOLE_CTX pCtx)
       }
       else if (IsCommand(_T("DBSTATS"), szBuffer, 3))
       {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         ConsolePrintf(pCtx, _T("SQL query counters:\n"));
+         ConsolePrintf(pCtx, _T("   Total .......... ") INT64_FMT _T("\n"), counters.totalQueries);
+         ConsolePrintf(pCtx, _T("   SELECT ......... ") INT64_FMT _T("\n"), counters.selectQueries);
+         ConsolePrintf(pCtx, _T("   Non-SELECT ..... ") INT64_FMT _T("\n"), counters.nonSelectQueries);
+         ConsolePrintf(pCtx, _T("   Long running ... ") INT64_FMT _T("\n"), counters.longRunningQueries);
+         ConsolePrintf(pCtx, _T("   Failed ......... ") INT64_FMT _T("\n"), counters.failedQueries);
+
          ConsolePrintf(pCtx, _T("Background writer requests:\n"));
          ConsolePrintf(pCtx, _T("   DCI data ....... ") INT64_FMT _T("\n"), g_idataWriteRequests);
          ConsolePrintf(pCtx, _T("   DCI raw data ... ") INT64_FMT _T("\n"), g_rawDataWriteRequests);
index 0f327e5..ade695f 100644 (file)
@@ -4159,6 +4159,36 @@ UINT32 Node::getInternalItem(const TCHAR *param, size_t bufSize, TCHAR *buffer)
       {
          _sntprintf(buffer, bufSize, _T("%f"), g_dAvgSyslogWriterQueueSize);
       }
+      else if (!_tcsicmp(param, _T("Server.DB.Queries.Failed")))
+      {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         _sntprintf(buffer, bufSize, UINT64_FMT, counters.failedQueries);
+      }
+      else if (!_tcsicmp(param, _T("Server.DB.Queries.LongRunning")))
+      {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         _sntprintf(buffer, bufSize, UINT64_FMT, counters.longRunningQueries);
+      }
+      else if (!_tcsicmp(param, _T("Server.DB.Queries.NonSelect")))
+      {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         _sntprintf(buffer, bufSize, UINT64_FMT, counters.nonSelectQueries);
+      }
+      else if (!_tcsicmp(param, _T("Server.DB.Queries.Select")))
+      {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         _sntprintf(buffer, bufSize, UINT64_FMT, counters.selectQueries);
+      }
+      else if (!_tcsicmp(param, _T("Server.DB.Queries.Total")))
+      {
+         LIBNXDB_PERF_COUNTERS counters;
+         DBGetPerfCounters(&counters);
+         _sntprintf(buffer, bufSize, UINT64_FMT, counters.totalQueries);
+      }
       else if (!_tcsicmp(param, _T("Server.DBWriter.Requests.IData")))
       {
          _sntprintf(buffer, bufSize, UINT64_FMT, g_idataWriteRequests);
index a844204..971b91b 100644 (file)
@@ -73,6 +73,11 @@ public class SelectInternalParamDlg extends AbstractSelectParamDlg
                        list.add(new AgentParameter("Server.AverageDCPollerQueueSize", Messages.get().SelectInternalParamDlg_DCI_AvgDCQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.AverageSyslogProcessingQueueSize", Messages.get().SelectInternalParamDlg_SyslogProcessingQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.AverageSyslogWriterQueueSize", Messages.get().SelectInternalParamDlg_SyslogWriterQueue, DataCollectionItem.DT_FLOAT)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Failed", "Failed DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.LongRunning", "Long running DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.NonSelect", "Non-SELECT DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Select", "SELECT DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
+         list.add(new AgentParameter("Server.DB.Queries.Total", "Total DB queries", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.IData", "DB writer requests (DCI data)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.Other", "DB writer requests (other queries)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$
          list.add(new AgentParameter("Server.DBWriter.Requests.RawData", "DB writer requests (raw DCI data)", DataCollectionItem.DT_UINT64)); //$NON-NLS-1$