Database Management SystemsPerformance Tuning

Performance Tuning

LevelAdvanced

Duration90 mins

TopicPerformance Tuning

4 / 5

Memory Tuning

Memory: The Performance Multiplier

Memory is the great equalizer in database performance. The difference between reading data from RAM versus disk is 100,000x—microseconds versus milliseconds. A database that fits entirely in memory performs radically differently from one that constantly reads from disk.

Beyond simple caching, memory enables efficient sorting, hashing, and query processing. Understanding how databases use memory—and how to optimize that usage—is essential knowledge for any DBA pursuing optimal performance.

What You Will Learn

By the end of this page, you will understand database memory architecture, buffer pool internals, working memory for query operations, memory pressure diagnosis, and optimization strategies for various memory configurations.

Database Memory Architecture

Database memory is divided into distinct regions serving different purposes. Understanding this architecture is fundamental to effective tuning.

Memory Region Overview
Region	Purpose	Key Parameters
Buffer Pool/Cache	Cache data and index pages from disk	shared_buffers, innodb_buffer_pool_size, buffer pool
Working Memory	Per-operation memory for sorts, hashes, aggregations	work_mem, sort_buffer_size, memory grants
WAL/Log Buffers	Buffer transaction log writes	wal_buffers, innodb_log_buffer_size
Connection Memory	Per-connection overhead and session state	Varies by connection count and activity
Plan Cache	Store compiled query plans	Query Store, prepared statement cache
Internal Structures	Lock tables, metadata caches, internal buffers	Usually auto-managed

Converting Mermaid diagram...

Buffer Pool Deep Dive

The buffer pool is the heart of database caching. It stores recently accessed data pages, avoiding expensive disk reads. Understanding buffer pool behavior is crucial for memory optimization.

buffer_pool_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- POSTGRESQL BUFFER POOL ANALYSIS
 
-- Install extension for detailed buffer analysis
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
 
-- Buffer cache hit ratio (target: >99%)
SELECT 
    sum(heap_blks_hit) as cache_hits,
    sum(heap_blks_read) as disk_reads,
    round(sum(heap_blks_hit)::numeric / 
          nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) 
        as hit_ratio
FROM pg_statio_user_tables;
 
-- What's in the buffer cache?
SELECT 
    c.relname as table_name,
    count(*) as buffers,
    pg_size_pretty(count(*) * 8192) as size_in_cache,
    round(100.0 * count(*) / (
        SELECT setting::integer FROM pg_settings 
        WHERE name = 'shared_buffers'), 2) as pct_of_cache
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 20;
 
-- Buffer usage by usage count (LRU behavior)
SELECT 
    usagecount,
    count(*) as buffers,
    pg_size_pretty(count(*) * 8192) as size
FROM pg_buffercache
GROUP BY usagecount
ORDER BY usagecount;
 
-- Sizing recommendation: monitor over time
-- If hit ratio <99%, consider increasing shared_buffers
-- If most buffers have usagecount=1, cache is thrashing

Buffer Pool Sizing

The optimal buffer pool size depends on your working set—the data actively queried. If your working set fits in memory, increase buffer pool to capture it. If working set exceeds available RAM, focus on query optimization and proper indexing rather than just adding more cache.

Working Memory for Query Operations

Beyond caching, databases need memory for query operations: sorting results, building hash tables for joins, creating bitmap indexes, and storing intermediate results. This working memory is often the key to avoiding expensive disk-based operations.

working_memory.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- POSTGRESQL WORKING MEMORY
 
-- Current work_mem setting
SHOW work_mem;
 
-- Detect sorts/hashes spilling to disk
-- (indicates work_mem too small for workload)
SELECT 
    query,
    calls,
    temp_blks_read + temp_blks_written as temp_blocks,
    round((temp_blks_read + temp_blks_written) * 8.0 / 1024, 2) as temp_mb
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;
 
-- Estimate work_mem needed for a query
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
-- Look for "Sort Method: external merge" = spilled to disk
-- "Sort Method: quicksort" = fit in memory
 
-- Session-level adjustment for specific queries
SET work_mem = '256MB';
-- Run heavy analytical query
RESET work_mem;
 
-- CAUTION: work_mem is PER OPERATION
-- A query with 5 sorts might use 5 * work_mem
-- Formula: (Total RAM - shared_buffers) / (max_connections * 5)

The Multiplication Trap

PostgreSQL's work_mem is allocated PER OPERATION, not per query or session. A complex query with multiple sorts and hash joins might allocate work_mem 10+ times. With 200 connections running such queries, you can quickly exhaust available memory. Always calculate: work_mem × operations × concurrent_queries.

Diagnosing Memory Pressure

Memory pressure occurs when the database needs more memory than available, leading to cache evictions, disk spills, and performance degradation. Recognizing the symptoms allows proactive intervention.

Symptoms of Memory Pressure

•Low buffer cache hit ratio — Below 95% indicates cache thrashing
•Frequent disk spills — Sorts and hashes writing to temp space
•Page Life Expectancy drops — SQL Server metric; <300 seconds is concerning
•Swap usage — OS swapping database memory is catastrophic
•Memory grant waits — Queries waiting for memory allocation
•OOM killer activity — Linux killing database processes (check dmesg)

memory_diagnosis.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- POSTGRESQL: Memory pressure indicators
-- Check for cache pressure
SELECT 
    datname,
    blks_hit,
    blks_read,
    round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2) 
        as hit_ratio
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';
 
-- Check temp file usage (sorts/hashes spilling)
SELECT 
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database
WHERE temp_files > 0;
 
-- SQL SERVER: Memory pressure indicators
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
  AND counter_name IN (
    'Memory Grants Pending',
    'Memory Grants Outstanding',
    'Target Server Memory (KB)',
    'Total Server Memory (KB)'
  );
 
-- MYSQL: InnoDB memory indicators
SELECT 
    variable_name,
    variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_pages_flushed',
    'Innodb_buffer_pool_wait_free'
);

Memory Optimization Strategies

When memory is limited, strategic optimization maximizes effectiveness. These strategies help you get the most performance from available memory.

Memory Optimization Techniques

•Right-size buffer pool — Match to working set, not total data size. 25-80% of RAM depending on workload.
•Use covering indexes — Reduce data fetched by storing needed columns in indexes.
•Partition large tables — Queries access only relevant partitions, improving cache efficiency.
•Connection pooling — Reduce connection count to lower per-connection memory overhead.
•Optimize queries — Better queries need less working memory for sorts and hashes.
•Enable huge pages — Reduces memory management overhead (Linux: huge_pages, Windows: Lock Pages in Memory).
•Temperature-based caching — Hot data stays in cache, cold data evicts quickly.

Memory Allocation Quick Reference
Server RAM	Buffer Pool	Work Memory	Connections
16 GB	4 GB	32-64 MB	100-200
32 GB	8 GB	64-128 MB	150-300
64 GB	16-24 GB	64-256 MB	200-400
128 GB	32-48 GB	128-512 MB	300-500
256 GB	64-100 GB	256 MB-1 GB	400-600

Cloud Considerations

Cloud instances often have memory ratios different from on-premise servers. Read-heavy workloads benefit from memory-optimized instances. Burstable instances (t3, B-series) may not suit databases due to unpredictable memory performance.

Summary: Memory Tuning Mastery

Key Takeaways

•Memory is the performance multiplier — RAM access is 100,000x faster than disk; maximize cache effectiveness.
•Understand memory regions — Buffer pool, working memory, and connection overhead serve different purposes.
•Monitor hit ratios — Target 99%+ buffer cache hit ratio for OLTP workloads.
•Watch for spills — Temp file usage indicates working memory needs tuning.
•Calculate working memory carefully — work_mem multiplies by operations and connections.
•Diagnose pressure early — Low hit ratios, PLE drops, and memory waits signal problems.

What's Next:

With query, index, configuration, and memory tuning covered, you need visibility into ongoing performance. The next page covers Monitoring Tools—the dashboards, queries, and utilities that reveal database health and identify issues before they become outages.

Page Complete

You now understand database memory architecture and optimization strategies. Effective memory tuning ensures your database performs optimally within available resources. Next, we'll explore monitoring tools for ongoing performance visibility.

4 / 5

Loading learning content...

Database Management SystemsPerformance Tuning

Performance Tuning

LevelAdvanced

Duration90 mins

TopicPerformance Tuning

4 / 5

Memory Tuning

Memory: The Performance Multiplier

What You Will Learn

Database Memory Architecture

Database memory is divided into distinct regions serving different purposes. Understanding this architecture is fundamental to effective tuning.

Memory Region Overview
Region	Purpose	Key Parameters
Buffer Pool/Cache	Cache data and index pages from disk	shared_buffers, innodb_buffer_pool_size, buffer pool
Working Memory	Per-operation memory for sorts, hashes, aggregations	work_mem, sort_buffer_size, memory grants
WAL/Log Buffers	Buffer transaction log writes	wal_buffers, innodb_log_buffer_size
Connection Memory	Per-connection overhead and session state	Varies by connection count and activity
Plan Cache	Store compiled query plans	Query Store, prepared statement cache
Internal Structures	Lock tables, metadata caches, internal buffers	Usually auto-managed

Converting Mermaid diagram...

Buffer Pool Deep Dive

The buffer pool is the heart of database caching. It stores recently accessed data pages, avoiding expensive disk reads. Understanding buffer pool behavior is crucial for memory optimization.

buffer_pool_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- POSTGRESQL BUFFER POOL ANALYSIS
 
-- Install extension for detailed buffer analysis
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
 
-- Buffer cache hit ratio (target: >99%)
SELECT 
    sum(heap_blks_hit) as cache_hits,
    sum(heap_blks_read) as disk_reads,
    round(sum(heap_blks_hit)::numeric / 
          nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) 
        as hit_ratio
FROM pg_statio_user_tables;
 
-- What's in the buffer cache?
SELECT 
    c.relname as table_name,
    count(*) as buffers,
    pg_size_pretty(count(*) * 8192) as size_in_cache,
    round(100.0 * count(*) / (
        SELECT setting::integer FROM pg_settings 
        WHERE name = 'shared_buffers'), 2) as pct_of_cache
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 20;
 
-- Buffer usage by usage count (LRU behavior)
SELECT 
    usagecount,
    count(*) as buffers,
    pg_size_pretty(count(*) * 8192) as size
FROM pg_buffercache
GROUP BY usagecount
ORDER BY usagecount;
 
-- Sizing recommendation: monitor over time
-- If hit ratio <99%, consider increasing shared_buffers
-- If most buffers have usagecount=1, cache is thrashing

Buffer Pool Sizing

Working Memory for Query Operations

working_memory.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- POSTGRESQL WORKING MEMORY
 
-- Current work_mem setting
SHOW work_mem;
 
-- Detect sorts/hashes spilling to disk
-- (indicates work_mem too small for workload)
SELECT 
    query,
    calls,
    temp_blks_read + temp_blks_written as temp_blocks,
    round((temp_blks_read + temp_blks_written) * 8.0 / 1024, 2) as temp_mb
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;
 
-- Estimate work_mem needed for a query
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
-- Look for "Sort Method: external merge" = spilled to disk
-- "Sort Method: quicksort" = fit in memory
 
-- Session-level adjustment for specific queries
SET work_mem = '256MB';
-- Run heavy analytical query
RESET work_mem;
 
-- CAUTION: work_mem is PER OPERATION
-- A query with 5 sorts might use 5 * work_mem
-- Formula: (Total RAM - shared_buffers) / (max_connections * 5)

The Multiplication Trap

Diagnosing Memory Pressure

Symptoms of Memory Pressure

•Low buffer cache hit ratio — Below 95% indicates cache thrashing
•Frequent disk spills — Sorts and hashes writing to temp space
•Page Life Expectancy drops — SQL Server metric; <300 seconds is concerning
•Swap usage — OS swapping database memory is catastrophic
•Memory grant waits — Queries waiting for memory allocation
•OOM killer activity — Linux killing database processes (check dmesg)

memory_diagnosis.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- POSTGRESQL: Memory pressure indicators
-- Check for cache pressure
SELECT 
    datname,
    blks_hit,
    blks_read,
    round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2) 
        as hit_ratio
FROM pg_stat_database
WHERE datname NOT LIKE 'template%';
 
-- Check temp file usage (sorts/hashes spilling)
SELECT 
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database
WHERE temp_files > 0;
 
-- SQL SERVER: Memory pressure indicators
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
  AND counter_name IN (
    'Memory Grants Pending',
    'Memory Grants Outstanding',
    'Target Server Memory (KB)',
    'Total Server Memory (KB)'
  );
 
-- MYSQL: InnoDB memory indicators
SELECT 
    variable_name,
    variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_pages_flushed',
    'Innodb_buffer_pool_wait_free'
);

Memory Optimization Strategies

When memory is limited, strategic optimization maximizes effectiveness. These strategies help you get the most performance from available memory.

Memory Optimization Techniques

•Right-size buffer pool — Match to working set, not total data size. 25-80% of RAM depending on workload.
•Use covering indexes — Reduce data fetched by storing needed columns in indexes.
•Partition large tables — Queries access only relevant partitions, improving cache efficiency.
•Connection pooling — Reduce connection count to lower per-connection memory overhead.
•Optimize queries — Better queries need less working memory for sorts and hashes.
•Enable huge pages — Reduces memory management overhead (Linux: huge_pages, Windows: Lock Pages in Memory).
•Temperature-based caching — Hot data stays in cache, cold data evicts quickly.

Memory Allocation Quick Reference
Server RAM	Buffer Pool	Work Memory	Connections
16 GB	4 GB	32-64 MB	100-200
32 GB	8 GB	64-128 MB	150-300
64 GB	16-24 GB	64-256 MB	200-400
128 GB	32-48 GB	128-512 MB	300-500
256 GB	64-100 GB	256 MB-1 GB	400-600

Cloud Considerations

Summary: Memory Tuning Mastery

Key Takeaways

•Memory is the performance multiplier — RAM access is 100,000x faster than disk; maximize cache effectiveness.
•Understand memory regions — Buffer pool, working memory, and connection overhead serve different purposes.
•Monitor hit ratios — Target 99%+ buffer cache hit ratio for OLTP workloads.
•Watch for spills — Temp file usage indicates working memory needs tuning.
•Calculate working memory carefully — work_mem multiplies by operations and connections.
•Diagnose pressure early — Low hit ratios, PLE drops, and memory waits signal problems.

What's Next:

Page Complete

4 / 5