vsql_stat_ch extension for MySQL

vsql_stat_ch records one event for every statement the server finishes and ships them to ClickHouse in batches. Query history then lives somewhere you can aggregate it, without a collector process between the two. It speaks ClickHouse’s native protocol or HTTP, and you can switch between them while the server is running.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-stat-ch
License	GPL-2.0
Capability	Preview

This extension uses four preview capabilities, so the server must be started with --vsql_allow_preview_extensions=ON. INSTALL EXTENSION is refused otherwise.

Install

vsql_stat_ch is not bundled with the server, so build it before installing it. Clone the repository with its submodules, because it vendors a ClickHouse client:

git clone --recurse-submodules https://github.com/villagesql/vsql-stat-ch.git
cd vsql-stat-ch
VillageSQL_BUILD_DIR=/path/to/villagesql/build ./build.sh

Copy the resulting vsql_stat_ch.veb into the directory named by the veb_dir system variable, then install it:

INSTALL EXTENSION vsql_stat_ch;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_stat_ch';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_stat_ch   |
+----------------+

What it adds

It adds a capture path, a set of settings, and a set of status counters, and no SQL functions.

Setting	Default	What it controls
`vsql_stat_ch.enabled`	`OFF`	Whether statements are captured
`vsql_stat_ch.transport`	`native`	`native` for port 9000, or `http` for the HTTP interface
`vsql_stat_ch.clickhouse_host`	`localhost`	The ClickHouse host, for the native transport
`vsql_stat_ch.clickhouse_port`	`9000`	Its port
`vsql_stat_ch.clickhouse_url`	empty	The base URL, for the HTTP transport
`vsql_stat_ch.clickhouse_database`	`default`	The target database
`vsql_stat_ch.clickhouse_table`	`events_raw`	The target table
`vsql_stat_ch.clickhouse_user`, `vsql_stat_ch.clickhouse_password`	`default`, empty	The credentials
`vsql_stat_ch.compression`	`1`	0 for none, 1 for LZ4, 2 for zstd
`vsql_stat_ch.batch_max`	`10000`	How many events go in one batch
`vsql_stat_ch.flush_interval_ms`	`1000`	How long a partial batch waits
`vsql_stat_ch.queue_capacity`	`100000`	How many events may wait in memory, at least `1000`; set it before the first event is captured or with `SET PERSIST` and a restart
`vsql_stat_ch.statement_max_bytes`	`4096`	Where statement text is truncated
`vsql_stat_ch.http_timeout_secs`	`10`	The timeout for the HTTP transport

Example

Point it at a ClickHouse instance and start capturing:

SET GLOBAL vsql_stat_ch.clickhouse_host = 'clickhouse';
SET GLOBAL vsql_stat_ch.clickhouse_database = 'default';
SET GLOBAL vsql_stat_ch.clickhouse_table = 'events_raw';
SET GLOBAL vsql_stat_ch.enabled = ON;

Watch what it is doing through the status counters:

SHOW STATUS LIKE 'vsql_stat_ch%';

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| vsql_stat_ch.events_archived  | 0     |
| vsql_stat_ch.events_captured  | 0     |
| vsql_stat_ch.events_dropped   | 0     |
| vsql_stat_ch.flush_errors     | 0     |
| vsql_stat_ch.last_flush_utime | 0     |
| vsql_stat_ch.queue_depth      | 0     |
+-------------------------------+-------+

events_dropped climbing means the queue filled faster than it drained, so either ClickHouse is slow to answer or queue_capacity is too small for the statement rate.

Statement text is truncated at statement_max_bytes, and statement text can carry literals from your data. Decide what your ClickHouse table is allowed to hold before you turn capture on.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

Example

See also

​Install

​What it adds

​Example

​See also

Install

What it adds

Example

See also