Connectivity

The database connection experience is often the first impression a user will receive of your MySQL server. This next section discusses a collection of performance-specific connection settings.

Creating a Connection

To perform work in a database, a client application must first issue a connect request. After a connection is established, the client performs some work and eventually disconnects (or is timed-out by the database). Use connect_timeout to specify the number of seconds that MySQL should wait when servicing a connect request from a client.

Be careful about setting this value too low, especially if your clients are connecting via a slow network, are prone to momentary network lapses, or are underpowered.

You also can choose to optimize a number of communication factors for clients and database servers. To begin, you have control over the size of messages sent from the MySQL engine to your client application. Use the net_buffer_length setting, specified in bytes, to set the initial message size. This variable works in tandem with max_allowed_packet: If a message is larger than net_buffer_length, MySQL automatically raises the limit up to max_allowed_packet, which can hold up to 1GB of information.

If your applications employ large TEXT or BLOB values, consider setting the max_allowed_packet high; you can keep the net_buffer_length value low, and MySQL automatically adjusts when necessary (and then downsizes afterward).

Because connections are supported via threads (more on threads a little later), every time a connection request is made, MySQL's main thread services the request and then launches a new thread (or retrieves one from the thread cache) to support the link. This usually happens almost instantaneously.

However, there might be rare occasions in which large numbers of connection requests are submitted at one time to the server. The back_log parameter designates how many connection requests will be allowed to queue up for servicing before the database engine starts ignoring them (and the client receives an error). After the number of connection requests drops below back_log, MySQL again allows these requests to enter the queue.

Recent versions of MySQL set this value to a default of 50. If you want to change it, try to estimate the maximum number of concurrent connection requests your server is likely to receive. It's important to understand that this is different than concurrent connections; we're only interested in the number of connections in the queue at the same time. After you arrive at a number, set back_log slightly larger than this value. Finally, note that if you try to make this value larger than the number of incoming TCP/IP requests that your server can handle, MySQL will simply use this server-controlled maximum number.

To control the number of actual concurrent client connections, use the max_connections variable. If you set it too low, users will receive an error like the one shown in Figure 10.1.

Figure 10.1. A failed connection attempt.

You can determine your ratio of good connects to aborted connects by examining the connections and aborted_connects status variables, respectively. For the overall connection high-water-mark, look at the max_used_connections status variable:

 mysql> SHOW STATUS LIKE '%CONNECT%'; +----------------------+-------+ | Variable_name        | Value | +----------------------+-------+ | Aborted_connects     | 49    | | Connections          | 2191  | | Max_used_connections | 117   | | Threads_connected    | 58    | +----------------------+-------+ 

These results tell us that approximately 2% of all connections attempted have failed since the server was started or the statistics cleared. Remember that you can clear this data with the FLUSH STATUS command; this is a good idea when trying to debug a performance or other problem. Even FLUSH STATUS is not immune to tracking: See flush_commands to track how many times the server statistics have been reset.

Managing a Connection

After a client is connected, you can configure MySQL to drop the session under several conditions:

• Inactivity If a certain amount of time passes with no activity, you can instruct MySQL to end the conversation. In fact, you have two choices when determining this behavior:

  1. If your client is one that involves live user interaction with the database (typically, but not always, an application that presents a user interface), set the interactive_timeout variable to the number of seconds to wait before dropping the connection.

  2. If your client is more of a machine-to-machine application (that is, it doesn't present a user interface for direct database interaction), set the wait_timeout variable instead.

  Because open, inactive connections still consume resources (and can affect performance for active sessions), be certain to set this value low enough so that these clients are disconnected on a timely basis. Clients who attempt to continue working on a closed session receive a message like the following:

   ERROR 2013 (HY000): Lost connection to MySQL server during query 

  
  

  How can you be sure which of these two variables will take precedence in your environment? The only way to truly be sure is to know if the CLIENT_INTERACTIVE connection option has been set in the source code of the client. Obviously, if you don't have access to source code, you'll need to make an assumption and then test your hunch.

• Errors The net_read_timeout and net_write_timeout parameters work in conjunction, with the former tracking the amount of seconds that MySQL has waited when reading from a client and the latter monitoring the length of time to write to the client. If either of these values is tripped, MySQL terminates the connection.

  Try not to make these values too low, especially if your database clients and/or network are sluggish. Undersizing these parameters might cause valid connections to be dropped, possibly triggering long rollbacks if a transaction-enabled bulk loading operation is under way.

• Interruptions The net_retry_count variable specifies the number of times you are willing to let MySQL attempt to recover from a communication interruption. If you want to block a repeatedly interrupted, troublesome client, use the max_connect_errors parameter. When tripped, it prevents any future connections from that client until you clear the blockade via the FLUSH HOSTS command.

To see how many sessions have been closed abnormally, view the aborted_clients status variable. To get a better idea of your overall communication volume with your database clients, consult the bytes_received and bytes_sent status variables.