Administrasi database bukan hanya soal menanggulangi masalah, tetapi melihat masalah datang dari jauh. Berkas yang membesar tanpa henti, halaman kosong yang menumpuk, dan tabel yang tumbuh tak terkendali semuanya meninggalkan jejak angka — dan SQLite menyediakan sinyal-sinyal itu hampir tanpa biaya. Bab ini merangkum metrik yang layak dipantau, dari ukuran berkas sampai peta halaman per tabel, lalu membungkusnya menjadi satu skrip pemeriksaan mingguan.

Sinyal Dasar: Ukuran dan Halaman #

Bahan latihan kita adalah tabel log berisi 10.000 baris plus satu index:

sql 
CREATE TABLE log_pesanan (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  payload TEXT NOT NULL
);
WITH RECURSIVE angka(x) AS (
  SELECT 1 UNION ALL SELECT x + 1 FROM angka WHERE x < 10000
)
INSERT INTO log_pesanan (kode, payload)
SELECT 'LOG-' || printf('%05d', x), printf('payload nomor %d', x) FROM angka;
CREATE INDEX idx_log_kode ON log_pesanan(kode);

Sinyal termurah datang dari berkas itu sendiri. Pola awk berikut mengambil ukuran dan nama berkas tanpa kolom tanggal yang selalu berubah:

bash 
ls -l toko.db | awk '{print $5, $9}'

Output yang Dihasilkan:

text 
548864 toko.db

Angka itu menjadi lebih bermakna dipecah menjadi halaman (struktur berkas database dibahas pada chapter Format Berkas Database):

bash 
sqlite3 toko.db "PRAGMA page_size; PRAGMA page_count; PRAGMA freelist_count;"

Output yang Dihasilkan:

text 
4096
134
0

Ukuran berkas selalu merupakan page_size × page_count — 4.096 × 134 = 548.864 byte, cocok dengan ls. Angka yang paling layak dicermati adalah freelist_count: halaman kosong yang menunggu dipakai ulang. Setelah DELETE besar, freelist yang terus membengkak sementara jumlah baris tetap adalah sinyal klasik untuk menjalankan VACUUM, lengkap dengan pembahasannya di chapter VACUUM dan Maintenance Database.

dbstat: Peta Penggunaan per Tabel dan Index #

Virtual table dbstat membedah isi berkas per objek: setiap barisnya menggambarkan satu halaman milik satu tabel atau index:

bash 
sqlite3 toko.db "SELECT name, pagetype, count(*) AS jumlah
FROM dbstat GROUP BY name, pagetype ORDER BY name, pagetype;"

Output yang Dihasilkan:

text 
idx_log_kode|internal|1
idx_log_kode|leaf|42
log_pesanan|internal|1
log_pesanan|leaf|89
sqlite_schema|leaf|1

Dua kolom kuncinya: name adalah nama tabel atau index, dan pagetype membedakan halaman leaf (penyimpan data) dari internal (penunjuk jalan di B-tree). Jumlah halaman per objek bisa dijumlahkan menjadi byte:

bash 
sqlite3 toko.db "SELECT name, sum(pgsize) AS total_byte
FROM dbstat GROUP BY name ORDER BY total_byte DESC;"

Output yang Dihasilkan:

text 
log_pesanan|368640
idx_log_kode|176128
sqlite_schema|4096

Tabel data menempati 368.640 byte dan index-nya 176.128 byte — rasio yang sehat untuk index di satu kolom. Dari sini pertanyaan seperti “index mana yang membengkak lebih cepat dari tabelnya?” menjadi terukur. Untuk satu objek saja, tambahkan filter WHERE name = 'log_pesanan'.

.dbinfo: Kop Berkas dalam Satu Perintah #

Dot command .dbinfo membaca header berkas dan menampilkan identitasnya sekali jalan:

bash 
sqlite3 toko.db ".dbinfo"

Output yang Dihasilkan:

text 
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 3
database page count: 134
freelist page count: 0
schema cookie:       2
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3050001
number of tables:    1
number of indexes:   1
number of triggers:  0
number of views:     0
schema size:         146
data version         1

Baris-baris yang paling relevan untuk monitoring: database page count dan freelist page count (versi siap-baca dari dua PRAGMA di atas), file change counter yang bertambah setiap kali berkas ditulis — membandingkannya antar pemeriksaan memberi tahu seberapa sibuk database — serta software version 3050001 yang mencatat versi SQLite pembentuk berkas.

Jumlah Baris dan sqlite_stat1 #

Menghitung baris di SQLite membaca data — engine tidak menyimpan penghitung per tabel. Rencana query berikut memperlihatkannya:

bash 
sqlite3 toko.db "EXPLAIN QUERY PLAN SELECT count(*) FROM log_pesanan;"

Output yang Dihasilkan:

text 
QUERY PLAN
`--SCAN log_pesanan USING COVERING INDEX idx_log_kode

COUNT(*) di tabel 10.000 baris selesai sekejap, tetapi di tabel ratusan juta baris query ini menjadi full scan — jangan taruh query seperti ini di dashboard yang direfresh tiap detik. Untuk angka perkiraan yang murah, pakai sqlite_stat1 yang diisi ANALYZE (topik lengkapnya di chapter ANALYZE dan Statistik Query Planner):

bash 
sqlite3 toko.db "ANALYZE; SELECT * FROM sqlite_stat1;"

Output yang Dihasilkan:

text 
log_pesanan|idx_log_kode|10000 1

Kolom ketiganya berbunyi 10000 1: perkiraan jumlah baris tabel, lalu rata-rata baris per nilai index. Nilai ini hanya diperbarui saat ANALYZE dijalankan, jadi perlakukan sebagai estimasi untuk keperluan monitoring — COUNT(*) tetap satu-satunya cara mendapat angka pasti.

wal_checkpoint sebagai Sinyal Aktivitas #

Pada database yang berjalan dalam mode WAL, PRAGMA wal_checkpoint memberi efek samping yang berguna: angkanya menggambarkan aktivitas penulisan:

bash 
sqlite3 toko.db "PRAGMA journal_mode = WAL;
INSERT INTO log_pesanan (kode, payload) VALUES ('LOG-99999', 'payload tambahan');
PRAGMA wal_checkpoint(PASSIVE);"

Output yang Dihasilkan:

text 
wal
0|2|2

Tiga angkanya berurutan: busy (0 — tidak ada penulis lain menghalangi), log (2 frame berada di berkas WAL), dan checkpointed (2 frame berhasil dipindahkan ke berkas utama). Database yang diam menjawab 0|0|0; angka log yang tinggi setiap kali diperiksa berarti banyak tulisan menumpuk tanpa checkpoint — perilaku checkpoint ini dibahas pada chapter Journal Modes dan WAL.

Skrip Pemeriksaan Mingguan #

Semua sinyal di atas bisa dibungkus satu skrip mungil yang aman dijalankan rutin:

bash 
#!/bin/bash
DB=${1:-toko.db}
SQLITE=sqlite3

ukuran=$(ls -l "$DB" | awk '{print $5}')
halaman=$($SQLITE "$DB" "PRAGMA page_count;")
kosong=$($SQLITE "$DB" "PRAGMA freelist_count;")
echo "berkas  : $DB"
echo "ukuran  : $ukuran byte"
echo "halaman : $halaman (kosong: $kosong)"
echo "quick   : $($SQLITE "$DB" "PRAGMA quick_check;")"
bash 
./cek-db.sh toko.db

Output yang Dihasilkan:

text 
berkas  : toko.db
ukuran  : 552960 byte
halaman : 135 (kosong: 0)
quick   : ok

Simpan output skrip ini ke berkas log setiap minggu lewat cron — ./cek-db.sh toko.db >> laporan-mingguan.log — dan kita mendapat riwayat gratis: ukuran yang melompat, freelist yang menanjak, atau quick_check yang berubah dari ok semuanya terlihat sebagai anomali di riwayatnya.