Monitoring is needed for the following information reported as issues befre:

• InnoDB needed pool size vs current innodb_buffer_pool_size (see T1130)
• Max number of connections (that's for DevCentral for example)

We should create a map of important points to monitor, by category.

Metrics example

Memory

Buffer pool efficiency

• Buffer pool hit ratio
• Buffer pool size
• Free pages
• Dirty pages - innodb_buffer_pool_bytes_dirty
• Reads from disk vs logical reads

• Alert when hit ratio falls below ~99% for OLTP
• Alert when free pages approach zero
• Alert when constant disk reads increase

Host memory

• Take back from Prometheus node exporter general RAM use
• Take back from Prometheus node exporter swap usage
• OOM kills

• For db servers, alert as soon as swap is in use
• For all servers, try to alert as soon as the db process uses swap

Connections

• Current connections
• Max connections
• Aborted connections
• Connection errors
• Connection creation rate

• Threads connected
• Threads running

• Create in dashboard a block connections / threads running / threads connected
• Alert at warning level when <threads running / connections> approaches 1.0
• Alert at critical level when <threads running / connections> >= 1

Query performance

• Check what we can do with Performance Schema data
• Check what we can do with Prometheus exporters

• Log worst queries, ie queries with highest average execution time, so we can detect inefficient queries
• Log most executed queries (so we can ensure they use correct indices)
• Log most time consuming queries, ie highest cumulative execution time (*), so we can decide to cache

• Map percentile of queries performances: P50 / P95 / P99
• Alert when average latency is above a threshold

(*) A query can be time-efficient but very often requested, so it's logged here but not in worst queries.

InnoDB I/O

• Reads/sec
• Writes/sec
• fsync/sec
• Log writes
• Log waits - innodb_log_waits

• checkpoint age to shows whether flushing keeps up

• Alert when log waits > 0, and in that case, offer to consider increasing innodb_log_file_size or innodb_redo_log_capacity values

Temporary objects

• created_tmp_disk_tables as symptom of missing indexes, bad queries or insufficient temp table memory
• created_tmp_tables

• Alert when created_tmp_disk_tables > 1

Table scans

• handler_read_rnd_next, see this post

Index usage

• handler_read_key
• handler_read_rnd_next

• Graph handler_read_key / handler_read_rnd_next

• Alert at warning level when handler_read_key / handler_read_rnd_next >= 1.0 as we want more key reads than random scans

Locking

• innodb_deadlocks
• Lock wait time
• Row lock waits
• Lock timeout count

• Alert on increasing trend for those values

Transactions

• transactions/s
• Rollback rate
• History list length

• Alert if history list length continually grows, suggest it's often caused by long or forgotten transactions, backups or idle sessions

Replication

We don't currently use replication, but we plan to do some HA very soon, so it's a good idea to include them in our dashboards.

• Replication delay
• Replica SQL stopped
• Replica IO stopped
• GTID lag
• Seconds behind source

Disk usage

• Filesystem usage - ZFS version
• Filesystem usage - non-ZFS version
• Inode usage
• Read latency
• Write latency
• IOPS
• Queue depth
• fsync latency

Complicated on devserver, but on db-B-001, it makes sense

Binary logs

• Binary log size
• Retention
• Expiration
• Disk usage

• Find a way to compute growth/day

• Alert when log > some threshold (we already got a full disk with that)

Table cache

• open_tables
• opened_tables
• open_files

• Alert when opened_tables is increasing, document it usually indicates table_open_cache is to small

Metadata locks

• Metadata lock waits
• DDL waiting
• Blocked ALTER TABLE queries

• Add on dashboard section To watch during deployments and migrations.
• Document on Agora this in best practices for deployment

Network

• Bytes sent/sec
• Bytes received/sec
• Aborted clients
• Packet errors

Again, complicated on devserver, but on db-B-001, it makes sense

Availability

• Server up or down?
• Uptime

• Find a way to count restart
• Find a way to count crash recovery occurrences (flapping start/stop)

When hosting databases for Phabricator / Phorge

• SELECT count(*) FROM phabricator_daemon.daemon_logevent

Configuration saturation

A section "Are we correctly configured?" would be interesting with:

• current connections count vs max_connections
• Buffer pool vs working set
• Table cache vs table_open_cache
• Open files vs open_files_limit