Operations per second
Queries per second
- Insert Commands
- Select Commands
- Update Commands
- Delete Commands
Slow Queries per minute, by node
decides long query time
3 seconds
Explain statement
to analyze the query.
Active Connections, by node
Max Connections, by node
Read vs Written InnoDB Bytes per second
Read InnoDB Data = > 90% mycase
Write InnoDB Data
Received vs Sent Bytes per second
throughput
query performance
query.questionsPerSecond: The number of queries sent by clients.
query.queriesPerSecond: The total number of queries per second (QPS)
query.maxExecutionTimeExceededPerSecond
timeout queries
mysql execution time is by default
to know last run query time
set profiling=1; and use show profiles; to get execution times.
Slow query logs
/ 2 seconds threshold
SET GLOBAL long_query_time = 3;
check more on enable & understand mysql slow query logs.
Disk io SSD vs nvme
db.innodb.dataReadsPerSecond: The number of reads per second.
db.innodb.dataWritesPerSecond: The number of writes per second.
db.innodb.osLogWrittenBytesPerSecond: The transaction log throughput. This value is proportional to how much the data changes over time.
temporary disk activity by adjusting sort_buffer_size and join_buffer_size, and rewriting queries.
Table locks
row-level locks to ensure data consistency
db.tablesLocksWaitedPerSecond for tables,
and db.innodb.rowLockTimeAvg and
db.innodb.rowLockWaitsPerSecond for rows.
read myisam vs innodb
missing index
MySQL uses indexes for filtering, sorting, and joining tables.
when index misses it scans whole tables.
db.selectFullJoinPerSecond and db.selectFullJoinRangePerSecond indicate whether your tables need additional indexes.
db.innodb.bufferPoolReadAheadRndPerSecond helps detect inefficient table-level reads.
Advanced metrics at data explorer
At the Browse data dropdown, select Events or Metrics.
mysql configuration settings
config/mysql (593)
config/mysql/have_query_cache
no: disable from 5.7 onwards
config/mysql/innodb_buffer_pool_chunk_size
variant hosts value
1 item
134217728 //134mb
mysql profiling
config/mysql/profiling =>off
newrelic mysql metrics explorer
mysql.node.createdTmpFilesPerSecond
mysql.node.createdTmpTablesPerSecond
mysql.node.handler.deletePerSecond
mysql.node.handler.readFirstPerSecond
mysql.node.handler.readKeyPerSecond
mysql.node.handler…eadRndNextPerSecond
mysql.node.handler.readRndPerSecond
mysql.node.handler.rollbackPerSecond
mysql.node.handler.updatePerSecond
mysql.node.handler.writePerSecond
mysql.node.innodb.bufferPool.pagesData
mysql.node.innodb.…fferPool.pagesDirty
mysql.node.innodb.b…esFlushedPerSecond
mysql.node.innodb.bufferPool.pagesFree
mysql.node.innodb.…fferPool.pagesTotal
mysql.node.innodb.b…adEvictedPerSecond
mysql.node.innodb.b…readAheadPerSecond
mysql.node.innodb.b…dAheadRndPerSecond
mysql.node.innodb.b…dRequestsPerSecond
mysql.node.innodb.b…ool.readsPerSecond
mysql.node.innodb.b….waitFreePerSecond
mysql.node.innodb.b…eRequestsPerSecond
mysql.node.innodb.dataFsyncsPerSecond
mysql.node.innodb.dataPendingFsyncs
mysql.node.innodb.dataPendingReads
mysql.node.innodb.dataPendingWrites
mysql.node.innodb.d…ReadBytesPerSecond
mysql.node.innodb.dataReadsPerSecond
mysql.node.innodb.dataWritesPerSecond
mysql.node.innodb.d…ttenBytesPerSecond
mysql.node.innodb.logWaitsPerSecond
mysql.node.innodb.l…eRequestsPerSecond
mysql.node.innodb.logWritesPerSecond
mysql.node.innodb.numOpenFiles
mysql.node.innodb.osLogFsyncsPerSecond
mysql.node.innodb.osLogPendingFsyncs
mysql.node.innodb.osLogPendingWrites
mysql.node.innodb.o…ttenBytesPerSecond
mysql.node.innodb.…gesCreatedPerSecond
mysql.node.innodb.pagesReadPerSecond
mysql.node.innodb.…gesWrittenPerSecond
mysql.node.innodb.rowLockCurrentWaits
mysql.node.innodb.rowLockTimeAvg
mysql.node.innodb.…wLockWaitsPerSecond
mysql.node.innodb.rowsDeletedPerSecond
mysql.node.innodb.…wsInsertedPerSecond
mysql.node.innodb.rowsReadPerSecond
mysql.node.innodb.rowsUpdatedPerSecond
mysql.node.maxExecu…eExceededPerSecond
mysql.node.net.abortedClientsPerSecond
mysql.node.net.abo…edConnectsPerSecond
mysql.node.net.bytesReceivedPerSecond
mysql.node.net.bytesSentPerSecond
mysql.node.net.conn…nnectionsPerSecond
mysql.node.net.connectionsPerSecond
mysql.node.net.maxUsedConnections
mysql.node.net.threadsConnected
mysql.node.net.threadsRunning
mysql.node.openedTablesPerSecond
mysql.node.openFiles
mysql.node.openTables
mysql.node.qCache.utilization
mysql.node.query.commitPerSecond
mysql.node.query.deleteMultiPerSecond
mysql.node.query.deletePerSecond
mysql.node.query.insertPerSecond
mysql.node.query.insertSelectPerSecond
mysql.node.query.pr…StmtCountPerSecond
mysql.node.query.queriesPerSecond
mysql.node.query.questionsPerSecond
mysql.node.query.r…laceSelectPerSecond
mysql.node.query.rollbackPerSecond
mysql.node.query.selectPerSecond
mysql.node.query.slowQueriesPerSecond
mysql.node.query.updateMultiPerSecond
mysql.node.query.updatePerSecond
mysql.node.selectFullJoinPerSecond
mysql.node.selectF…lJoinRangePerSecond
mysql.node.selectRangeCheckPerSecond
mysql.node.selectRangePerSecond
mysql.node.sortMergePassesPerSecond
mysql.node.sortRangePerSecond
mysql.node.sortRowsPerSecond
mysql.node.sortScanPerSecond
mysql.node.tableOpenCacheHitsPerSecond
mysql.node.tableOpe…cheMissesPerSecond
mysql.node.tableOpe…OverflowsPerSecond
mysql.node.tablesLocksWaitedPerSecond
mysql.node.threadCacheMissRate
mysql.node.threadsCached
mysql.node.threadsCreatedPerSecond
newrelic.goldenmetr…nnectionsPerSecond
newrelic.goldenmetr…e.queriesPerSecond
newrelic.goldenmetr…owQueriesPerMinute
understanding mysql performance metrics
Reference:
