Database Performance Monitoring for DUNE

Abstract

This report presents the research, design, and implementation of improved PostgreSQL monitoring for DUNE Rucio database services using Checkmk. The project began with a request to improve dashboard visibility for database performance metrics, including connection usage, configured connection limits, lock activity, wait behavior, storage trends, query performance, and saturation alerts. The initial implementation focused on the dune_rucio_prod database on the rucio_prod PostgreSQL instance because connection saturation and lock contention are direct reliability risks for database-backed services. Existing Checkmk PostgreSQL monitoring was investigated, and several gaps were identified. Built-in connection monitoring did not clearly separate active, idle, idle-in-transaction, total, and usage-percent metrics, while the built-in lock monitoring simplified PostgreSQL lock modes into shared and exclusive categories. To address these gaps, two DSG-specific Checkmk local checks were created: one for connection-state monitoring and one for lock-state monitoring. These checks supplement the built-in PostgreSQL checks and provide additional performance data for dashboard graphs, service states, and alerts.