Recording Datadog / StatsD Gauges For Database Key Utilization In Lucee CFML 5.3.7.47

DatabaseKeyGateway.cfc

component
	output = false
	hint = "I provide data-access methods for monitoring the database key-space."
	{

	/**
	* I get the columns that represent numeric keys in the database.
	*/
	public query function getColumns() {

		```
		<cfquery name="local.results">
			/*
				Since the database schema may not have the same name in every environment
				we'll use the contextual database used by the ColdFusion data-source.
			*/
			SET @schemaName = DATABASE();

			/* DEBUG: databaseKeyGateway.getColumns(). */
			SELECT
				t.table_name,
				s.column_name,
				s.index_name,
				c.column_type
			FROM
				information_schema.TABLES t
			INNER JOIN
				information_schema.STATISTICS s
			ON
				(
						t.table_schema = @schemaName
					AND
						s.table_schema = t.table_schema
					AND
						s.table_name = t.table_name
					AND
						/*
							Some tables have compound keys - that is, a non-synthetic
							primary key that is composed of multiple columns. For the
							sake of our monitoring, we're going to assume that the first
							column is the one that is likely to run out of space. As
							such, we're going to limit the results to columns that are
							the FIRST column in the sequence of indexed-columns.
						*/
						s.seq_in_index = 1
				)
			INNER JOIN
				information_schema.COLUMNS c
			ON
				(
						c.table_schema = t.table_schema
					AND
						c.table_name = t.table_name
					AND
						c.column_name = s.column_name
					AND
						/*
							For the sake of our monitoring, we only care about INT vs.
							UNSIGNED INT. While there are other types of numeric values,
							those are less likely to cause a problem for us.
						*/
						c.data_type = 'int'
				)
			ORDER BY
				c.table_name ASC,
				c.column_name ASC
			;
		</cfquery>
		```

		return( results );

	}


	/**
	* I get the last key value stored in the given column.
	* 
	* CAUTION: This query is SAFE to run because - and ONLY BEACUSE - we are expecting
	* the given column to the first column in an index on the given table. This should,
	* therefore, use a COVERING INDEX to pull this value right out of memory.
	*/
	public numeric function getLastColumnValue(
		required string tableName,
		required string columnName
		) {

		```
		<cfquery name="local.results">
			/* DEBUG: databaseKeyGateway.getLastColumnValue(). */
			SELECT
				( #columnName# ) AS value
			FROM
				#tableName#
			ORDER BY
				#columnName# DESC
			LIMIT
				1
			;
		</cfquery>
		```

		return( val( results.value ) );

	}

}

DatabaseKeyService.cfc

component
	accessors = true
	output = false
	hint = "I provide service methods for monitoring the database key-space."
	{

	// Define properties for dependency-injection.
	property databaseKeyGateway;

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I get the database key usage stats.
	*/
	public array function getKeyStats() {

		var columnsQuery = databaseKeyGateway.getColumns();
		var stats = [];

		loop query = columnsQuery {

			var indexType = ( columnsQuery.index_name == "PRIMARY" )
				? "Primary"
				: "Secondary"
			;
			var maxIntValue = columnsQuery.column_type.findNoCase( "unsigned" )
				? 4294967295
				: 2147483647
			;
			var lastColumnValue = databaseKeyGateway.getLastColumnValue(
				columnsQuery.table_name,
				columnsQuery.column_name
			);

			// Calculate the relative percentage of key-space that we used for this
			// column (based on the current data-type).
			var percentUsed = ceiling( lastColumnValue / maxIntValue * 100 );

			stats.append({
				table: columnsQuery.table_name,
				column: columnsQuery.column_name,
				indexType: indexType,
				currentValue: lastColumnValue,
				maxValue: maxIntValue,
				percentUsed: percentUsed
			});

		}

		return( stats );

	}

}

snippet-1.sql

SELECT
	t.table_name,
	s.column_name,
	s.index_name,
	c.column_type
FROM
	information_schema.TABLES t
INNER JOIN
	information_schema.STATISTICS s
ON
	(
			t.table_schema = DATABASE()
		AND
			t.table_name NOT LIKE '\_%'
		AND
			s.table_schema = t.table_schema
		AND
			s.table_name = t.table_name
		AND
			s.seq_in_index = 1
	)
/* .... truncated .... */

WatchDatabaseKeysTask.cfc

component
	accessors = true
	output = false
	hint = "I watch the database key-space to make sure it isn't running out of space!"
	{

	// Define properties for dependency-injection.
	property databaseKeyService;
	property datadog;

	// ---
	// PUBLIC METHODS.
	// ---

	/**
	* I inspect the numeric key-columns in the database and record stats regarding how
	* much of their INT-space has been used.
	*/
	public void function execute() {

		// Since there are HUNDREDS of numeric keys in the database, let's limit our
		// results to columns that reach a threshold of interest. This should give us
		// plenty of time to react without as much noise.
		var stats = databaseKeyService.getKeyStats().filter(
			( stat ) => {

				return( stat.percentUsed >= 20 );

			}
		);

		for ( var stat in stats ) {

			datadog.gauge(
				key = "database.key_space",
				value = stat.percentUsed,
				tags = [ lcase( "column:#stat.table#.#stat.column#" ) ]
			);
		}

	}

}