> ## 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 a preview capability, so the server must be started with
  `--vsql_allow_preview_extensions=ON`. `INSTALL EXTENSION` is refused
  otherwise.
</Warning>

## 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                       |
| `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.
</Note>

## See also

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