Database optimization

Database selection

Alibaba Cloud database products span four categories: relational databases, NoSQL databases, data warehouses, and database ecosystem tools, covering the full data lifecycle. Major relational databases include ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for SQL Server, PolarDB for MySQL, PolarDB for PostgreSQL, and PolarDB for Xscale. Major NoSQL databases include Tair (Redis OSS-compatible), ApsaraDB for MongoDB, Tair, and Lindorm. Choose a database based on your business stage, traffic volume, scale, and workload characteristics, and review your choice during and after cloud migration. For example, a startup or a business that has just migrated to the cloud can begin with ApsaraDB RDS for MySQL HA instances for disaster recovery, scale to read/write splitting for transactional workloads, and adopt PolarDB for high-concurrency, elasticity, or mass-storage scenarios.

Common issues and optimizations

• Slow SQL: Common causes include poor schema design, missing indexes, excessive row scans, and undersized instance types. Optimize queries, upgrade instances, or split large batch data queries and imports into off-peak jobs. For complex diagnostics, use Database Autonomy Service (DAS).

• Stacked active threads: When SQL statements pile up, active connections exhaust the connection pool and can freeze the instance. Common causes and fixes: slow SQL stacking — use SQL throttling or kill sessions; insufficient table cache — queries enter the `Opening table` state under high QPS or when there are many tables, adjust `table_open_cache` and `table_open_cache_instances`; metadata locks (MDLs) — DDL operations block on uncommitted transactions or slow queries, then block subsequent SQL. Abort the blocking transaction, query, or DDL statement to resolve.

• Memory, disk, and I/O: Issues may stem from the buffer pool, temporary tables, indexes, or binary logging (binlog). Use Database Autonomy Service (DAS) for diagnostics and optimization.

Database Autonomy Service (DAS) applies machine learning and expert experience to automate database self-perception, self-healing, self-optimization, self-maintenance, and self-protection, reducing manual overhead and service failures. Use DAS for performance diagnostics and optimization. Key features include quick diagnostics, real-time performance monitoring, lock analysis, capacity assessment, Performance Insight, Performance Trend, slow SQL analysis, SQL Explorer, audit, and automatic SQL throttling.