«Situsnya lambat» hampir selalu berujung ke basis data. Dan kalau diperiksa dengan urutan yang benar itu memakan beberapa menit: pertama apa yang terlihat justru selagi lambat, lalu apa yang sudah menumpuk berminggu-minggu.

Lihatlah selagi masalahnya berlangsung

Aturan dasarnya: informasi paling berharga tersedia justru saat semuanya sedang lambat. Perintah pertama untuk MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Untuk PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Anda akan melihat apa yang sedang berjalan saat ini dan sudah berapa lama. Dan jawabannya biasanya datang seketika: satu kueri berat menghalangi semua yang lain, atau seratus kueri serupa sedang menunggu sebuah kunci dilepas.

Koneksi

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Kalau Max_used_connections mendekati max_connections maka aplikasinya sesekali menerima penolakan «too many connections». Dan menaikkan batasnya tidak boleh jadi tindakan pertama: setiap koneksi memakan memori, dan pada server yang memorinya memang sudah tipis menaikkannya hanya mempercepat perjalanan menuju swap. Cari tahu dulu mengapa koneksinya tidak dilepas: biasanya karena sebuah kueri panjang atau tidak adanya connection pool di sisi aplikasi.

Dan lihat juga dari sisi lain: lonjakan koneksi bisa datang bukan dari kenaikan popularitas melainkan dari bot yang menggempur suatu halaman berat — sebuah pencarian, filter daftar, penyusun laporan.

Kueri lambat

Tanpa log kueri lambat tidak ada tempat untuk melangkah. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Sehari kemudian:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, di postgresql.conf:

log_min_duration_statement = 1000

Lalu ekstensi pg_stat_statements, yang menunjukkan langsung total waktu per kueri:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Perhatikan: yang penting bukan kueri terpanjang melainkan yang total waktunya terbesar. Sebuah kueri 50 milidetik yang berjalan seribu kali per menit lebih merugikan daripada laporan sepuluh detik yang berjalan sekali sehari.

Pengaturan yang paling sering dilupakan

Untuk MySQL dan MariaDB itu adalah ukuran buffer pool InnoDB. Nilai bawaannya 128 megabita dan tidak berubah selama puluhan tahun. Dan pada server yang basis datanya memakan beberapa gigabita, itu berarti membaca terus-menerus dari disk alih-alih dari memori.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Tolok ukur yang masuk akal untuk server basis data khusus adalah separuh RAM; dan untuk server yang juga menjalankan server web serta PHP adalah seperempat, dengan perhitungan agar tidak jatuh ke swap. Dan satu pengaturan ini saja biasanya memberi lebih banyak daripada sepekan mengoptimalkan kueri.

Dua hal yang diam-diam memakan disk

Log biner MySQL. Mereka diperlukan untuk replikasi dan pemulihan ke suatu titik waktu, terus bertambah, dan secara bawaan kadang tidak pernah dihapus. Untuk memeriksanya:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Kalau Anda tidak punya replikasi dan tidak memakai pemulihan ke titik waktu, tetapkan masa simpannya beberapa hari yang wajar.

Berkas WAL PostgreSQL dengan slot replikasi yang terlupakan. Situasi yang kurang dikenal dan lebih berbahaya: kalau ada slot replikasi tetapi konsumennya tidak lagi terhubung maka PostgreSQL wajib menyimpan setiap log sejak saat terputus — dan akan menyimpannya sampai disknya habis.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Slot tidak aktif semacam itu yang tidak diperlukan siapa pun sebaiknya dihapus. Dan ini adalah salah satu dari sedikit situasi di mana basis data pasti membawa disknya ke nol.

Ukuran dan pertumbuhan

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Dan yang berguna bukan angka saat ini melainkan laju perubahannya. Basis data yang berlipat dua dalam sebulan tanpa kenaikan lalu lintas yang sebanding biasanya berarti ada tabel pencatatan yang ditulisi dan tidak pernah dibersihkan — sesi, riwayat tugas, log peristiwa suatu plugin.

Keamanan dalam dua baris

Dan mumpung konsol basis datanya sudah terbuka, ada dua hal yang layak diperiksa. Pertama, apakah basis datanya terjangkau dari luar (ss -tulpn | grep 3306): ia hampir tidak pernah boleh menghadap keluar. Dan kedua, akun yang bisa terhubung dari alamat mana pun:

mysql -e "SELECT user, host FROM mysql.user"

Sebuah entri dengan host = '%' berarti «koneksi dari mana saja». Dan kalau portnya juga terbuka maka di antara basis data dan internet hanya berdiri satu kata sandi.

Cadangan

Hal terakhir. Adanya berkas cadangan tidak menjamin apa-apa; yang menjamin hanyalah pemulihan yang sudah diuji. Sebuah salinan yang tidak pernah dipulihkan punya kemungkinan sama besarnya untuk terpotong, diambil dengan galat kunci, atau berisi basis data yang keliru. Dan itu harus diperiksa lebih dulu di mesin terpisah, bukan pada hari salinannya diperlukan.

Untuk pengawasan sehari-hari tiga angka sudah cukup: jumlah koneksi, jumlah kueri lambat per hari, dan ukuran basis data. Ketiganya berubah pelan dan bisa diprediksi, dan gerakan cepat salah satunya layak dilirik. Bagaimana ketiganya tampak pada satu halaman — lihat di demo bawah.