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

>> continued from previous page

The Pool Is Open

	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

Now that you have data on how your database traffic affects your network, you can reduce that traffic. Like any attempt to tune a database, the quality of the SQL query hitting your database plays a big role. Cleaning up SQL usually solves database performance issues.

Then look at connection pooling. The concept may seem simple--keep connections open between the database and application or application server so you can reuse them right away--but it has several facets.

First and foremost is security. Database connections are cached at the application-instance level or thread level. With some database technologies that cache connections by application, a user can gain unauthorized access to data. However, thread-based technologies don't pose this problem because they don't share connections across the application; each thread has its own security.

The technology you choose for connection pooling is also important. If you control the source code to your programs and they use ODBC or JDBC (Java Database Connectivity), you can enable connection pooling in your code (at the application-instance level). If your applications are running on an application server, that's OK--most servers support connection pooling in one form or another.

Finally, most database technologies let you set the number of pooled connections and the length of time to keep unused connections open. If you have too few pooled connections, network performance gains are minimal. And each open connection uses resources on the database server, so too many connections, especially with a long time-out value, hurts overall database performance.

Tracking connection problems can be difficult. Network performance for the database improves with reduced bandwidth. And as the database slows down, there's less traffic. That can fake you into thinking you achieved your goal of improving database behavior. Still, you can find out how many connections are open by checking your database-management interface.

Change Is Good

Connection pooling is the best short-term solution. By making a few simple changes to your application server's configuration files or a slight modification to your source code, you can get a huge reduction in bandwidth usage. Much of the benefit of this approach depends on your users' access patterns. If many users hit the database repeatedly, the performance improvement will be noticeably large because you won't be rebuilding connections repeatedly. But if you have just a few users performing long-running queries, the improvement will be minimal.

A long-term solution is to review the queries your applications use against your database. Queries that return unnecessary columns or too many rows, and queries that return data and then use the result to go back to the database for more data, are problems.

If you still can't reduce network traffic using these methods, go Gigabit Ethernet on the connection from the switch to your database and application server. Gigabit networking technology costs have dropped, so increasing the pipe size is no big deal. This is a good solution if your application uses an application server or if you're having problems because of congestion between the database and the application server. If your application has a fat client to the database, you need to upgrade your backbone so the switches are gigabit-connected as well.

Watch out for Web services. If they aren't configured properly, they can hit your database too much. Writing a Web service that selects and returns a single value from a database row is a no-brainer, but it poses a health hazard to your database and your network. If your organization has 15 Web services modules that each return a single field from the same table, for instance, that places 15 expensive call-response sequences on your network, and your database executes the query 15 times.

Even when implemented correctly, Web services create more network traffic than a traditional query because of two factors: the SOAP (Simple Object Access Protocol) envelope and the translation of result sets to XML/text. When used incorrectly, the overhead can be large--about 250 bytes per call, plus the difference in size between your database's representation of the data and a text translation of that data.

From the network side, there aren't many options for optimizing your database other than throwing bandwidth at the problem. As database and hardware technologies advance, though, more developers will tackle solutions for reducing database traffic. Until then, it's up to you and your packet sniffers.

Don MacVittie is an applied technologist at WPS Resources. Write to him at dmacvittie@nwc.com.

Post a comment or question on this story.