Upcoming Events

Cloud Connect
Santa Clara
Feb 13-16, 2012

Cloud Connect brings together the entire cloud eco-system to better understand the transformation we're experiencing and promises to be the defining event of the cloud computing industry. Learn about the latest cloud technologies and platforms from thought leaders in Cloud Connect’s comprehensive conference.

Register Now!

W O R K S H O P

Clearing Database Network Clogs

May 1, 2003
By Don MacVittie

	Issue TOC
	Print full article
	Print this page
	Download as PDF
	E-Mail this URL
	Discuss this article
	Flame the author

	In this article
	Introduction
	The Pool Is Open
	Step by Step
	Sites To See

You may not be losing much sleep over how your database affects your network, but here's a wake-up call: Databases today are more likely than ever to cause network congestion.

With advances in database and hardware technology, it's not unusual to have a database server with eight CPUs, 2 GB of memory and two, or even four, teamed NICs. The network can become the bottleneck for this supersize database, especially if the network wasn't designed to handle supersize traffic.

A database generates bursty traffic on the network because it waits for requests and then returns the result sets in spurts. Until recently, the limitations in database file system I/O, CPU time, memory and network-access speed kept databases and their bursty payloads from taxing the network. Other servers, including FTP servers, emit bursty traffic on the network, too.

The best way to remedy database traffic overload is to fine-tune the way your client application queries the database. There's a huge difference between configuring your client to query the database with "give me all the data about all our customers and I'll sort through them" and "give me information on customers such as X, but only their names, addresses and phone numbers."

If the application's SQL implementation limits the result set, the query will carry less data. The more users, however, the greater the impact queries have on the applications and the network. One or two users can get away with a query that selects, for instance, all 25,000 100-byte long records. But when 300 users attempt that, you have a problem. Then you need to optimize your queries or your database and revisit your network bandwidth and design.

If you have a single active connection to the database, meanwhile, only a minimal amount of traffic can traverse it--bad news for database performance but good news for the network. If you have multiple active connections that sit idle for a long time, however, you may be placing an unnecessary load on your network because most database technologies use messages to keep a connection open. Depending on the type of idle connection, you may want to reduce your number of active connections.

A Measure of Success

To gauge your database traffic's effect on the network, you must measure it with a network monitor. We used the open-source packet sniffer Ethereal, but any packet sniffer will do. After pointing Ethereal at the database server, we ran our application code and walked through the results to see what was happening.

First, determine the size of the packets the database returns with the data. Some experts recommend fine-tuning the packet size with which your database communicates, but this is risky. Such fine-tuning can degrade performance, and a database application rarely requests chunks of data in a single size, so don't change this setting without understanding the potential impact. You can get more information on how to fine-tune packet size from your database vendor (see "Sites To See," below).

The sniffer, meanwhile, lets you determine how many times database connections are made. In our sniffer tests, the databases we examined exchanged multiple packets when establishing a connection. This can increase traffic if connections are being reopened consistently.

And finally, measure the size of the query result sets the database returns to the client. We parsed through the result sets and determined the quality of the application vendors' code. If you find that a particular application is pushing many large data sets across your network, you can work with the application vendor to improve the software. If you're having problems sniffing traffic from a particular database, meanwhile, you can set up a port mirror on your switch. That ensures you receive traffic for that machine, and not traffic destined for others.

Once you've gathered all this information about the application's behavior, you can simulate a single client and predict the impact of multiple clients. If the database performance degrades in other ways when you add clients--producing more I/O than your disk can handle--it will appear to lessen the load on the network. But don't be fooled. The minute you fix the bottleneck on your server, you'll encounter traffic problems again.