[BUG] MOI IDC (QA) MO OOM

## Information

- Environment: QA
- MOI namespace: `moi-qa`
- MatrixOne namespace / cluster: `mo-new-moi` / `moi-mo-2606`
- MOI release first correlated with the regression: `5.1.0-302a531e-20260729`, deployed at `2026-07-30 05:39 CST`
- Relevant implementation commit included by that release: `85245b375` (`fix(catalog): move external metadata discovery into core jobs (#14234)`)
- MatrixOne image: `v4.1.3-a185c23b3-2026-07-21`
- CN topology: 2 replicas, each limited to `36GiB`; `GOMEMLIMIT=35000MiB`, `GOGC=200`, shared memory cache capacity `12GiB`

## Describe the bug

The Catalog metadata discovery job performs a full inventory and reconciliation of every active workspace on a fixed 5-second cadence.

The scheduler has a 1-second base tick, 8 workers and an in-flight guard. The guard prevents one pass from overlapping itself, but it does not avoid rescanning unchanged workspaces after the pass finishes. At QA scale, a full pass currently finishes in roughly 18 seconds, so all active workspaces and their databases are continuously rescanned approximately every 18 seconds.

This creates sustained SQL and memory pressure on MatrixOne. Both CNs repeatedly reach the `36GiB` cgroup limit and are terminated with `OOMKilled` / exit code 137. The most affected CN recently survived only 16–25 minutes between OOM restarts.

This issue is scoped to the MOI metadata discovery workload and CN OOM. The independent QA Log Service problem is being investigated separately and is intentionally excluded.

## Timeline and observed impact

- `2026-07-30 05:39 CST`: MOI release `302a531e` deployed; it includes the new Catalog metadata discovery scheduler.
- `05:44 CST`: first CN OOM in the dense restart sequence.
- Around `05:50 CST`: aggregate SQL request rate increased to approximately `600–2000 requests/s` per CN.
- Previous comparable 12-hour period: approximately `130–165 requests/s` per CN.
- Hourly statement volume increased from approximately `0.8–1.6M/hour` to `6.4–8.5M/hour`.
- The affected CN restart count increased from 16 to 25 during the day. Recent lifetimes were approximately 16 minutes and 25 minutes.
- Immediately before the latest OOM, CN logged:
- RSS: `34.49GiB`
- cgroup memory: `36GiB`
- memory pressure state: `hard`
- workspace-accounted demand: `251.5GiB`
- cache eviction was attempted, but the process was subsequently OOM-killed.

## Job behavior

Relevant implementation properties in `moi-core/catalog/pkg/jobs` at commit `85245b375`:

- `MetadataDiscoveryJob.Interval()` returns a fixed `5 * time.Second`.
- Scheduler `baseTick = 1s`.
- Scheduler `WorkerCount = 8`.
- Every pass lists all workspaces and enqueues every active workspace.
- Each workspace opens a short-lived owner database connection.
- Each workspace runs `SHOW DATABASES`.
- Catalog assignments are fully listed with pagination.
- Every discovered user database runs `SyncMetadata`, even when there is no known change.
- `SyncMetadata` inventories all tables and reconciles Catalog metadata.

## Schemas and tables involved

### Catalog system database (`moi_qa` in QA)

Used to enumerate workspaces and resolve workspace owner/admin database credentials. Observed statements include access to:

- `workspaces`
- `db_users`
- encrypted database credential / database instance records
- `moi_version` and upgrade state tables from concurrent Catalog control-plane processing

### Per-workspace MOI metadata database (`moi`)

Repeated reads and reconciliation touch:

- `catalog`
- `catalog_database`
- `catalog_table`

### MatrixOne metadata schemas in each workspace account

Repeated inventory queries touch:

- `information_schema.SCHEMATA`
- `information_schema.TABLES`
- `mo_catalog.mo_database`

## Exact deployed SQL and execution profile

The following is not reconstructed or simplified SQL. It is extracted from the exact QA-deployed MatrixFlow commit `302a531e` (which contains implementation commit `85245b375`). The source entry points are:

- `moi-core/catalog/pkg/jobs/metadata_discovery.go`
- `moi-core/catalog/pkg/jobs/scheduler.go`
- `moi-core/catalog/pkg/service/storage/system/impl.go`
- `moi-core/catalog/pkg/service/storage/tenant/impl.go`
- `moi-core/catalog/pkg/service/storage/tenant/system_resource_display.go`
- `moi-core/catalog/pkg/service/database/metadata_executor.go`
- `moi-core/catalog/pkg/service/database/service_impl.go`

### Catalog system-database SQL

Once per scheduler pass, Catalog enumerates every workspace:

```sql
SELECT id, name, description, owner_id, owner_revision, owner_changed_at, account_name, status, created_at, updated_at
FROM moi_qa.workspaces;
```

For every accepted active workspace, `GetOwnerDBConnection` runs these exact queries before opening the workspace owner connection:

```sql
SELECT id, name, description, owner_id, owner_revision, owner_changed_at, account_name, status, created_at, updated_at
FROM moi_qa.workspaces
WHERE id = ?;

SELECT id, user_id, workspace_id, db_username, is_admin, created_at, updated_at
FROM moi_qa.db_users
WHERE workspace_id = ?;

SELECT encrypted_db_password
FROM moi_qa.db_users
WHERE user_id = ? AND workspace_id = ?;
```

### Per-workspace inventory SQL

A new short-lived owner `*sql.DB` is opened for every workspace and closed after that workspace completes. It first runs exactly:

```sql
SHOW DATABASES;
```

The code removes these names in memory and synchronizes every remaining database: `information_schema`, `mysql`, `mo_catalog`, `system_metrics`, `system`, `mo_task`, `moi`.

`ListCatalogs`, with `page_size = 100` and offset decoded from the page token, runs in its own tenant transaction:

```sql
START TRANSACTION;

SELECT COUNT(*) FROM catalog;

SELECT catalog_id, catalog_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog
ORDER BY catalog_id ASC
LIMIT ? OFFSET ?;

COMMIT;
```

For each catalog returned above, `ListDatabases`, also with `page_size = 100`, runs in its own tenant transaction:

```sql
START TRANSACTION;

SELECT catalog_id, catalog_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog
WHERE catalog_id = ?;

SELECT COUNT(*)
FROM catalog_database
WHERE catalog_id = ?;

SELECT database_id, catalog_id, database_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog_database
WHERE catalog_id = ?
ORDER BY database_id ASC
LIMIT ? OFFSET ?;

COMMIT;
```

Both list paths also resolve display mappings. For `N` returned resources the source generates exactly one predicate per resource field (`name` and `description`), so `N × 2` parenthesized predicates and `N × 6` bound values are sent:

```sql
SELECT resource_type, resource_id, field, display_owner, display_key, default_text, created_at, updated_at
FROM system_resource_display_mapping
WHERE (resource_type = ? AND resource_id = ? AND field = ?)
OR (resource_type = ? AND resource_id = ? AND field = ?);
```

The second predicate above is repeated by the source for every additional field after the first. This is a dynamically generated statement, not a fixed SQL literal; the exact statement length is therefore determined by the number of catalogs or databases returned in that page.

### Per-database `SyncMetadata` SQL

Every non-system database from `SHOW DATABASES` starts a separate tenant transaction and runs the following stable-state path:

```sql
START TRANSACTION;

SELECT catalog_id, catalog_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog
WHERE catalog_id = ?;

SELECT COUNT(*)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = ?;

SELECT database_id, catalog_id, database_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog_database
WHERE database_name = ?;

SELECT dat_type
FROM mo_catalog.mo_database
WHERE datname = ?;

SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ?
AND TABLE_TYPE = 'BASE TABLE';

SELECT table_id, database_id, catalog_id, table_name, comment, extra,
created_at, created_by, updated_at, updated_by
FROM catalog_table
WHERE database_id = ?
ORDER BY table_id ASC;

COMMIT;
```

For a subscription database, the `information_schema.TABLES` statement is replaced by the exact dynamically quoted statement:

```sql
SHOW TABLES FROM ``;
```

When discovered metadata differs, the same transaction conditionally executes the following exact source statements:

```sql
INSERT INTO catalog_database (catalog_id, database_name, comment, created_by, updated_by)
VALUES (?, ?, ?, ?, ?);

SELECT database_id, catalog_id, database_name, comment, created_at, created_by, updated_at, updated_by
FROM catalog_database
WHERE database_id = ?;

UPDATE catalog_database
SET catalog_id = ?, database_name = ?, comment = ?, updated_by = ?, updated_at = CURRENT_TIMESTAMP
WHERE database_id = ?;

DELETE FROM catalog_table WHERE table_id = ?;
DELETE FROM catalog_table WHERE database_id = ?;
DELETE FROM catalog_database WHERE database_id = ?;

INSERT INTO catalog_table (database_id, catalog_id, table_name, comment, extra, created_by, updated_by)
VALUES (?, ?, ?, ?, ?, ?, ?);
```

### Exact client/scheduling behavior required to reproduce the load

A plain SQL file is not an identical reproduction of this workload. The deployed code uses Go `database/sql` plus `go-sql-driver/mysql` with parameter placeholders. MatrixOne therefore observes parameterized statements as `Prepare -> Execute -> Deallocate`. Each list operation and each per-database `SyncMetadata` call has its own `START TRANSACTION -> COMMIT` pair.

The deployed scheduler behavior is:

- fixed interval: `5s`
- scheduler tick: `1s`
- workers: `8`
- queue capacity: `1024`
- one complete pass enqueues every active workspace
- overlap is prevented only while the previous pass still has queued/running units
- if a pass takes longer than 5 seconds, the next 1-second scheduler tick after completion immediately starts another full pass
- one short-lived owner database connection per workspace
- one `SHOW DATABASES` per workspace
- one `SyncMetadata` transaction per discovered non-system database

Therefore an identical replay must use the same client protocol behavior, transaction boundaries, 8-way concurrency, workspace/database cardinality and loop timing; replaying only the SQL strings with the MySQL CLI is not equivalent.

## QA data scale and measured workload

A five-minute Catalog log sample showed:

| Metric | Value |
|---|---:|
| Discovery passes enqueued | 17 |
| Workspace units enqueued | 1,347 |
| Workspace executions completed | 1,212 |
| Distinct successfully completed workspaces | 73 |
| Active workspace units per pass | 79–80 |
| Databases inventoried and synchronized | 5,039 |
| Average databases per completed workspace execution | 4.16 |
| Maximum databases in one workspace | 27 |
| Logged per-database sync failures | 0 |

In the same five-minute window, all `cloud_nonuser_sql` traffic recorded by MatrixOne was:

| Statement type | Count |
|---|---:|
| `Select` | 87,309 |
| `Prepare` | 86,776 |
| `Deallocate` | 86,776 |
| `Start Transaction` | 14,373 |
| `Commit` | 14,370 |
| `Show Databases` | 1,796 |
| `Update` | 623 |
| `Insert` | 219 |
| `Show Tables` | 89 |

The `Select` statements read approximately `17.0M` rows and scanned approximately `3.29GiB` in five minutes. `SHOW TABLES` recorded an additional approximately `536K` rows read and `105MiB` scanned. These `cloud_nonuser_sql` totals include a small amount of other MOI control-plane traffic, but discovery is the dominant workload during the sample.

## How to reproduce

1. Deploy a MOI build containing commit `85245b375` with Catalog jobs enabled.
2. Prepare approximately 80 active workspaces. QA currently has 79–80 eligible units per pass.
3. Give each workspace one or more user databases; the QA average is 4.16 and maximum is 27.
4. Start Catalog and observe `catalog job units enqueued` and `metadata discovery workspace completed` logs.
5. Confirm that a new full pass begins shortly after the previous pass completes, because the fixed 5-second interval has already elapsed.
6. Monitor:
- `mo_frontend_request_count`
- CN CPU and `container_memory_working_set_bytes`
- `kube_pod_container_status_restarts_total`
- CN `MemoryThrottler-*` logs
7. Observe sustained high SQL rate, CN RSS approaching `36GiB`, hard memory pressure and repeated `OOMKilled` restarts.

## Expected behavior / acceptance criteria

- Unchanged workspaces must not receive a complete database and table reconciliation every few seconds.
- Discovery scheduling must scale with actual metadata changes rather than `workspace_count × database_count × fixed cadence`.
- Concurrency and database work must remain bounded at QA scale.
- The implementation must expose job duration, queue depth, workspace/database counts and failures as metrics.
- With the current QA dataset, CN memory must stabilize below its cgroup limit and run for at least 30 minutes without OOM while normal QA traffic continues.
- SQL request rate should return near the pre-regression baseline when there are no metadata changes.