Implementasi algoritma grafika seperti ray tracing di dalam SQL analitik (seperti ClickHouse atau PostgreSQL) sering digunakan untuk menguji batas kapabilitas engine kalkulasi berbasis set. Namun, eksekusi query iteratif kompleks dengan WITH RECURSIVE kerap memicu memory spike agresif hingga Out of Memory (OOM) crash. Artikel ini membedah investigasi insiden OOM saat mengeksekusi RayTracer SQL dan menyajikan solusi arsitektural untuk menstabilkan konsumsi RAM.

Gejala Masalah: Lonjakan RAM dan Degradasi Engine

Saat query ray tracing dijalankan, server database mengalami degradasi bertahap sebelum proses crash atau dibunuh oleh OOM killer kernel sistem:

  • RAM per thread melonjak eksponensial: Penggunaan memori pada query thread meningkat dalam hitungan detik hingga melampaui batas batas max_memory_usage.
  • Query timeout dan tail latency membengkak: Engine buffer gagal melakukan alokasi baru, menyebabkan query lain tersendat (head-of-line blocking).
  • Engine crash (OOM Killer): Sistem operasi memicu terminasi paksa proses basis data karena akumulasi buffer pada working memory CTE melebihi alokasi host fisik.

Root Cause: Unpruned State Accumulation

Akar masalah utama dari insiden ini adalah mekanisme evaluasi WITH RECURSIVE pada database engine analitik. Secara konseptual, rekursi SQL bekerja dengan mempertahankan dua state buffer: Intermediate Working Table (data hasil langkah n) dan Accumulator Result Table (akumulasi seluruh iterasi 0 sampai n).

Pada simulasi ray tracing, setiap sinar (ray) memantul secara iteratif. Jika sinar tidak membentur objek atau parameter divergensi pantulan menghasilkan penambahan baris baru secara multiplikatif tanpa pemangkasan state lama, terjadi state explosion. Engine query menyimpan seluruh lintasan partikel dari setiap bounce di memori sebelum final result dikirimkan, menyebabkan working table membengkak tanpa henti.

Query Ray Tracing Bermasalah

-- Query bermasalah: Tidak membatasi depth dan mempertahankan state ray mati
WITH RECURSIVE RayMarch AS (
    -- Anchor member: Inisialisasi posisi kamera dan vektor arah sinar
    SELECT 
        px, py, 
        0 AS bounce, 
        px AS origin_x, py AS origin_y, 1.0 AS dir_z,
        1.0 AS intensity,
        FALSE AS terminated
    FROM ScreenPixels

    UNION ALL

    -- Recursive member: Tracing kalkulasi pantulan cahaya
    SELECT 
        r.px, r.py, 
        r.bounce + 1,
        r.origin_x + (r.dir_z * 0.1), 
        r.origin_y + (r.dir_z * 0.1), 
        r.dir_z,
        r.intensity * 0.8,
        -- Evaluasi terminasi tidak memfilter row dari langkah iterasi berikutnya
        CASE WHEN (r.origin_x > 100.0 OR r.bounce > 50) THEN TRUE ELSE FALSE END
    FROM RayMarch r
    -- Kesalahan: r.bounce tetap dievaluasi tanpa bounded hard-limit di WHERE clause
    WHERE r.intensity > 0.001
)
SELECT px, py, SUM(intensity) 
FROM RayMarch 
GROUP BY px, py;

Investigasi Profiling: EXPLAIN dan Memory Metrics

Langkah pertama investigasi adalah menganalisis query execution plan dan memantau tabel sistem metrik memori sebelum eksekusi menghabiskan alokasi RAM.

-- Periksa pergerakan alokasi memori pada sistem analitik (contoh: ClickHouse)
SELECT 
    query_id, 
    user, 
    memory_usage, 
    peak_memory_usage 
FROM system.processes 
WHERE query LIKE '%RayMarch%';

-- Periksa query plan
EXPLAIN PIPELINE 
SELECT * FROM RayMarch;

Hasil profil menunjukkan pipeline tidak dapat melakukan streaming hasil (streaming aggregation gagal) karena operator UNION ALL pada sub-rekursi memicu retensi seluruh frame data intermediate di dalam memory buffer engine.

Solusi dan Mitigasi

Untuk menstabilkan penggunaan memori engine analitik dari resiko akumulasi frame iteratif, terapkan mitigasi struktural berikut:

1. Strict Bounded Depth Control

Pastikan kondisi terminasi memotong pertumbuhan baris pada Recursive Member secara absolut, menghentikan evaluasi untuk thread ray yang sudah keluar bounds atau mati.

-- Perbaikan: Hard-limit depth dan pruning state ray yang terminated
WITH RECURSIVE RayMarchBounded AS (
    SELECT 
        px, py, 0 AS bounce, 
        px AS origin_x, py AS origin_y, 1.0 AS dir_z,
        1.0 AS intensity
    FROM ScreenPixels

    UNION ALL

    SELECT 
        r.px, r.py, r.bounce + 1,
        r.origin_x + (r.dir_z * 0.1), 
        r.origin_y + (r.dir_z * 0.1), 
        r.dir_z,
        r.intensity * 0.8
    FROM RayMarchBounded r
    WHERE r.bounce < 16           -- Bounded depth mutlak
      AND r.origin_x <= 100.0     -- Geometry cutoff
      AND r.intensity > 0.05      -- Alpha threshold pruning
)
SELECT px, py, SUM(intensity)
FROM RayMarchBounded
GROUP BY px, py;

2. Window Partition Reduction

Jika ray tracing menghasilkan percabangan (misalnya pencahayaan difusi), batasi jumlah percabangan per pixel menggunakan window function untuk mencegah duplikasi geometris di dalam buffer rekursi.

3. Streaming Batching per Tile/Bucket

Jangan menjalankan kalkulasi seluruh screen matrix dalam satu query tunggal. Pisahkan resolusi target menggunakan sistem tiling atau bucket ID. Eksekusi batching memungkinkan engine membersihkan intermediate buffer tiap tile selesai diproses:

-- Eksekusi dibatasi per tile
SELECT * FROM compute_ray_tile(tile_x => 0, tile_y => 0);
SELECT * FROM compute_ray_tile(tile_x => 0, tile_y => 1);

Kesimpulan

Lonjakan memori akibat Recursive CTE umumnya disebabkan oleh state yang tidak dipangkas (unpruned state) dan absennya batas iterasi mutlak pada recursive member. Dengan menerapkan bounded recursion depth, pruning predikat ketat, dan segmentasi batch processing (tiling), pemrosesan beban berat bertipe ray tracing pada SQL analitik dapat berjalan stabil tanpa membebani working memory engine.