Performance Monitoring of MySQL Server:
Following are the command which we can use for session or server level performance for MySQL server.
SHOW GLOBAL STATUS – shows global server status
SHOW LOCAL STATUS - This is used for session level server status
Have to check following values to know how server works.
Aborted_clients : Usually no need to worry for this because many programs/application don’t close connection properly.
Aborted_connects: This means authentication failure, network timeout or any other error. If the value is high than its possible that someone tries to break the password or something.
Com_XXX: This can be used to check server load that which statements are running most on server.
- Temporary Objects
Created_tmp_tables: Temporary tables can often be avoided by query optimization
Created_tmp_disk_tables: Not enough memory is allocated, need to increase tmp_table_size and max_heap_table_size
Handler_read_key, Handler_read_next – Indexes are used or not by the queries
Handler_read_rnd, Handler_read_rnd_next – full table scans are done or not
- Key Cache Info
Key_blocks_used / Key_blocks_unused : This will show how much key_buffer is used, key_blocks_used should be key_blocks_ever_used or not. This will help us that how much key_buffer should be set.
Key_read_requests, Key_reads, Key_write_requests,Key_writes: This will show how much good is key buffer usage
- Connections and Tables
Max_used_connections: If this is >= max_connections than you need to increase max_connections size.
Open_files: This should not be run out of limit.
Open_tables: – This will show how table cache is used
Opened_tables We can adjust –table-cache variable for this if its value is high or as per our requirement. Before that we have to make sure that open-file-limit should be large enough.
- Query Cache Status
Qcache_hits: This will show how frequently query is used from query cache.
Qcache_inserts: How much queries are stored in query cache.
Qcache_free_memory: Free/Unused memory in query cache. Often query cache can use limited memory because of invalidation
Qcache_lowmem_prunes: Not enough memory or too fragmented
Select_full_join: Joins without indexes. This very bad and dangerous for query performance.
Select_range: This will show range scan queries.
Select_range_check: Usually queries worth looking to optimize because queries are not using indexes.
Select_scan: Full Table Scan. Small or Large. This is also dangerous.
Sort_merge_passes: If this is high than should increase sort_buffer_size.
Sort_range: This shows sorting of ranges
Sort_scan: This shows sorting full table scans
- Table locks
Table_locks_immediate: This shows table locks granted without waiting
Table_locks_waited: This shows table locks which had to be waited. Long wait on table lock is bad for server performance.
Threads_cached: This shows how many threads are cached.
Threads_connected: This shows how many thread are connected.
Threads_created: This shows how much threads are missed to cache. If this is high than should increase thread_cache.
Threads_running: This shows how many threads are running currently.