Best Practices for Linux Database Performance Tuning

System-Level Tuning: Linux Kernel & OS Parameters
Storage Optimization: I/O Performance
Memory Management: Maximizing Data Locality
Database-Specific Tuning
- MySQL/MariaDB
- PostgreSQL
Query Optimization: The Foundation of Performance
Concurrency & Connection Management
Monitoring & Benchmarking: Measure to Improve
Security vs. Performance: Striking the Balance
Conclusion
References

1. System-Level Tuning: Linux Kernel & OS Parameters

The Linux kernel and OS settings directly impact how the database interacts with hardware. Misconfigured parameters can lead to bottlenecks in CPU, I/O, or memory. Below are key optimizations:

Best Practice 1: Tune I/O Schedulers

The I/O scheduler manages how read/write requests are queued and dispatched to storage. For databases (especially with SSDs), prioritize low latency and predictable I/O:

SSDs/NVMe: Use mq-deadline (multi-queue deadline) or kyber scheduler. These minimize latency by prioritizing deadline-based request ordering.
HDDs: Use deadline scheduler (legacy single-queue) to avoid request starvation.

How to Implement:

Check current scheduler:
```
cat /sys/block/sda/queue/scheduler  
```

Set temporarily (replace sda with your disk):

echo mq-deadline > /sys/block/sda/queue/scheduler

Persist across reboots: Use udev rules or modify GRUB (e.g., GRUB_CMDLINE_LINUX="elevator=mq-deadline" in /etc/default/grub, then update-grub).

Best Practice 2: Optimize Virtual Memory (Swap)

Databases rely heavily on physical memory. Linux’s swap mechanism can degrade performance if overused, as disk I/O is orders of magnitude slower than RAM.

Set vm.swappiness to 1-10: This kernel parameter controls how aggressively the OS swaps memory pages. A low value (e.g., 1) prioritizes keeping database data in physical memory.
```
sysctl vm.swappiness=1  
# Persist: Add `vm.swappiness=1` to /etc/sysctl.conf  
```
Disable swap entirely (if possible): For dedicated database servers with sufficient RAM, swap can be disabled to eliminate I/O overhead. Use swapoff -a and comment out swap entries in /etc/fstab.

Best Practice 3: Increase File Descriptors

Databases open many file descriptors (FDs) for connections, logs, and data files. Linux defaults (e.g., 1024 FDs per process) are often too low.

Raise FD limits:
- Temporarily: ulimit -n 65535 (per shell session).
- Permanently: Edit /etc/security/limits.conf to set soft/hard limits for the database user (e.g., postgres or mysql):
```
postgres soft nofile 65535  
postgres hard nofile 65535  
```

Best Practice 4: Tune Network Stack

For remote database access, optimize TCP settings to reduce latency and improve throughput:

Increase TCP buffer sizes:

sysctl net.core.rmem_max=16777216  # Max receive buffer  
sysctl net.core.wmem_max=16777216  # Max send buffer  
sysctl net.ipv4.tcp_window_scaling=1  # Enable window scaling

Reduce TIME_WAIT connections:

sysctl net.ipv4.tcp_tw_reuse=1  # Reuse TIME_WAIT sockets  
sysctl net.ipv4.tcp_fin_timeout=30  # Lower timeout (default 60s)

2. Storage Optimization: I/O Performance

Databases are I/O-bound by nature—slow storage cripples query response times. Optimize storage to minimize I/O latency and maximize throughput.

Best Practice 1: Use Fast Storage (SSDs/NVMe)

Solid-state drives (SSDs) or NVMe devices deliver 10-100x faster I/O than HDDs. For databases:

Data files: Store on SSD/NVMe for random read/write performance (critical for indexes and transaction logs).
Logs: Separate transaction logs (e.g., PostgreSQL’s WAL, MySQL’s InnoDB log) onto a dedicated SSD to avoid contention with data files.

Best Practice 2: Choose the Right Filesystem

Filesystem choice impacts I/O overhead, scalability, and recovery. For databases:

XFS: Ideal for large databases. Supports high throughput, large file sizes, and online defragmentation. Use with inode64 (for large inode numbers) and noatime (disable access time logging).
Example mount options:
```
/dev/sdb1 /var/lib/postgresql xfs defaults,noatime,inode64 0 0  
```
Ext4: More mature but less performant than XFS for high-throughput workloads. Avoid for databases larger than 10TB.
Btrfs: Experimental for databases (avoid unless stability is confirmed).

Best Practice 3: RAID Configuration

Use RAID to balance performance and redundancy:

RAID 10 (Mirror + Striping): Best for databases. Combines striping (high read/write throughput) with mirroring (redundancy). Requires 4+ disks.
Avoid RAID 5/6: Write penalties (due to parity calculations) make them unsuitable for write-heavy databases.

Best Practice 4: Align Partitions and Disable Unnecessary Layers

Partition Alignment: Ensure partitions are aligned with the storage device’s physical block size (typically 4KB or 512e for modern drives) to avoid read-modify-write cycles. Use parted with align-check optimal to verify.
Avoid LVM (if possible): Logical Volume Manager (LVM) adds overhead. Use direct disk access unless you need features like snapshots.

3. Memory Management: Maximizing Data Locality

Linux uses memory for caching (page cache) and database-specific buffers. The goal is to keep frequently accessed data in RAM to avoid costly disk I/O.

Best Practice 1: Allocate Sufficient Database Buffers

Databases use in-memory buffers to cache data and indexes. Size these buffers based on total RAM:

PostgreSQL: shared_buffers (caches database pages) should be ~25% of total RAM (e.g., 8GB on a 32GB server).
MySQL (InnoDB): innodb_buffer_pool_size (caches tables/indexes) should be 50-70% of RAM (e.g., 22GB on a 32GB server).

Best Practice 2: Leverage the Linux Page Cache

Linux automatically caches file data in the page cache. For databases, this acts as a secondary cache alongside database-specific buffers. Avoid disabling the page cache (e.g., with O_DIRECT unless explicitly recommended by the database).

Best Practice 3: Enable Huge Pages

The default Linux memory page size (4KB) leads to high Translation Lookaside Buffer (TLB) misses for large databases. Huge pages (2MB or 1GB) reduce TLB pressure and improve memory access speed.

Enable huge pages:

# Allocate 10GB of 2MB huge pages (10GB / 2MB = 5120 pages)  
sysctl vm.nr_hugepages=5120  
# Persist: Add `vm.nr_hugepages=5120` to /etc/sysctl.conf

Configure the database to use huge pages:
- PostgreSQL: Set huge_pages = on in postgresql.conf.
- MySQL: Set innodb_hugepages=1 (requires libhugetlbfs).

4. Database-Specific Tuning

Each database has unique knobs for optimization. Below are key settings for the most popular Linux databases.

MySQL/MariaDB

InnoDB Buffer Pool

innodb_buffer_pool_size: As noted earlier, set to 50-70% of RAM.
innodb_buffer_pool_instances: Split the buffer pool into multiple instances (1 per 4GB of pool size) to reduce contention.

Transaction Logs

innodb_log_file_size: Larger logs reduce checkpoint frequency. Set to 256MB-1GB (avoid exceeding 4GB, as recovery time increases).
innodb_flush_log_at_trx_commit:
- 1 (default): ACID-compliant (log flushed to disk on commit). Slow but safe.
- 2: Log flushed to OS cache on commit (risk of data loss on OS crash). Use for read-heavy workloads.

Concurrency

innodb_thread_concurrency: Limit concurrent threads to avoid CPU saturation (e.g., 0 for auto-tuning, or 8-16 per CPU core).
max_connections: Set based on available RAM (e.g., 500-1000 for a 32GB server). Use connection pooling to avoid hitting this limit.

PostgreSQL

Shared Buffers and Work Memory

shared_buffers: ~25% of RAM (e.g., 8GB on 32GB RAM).
work_mem: Memory per query operation (e.g., sorts, hashes). Set to (RAM - shared_buffers) / max_connections / 4 (e.g., 64MB for 32GB RAM, 100 connections).

Write-Ahead Log (WAL)

wal_buffers: Buffer for WAL writes. Set to 16MB (default is often too small).
checkpoint_timeout and max_wal_size: Extend checkpoint intervals to reduce I/O spikes. Set checkpoint_timeout=30min and max_wal_size=10GB.

Maintenance Memory

maintenance_work_mem: Memory for index creation/vacuum. Set to 1-2GB (higher for large databases).

5. Query Optimization: The Foundation of Performance

Even well-tuned systems suffer if queries are inefficient. Optimize queries to reduce execution time and resource usage.

Best Practice 1: Index Strategically

Covering Indexes: Include all columns needed by a query to avoid table lookups (e.g., CREATE INDEX idx_covering ON orders (customer_id) INCLUDE (order_date, total)).
Composite Indexes: Order columns by selectivity (most selective first). For example, (user_id, order_date) is better than (order_date, user_id) if user_id filters more rows.
Avoid Over-Indexing: Each index slows down writes (INSERT/UPDATE/DELETE). Remove unused indexes with tools like pg_stat_user_indexes (PostgreSQL) or sys.schema_unused_indexes (MySQL).

Best Practice 2: Analyze Slow Queries

PostgreSQL: Use pg_stat_statements to track query performance. Enable it with shared_preload_libraries = 'pg_stat_statements' and query:
```
SELECT query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;  
```

MySQL: Use the Performance Schema:

SELECT SCHEMA_NAME, DIGEST_TEXT, SUM_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Best Practice 3: Rewrite Inefficient Queries

Avoid SELECT *: Fetch only needed columns to reduce I/O and memory usage.
Use EXPLAIN: Analyze query plans to identify full table scans, missing indexes, or inefficient joins. Example (PostgreSQL):
```
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2023-01-01';  
```
Limit Results: Use LIMIT to avoid returning unnecessary rows.

6. Concurrency & Connection Management

Uncontrolled concurrency (too many connections or locks) leads to contention and slowdowns.

Best Practice 1: Use Connection Pooling

Database connections are expensive to create. Connection pools reuse connections to reduce overhead:

PostgreSQL: Use pgBouncer or Pgpool-II.
MySQL: Use ProxySQL or MaxScale.
Configure pools to limit connections (e.g., max_client_conn=500 in pgBouncer) and set pool_mode=transaction for efficiency.

Best Practice 2: Optimize Locking and Isolation Levels

Use Lower Isolation Levels: For read-heavy workloads, use READ COMMITTED (default) instead of REPEATABLE READ to reduce locking.
Avoid Long Transactions: Keep transactions short to minimize lock持有时间.

7. Monitoring & Benchmarking: Measure to Improve

Tuning is iterative—continuously monitor performance and benchmark changes.

Key Monitoring Tools

System-Level: top, vmstat, iostat (CPU, memory, I/O), sar (historical trends).
Database-Level:
- PostgreSQL: pg_stat_activity, pg_stat_statements, pgBadger (log analysis).
- MySQL: SHOW PROCESSLIST, Performance Schema, pt-query-digest (Percona Toolkit).

Benchmarking

pgBench (PostgreSQL): Simulate workloads with pgbench -i -s 100 (initialize 100GB database) and pgbench -c 10 -j 4 -T 60 (10 connections, 4 threads, 60s test).
sysbench (MySQL/PostgreSQL): Test CPU, memory, and I/O with sysbench --test=oltp --db-driver=mysql run.

8. Security vs. Performance: Striking the Balance

Security measures (encryption, authentication) can degrade performance. Optimize to minimize overhead:

Data at Rest: Use filesystem-level encryption (e.g., LUKS) instead of database-level TDE for lower overhead.
TLS/SSL: Use TLS 1.3 for faster handshakes and enable session resumption.
Authentication: Use scram-sha-256 (PostgreSQL) or caching_sha2_password (MySQL) instead of legacy methods like md5.

9. Conclusion

Linux database performance tuning is a holistic discipline that spans the OS, storage, memory, and database layers. By following these best practices—from kernel tuning to query optimization—you can achieve significant gains in throughput, latency, and scalability. Remember: tuning is iterative. Continuously monitor workloads, benchmark changes, and adapt to evolving demands. With the right approach, your Linux database will deliver reliable performance even under heavy load.

Table of Contents