A MySQL/MariaDB DBA/Consultant/Trainer with Expertise in Database Designing, Development, Maintenance, Installation, Upgradation, Configuration optimization, Setting and Managing Replication, Backup and Restore,Cross Database Migration, Optimization, Synchronization of replicated database, Performance and Monitoring, High Availability
Tuesday, November 6, 2012
Troubleshooting MySQL database performance
are some cool ways, how you can monitor the database performance of your MySQL
Monitoring of any database/Application is always an iterative and continuous
process. You need to check which graph is ok for your Application/Database. You
need to measure which values are the signs of Alert or DR and what may cause it.
Below are the main items you can use to monitor your system:
1) MySQL error log
-The error log is great source of information in case of there is any error. Nothing should write to the error log, after
the MySQL server has completed its initialization sequence, so everything written
in error log after initialization should check immediately.
2) mysqladmin processlist or “SHOW
FULL PROCESSLIST” command
It will return number of threads connected and running by using other
statistics, but this is a good way to check how long queries that are running
take. If there are some very long-running queries the admin/Application team should be informed.
3) mysqladmin extended
a client utility available with MySQL. You can monitor MySQL status using this
utility. You need to check below parameter.
# Slave_running: If replication is
configured on system, it will be provide slave status. This command should be
executed on slave server. #Threads_connected: The number of
clients currently connected to MySQL server. This should be less than configured
# Threads_running: If the database is overloaded you’ll get an increased
number of queries running. That also should be less than some preset value . It
is OK to have values over the limit for very short times. Then you can monitor
some other values, when the Threads_running was more than the preset value and
when it did not fall back in 5 seconds.
4) mysqladmin extended
idea is that you store the performance counter value and compute the difference
with the new values. The interval between the recordings should be more than 10
seconds. The following values are good candidates for checking:
* Aborted_clients: The number of clients that were aborted (because they did
not properly close the connection to the MySQL server). For some applications
this can be OK, but for some other applications you might want to track the
value, as aborted connects may indicate some sort of application failure.
* Questions: Number of queries you get per second. Also, it’s total queries,
not number per second. To get number per second, you must divide Questions by
* Handler_*: If you want to monitor low-level database load, these are good
values to track. If the value of Handler_read_rnd_next is abnormal relative to
the value that you normally would expect, it may indicate some optimization or
index problems. Handler_rollback will show the number of queries that have been
rolled back. You might want to wish to investigate them.
* Opened_tables: Number of table cache misses. If the value is large, you
probably need to increase table_cache. Typically you would want this to be less
than 1 or 2 opened tables per second.
* Select_full_join: Joins performed without keys. This should be zero. This is
a good way to catch development errors, as just a few such queries can degrease
the system’s performance.
* Select_scan: Number of queries that performed a full table scan. In some
cases these are OK but their ratio to all queries should be constant. if you
have the value growing it can be a problem with the optimizer, lack of indexes
or some other problem
* Slow_queries: Number of queries longer than –long-query-time or that are not
using indexes. These should be a small fraction of all queries. If it grows,
the system will have performance problems.
* Threads_created: This should be low. Higher values may mean that you need to
increase the value of thread_cache or you have the amount of connections
increasing, which also indicates a potential problem.
5) “SHOW INNODB STATUS”
This statement produces a great deal of information, from which you should
extract the parts in which you are interested. The first thing you need to
check is: “Per second averages calculated from the last xx seconds”. InnoDB
rounds stats each minute.
* Pending normal aio reads: These are InnoDB IO request queue sizes. If they
are bigger than 10-20 you might have some bottleneck.
* reads/s, avg bytes/read, writes/s, fsyncs/s: These are IO statistics. Large
values for reads/writes means the IO subsystem is being loaded. Proper values
for these depend on your system configuration.
* Buffer pool hit rate: The hit rate also depends a lot on your application.
Check your hit rate, when there are problems.
* inserts/s, updates/s, deletes/s, reads/s: These are low level row operations
that InnoDB does. You might use these to check your load if it is in expected
detail information this output you can get here.
6) OS Data. Good tools to see the system status are vmstat/iostat/mpstat.
#iostat will provide information about io on