Monitoring MySQL performance

If monitoring MySQL performance by analyzing its status values

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_XXX

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

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.

§  Sorting

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

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.