Variable's Day Out #14: log_queries_not_using_indexes

Properties:

Applicable To	MySQL Server
Server Startup Option	--log-queries-not-using-indexes
Scope	Global
Dynamic	Yes
Possible Values	Boolean
Default	False
Category	Performance, Monitoring, Best Practices

Description:

If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.

Usage:

While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable log_queries_not_using_indexes and start index usage testing. Analyze all queries, introduce indexes one by one and you are all set to go. This helps a lot in rewriting queries, figuring out the best possible index etc. Many times I have seen the need for swapping WHERE clause CONDITIONS for making it possible for the query to use an existing index.

For already deployed systems' performance glitches and production outages, this flag do serve as a starting point.

Though as always, usage of a variable is subject to circumstances. For my conditions and usage pattern, this variable comes very handy.

Read more:

• MySQL manual entry on log-queries-not-using-indexes
• Slow Query Logs

Hope you enjoyed reading this.

Variable's Day Out #9: long_query_time

Properties:

Applicable To	MySQL Server
Server Startup Option	--long-query-time=<value>
Scope	Both
Dynamic	Yes
Possible Values	(< 5.1.21): Integer (>=5.1.21): Numeric
Minimum Value	(< 5.1.21): 1 (>=5.1.21): 0
Default Value	10
Category	Monitoring

Description:

In case (as generally the case is) one wants to know about the bottlenecks in their system, MySQL's has a small answer to this in "Slow Query Logs". Any query that takes more time than specified by long_query_time, is labeled as a slow query and logged. Time measurement for a query is done in real time and not CPU time. So, whether a query is slow or not depends on the system that you are running on and the load the system is running under.

For many people's delight, as of MySQL 5.1.21, the long_query_time can be specified in microseconds and minimum value has been changed to 0. Earlier, the minimum value was 1.  If set to "0", it will log all the queries under slow query log. However, the new microsecond resolution in 5.1.21 is only supported when logging to a file and microsecond part is ignored when logging to table. The table logging was introduced in MySQL 5.1.6.

How to Use:

This is completely dependent on your use-case, SLA's etc. I have two systems, one of them is running with long_query_time being 5 seconds. Whereas for the second one, we are planning to upgrade so to become capable of using microsecond resolution (actually we need up-to millisecond resolution ;) ).

Related Variables:

• log_slow_queries
• log_queries_not_using_indexes
• min_examined_row_limits
• log_output
• slow_query_log_file

Read More:

• MySQL manual entry on long_query_time
• "The Slow Query Log" documentation
• ... & Slow Query Log Output Destinations

 

Hope you enjoyed reading this.

Variable's Day Out #3: max_connect_errors

 Properties:

Applicable To	MySQL Server
Server Startup Option	--max_connect_errors=<value>
Scope	Global
Dynamic	Yes
Possible Values	Integer Range: 1 - 4294967295
Default Value	10
Category	Security

Description:

This variable determines how many interrupted connections can occur from a host. If the number of interrupted connections from this host surpasses this number, that host is blocked from further connections. All of the following, as listed here, will cause the counter to be incremented.

1. Client program did not call mysql_close() before exiting.
2. Client had been sleeping more than wait_timeout or interactive_timeout without issuing any requests to the server.
3. Client program ended abruptly in the middle of a data transfer.
4. Client doesn't have privileges to connect to a database.
5. Client uses incorrect password.
6. It takes more than connect_timeout seconds to get a connect packet.

You can always unblock the hosts with a FLUSH HOSTS statement.

Prior to 5.1, any error would just increment the counter for a host. In 5.1, if a host is not yet blocked, any successful handshake resets the counter to 0.

Usage:

It depends on the system admin, how they want to use this variable. For example, If the MySQL server is at a physically distant location from it's clients, one might not want unnecessary network issues to get a host blocked. Otherwise, if the expected client machines are somewhat known, one might not want to invite hackers.

I generally consider a higher value for this variable to be a security threat, as some unfriendly people may use it for a DOS attack. It's better to keep the value low and system admin aware of the hosts being blocked.

Read More:

• MySQL manual entry on max_connect_errors
• Communication Errors and Aborted Connections
• Nice discussion on resetting counter
• Possible feature in future
• Host 'host_name' is blocked error explained

 

Hope this post was helpful to you. Keep posting your comments.

MyEye update

Since ages, I was busy with many projects including my marriage. Nice to be back on the blog with an update on MyEye - the project I announced in my very first post. MyEye is an (or is going to be an) open source monitoring tool for MySQL installations with handy knowledge-base and advisories for MySQL DBAs. So, I'm done with the first round of analysis and design for MyEye. As far as it is planned, it's going to be a framework that would provide interface for people to write their own monitoring rules. Meanwhile MyEye web site is also under construction and I will be publishing the documentation soon there. Will be looking forward to your contributions for creating an open knowledge base for MyEye.