Mekanisme Buffer Pool dan Masalah Disk Eviction

Indeks B-Tree dirancang untuk meminimalkan I/O disk dengan kompleksitas pencarian O(log N). Kendala performa muncul ketika working set indeks melampaui alokasi memori utama: shared_buffers pada PostgreSQL atau innodb_buffer_pool_size pada MySQL. Database membaca dan menulis data dalam unit halaman (biasanya 8 KB di PostgreSQL dan 16 KB di InnoDB).

Ketika indeks aktif sepenuhnya berada di RAM (100% memory-resident), traversal tree hanya membutuhkan referensi memori (~100 nanodetik per akses halaman). Jika halaman indeks terdorong keluar dari RAM akibat keterbatasan ruang, database dipaksa melakukan random read ke disk (NVMe SSD: ~50–100 mikrodetik; rotasi disk: ~5–10 milidetik). Penurunan latensi mencapai 1.000 kali lipat, memicu antrean kueri dan lonjakan disk IOPS.

Residency Math: Formulasi Working Set Indeks

Residency math memetakan rasio antara total halaman indeks yang dibutuhkan oleh beban kerja transaksional (working set) terhadap kapasitas buffer pool yang dialokasikan.

1. Perhitungan Ukuran B-Tree Leaf Page

Ukuran fisik indeks dipengaruhi oleh jumlah baris, ukuran payload kolom, dan overhead header halaman:

Bytes_Per_Index_Row = Header_Offset (8 byte) + Kolom_Indexed (N byte) + Heap_Tuple_Pointer (6 byte)
Index_Leaf_Pages = (Total_Rows * Bytes_Per_Index_Row) / (Page_Size * Page_Fillfactor)
Total_Index_Size = Index_Leaf_Pages * Page_Size

PostgreSQL menggunakan nilai default Page_Size 8.192 byte dengan fillfactor default 90%. MySQL InnoDB menggunakan ukuran halaman default 16.384 byte.

2. Index Residency Ratio (IRR)

Indikator residency mengukur seberapa banyak halaman indeks aktif tertampung di memori:

IRR = (Resident_Index_Pages_in_RAM / Working_Set_Index_Pages) * 100%
  • IRR = 100%: Beban kerja indeks sepenuhnya berada di RAM. Tidak ada I/O disk untuk pencarian root ke leaf.
  • IRR < 100%: Terjadi buffer churn. Halaman indeks berebut ruang dengan halaman data tabel (heap/clustered index). Eviction terjadi melalui algoritma Least Recently Used (LRU) atau Clock-Sweep.

3. Formula Kapasitas Buffer Pool Terpadu

Untuk mencegah disk eviction, ukuran buffer pool harus memenuhi batas batas aman berikut:

Buffer_Pool_Required >= (Total_Hot_Index_Size + Total_Hot_Table_Size) / Target_Memory_Saturation

Tetapkan Target_Memory_Saturation pada angka 0.75 hingga 0.80. Sisakan alokasi 20–25% memori untuk OS page cache, query execution workspace (work_mem di PG, sort/join buffer di MySQL), serta connection overhead.

Deteksi Buffer Churn dan Diagnostik Kueri SQL

PostgreSQL: Mengukur Resident Pages via pg_buffercache

Ekstensi pg_buffercache membaca snapshot isi memori shared_buffers secara langsung. Kueri ini mengidentifikasi berapa persen indeks yang saat ini menetap di buffer:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT
    c.relname AS index_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS total_disk_size,
    pg_size_pretty(count(*) * 8192) AS resident_buffer_size,
    round(100.0 * (count(*) * 8192) / NULLIF(pg_relation_size(c.oid), 0), 2) AS residency_percentage
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE c.relkind = 'i'
GROUP BY c.oid, c.relname
ORDER BY count(*) DESC
LIMIT 15;

Pantau rasio cache hit per indeks untuk mendeteksi I/O disk aktual melalui tampilan statistik:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_blks_read AS disk_reads,
    idx_blks_hit AS buffer_hits,
    round(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2) AS hit_ratio_pct
FROM pg_statio_user_indexes
WHERE (idx_blks_hit + idx_blks_read) > 1000
ORDER BY disk_reads DESC
LIMIT 10;
Tingkat hit_ratio_pct di bawah 99% pada sistem OLTP menandakan halaman indeks sering dieviction ke disk.

MySQL InnoDB: Diagnostik Buffer Eviction

Periksa metrik buffer pool internal InnoDB menggunakan perintah administrasi:

SHOW ENGINE INNODB STATUS;

Analisis bagian BUFFER POOL AND MEMORY:

  • Buffer pool hit rate: Harus mendekati 1000 / 1000. Nilai di bawah 990 / 1000 mengindikasikan eviction tinggi.
  • Pages evicted without access: Jika angka ini terus naik seiring waktu, working set melampaui alokasi innodb_buffer_pool_size.

Kueri distribusi halaman indeks di InnoDB Buffer Pool:

SELECT
    INDEX_NAME,
    COUNT(*) AS resident_pages,
    CONCAT(ROUND(COUNT(*) * 16 / 1024, 2), ' MB') AS allocated_mb
FROM information_schema.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE = 'INDEX'
GROUP BY INDEX_NAME
ORDER BY resident_pages DESC
LIMIT 15;

Strategi Mitigasi untuk Menjaga Indeks Tetap di RAM

1. Terapkan Partial Index (Filtered Index)

Pada PostgreSQL, buat indeks yang hanya mencakup baris data aktif. Kueri transaksional umumnya hanya mengakses status data tertentu (misalnya, pesanan belum selesai):

-- Buruk: Mengindeks seluruh tabel (status 'completed' mendominasi 95% tabel)
CREATE INDEX idx_orders_all ON orders (user_id, status);

-- Benar: Partial index, memotong 95% ukuran indeks dari buffer pool
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE status IN ('unpaid', 'processing');

Hasil: Ukuran indeks berkurang signifikan, memastikan working set pas di memori tanpa membuang ruang untuk data statis/historis.

2. Pangkas Composite Index (Index Trimming)

Indeks komposit yang terlalu lebar meningkatkan footprint per baris leaf page, menurunkan kapasitas fan-out B-Tree.

  • Hapus kolom duplikat atau redundant prefix (misal: indeks terpisah pada (a) tidak diperlukan jika sudah ada indeks pada (a, b)).
  • Hindari memasukkan kolom berukuran string besar (UUID v4, token teks, hashing) ke dalam indeks non-covering. Gunakan surrogate integer atau hash integer bila memungkinkan.
  • Gunakan fitur INCLUDE (Covering Index) di PostgreSQL daripada menambahkan kolom ke search key jika kolom tersebut hanya digunakan dalam klausa SELECT:
CREATE INDEX idx_users_lookup ON users (email) INCLUDE (display_name, last_login);

3. Partisi Tabel untuk Menjaga Hot Data Resident

Ketika volume tabel mencapai ratusan juta baris, partisi tabel berbasis rentang waktu (misalnya bulanan):

CREATE TABLE transactions (
    id bigserial,
    created_at timestamptz NOT NULL,
    amount numeric,
    user_id int,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Database secara otomatis memisahkan struktur B-Tree indeks per partisi. Transaksi aktif hanya mengakses partisi bulan berjalan. Halaman indeks partisi aktif akan tetap berada di dalam buffer pool, sedangkan indeks partisi historis tidak akan mengotori memori atau menyebabkan eviction jika tidak diakses.