Below will list 250 variables related to table locks, buffers and various other mysql parameters in general. This should be the entry point to see the health of MySQL on a system.
mysql> show status;
Example of show status, below will give the count of waiting locks for a table.
mysql> show status like 'Table_locks_waited'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | Table_locks_waited | 1 | +--------------------+-------+ 1 row in set (0.00 sec)
PROCEDURE_ANALYSE
PROCEDURE_ANALYSE, Get field types and field lengths correct which will improve performance of MySQL Indexes and Caches. Note that we should do this on a very mature database with actual values then the test values to gain benefit from this.
mysql> select user from vicidial_users PROCEDURE ANALYSE(1,1)G; *************************** 1. row *************************** Field_name: wikidb.vicidial_users.user Min_value: 101 Max_value: U3004 Min_length: 2 Max_length: 8 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 7.4016 Std: NULL Optimal_fieldtype: CHAR(8) NOT NULL 1 row in set (0.00 sec)
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html
MySQL Log Slow Queries
Log Slow Queries which are not indexed, queries taking more then 1 second are logged by default :
In my.cnf we need the following lines
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes
To see the logged slow queries :
cat /var/log/mysql/mysql-slow.log | more
MySQL QUERY PLAN
To see analysis of query execution use EXPLAIN :
mysql> EXPLAIN SELECT user FROM vicidial_users; +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | vicidial_users | index | NULL | user | 22 | NULL | 2 | Using index | +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Use EXPLAIN EXTENDED for more verbose output. The parameter ‘extended’ will get any additional information MySQL has on the query. The ‘G’ at the end of the query will format the output into a more readable form.
mysql> EXPLAIN EXTENDED select user from vicidial_users WHERE user NOT IN ()G; +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | vicidial_users | index | user | user | 22 | NULL | 2 | Using where; Using index | +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
Optimization Hints
MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.
It should noted that using the hints incorrectly cause our queries to perform even worse, so be careful that the hint makes sense before using them.
Use EXPLAIN with a sample query with hint before making a change to understand how the query will be processed.
It is also a good idea to enclose the hints within a SQL comment, for example
SELECT /*! SQL_NO_CACHE */ columns FROM table.
This can help us to make our application a bit more portable.
