Cara Membuat Postgres Menjadi Lambat

• Pengantar pendekatan eksperimental terhadap kombinasi parameter yang dapat menurunkan performa Postgres secara ekstrem
• Secara umum melakukan tuning terbalik pada berbagai elemen seperti cache, indeks, WAL, I/O, dan lainnya
• Berhasil menurunkan TPS hingga 42.000 kali dengan memanipulasi secara ekstrem shared_buffers, autovacuum, dan opsi terkait WAL
• Juga memanfaatkan fitur terbaru seperti io_method dan io_workers pada Postgres 18/19 untuk menguji pembatasan ke satu thread I/O
• Hasil eksperimen membuktikan bahwa bahkan hanya dengan file konfigurasi Postgres saja, penurunan performa ekstrem tetap memungkinkan

---

Gambaran umum

Tulisan ini membahas eksperimen yang, kebalikan dari tuning Postgres yang biasanya berfokus membuatnya lebih cepat, justru bertujuan semata-mata untuk membuatnya lambat, dengan menurunkan performa hingga batasnya hanya melalui perubahan berbagai nilai konfigurasi PostgreSQL.
Pengujian didasarkan pada workload TPC-C milik BenchBase (128 gudang, 100 koneksi, masing-masing mencoba hingga 10.000 transaksi/detik, Postgres 19devel terbaru, Ryzen 7950x, RAM 32GB, SSD 2TB, dijalankan selama 120 detik).
Pada nilai default, performanya adalah 7082 TPS, dan diamati secara bertahap seberapa lambat sistem menjadi melalui manipulasi tiap parameter.

Mengurangi caching secara drastis

• Postgres memanfaatkan cache yang kuat (shared_buffers) untuk mengurangi disk I/O
• Dari nilai default (10GB), ketika shared_buffers diturunkan menjadi 8MB, TPS turun ke sekitar 1/7 (1052 TPS)
• Rasio cache hit turun dari 99,90% menjadi 70,52%, dan teramati peningkatan read syscall lebih dari 300 kali
• Dicoba menurunkannya hingga 128kB, tetapi Postgres hanya mengizinkan minimum sekitar 2MB, sehingga TPS turun lagi menjadi 485

Meningkatkan pekerjaan latar belakang (tuning autovacuum)

• Semua ambang terkait autovacuum dibuat ke nilai minimum, sehingga vacuum berjalan hampir pada setiap pekerjaan
  • kombinasi seperti autovacuum_vacuum_insert_threshold=1, autovacuum_naptime=1, dan lainnya
  • vacuum berulang hampir setiap 1 detik bahkan saat secara praktis tidak ada pekerjaan yang perlu dilakukan
• Dalam proses ini, maintenance_work_mem diperkecil menjadi 128kB, dan semua logging autovacuum diaktifkan
• Hasilnya, TPS turun menjadi 293 (kurang dari 1/20 dibanding semula)
• Melalui log realtime terkait autovacuum, dipastikan bahwa pekerjaan latar belakang yang terlalu sering benar-benar menjadi penyebab penurunan performa

Membuat penulisan WAL (Write-Ahead Log) seburuk mungkin

• Semua parameter terkait WAL disetel ke kondisi terburuk
  • wal_writer_flush_after=0, wal_writer_delay=1, wal_sync_method=open_datasync, dan lainnya
  • checkpoint dipaksa setiap 30 detik, dengan min_wal_size/max_wal_size=32MB agar tetap pada ukuran minimum
  • wal_level=logical, wal_log_hints=on, sehingga informasi yang tidak perlu pun ikut dicatat ke WAL
  • beban tambahan seperti track_wal_io_timing, summarize_wal, dan lainnya juga diaktifkan
• Hasilnya TPS turun menjadi 98 (kurang dari 1/70 dibanding semula)
• Dari log terlihat perilaku tidak normal seperti checkpoint yang berulang setiap beberapa ratus ms

Menghilangkan efek indeks

• Penggunaan indeks disetel sehingga semuanya dihitung dengan biaya maksimum (random_page_cost=1e300, cpu_index_tuple_cost=1e300), sehingga indeks praktis dinonaktifkan
• shared_buffers dinaikkan kembali ke 8MB (untuk menjaga stabilitas), dan TPS turun hingga 0,87 (berhasil mencapai 7000 kali lebih lambat)

Memaksa I/O satu thread

• Memanfaatkan fitur terbaru Postgres 18+
• Dengan io_method=worker, io_workers=1, semua I/O dipaksa berjalan pada satu thread worker
• TPS turun lagi menjadi 0,016 (42.000 kali lebih lambat)
• Pada pengujian 100 koneksi selama 120 detik, performanya begitu terbatas hingga hanya 11 transaksi yang berhasil

Kesimpulan dan panduan reproduksi

• Dibuktikan bahwa hanya dengan total 32 manipulasi parameter, DB produksi dapat dibuat nyaris dalam keadaan "lumpuh"
• Penurunan performa dapat dimaksimalkan hanya dengan mengubah konfigurasi postgresql.conf
• Pengguna yang ingin mereproduksi eksperimen ini dapat merujuk ke BenchBase Postgres, lingkungan TPC-C yang disebutkan di atas, dan daftar semua konfigurasi
• Beberapa parameter tambahan atau upaya lain untuk memperlambat sistem tidak disertakan

Ringkasan daftar parameter

• shared_buffers = 8MB
• ambang/scale_factor terkait autovacuum = diminimalkan ke 0~1
• biaya serta memori/log terkait vacuum: diminimalkan & dimaksimalkan
• sync/flush/log/level terkait WAL: dipertahankan lambat
• random_page_cost, cpu_index_tuple_cost terkait indeks: ditetapkan ke 1e300
• io_method = worker, io_workers = 1
• nilai detail lainnya merujuk ke daftar pada isi utama

Penutup

• Hanya dengan file postgresql.conf, penurunan performa yang sangat parah dapat dipicu
• Dalam praktik, kombinasi tersebut layak dijadikan referensi secara terbalik (untuk perbaikan performa yang efisien)
• Tulisan ditutup dengan penyebutan bahwa eksperimen dihentikan karena sakit punggung yang dialami penulis

[GN⁺]

Komentar Hacker News

• Memperkenalkan pendekatan yang benar-benar melupakan hal-hal seperti indeks, banyak tabel, transaksi, relasi entitas, dan integritas referensial, lalu menggunakan semua data dalam satu tabel seperti KVS NoSQL SQL, mirip versi awal TRIRIGA
• Cara seperti ini terasa sangat menyenangkan sampai-sampai rasanya akan bagus jika ada buku seri lanjutan tentang 'cara membuat pekerjaan jadi lebih kacau', karena sambil belajar kita justru bisa menemukan cara yang lebih baik dari kebalikannya; kalau dibuat dengan gaya O’Reilly, sampulnya akan cocok dihiasi hewan fantasi yang digambar asal-asalan (misalnya unicorn berkepala dua yang sedang menelepon dengan AirPods, memberi uang kepada penipu, membuat PowerPoint, makan berlebihan, dan memakai narkoba)
  • Ada contoh nyata strategi seperti ini yang dipakai pada Perang Dunia II; demi meningkatkan peluang pilot untuk selamat, para ahli meteorologi lebih dulu mencari kondisi yang menimbulkan korban jiwa paling besar, lalu merancang misi agar menghindari kondisi tersebut; tautan referensi Suppose I wanted to kill a lot of pilots
  • Di kelas menulis kreatif juga pernah ada latihan membaca dan menganalisis tulisan yang salah saat latihan, atau sengaja menulis ulang tulisan bagus menjadi buruk, dan itu justru menjadi latihan menulis yang paling membantu
  • Situs parodi ORLYBooks juga bisa dijadikan referensi
• Rasanya akan sangat bagus jika ada sandbox lingkungan produksi untuk eksperimen alat observabilitas; sistem bergaya SaaS berukuran sedang, dengan simulasi penggunaan, lalu konfigurasi postgres/rabbit yang biasa-biasa saja untuk menguji kemampuan alat atau strategi debugging
• Ide ini benar-benar jenius; kalau ingin benar-benar mahir melakukan optimasi, menurut saya bagus untuk lebih dulu menghancurkannya ke arah sebaliknya sebagai titik awal atau kelompok kontrol; perlu bertanya pada diri sendiri apakah kita sungguh memperlakukan database atau sistem secara ilmiah, atau cuma ikut-ikutan secara samar
  • Setiap kali memakai layanan cloud baru, saya selalu begitu; pertama saya habiskan dulu Series A secepat mungkin, lalu setelah itu baru mulai optimasi biaya cloud
• Buku The Defence of Duffer's Drift adalah contoh awal dari genre seperti ini; pada cerita pertama, taktik peleton dibuat berantakan hingga sebagian besar pasukan hilang, lalu pada cerita-cerita berikutnya kondisi taktis diubah satu per satu sehingga hasilnya makin membaik; buku taktik modern seperti Musicians of Mars 2 juga memakai pendekatan yang sama, cerita pertama Team Badger sangat mirip dengan Duffer's Drift, hanya divariasikan menurut lokasi, perlengkapan, dan teknologi; PDF terkait bisa dilihat di The Defence of Duffer's Drift dan Musicians of Mars 2, dan bagian ketika komandan Team Badger meninjau kembali mengapa ia kalah telak, jauh lebih buruk daripada rasa percaya diri dan persiapannya, sangat berkesan
  • Film dengan nuansa serupa antara lain Groundhog Day dan terutama Edge of Tomorrow, dan versi penghormatan modern yang dimuat di blog militer Inggris baru-baru ini juga layak dilihat: Defence Baltic Bridge Dreams
• Penyebutan deadlock menarik; saya penasaran apakah ada juga bagian yang membahas penyesuaian pengaturan tingkat isolasi transaksi
• Senang melihat penyebutan B Sanderson
• Rasanya seperti gabungan Hyperbole and a Half dengan admin db; membacanya menyenangkan dan saya juga belajar beberapa hal
• Gaya penulisannya dan cara mengurai pikirannya benar-benar luar biasa; sangat menyenangkan untuk dibaca

[sonic0987]

Luar biasa. Saya sangat suka pendekatan seperti ini.