Skip to main content

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.

Comments

Post a Comment

Popular posts from this blog

MySQL thread error code of 1032- Translate binlog completely to avoid error

Could not execute Update_rows event on table db.table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's   master log mysql-bin.262297, end_log_pos 1983208 As of my knowledge the causing stop of slave SQL thread error code of 1032, due to lack of sync between Master - Slave. In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately) You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode. So, now check that particular binlog at that position where replication stopped with this error. When you convert the binlog with mysqlbinlog command, you may see some junk characters   where you were expecting some DMLs which caused the error(In our case its an Update statement). So you can use below command to translate binlog completely: mysqlbinlog --base64-output=DECO...
Post a Comment
Read more

Mongodb online Training course Agenda

  Goal:   In this module, you will get an understanding of NoSQL databases, design goals, requirement of NoSQL database/ MongoDB, MongoDB® architecture and introduction to JSON and BSON among others. This module will also cover the installation of MongoDB® and associated tools. Skills Understand NoSQL databases and  advantages Install MongoDB on Windows and Linux platform Security Enable and high availability Objectives After completing this module, you will  be able knowledge ed on SQL and noSQL Database usages and difference between these MongoDB design and architecture  MongoDB GUI tools Describe JSON and BSON Install MongoDB on Windows, Linux, MAC OS etc.  Setup MongoDB environment Topics • Understanding the basic concepts of a Database • Database categories: What is NoSQL? Why NoSQL? Benefit over RDBMS  • Types of NoSQL Database, and NoSQL vs. SQL Comparison, ACID & Base Property • CAP Theorem, implementing NoSQL and what is MongoDB?  • O...
Post a Comment
Read more

Linux Commands With Examples for Database Admins

Frequently Used Linux Commands With Examples 1. tar command examples Create a new tar archive. $ tar cvf archive_name.tar dirname/ Extract from an existing tar archive. $ tar xvf archive_name.tar View an existing tar archive. $ tar tvf archive_name.tar More tar examples: The Ultimate Tar Command Tutorial with 10 Practical Examples 2. grep command examples Search for a given string in a file (case in-sensitive search).  $ grep -i "the" demo_file Print the matched line, along with the 3 lines after it. $ grep -A 3 -i "example" demo_text Search for a given string in all files recursively $ grep -r "ramesh" * More grep examples: Get a Grip on the Grep! – 15 Practical Grep Command Examples 3. find command examples Find files using file-name ( case in-sensitve find) # find -iname "MyCProgram.c" Execute commands on files found by the find command $ find -iname "MyCProgram.c" -exec md5sum {} \; Find all empty...
Post a Comment
Read more