Database Operations, Optimization, High Availability and Self‑Service – Insights from DBA Yang Jianrong

Introduction

The rapid evolution of database technology raises questions about how to improve database operations, design, and high availability. Yang Jianrong, a senior DBA from dbaplus, shared his experience in an online community salon, covering technical changes, optimization, high‑availability design, and business self‑service.

Three Key Aspects Before the Formal Share

1. Process standardization – improving workflows and specifications. 2. Technical support – strengthening backup/recovery and cluster‑wide recovery capabilities. 3. Security policies – establishing robust security mechanisms.

1. Technical Changes

Database operations can be summarized into four points: technology changes, optimization & design, business high‑availability, and business self‑service. A personal anecdote describes a MySQL slave that lacked binlog, leading to a critical failure and highlighting the importance of proper configuration.

2. Optimization and Design

Optimization now focuses on simplifying SQL rather than extensive tuning. Large‑scale migrations (e.g., 500k QPS reduced to 20k QPS) demonstrate that redesign can improve stability. For tables with tens of millions of rows, optimization is approached in three dimensions: scale (tens of millions), object (tables), and goal (optimization). Strategies include partitioning, date‑based archiving, and using NoSQL for extremely high‑DML workloads.

Key steps for large‑table optimization:

Understand table properties and choose appropriate optimization layers.

Apply index and query‑plan tuning as a small part of overall improvement.

Consider middleware for read/write separation and HTAP concepts.

3. Business High‑Availability

High‑availability should consider both system/database layers and overall business continuity. A case study describes a data‑migration scenario with 800 GB of data, using a “bypass” architecture to copy data to a new service, perform full and incremental sync, and switch traffic with minimal downtime. Incremental sync is combined with online verification to ensure data consistency.

4. Business Self‑Service

Self‑service reduces unnecessary communication. Examples include automated SQL deployment, automated slow‑log analysis, and visual dashboards. The workflow involves collecting slow logs, analyzing them with PT‑tools, storing results via APIs, and presenting them through front‑end visualizations. Plugins can provide optimization suggestions and blacklist SQL filtering.

Q&A Highlights

Q: Is the monitoring platform internally developed? A: Yes, built with a front‑end/back‑end separation; back‑end provides APIs, front‑end renders visualizations.

Q: How to quickly fix problematic SQL? A: Use online repair via binlog flashback or targeted SQL statements.

Q: Archiving strategies for tables with massive DML? A: Use date‑based or partition‑based archiving, possibly moving data to data‑warehouse or big‑data platforms; consider NoSQL for extreme scales.

Q: Recommended open‑source tools for building a DB ops system? A: Tools like Ops Manager can be extended with a front‑end/back‑end architecture.

Q: Slow‑log platform architecture? A: Collect logs from multiple IDC nodes, aggregate, analyze with PT‑tools, store via APIs, and visualize; optional plugins provide automated optimization advice.

Q: How to detect and locate deadlocks quickly? A: Enable MySQL 5.7+ deadlock logging to error log, forward logs to Elasticsearch, or set up monitoring alerts.

Q: Career advice for aspiring DBAs? A: Focus on mastering one database, then broaden knowledge; continuously summarize problems to build a personal knowledge base.

Q: Transitioning from Oracle to MySQL? A: The transition is manageable; Oracle concepts help understand MySQL, while MySQL offers strong community support and high‑availability features.

Q: Data synchronization between primary and standby services? A: Use time‑based incremental sync, online verification, and cache‑based pipelines to avoid impacting live services.

Conclusion

Database operations involve fragmented daily tasks; systematic summarization and knowledge‑base building are essential for long‑term growth. Yang Jianrong’s experience illustrates the importance of process, technology, security, and continuous learning in DBA work.