SQL Pool Tracing

Typedefs
typedef struct M_sql_trace_data	M_sql_trace_data_t
typedef void(*	M_sql_trace_cb_t) (M_sql_trace_t event_type, const M_sql_trace_data_t *data, void *arg)

Enumerations
enum	M_sql_trace_t {   M_SQL_TRACE_CONNECTING = 1 ,   M_SQL_TRACE_CONNECTED = 2 ,   M_SQL_TRACE_CONNECT_FAILED = 3 ,   M_SQL_TRACE_DISCONNECTING = 4 ,   M_SQL_TRACE_DISCONNECTED = 5 ,   M_SQL_TRACE_BEGIN_START = 6 ,   M_SQL_TRACE_BEGIN_FINISH = 7 ,   M_SQL_TRACE_ROLLBACK_START = 8 ,   M_SQL_TRACE_ROLLBACK_FINISH = 9 ,   M_SQL_TRACE_COMMIT_START = 10 ,   M_SQL_TRACE_COMMIT_FINISH = 11 ,   M_SQL_TRACE_EXECUTE_START = 12 ,   M_SQL_TRACE_EXECUTE_FINISH = 13 ,   M_SQL_TRACE_FETCH_START = 14 ,   M_SQL_TRACE_FETCH_FINISH = 15 ,   M_SQL_TRACE_CONNFAIL = 16 ,   M_SQL_TRACE_TRANFAIL = 17 ,   M_SQL_TRACE_DRIVER_DEBUG = 18 ,   M_SQL_TRACE_DRIVER_ERROR = 19 ,   M_SQL_TRACE_STALL_QUERY = 20 ,   M_SQL_TRACE_STALL_TRANS_IDLE = 21 ,   M_SQL_TRACE_STALL_TRANS_LONG = 22 }
enum	M_sql_conn_type_t {   M_SQL_CONN_TYPE_UNKNOWN = 0 ,   M_SQL_CONN_TYPE_PRIMARY = 1 ,   M_SQL_CONN_TYPE_READONLY = 2 }

Functions
M_bool	M_sql_connpool_add_trace (M_sql_connpool_t *pool, M_sql_trace_cb_t cb, void *cb_arg)
void	M_sql_connpool_trace_stalls (M_sql_connpool_t *pool, M_uint64 max_query_s, M_uint64 max_trans_idle_s, M_uint64 max_trans_s)
void	M_sql_trace_ignore_tranfail (M_sql_stmt_t *stmt)
const char *	M_sql_trace_get_error_string (const M_sql_trace_data_t *data)
M_sql_error_t	M_sql_trace_get_error (const M_sql_trace_data_t *data)
M_uint64	M_sql_trace_get_duration_ms (const M_sql_trace_data_t *data)
M_uint64	M_sql_trace_get_total_duration_ms (const M_sql_trace_data_t *data)
M_sql_conn_type_t	M_sql_trace_get_conntype (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_conn_id (const M_sql_trace_data_t *data)
const char *	M_sql_trace_get_query_user (const M_sql_trace_data_t *data)
const char *	M_sql_trace_get_query_prepared (const M_sql_trace_data_t *data)
char *	M_sql_trace_get_query_formatted (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_bind_cols (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_bind_rows (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_bind_rows_current (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_bind_rows_processed (const M_sql_trace_data_t *data)
M_bool	M_sql_trace_get_has_result_rows (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_affected_rows (const M_sql_trace_data_t *data)
size_t	M_sql_trace_get_result_row_count (const M_sql_trace_data_t *data)

Detailed Description

SQL Pool Tracing

Typedef Documentation

M_sql_trace_data_t

typedef struct M_sql_trace_data M_sql_trace_data_t

Typedef for trace data

M_sql_trace_cb_t

typedef void(* M_sql_trace_cb_t) (M_sql_trace_t event_type, const M_sql_trace_data_t *data, void *arg)

Callback prototype used for tracing SQL subsystem events.

Parameters

[in]	event_type	The event type
[in]	data	The metadata about the event, use the M_sql_trace_*() functions to get details.
[in]	arg	User-supplied argument passed to the trace callback.

Enumeration Type Documentation

M_sql_trace_t

enum M_sql_trace_t

Event types for SQL pool tracing

Enumerator
M_SQL_TRACE_CONNECTING	Starting a new connection
M_SQL_TRACE_CONNECTED	New connection was successfully started
M_SQL_TRACE_CONNECT_FAILED	New connection failed
M_SQL_TRACE_DISCONNECTING	Starting graceful disconnect from server (idle, etc)
M_SQL_TRACE_DISCONNECTED	Graceful disconnect has completed
M_SQL_TRACE_BEGIN_START	SQL Transaction Begin starting. This may internally also call M_SQL_TRACE_EXECUTE_START and M_SQL_TRACE_EXECUTE_FINISH depending on how the driver handles transactions.
M_SQL_TRACE_BEGIN_FINISH	SQL Transaction Begin completed (possibly failed)
M_SQL_TRACE_ROLLBACK_START	SQL Transaction Rollback starting. This may internally also call M_SQL_TRACE_EXECUTE_START and M_SQL_TRACE_EXECUTE_FINISH depending on how the driver handles transactions.
M_SQL_TRACE_ROLLBACK_FINISH	SQL Transaction Rollback completed (possibly failed)
M_SQL_TRACE_COMMIT_START	SQL Transaction Commit starting. This may internally also call M_SQL_TRACE_EXECUTE_START and M_SQL_TRACE_EXECUTE_FINISH depending on how the driver handles transactions. On failure, this may also flow through M_SQL_TRACE_ROLLBACK_START and M_SQL_TRACE_ROLLBACK_FINISH
M_SQL_TRACE_COMMIT_FINISH	SQL Transaction Commit completed (possibly failed)
M_SQL_TRACE_EXECUTE_START	SQL Statement Execution started
M_SQL_TRACE_EXECUTE_FINISH	SQL Statement Execution finished (possibly failed)
M_SQL_TRACE_FETCH_START	SQL Statement Fetching result data started
M_SQL_TRACE_FETCH_FINISH	SQL Statement Fetching result data finished (possibly failed or canceled)
M_SQL_TRACE_CONNFAIL	Connection to SQL server failed unexpectedly
M_SQL_TRACE_TRANFAIL	SQL Transaction failed (duplicative of M_SQL_TRACE_EXECUTE_FINISH), but only on fatal (non-retryable) failure
M_SQL_TRACE_DRIVER_DEBUG	Debug/Informational message generated by driver
M_SQL_TRACE_DRIVER_ERROR	Error message generated by driver
M_SQL_TRACE_STALL_QUERY	Possibly stalled query. Only generated via M_sql_connpool_trace_stalls()
M_SQL_TRACE_STALL_TRANS_IDLE	Possibly stalled transaction that is not executing any queries. Only generated via M_sql_connpool_trace_stalls()
M_SQL_TRACE_STALL_TRANS_LONG	Possibly stalled transaction due to long run time. Only generated via M_sql_connpool_trace_stalls()

M_sql_conn_type_t

enum M_sql_conn_type_t

Connection type

Enumerator
M_SQL_CONN_TYPE_UNKNOWN	Unknown, probably misuse
M_SQL_CONN_TYPE_PRIMARY	Primary (read/write) sub-pool
M_SQL_CONN_TYPE_READONLY	Read Only sub-pool

Function Documentation

M_sql_connpool_add_trace()

M_bool M_sql_connpool_add_trace	(	M_sql_connpool_t *	pool,
		M_sql_trace_cb_t	cb,
		void *	cb_arg
	)

Add a trace callback to the SQL subsystem.

Only one trace callback can be registered per pool. If one is already registered, it will be replaced.

Note

This must be called prior to M_sql_connpool_start()

Parameters

[in]	pool	Initialized pool object by M_sql_connpool_create().
[in]	cb	Callback to register
[in]	cb_arg	User-supplied argument to pass to callback.

Returns

M_TRUE on success, M_FALSE on misuse

M_sql_connpool_trace_stalls()

void M_sql_connpool_trace_stalls	(	M_sql_connpool_t *	pool,
		M_uint64	max_query_s,
		M_uint64	max_trans_idle_s,
		M_uint64	max_trans_s
	)

Run a check to see if there are any stalled connections as per the provided criteria.

This function is used to help identify if a connection or transaction appear to be stalled, whether due to user error, a server issue, or a bug in the client side library.

It depends on M_sql_connpool_add_trace() having already been called and will iterate across all non-idle connections looking for stalls. Output from this function will use the registered trace callback.

Since SQL is not implemented asynchronously this must be called from another thread periodically to look for stalls.

Parameters

[in]	pool	Initialized pool object by M_sql_connpool_create(), that has had M_sql_connpool_add_trace() called.
[in]	max_query_s	Maximum query run time before it is considered stalled in seconds. A good starting place may be 60s. Use 0 to ignore this metric.
[in]	max_trans_idle_s	Maximum transaction time where a connection is held but the transaction is idle, helps identify user error such as leaving a transaction open or a long-running operation while holding a connection. A good starting place can be fairly short, like 10s. Use 0 to ignore this metric.
[in]	max_trans_s	Maximum transaction overall time, regardless if queries are actively executing. Can help identify transactions that might need to be split into smaller operations. A good starting place should be fairly long, such as 5minutes/300s. Use 0 to ignore this metric.

M_sql_trace_ignore_tranfail()

void M_sql_trace_ignore_tranfail

(

M_sql_stmt_t *

stmt

)

Set a flag on the statement to ensure a M_SQL_TRACE_TRANFAIL is not triggered in the event of a failure.

This is used to silence warnings in the trace system for failures that may be expected. For instance, this is used internally by M_sql_table_exists() otherwise a warning might be emitted when the table does not exist.

Parameters

[in]

stmt

Initialized statement handle to apply flag

M_sql_trace_get_error_string()

const char * M_sql_trace_get_error_string

(

const M_sql_trace_data_t *

data

)

Retrieve the error string containing the most recent error condition.

Only Valid on:

• M_SQL_TRACE_CONNECT_FAILED
• M_SQL_TRACE_BEGIN_FINISH
• M_SQL_TRACE_ROLLBACK_FINISH
• M_SQL_TRACE_COMMIT_FINISH
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_CONNFAIL
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_DRIVER_DEBUG
• M_SQL_TRACE_DRIVER_ERROR

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Pointer to error string, or NULL if no error string available.

M_sql_trace_get_error()

M_sql_error_t M_sql_trace_get_error

(

const M_sql_trace_data_t *

data

)

Retrieve the most recent error condition identifier.

Only valid on:

• M_SQL_TRACE_CONNECT_FAILED
• M_SQL_TRACE_BEGIN_FINISH
• M_SQL_TRACE_ROLLBACK_FINISH
• M_SQL_TRACE_COMMIT_FINISH
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_CONNFAIL
• M_SQL_TRACE_TRANFAIL

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Most recent error code, possibly M_SQL_ERROR_SUCCESS if no error.

M_sql_trace_get_duration_ms()

M_uint64 M_sql_trace_get_duration_ms

(

const M_sql_trace_data_t *

data

)

Retrieve the duration, in milliseconds of operation

Only valid on:

• M_SQL_TRACE_CONNECTED - Time to establish connection
• M_SQL_TRACE_DISCONNECTING - Time connection was up before disconnect was attempted.
• M_SQL_TRACE_DISCONNECTED - Time connection took to disconnect (from start of disconnect)
• M_SQL_TRACE_CONNECT_FAILED - Time it took for connection to fail.
• M_SQL_TRACE_BEGIN_FINISH - Time it took to begin a transaction.
• M_SQL_TRACE_ROLLBACK_FINISH - Time it took to rollback.
• M_SQL_TRACE_COMMIT_FINISH - Time it took to commit a transaction.
• M_SQL_TRACE_EXECUTE_FINISH - Time it took to execute the transaction.
• M_SQL_TRACE_FETCH_FINISH - Time it took to retrieve the rows after execution.
• M_SQL_TRACE_CONNFAIL - Time connection was up before a failure was detected.
• M_SQL_TRACE_TRANFAIL - Time query execution took before failure was returned.
• M_SQL_TRACE_STALL_QUERY - Current runtime of query.
• M_SQL_TRACE_STALL_TRANS_IDLE - Time since last query was performed.
• M_SQL_TRACE_STALL_TRANS_LONG - Time since transaction was started.

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Time in milliseconds.

M_sql_trace_get_total_duration_ms()

M_uint64 M_sql_trace_get_total_duration_ms

(

const M_sql_trace_data_t *

data

)

Retrieve the total duration of a sequence of events, for a limited set of events.

Only valid on:

• M_SQL_TRACE_FETCH_FINISH - Total time of query execution plus row fetch time.
• M_SQL_TRACE_DISCONNECTED - Total time from connection establishment to disconnect end.
• M_SQL_TRACE_STALL_TRANS_IDLE - Total time since transaction was started.

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Time in milliseconds.

M_sql_trace_get_conntype()

M_sql_conn_type_t M_sql_trace_get_conntype

(

const M_sql_trace_data_t *

data

)

Retreive type of connection (Primary vs ReadOnly)

Available on all

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Type of connection

M_sql_trace_get_conn_id()

size_t M_sql_trace_get_conn_id

(

const M_sql_trace_data_t *

data

)

Retrieve the internal connection id, enumerated from 0 - max_conns for each primary and readonly member pool.

Available on all

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Identifier

M_sql_trace_get_query_user()

const char * M_sql_trace_get_query_user

(

const M_sql_trace_data_t *

data

)

Retreive the user-supplied query being executed.

Only available on:

• M_SQL_TRACE_EXECUTE_START
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

User-supplied query.

M_sql_trace_get_query_prepared()

const char * M_sql_trace_get_query_prepared

(

const M_sql_trace_data_t *

data

)

Retrieve string for prepared query (rewritten by driver) that has been executed by the server.

Only available on:

• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Rewritten user-supplied query.

M_sql_trace_get_query_formatted()

char * M_sql_trace_get_query_formatted

(

const M_sql_trace_data_t *

data

)

Retrieve a fake formatted string with substituted data instead of seeing prepared statement place holders. This can be useful for debugging but is quite heavy.

Only available on:

• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Rewritten query. Must be M_free()'d by caller.

M_sql_trace_get_bind_cols()

size_t M_sql_trace_get_bind_cols

(

const M_sql_trace_data_t *

data

)

Retrieve the number of request columns bound to the query

Only available on:

• M_SQL_TRACE_EXECUTE_START
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Number of columns bound to the query by the caller.

M_sql_trace_get_bind_rows()

size_t M_sql_trace_get_bind_rows

(

const M_sql_trace_data_t *

data

)

Retrieve the number of request rows (total) bound to the query

Only available on:

• M_SQL_TRACE_EXECUTE_START
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Total number of rows bound to the query by the caller.

M_sql_trace_get_bind_rows_current()

size_t M_sql_trace_get_bind_rows_current

(

const M_sql_trace_data_t *

data

)

Retrieve the number of request rows (current subset) bound to the query

Only available on:

• M_SQL_TRACE_EXECUTE_START
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Subset of rows currently being executed.

M_sql_trace_get_bind_rows_processed()

size_t M_sql_trace_get_bind_rows_processed

(

const M_sql_trace_data_t *

data

)

Retrieve the number of processed rows bound to the query (including current subset)

Only available on:

• M_SQL_TRACE_EXECUTE_START
• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_TRANFAIL
• M_SQL_TRACE_STALL_QUERY
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Subset of rows that have been executed or are being executed.

M_sql_trace_get_has_result_rows()

M_bool M_sql_trace_get_has_result_rows

(

const M_sql_trace_data_t *

data

)

Retrieve whether or not the query potentially has result data.

If the query has result data, and this is a M_SQL_TRACE_EXECUTE_FINISH, then you know for sure M_SQL_TRACE_FETCH_START/M_SQL_TRACE_FETCH_FINISH will also be called later.

Only available on:

• M_SQL_TRACE_EXECUTE_FINISH
• M_SQL_TRACE_FETCH_START
• M_SQL_TRACE_FETCH_FINISH
• M_SQL_TRACE_STALL_QUERY (possibly)
• M_SQL_TRACE_STALL_TRANS_LONG (possibly)

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

M_TRUE if the query could have result data, M_FALSE otherwise.

M_sql_trace_get_affected_rows()

size_t M_sql_trace_get_affected_rows

(

const M_sql_trace_data_t *

data

)

Retrieve the number of rows affected by a query.

This mostly applies to INSERT/UPDATE/DELETE type queries.

Only available on:

• M_SQL_TRACE_EXECUTE_FINISH

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Count of affected rows.

M_sql_trace_get_result_row_count()

size_t M_sql_trace_get_result_row_count

(

const M_sql_trace_data_t *

data

)

Retrieve the total number of rows fetched from the server.

Only available on:

• M_SQL_TRACE_FETCH_FINISH

Parameters

[in]

data

Trace Data structure passed to trace callback

Returns

Count of retrieved rows.