How to Monitor MySQL’s performance
Here are some ideas, how you can monitor the database performance of your MySQL installation. Monitoring is always an iterative and continuous process. You need to learn what patterns are OK for your database and what are the signs of slight problems or even dangerous situations.
Below are the main items you can use to monitor your system:
- mysqladmin extended (absolute values)
- mysqladmin extended -i10 -r (relative values)
- mysqladmin processlist
- mysql -e “show innodb status”
- OS data. vmstat/iostat
- MySQL error log
- InnoDB tablespace info.
1) mysqladmin extended (absolute values)
The values making most sense to monitor are:
* Slave_running: If the system is a slave replication server, this is an indication of the slave’s health.
* Threads_connected: The number of clients currrently connected. This should be less than some preset value (like 200), but you can also monitor that it is larger than some value to ensure that clients are active.
* 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 (20?). 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.
2) mysqladmin extended (counters)
The 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 Uptime.
* 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.
3) mysqladmin processlist or “SHOW FULL PROCESSLIST” command
You can get the 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 (e.g. due to being badly formulated) the admin should be informed. You might also want to check how many queries are in “Locked” state – these are not counted as running but are inactive, i.e. a user is waiting on the database to respond.
4) “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 range.
4) OS Data. Good tools to see the system status are vmstat/iostat/mpstat.
To see what kind of information these tools can provide for you,
read their man pages.
5) MySQL error log – Nothing should written to the error log, after the server has completed its initialization sequence, so everything appearing in the log should be brought to admin’s attention immediately.
6) InnoDB tablespace info.
With InnoDB the only danger is that the tablespace gets full – the logs can’t get full. Best way to check this is to do: show table status;
You can use any InnoDB table for monitoring the InnoDB table space free space.
shell> mysqladmin variables
MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance.
When tuning a MySQL server, the two most important variables to use are key_buffer_size and table_cache. You should first feel confident that you have these right before trying to change any of the other variables.
If you have much memory (>=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this:
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O read_buffer_size=1M &
If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like:
shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M
If you have little memory and lots of connections, use something like this:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O read_buffer_size=100k &
or even:
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K &
If you are doing a GROUP BY or ORDER BY on files that are much bigger than your available memory you should increase the value of record_rnd_buffer to speed up the reading of rows after the sorting is done.
When you have installed MySQL, the `support-files’ directory will contain some different `my.cnf’ example files, `my-huge.cnf’, `my-large.cnf’, `my-medium.cnf’, and `my-small.cnf’, you can use as a base to optimise your system.
If there are very many connections, “swapping problems” may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.
Note that if you change an option to mysqld, it remains in effect only for that instance of the server.
To see the effects of a parameter change, do something like this:
shell> mysqld -O key_buffer=32m –help
Make sure that the –help option is last; otherwise, the effect of any options listed after it on the command-line will not be reflected in the output.
These parameters can be seen here
We are going to see one example now this is the best tuned my.cnf file
[mysqld]
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Let’s just look at the important bits.
max_connections = 500 – You need to use mysqlreport tool(http://hackmysql.com/mysqlreport) to check how many current connections you have, and under very heavy load (2000 simultaneous users) You may rarely hit 400 concurrent connections to the database. This is because most connections only last for a few milliseconds.
key_buffer = 384M – When tuning a MySQL server, key_buffer_size is very important. This number works well for me and with the mysqlreport script I rarely use 50% of the available memory.
table_cache = 1800 – After key_buffer the next most important variable is your table cache. Again this is set for vBulletin so you may be able to significantly reduce this value depending on the number of tables in your database.
wait_timeout = 7200 – This variable determines the timeout in seconds before mysql will dump a connection. If set to low you will likely receive mySQL server has gone away errors in your log, which in vBulletin’s case is quite common.
max_allowed_packet = 16M – Again if set to low (the default is 8M) users will likely experience errors. 16M has always worked fine for my production environments.
One more important tool we have already menctioned mysqlreport this is the best tool available for free you can check your mysql database server performance.
Optimize Mysql Database response time
The most important part for getting a system fast is of course the basic design. You also need to know what kinds of things your system will be doing, and what your bottlenecks are.
The most common bottlenecks are:
Disk seeks. It takes time for the disk to find a piece of data. With modern disks in 1999, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimise for a single table. The way to optimise this is to spread the data on more than one disk.
Disk reading/writing. When the disk is at the correct position we need to read the data. With modern disks in 1999, one disk delivers something like 10-20 MB. This is easier to optimise than seeks because you can read in parallel from multiple disks.
CPU cycles. When we have the data in main memory (or if it already were there) we need to process it to get to our result. Having small tables compared to the memory is the most common limiting factor. But then, with small tables speed is usually not the problem.
Memory bandwidth. When the CPU needs more data than can fit in the CPU cache the main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one should be aware of it.
Some of the available options we will see now
Disable DNS Hostname Lookup
open you /etc/mysql/my.cnf file and use –skip-name-resolve option file looks like below.
[mysqld]
…..
……
skip-name-resolve
When this option is activated, you can only use IP numbers in the MySQL Grant table.
How MySQL uses DNS
When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.
If the operating system doesn’t support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.
You can disable DNS host lookup by starting mysqld with –skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with –skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.
You can disable the hostname cache with –skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.
If you don’t want to allow connections over TCP/IP, you can do this by starting mysqld with –skip-networking.
Activate Query Cache
mysql> SHOW STATUS LIKE ‘Qcache%’;
If you find any problems with query cache memory was left. It was neccessary to increase the query cache size.
To get an overview of your query_cache variables state, use the following syntax:
mysql> SHOW VARIABLES LIKE ‘%query_cache%’;
You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.
Now, you can increase the query cache size (let say you want 50M) using:
mysql> SET GLOBAL query_cache_size = 52428800;
If you want this setting to be kept when restarting mysql, add:
[mysqld]
…
…
query_cache_size = 52428800;
query_cache_type = 1
