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.
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.
Full-Stack Internet Architecture
Introducing full-stack Internet architecture technologies centered on Java
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
