# Distribution DB — Instance Separation & Index Optimization
AI Summary
Purpose:
- Capture durable knowledge about the Distribution DB (DIST DB), its instance
separation work, and the index cleanup/optimization performed to keep binlog shipping to customer on-premise servers healthy.
Key points:
- DIST DB is the master that produces binlogs shipped to customers (see BTS).
Topology: dist-m (master), dist-r#IDC and dist-r#IN-HOUSE (replicas).
- Instance separation problem: as FILE COMPONENT data grew, the binlog volume
flowing to on-premise servers grew too. Low-spec on-premise servers could not import binlogs in time, which then impacted other data. Splitting FILE COMPONENT vs other instances isolates this.
- Separation approach: split via docker compose into separate instances, with
per-container resource limits (planned for future additional instances), plus download web server and Updater improvements per instance.
- Index work (2025-03): DIST DB and lab-master serve different purposes, so each
carried indexes the other did not need. Unused indexes were dropped and tables optimized to shrink size and improve performance.
- Index work required a careful, ordered maintenance procedure because
OPTIMIZE-type changes are NOT replicated through binlog; they must be run on each instance independently, and binlog generation pauses for 2-4 days.
Relevant when:
- Working on DIST DB topology, binlog shipping, replica health, index/optimize
maintenance, or customer download-server behavior.
Do not read full document unless:
- You need the exact maintenance step order or the customer 404 / IP-block
caveat.
Linked documents:
- [[bts.md]]
- [[infra-db-monitoring.md]]
- [[index.md]]
Open Questions
- Final status of download-web-server scrambling and Updater per-instance
improvements. Needs confirmation.
- Exact storage savings / performance gain from index optimization (metrics not
recorded). Needs confirmation.
Details
Instance separation
- Goal: apply separation cleanly to
dist-m,dist-r#IDC,dist-r#IN-HOUSE. - Driver: FILE COMPONENT data growth inflated binlog volume; weak on-premise
servers could not keep up with import, harming other data flows.
- Method: separate FILE COMPONENT and other instances at the docker compose
level, with container memory/resource limits, anticipating more instances later. Per-instance binlogs feed the download web server and Updater, which were to be improved accordingly (scrambling of the download web server was considered).
Index cleanup & optimization (2025-03-21 ~ 03-24)
- Rationale: DIST DB and lab-master diverged in usage; each held indexes unused
by the other. Drop unused indexes and run OPTIMIZE to shrink size.
- Key constraint: optimize/index DDL on these instances does NOT propagate via
binlog. It must be executed on each instance (dist-m, dist-r#IDC, dist-r#in-house) separately. During the work, binlog generation stops for about 2-4 days, which can look like an error but is expected.
- Ordered procedure (durable runbook):
1. dist-M: comment out the synchronizer cron. 2. dist-R#in-house: comment out backup + rsync cron. 3. dist-r#in-house: stop BTS (docker compose down). 4. Disable binlog monitoring (set alarm flag false in the allowed-IPs config on the monitoring server). 5. dist-M: generate an index-work binlog (./flush.sh). 6. dist-M: drop indexes; verify replicas; stop replication for optimizing. 7. Run OPTIMIZE on dist-m, dist-r#idc, dist-r#in-house (2-4 days; per-instance because not replicated). 8. Manipulate the binlog files created by the work: stop dist-m, find and remove the latest binlog, edit binlog.index, restart, verify. 9. dist-R#in-house: re-enable backup + rsync cron. 10. dist-M: re-enable synchronizer cron. 11. dist-r#in-house: restart BTS (docker compose up -d).
- Customer-impact caveat: while binlogs are unavailable, customers downloading
binlogs receive 404 for 3-4 days. Some customers auto-block IPs that return 404 for security reasons, so coordinate with customers to re-open the IP.
- Success criteria: table size reduced; Updater confirmed progressing afterward.