> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_stat_ch extension for MySQL

> The vsql_stat_ch extension records one event per finished MySQL statement and ships batches to ClickHouse over its native protocol or HTTP.

`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](https://github.com/villagesql/vsql-stat-ch) |
| **License** | GPL-2.0 |
| **Capability** | Preview |

> [!WARNING]
>
> 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:

```bash theme={null}
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:

```sql theme={null}
INSTALL EXTENSION vsql_stat_ch;
```

Confirm it is there:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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.

> [!NOTE]
>
> 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.

## See also

* [Managing extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/managing.md) — how extension settings are read and written
* [Preview capabilities](https://villagesql.com/docs/mysql-8.4/0.0.6/preview-capabilities.md) — what the preview flag turns on and what it promises
* [Available extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/extensions.md) — the full catalog
* [villagesql/vsql-stat-ch](https://github.com/villagesql/vsql-stat-ch) — source, the event schema, and the known limitations


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.