|< Day Day Up >|
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:
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.
|< Day Day Up >|