Change Details

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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_buffer_pool_bytes_dirty | 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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_log_waits | innodb_log_waits ]] [ ] [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-checkpoint-age/ | 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 [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-full-table-scans/ | 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 [ ]

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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_buffer_pool_bytes_dirty | 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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_log_waits | innodb_log_waits ]] [ ] [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-checkpoint-age/ | 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 [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-full-table-scans/ | 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) ==== 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

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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_buffer_pool_bytes_dirty | 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 - [[ https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/innodb-status-variables#innodb_log_waits | innodb_log_waits ]] [ ] [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-checkpoint-age/ | 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 [[ https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-full-table-scans/ | 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) ==== 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