Memory Tuning

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

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)

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'
);