Connectivity Tuning

Chances are that your MySQL server does not exist in isolation; it's likely that other computers connect to it for a myriad of reasons, including accessing and updating information. This section examines ways to make these connections more efficient.

Protocol Choices

Depending on your platform, MySQL offers a number of different protocols for communication between clients and the database server. This section looks at each of these major choices, as well as when to employ them for optimal speed.

TCP/IP

So much of today's computing relies on TCP/IP that it's natural that the first choice of many administrators is to turn to this protocol. In most cases, this is the right choice. In fact, it is the only choice if the database client and server are not resident on the same machine. Even if they are on the same machine, computers running certain older versions of Windows have no option but to use this protocol.

When the database client and server run on the same machine, administrators' choices become more interesting. The next sections cover these alternatives. If you elect to employ these other options and have no need for TCP/IP, you can disable this protocol by launching your server with the - skip-networking directive. As a side benefit, this setting also improves security because remote users cannot log in to the database.

Named Pipes

Administrators can choose the named pipe protocol for Windows-based computers with the database server and clients both present on the same machine. To launch the server with named pipes enabled, specify - enable-named-pipe when starting MySQL.

In terms of performance, there is some debate as to whether named pipes are better than TCP/IP. When faced with conflicting information, the wisest approach is to test both alternatives in your own unique environment. If you don't have the time or patience to run these tests, this is probably one of those cases in which the status quo (TCP/IP in this instance) is the better choice.

Sockets

These Linux/Unix counterparts to the Windows named pipes can be selected as the protocol for clients and servers running on the same computer by specifying localhost in the connection string.

Shared Memory

Long a fixture of other database platforms, MySQL introduced shared memory connections in version 4.1 for Windows servers. Note that this is only relevant if the clients are running on the server. However, shared memory connections typically are very fast it's hard to beat memory for speed.

Regardless of your choice of protocol, you can use the - protocol directive (with available options of TCP, PIPE, SOCKET, and MEMORY) when launching the client.

Scalable Coherent Interface (SCI)

One final protocol merits mention: SCI. Used in conjunction with clustering, this technology can greatly boost the communication speed among servers participating in a NDB Cluster. This protocol is discussed in more detail in Chapter 17's examination of clustering.

Costs of Hostname Resolution

To ensure that a new client has the right to connect to your database, MySQL performs a series of steps to validate this client. Part of this validation includes looking up the hostname of the client and then matching it against the internal grant tables.

However, depending on the multithreading capabilities of your operating system, it's possible that the act of looking up the hostname can cause a bottleneck; similar operations might have to queue until the search has been resolved. This problem can be exacerbated if your DNS lookup service is slow.

If you are concerned that these lookups might be causing undue delays, and if your clients have known, static IP addresses, one alternative is to simply use these IP addresses when granting access:

 GRANT ALL PRIVILEGES ON high_hat.* TO 'melvin'@'218.22.192.244'; 

If you elect to follow this route, you must also tell MySQL not to perform the DNS lookup by launching mysqld with the -skip-name-resolve option enabled.

Of course, if you have no way of knowing these IP addresses in advance, hard-coding numeric IP addresses don't solve the problem. In this kind of situation, you can follow still other approaches, including finding a faster DNS server or increasing your name caching. A truly radical approach is to use --skip-grant-tables, which disables verification of client connections via the grant tables. Of course, this also disables GRANT and REVOKE, so it is an extremely drastic measure.

Costs of Creating and Destroying Connections

Chapter 10, "General Server Performance and Parameters Tuning," provides a general MySQL engine tuning review and describes all of the internal work that MySQL must perform to satisfy an incoming connection request. To reiterate, a number of key internal structures and buffers must be initialized and staged so that the new connection can be supported correctly.

Given this overhead, anything you can do to either consolidate or eliminate the number of new connection requests translates into less work for the database server.

Let's look at a simple "before-and-after" example to give you an idea of the impact of excess connection management loads for the server. To begin, assume that this sample application is served by a single web server in contact with a single database server. A collection of PHP processes are running on the web server, responsible for measuring, recording, and tracking a variety of statistics (page visited, time spent per page, and so on). The web server is heavily loaded, so the database is accessed frequently.

The "before" application was developed to minimize its connections to MySQL; every time a new instance of the web application is launched, the PHP script creates a new connection and registers key information (date, time, IP address, and so forth). After this data has been committed into MySQL, the PHP script drops the connection.

Can you see the inefficiency here? Contrast this scenario with one that keeps the connection alive, inserting rows as needed. To illustrate the costs of this extra work, two sample PHP scripts were written and then benchmarked. The "after" script, which used a persistent connection to MySQL and inserted 100,000 rows, ran four times faster than the "before" script, which had to wade through the same amount of work while continually opening and closing database connections.

Costs of SSL

Just as TCP/IP is the foundation of the communication that makes the Internet possible, SSL technology is the groundwork of much of the Internet's security.

MySQL administrators can employ SSL to reduce the likelihood of undesired eavesdropping on their client-to-server connections. This security comes at a cost: There are extra configuration tasks for the administrator, and both sides of the connection must expend additional CPU cycles to encrypt and then decrypt message traffic.

However, if your clients and database servers communicate over an open network, you really have no choice but to implement SSL. Fortunately, MySQL gives you the flexibility of using SSL on a per-connection basis. Given that option, it's wisest to mandate SSL for only those clients that are not connecting securely. Other clients can continue using their default connections.

If SSL performance is a big concern for your environment, consider one of the many SSL accelerator appliances now available. These devices off-load the time-consuming encryption/decryption tasks, thereby unburdening your server.