/sql-performance-counters-nodejs

Simple performance metrics for SQL queries.

Primary LanguageJavaScriptOtherNOASSERTION

SQL Performance Counters

Version npm

Linux build

Performance metrics of database queries per query.

String literals and number constants are removed from queries using unparametrize-sql-query before adding an entry in the database.

The database is accessible as a Map on the PerformanceCounters.metrics property or as an object on PerformanceCounters.metricsAsObject and it looks something like this:

{ 
	"SELECT ? FROM table": {
		successCount: 1,
		errorCount: 0,
		successMillisecondsTotal: 50,
		errorMillisecondsTotal: 0,
		successMillisecondsAverage: 50,
		errorMillisecondsAverage: 0,
		fetchedRows: 1,
		affectedRows: 0,
		changedRows: 0
	},
	"SELECT ? FROM another_table": {
		successCount: 0,
		errorCount: 1,
		successMillisecondsTotal: 0,
		errorMillisecondsTotal: 3,
		successMillisecondsAverage: 0,
		errorMillisecondsAverage: 3,
		fetchedRows: 0,
		affectedRows: 0,
		changedRows: 0
	}
}

The above object format ca be rendered in a live HTML table in browsers using sql-performance-counters-ui.

Installation:

npm i sql-performance-counters

Usage

See tests.js and PerformanceCounters.js for usage.

You should use PerformanceCounters.js as a global singleton. For example, create a file named PerformanceCountersSingleton.js with these contents:

const PerformanceCounters = require("sql-performance-counters").PerformanceCounters;

module.exports = new PerformanceCounters();

Then throughout your application you may acces the same instance to get the metrics or record activity:

const performanceCounters = require("./PerformanceCountersSingleton");


performanceCounters.setLatency(50);

performanceCounters.onQuery();
console.log(performanceCounters.runningQueriesCount);

performanceCounters.onQuery();
console.log(performanceCounters.runningQueriesCount);


performanceCounters.onResult("SELECT 1 FROM table", /*nDurationMilliseconds*/ 100, /*nFetchedRows*/ 0);
console.log(performanceCounters.runningQueriesCount);

performanceCounters.onError("SELECT 1 FROM another_table", /*nDurationMilliseconds*/ 53, /*error*/ new Error("test"));
console.log(performanceCounters.runningQueriesCount);


const objMetrics = performanceCounters.metricsAsObject;
console.log(objMetrics);


performanceCounters.clear();

Usage with promise-mysql

If using MySQL and using promise-mysql, you may conveniently use the PerformanceCounters.onMySQLPromiseConnection() function to get started rapidly.

const MySQL = require("promise-mysql");

/* [...] */
const pool = MySQL.createPool(mysqljsConfigObject);

pool.on(
	"connection", 
	(connection) => {
		PerformanceCounters.onMySQLPromiseConnection(connection);

		/* [...] */
	}
);

(async() => {
	const connection = await MySQL.createConnection(mysqljsConfigObject);

	PerformanceCounters.onMySQLPromiseConnection(connection.connection);

	/* [...] */
})();

See PerformanceCounters.js for how PerformanceCounters.onMySQLPromiseConnection() works. 810279