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.

Variable's Day Out #2: key_buffer_size

Properties:

Engine(s)	MyISAM
Server Startup Option	--key_buffer_size=<value>
Scope	Global
Dynamic	Yes
Possible Values	Integer Range: 8 - 4294967295 (4 GB)
Default Value	131072 (128 KB)
Category	Performance

Description:

This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the MySQL Documentation, "using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common". As suggested by Peter, "Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload".

Starting with 25%, one can keep monitoring key cache hit ratio and increase it over time. A key cache hit ratio of more than 95% should be good enough. Also, for preventing oneself from allocating too much memory to this buffer, monitoring key buffer usage should be helpful. I feel one should target towards more than 80% of buffer usage.

If you are not using very few MyISAM tables, still key_buffer can be useful for MySQL's tables and temporary tables as well.

Calculations:

• key_cache_hit_ratio = (1 - Key_reads/Key_read_requests) * 100
• key_buffer_usage    = (1 - (Key_blocks_unused * key_cache_block_size)/key_buffer_size) * 100

Read More:

MySQL manual entry on key_buffer_size

What to tune in MySQL server after installation

MyISAM Key Cache - MySQL Manual

 

Hope this post was helpful to you.

Variable's Day Out #1: delay_key_write

After seeing some very good "of-the-day" series, I thought why should MySQL variables be left behind. So, my contribution in the race - "variable's day out". I won't brand it "of-the-day" series as it calls for too much of dedication and given my track record I shouldn't promise that. Henceforth, instead of calling a variable lucky enough for a day, lets call a day lucky enough for a variable. Though I will try my best to keep the lucky days rolling.

Today's variable is delay_key_write. Properties:

Engines	MyISAM
Server Startup Option	--delay-key-writes[=<OFF|ON|ALL>]
Scope	Global
Dynamic	Yes
Possible Values	enum('ON', 'OFF', 'ALL')
Default Value	ON
Category	Performance

Description:

For MyISAM tables, delayed key writes doesn't allow key buffers to be flushed between writes.

Values:

OFF	Disables delayed key writing.
ON	Enables for the tables created with DELAYED_KEY_WRITE option.
ALL	Enables delayed key writing for all MyISAM tables.

Pros:

Enabling delayed key writing is good for heavy load tables (read or write heavy) as it decreases disk access.

Cons:

If the server goes down, the indexes have to be rebuilt.

Read more:

MySQL manual entry on delay_key_write

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.