Databases 10 min read

Database Optimization Methodology and Parameter Tuning for Oracle, MySQL, PostgreSQL, and DM

This article presents a comprehensive methodology for database performance optimization, covering objectives, key metrics, common pitfalls, a step‑by‑step process, instance‑level best practices, and detailed parameter‑tuning tables for Oracle, MySQL (InnoDB), PostgreSQL, and DM databases.

Full-Stack Internet Architecture
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Database Optimization Methodology and Parameter Tuning for Oracle, MySQL, PostgreSQL, and DM

A newly installed database using default parameters rarely meets production performance requirements; therefore, systematic parameter optimization is essential for achieving optimal throughput and latency.

Database Optimization Objectives

Depending on the stakeholder role, optimization goals are divided into three perspectives:

Business (key users): Reduce page response time.

Database (developers): Reduce SQL response time.

Server (operations): Fully utilize physical resources and lower CPU, I/O, and memory usage on the database server.

Optimization Metrics

Typical quantitative targets include:

Average SQL response time (e.g., from 500 ms to 200 ms).

Server CPU usage during peak periods (e.g., from 70 % to 50 %).

Server I/O wait (e.g., from 30 % to below 10 %).

Common Pitfalls

Assuming deep knowledge of internal mechanisms alone guarantees success; in practice, proven tuning “recipes” are more effective.

Believing that endless parameter tweaking can solve any problem, while architectural flaws may render changes useless.

Focusing only on OS‑level tuning without addressing database‑level bottlenecks.

Underestimating the impact of application design on database performance.

Prematurely applying read/write splitting or sharding before data volume justifies it.

Optimization Process

The full workflow consists of:

Gathering detailed system information about the performance issue.

Defining measurable optimization goals and aligning them with stakeholders.

Analyzing the problem with appropriate tools, drafting an optimization plan, and reviewing it.

Implementing the plan; if goals are met, producing a report, otherwise revisiting the plan.

Database Instance Optimization

Three key principles guide instance tuning: ensure the redo log is sufficiently large, allocate ample buffer cache, and provision enough connections.

Two fundamental techniques are used:

Write transactions first to the redo log (WAL) to convert random writes into sequential writes.

Introduce a buffer layer (Buffer Cache) so that subsequent writes also become sequential.

Insufficient connections cause exceptions that render the system inaccessible, so connection limits must also be tuned.

Parameter‑Tuning Focus Areas

Across major database engines, the following components are commonly tuned:

Data cache

SQL parsing area

Sort memory

Redo/undo logs

Locks, latches, mutexes

Listeners and connections

File I/O performance

Oracle

Parameter Category

Parameter Name

Recommended Value

Remarks

Data Cache

SGA_TARGET, MEMORY_TARGET

70‑80 % of physical memory

Larger is better

Data Cache

DB_CACHE_SIZE

70‑80 % of physical memory

Larger is better

SQL Parsing

SHARED_POOL_SIZE

4‑16 GB

Avoid setting excessively large

Listeners & Connections

PROCESSES, SESSIONS, OPEN_CURSORS

Based on business demand

Typically 120 % of estimated connections

Other

SESSION_CACHED_CURSORS

>200

Facilitates soft parsing

MySQL (InnoDB)

Parameter Category

Parameter Name

Recommended Value

Remarks

Data Cache

INNODB_BUFFER_POOL_SIZE

50‑80 % of physical memory

Larger generally yields better performance

Log‑related

innodb_log_buffer_size

16‑32 MB

Adjust according to workload

Log‑related

sync_binlog

1 (safest), 0 or 100 as alternatives

1 offers highest durability

Listeners & Connections

max_connections

Based on business needs

Reserve some headroom

File I/O

innodb_flush_log_at_trx_commit

2

Balance between safety and performance

Other

wait_timeout, interactive_timeout

28800

Prevent idle connections from being terminated

PostgreSQL

Parameter Category

Parameter Name

Recommended Value

Remarks

Data Cache

shared_buffers

10‑25 % of physical memory

Data Cache

cache_buffer_size

50‑60 % of physical memory

Log‑related

wal_buffers

8‑64 MB

Avoid setting too large or too small

Listeners & Connections

max_connections

Based on business demand

Typically 120 % of estimated connections

Other

maintenance_work_mem

512 MB or larger

Other

work_mem

8‑16 MB

Default 1 MB is often insufficient

Other

checkpoint_segments

32 or higher

DM Database

Parameter Category

Parameter Name

Recommended Value

Remarks

Data Cache

MEMORY_TARGET, MEMORY_POOL

90 % of physical memory

Data Cache

BUFFER

60 % of physical memory

Data cache

Data Cache

MAX_BUFFER

70 % of physical memory

Maximum data cache

Listeners & Connections

max_sessions

Based on business demand

Typically 120 % of estimated connections

Conclusion

Database performance can be improved through hardware upgrades, SQL rewriting, indexing, and especially through careful parameter tuning; applying the configurations shown in the tables can help most databases achieve up to 80 % of their theoretical optimal performance.

databasePerformance TuningMySQLPostgreSQLOracleDM Databaseparameter optimization
Full-Stack Internet Architecture
Written by

Full-Stack Internet Architecture

Introducing full-stack Internet architecture technologies centered on Java

0 followers
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.