Unlocking MySQL: DBA Daily Tasks and Proven Performance Optimization Strategies

DBA Daily Work

First, we look at the specific tasks of a DBA: backup and recovery, monitoring, cluster building and scaling, data migration, and high availability.

Understanding these functions requires deeper knowledge of architecture and how to handle failures and complaints.

Therefore we need to understand caching/threads, SQL optimization, storage engines, SQL auditing, locks, architecture, kernel principles, and source‑code customization. DBA work is extensive.

Today I share my experience from over a year of DBA work, hoping to inspire and help.

1. MySQL Performance Optimization

Performance optimization aims to make MySQL run faster and smoother. Before starting, I propose three key points: Why? What? How? Why do we need optimization?

Our operations reported that query time increased from 1 second to 10 seconds, prompting optimization. The goal is to reduce response time, e.g., from 1 s to 0.01 s.

Second, we must identify the root cause of performance degradation and target it.

1.1 MySQL Optimization Basic Process

There is a basic process for MySQL optimization.

First, log into the OS and check resource usage (CPU, memory, etc.) to find bottlenecks.

Identify which process consumes the high resource.

Usually the MySQL layer is responsible when CPU usage exceeds 70%.

If a large query is running, the issue may be the statement itself.

Use MySQL monitoring or logs to pinpoint the problem.

If OS resources appear normal, we can distinguish three cases: OS issue, DB instance configuration issue, or problematic SQL statements.

1.2 Key Points of Optimization

From the basic process we can identify several key optimization points.

Application access optimization: request handling, data storage, network interaction.

Server hardware selection: choose hardware based on MySQL characteristics, e.g., SSD for data, SAS for logs.

Operating system optimization before deploying the DB.

Database‑wide optimization, not just the DB engine.

1.2.1 Application Access Optimization

Reduce data access to minimize disk I/O; keep hot data in memory, which can improve performance dramatically.

Reduce the amount of data returned to lower network transmission latency.

Reduce interaction次数 to cut cumulative latency.

Server hardware selection example: HP DL360G9 (2 × e5‑2650V4, 8 × 32 GB RAM, 6 × 1.2 TB SAS, 4 × 10 GbE, 4 × 1 GbE, IPMI). If the DBA has authority, place data on SSD and logs on SAS.

1.2.2 OS‑Level Optimization

Prefer Linux; avoid swap, keep a small swap value (10) to prevent OOM while not using virtual memory excessively.

Disable NUMA to keep CPU and memory on the same side for dedicated DB servers.

Network card optimization via bonding multiple physical NICs.

Disk scheduler: use the Deadline algorithm rather than NOOP or CFQ for MySQL workloads.

File‑system recommendations: XFS, Ext4, noatime, nobarrier.

1.2.3 DB Instance Optimization

Standardize and configure key MySQL parameters that greatly affect instance performance.

1.2.4 SQL Statement Optimization

Teach developers efficient SQL principles so business code does not generate problematic statements.

1.3 Index Design

Covering indexes allow queries to be satisfied entirely from the index. Avoid low‑selectivity indexes, avoid leading‑wildcard LIKE, and consider separate indexes for filtering and sorting.

2. Automation Operations Practice

Automation provides tools for DBA to streamline tasks, especially at scale (hundreds to thousands of databases).

Standardized installation and deployment packages.

Automated backups using innobackup, scripts, and cron (weekly full backup, pre‑change backup).

Automated monitoring with Zabbix, alert thresholds, SMS/email notifications.

Automated deep inspection via Ansible scripts and generated reports.

Automated failover using Keepalive, VRRP scripts.

Automated node expansion when a node fails.

Automated security auditing with plugins and log analysis.

Automated password auditing enforcing strong password policies.

Automated log analysis using Percona pt‑query‑digest.

Automated data verification with scheduled scripts.

Automated backup cleanup (remove files older than two months).

Automated log rotation for large slow/error logs.

These practices represent our accumulated experience in database operations at Mobile Cloud.