MySQL cannot use indexes - common mistake

I recently helped one customer to help resolve performance issue. The problem was caused by a very simple update query that modifies only one record:

EXPLAIN UPDATE PROD_CHECK_MODULES SET DATE_CHECK = NOW() WHERE COUGAR_NAME='cougar-p01' AND ID_MODULE=4 AND NUM_MODULE=03;
+------+-------------+--------------------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table              | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+--------------------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | PROD_CHECK_MODULES | range | ID_MODULE     | ID_MODULE | 4       | NULL |    6 | Using where |
+------+-------------+--------------------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)


 SHOW CREATE TABLE PROD_CHECK_MODULES\G
*************************** 1. row ***************************
....
`COUGAR_NAME` varchar(64) NOT NULL,
  `ID_MODULE` int(11) NOT NULL,
  `NUM_MODULE` char(3) NOT NULL
...
UNIQUE KEY `COUGAR_NAME` (`COUGAR_NAME`,`ID_MODULE`,`NUM_MODULE`),

KEY `ID_MODULE` (`ID_MODULE`)
.....

Ideally it should be using "unique key" to examine/update 1 row. Table in question has got < 200 rows. However, it still does not look too bad "6 rows vs 1 rows" but it blocked large number of similar update queries due to long running transaction.  The first thing done was to identify and kill long running transaction that was blocking other 'update' queries. and then tune 'update' query. 

The issue here is that value 03 cannot be compared to column NUM_MODULE without data type conversion i.e. we are comparing numeric value with string column, that is why MySQL could not use available and most suitable index . We had two solutions to fix this:

a) Modify query to use quotes with a value e.g. 

NUM_MODULE="03"

b) Modify table structure to use type integer 

NUM_MODULE SMALLINT NOT NULL

The easy fix was to modify table structure, because all NUM_MODULE values  are numeric and table is quite small.

Tracking long running processes in MySQL

Tracking long running processes in MySQL is not difficult. A process can be:

• SQL query
• Transaction 

There are different ways of tracking both type of processes.. First we would look at some of the available methods to identify long running queries:

• slow query log
• [pt-kill]
• performance schema
• packet inspection

Slow query log - This is the common method of identifying slow queries. You would need to check if its already enabled:

mysql> SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE 'slow_query%' OR VARIABLE_NAME LIKE 'long_query_time';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| long_query_time     | 10.000000                        |
| slow_query_log      | OFF                               |
| slow_query_log_file | /data/mysql/log/mariadb-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)

In this example, it is not. To enable slow query log, issue following commands: 

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=1.0;
Query OK, 0 rows affected (0.00 sec)

I took the opportunity to reduce long_query_time value from 10 seconds to just 1 second as well. The idea is to capture all queries that take at least 1 second. To make those changes persistent you would have to modify MySQL configuration file (e.g. /etc/my.cnf or my.ini) 

[mysqld]
..
slow_query_log  = ON
long_query_time = 1

Next you would like to summarize slow query log,  you have two famous methods to choose from:

mysqldumpslow - classic method that have been used since mysql is born, on-line docs explain it very well, click here

pt-query-digest - This method has been widely adopted by the community, click here to learn more about it. 

If you are still using older release of MySQL e.g. 5.0 or earlier. It is not possible to enable slow query logging without rebooting MySQLd process. If you cannot restart MySQL process, you can still track slow queries using pt-kill utility:

$ pt-kill --host=localhost -udba --ask-pass --busy-time 1 --print --daemonize --log /var/log/slow-query.log # --busy-time decides queries that have been running for longer than this time

Remember NOT to specify --kill option, as it would start killing your queries

Performance schema - It was introduced in version 5.5, by default it is enabled in 5.6+ release. It provides a way to inspect internal server events (such as IO activity, locks, query profiling etc). It can also be used to track timing on long running processes.  Below are few examples of the commands that can be used to track long running queries, queries that use temporary table and/or the ones that perform full-table scan:

###
## A high level overview of the statements like Query Analysis, sorted by those queries with the highest latency
##
 
SELECT DIGEST, DIGEST_TEXT AS query,
       first_seen,
    last_seen,
       COUNT_STAR AS exec_count,
       SUM_NO_INDEX_USED AS no_index_used_count,
       SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
       ROUND((SUM_NO_INDEX_USED / COUNT_STAR) * 100) no_index_used_pct
      FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY no_index_used_pct DESC, exec_count DESC LIMIT 5\G 
 
###
# List all normalized statements that use temporary tables ordered by number of on disk temporary tables descending first, then by the number of memory tables.
###
 
SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
       SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
       ROUND(SUM_CREATED_TMP_TABLES / COUNT_STAR) AS avg_tmp_tables_per_query,
       ROUND((SUM_CREATED_TMP_DISK_TABLES / SUM_CREATED_TMP_TABLES) * 100) AS tmp_tables_to_disk_pct,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC LIMIT 5;
 
###
# List all normalized statements that use have done a full table scan ordered by the percentage of times a full scan was done, then by the number of times the statement executed
###
 
SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_NO_INDEX_USED AS no_index_used_count,
       SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
       ROUND((SUM_NO_INDEX_USED / COUNT_STAR) * 100) no_index_used_pct,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY no_index_used_pct DESC, exec_count DESC LIMIT 5;

Packet inspection - this can be achieved using tcpdump utility, not covered in this article. This is due to its known limitations:

• you can only observe queries sent over the network.
• you don’t see queries sent through Unix sockets or via replication. 
• you also can’t see queries run from stored procedures.
• security concern, this method requires root access.

 if you are keen to explore this method of tracking slow queries click here and here 

In the next article, I would blog about how to identify long running transactions...

MySQL-5.7.5-m15 - out of the box security improvements

In this article I would understand out of the box security improvement available in MySQL 5.7.5 release (not production ready yet). I am using Centos 6.5 and using RPM packages. 

Installing using RPMs

[root@localhost ~]# rpm -ivh mysql-community-server-5.7.5-0.6.m15.el6.i686.rpm mysql-community-client-5.7.5-0.6.m15.el6.i686.rpm mysql-community-libs-5.7.5-0.6.m15.el6.i686.rpm mysql-community-common-5.7.5-0.6.m15.el6.i686.rpm

Preparing...                ########################################### [100%]

   1:mysql-community-common ########################################### [ 25%]

   2:mysql-community-libs   ########################################### [ 50%]

   3:mysql-community-client ########################################### [ 75%]

   4:mysql-community-server ########################################### [100%]

Starting MySQL Server

[root@localhost ~]# /etc/init.d/mysqld start

Initializing MySQL database:                               [  OK  ]

Starting mysqld:                                           [  OK  ]

Securing the MySQL server deployment.

Connecting to MySQL server using password in '/root/.mysql_secret'

VALIDATE PASSWORD PLUGIN can be used to test passwords

and improve security. It checks the strength of password

and allows the users to set only those passwords which are

secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:  y

There are three levels of password validation policy:

LOW    Length >= 8

MEDIUM Length >= 8, numeric, mixed case, and special characters

STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:  2

By default, a MySQL installation has an anonymous user,

allowing anyone to log into MySQL without having to have

a user account created for them. This is intended only for

testing, and to make the installation go a bit smoother.

You should remove them before moving into a production

environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :  y

Success.

Normally, root should only be allowed to connect from

'localhost'. This ensures that someone cannot guess at

the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :  y

Success.

By default, MySQL comes with a database named 'test' that

anyone can access. This is also intended only for testing,

and should be removed before moving into a production

environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :  y

 - Dropping test database...

Success.

 - Removing privileges on test database...

Success.

Reloading the privilege tables will ensure that all changes

made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :  y

Success.

All done!

-------------------------------------------------------------------------------------------------------

A very long output but easy to follow, many improvements:

* Password validation plugin is by default loaded and the password validation policy is set to 'STRONG'.
*  Installation removes test database and anonymous user accounts.
*  Installation creates only one 'root' account and automatically generates a password for this account..
* Installation automatically remove permissions on non-existing test% databases.

To  login to the MySQL server, I would need to use random password generated by the installation that is located in /root/.mysql_secret and select a new password using SET PASSWORD() command.


SQL MODE Changes


Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default. An error occurs for invalid or missing values in a data-change statement. The statement is aborted and rolled back. For example, if you wish to insert a value that exceeds the specified column width i.e. VARCHAR(20)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> insert into t values (111, repeat('a',21));

ERROR 1406 (22001): Data too long for column 'b' at row 1


Password Expiration Policy

User accounts would automatically expire after 360 days! this is great improvement. As you would want (wish) some of the accounts to definitely expire. At the same time you can disable password expiration for some of the accounts.  You can read MySQL manuals to learn more about this feature here

MySQL OLD Passwords

The mysql_old_password authentication plugin is removed. Accounts that use this plugin are disabled at startup and the server writes an “unknown plugin” message to the error log. For instructions on upgrading accounts that use this plugin, see