Database performance tuning: Five ways for IT to save the day

IT teams can play heroes when database performance issues disrupt applications. Try these five tips for performance tuning before there's a problem.

This article can also be found in the Premium Editorial Download: Modern Infrastructure: Production workloads go boldly to the cloud:

When database performance takes a turn for the worse, IT can play the hero.

Production database performance can slow dramatically as both data and the business grow. Whenever a key database slows down, widespread business damage can result.

Technically, performance can be tackled at many different levels -- applications can be optimized, databases tuned or new architectures built. However, in production, the problem often falls on IT operations to implement something fast and in a minimally disruptive manner.

There are some new ways for IT pros to tackle slowdown problems. However, one question must be addressed first: Why is it up to IT?

Database administrators and developers have many ways to achieve database performance tuning. They can adjust configuration files to better align database requirements with underlying infrastructure, add indexing, implement stored procedures or even modify the schema to (gasp!) denormalize some tables.

Developers have significant control over how a database is used; they determine what data is processed and how it is queried. Great developers wield fierce SQL ninja skills to tune client queries, implement caching and build application-side throttling. Or, they rewrite the app to use a promising new database platform, such as a NoSQL variant.

All kinds of databases, however, can eventually suffer performance degradation at operational scales. Worse, many developers simply expect IT to simply add more infrastructure if things get slow in production, which clearly isn't the best option.

Five ways to address database performance issues

  1. Infrastructure scale-up. The common approach is to throw more resources at the issue, though this may not actually improve performance. Even if the right resource pool is enlarged, there can still be limits to the amount of resources that are effectively used. Adding infrastructure can be relatively challenging with physically hosted databases, but virtual or cloud hosting can make provisioning incremental resources easier.
  2. Scale-out expansion. Some databases like clustered MySQL are scalable. Clustering, however, can have practical limits or require partitioning techniques that impact application consistency or reliability. Clustering also requires changes in system management and updated data protection plans.
  3. Active archiving. Performance can sometimes be maintained by archiving out older data. Archiving used to imply that some data is no longer fully useful, but today there are options like Rainstor or Oracle's built-in Hybrid Columnar Compression that work on still-relevant data. Within these active archives, analytical query performance actually increases as data becomes more static and compressed.
  4. Accelerating with flash. Flash is a popular performance solution. When deployed strategically, it can accelerate far more than just a database. A big benefit is that flash is transparent to the database. Flash can be used as server or storage cache (e.g., Fusion-io) or solid-state drives, and also be put in the network, like Astute VisX. Some vendors such as PernixData are blurring the lines further by pooling server flash into its own storage tier. Still, flash is an expensive fix and some solutions address patterns of read I/O that may not solve the current database bottleneck.
  5. Database engine upgrade.  A new and interesting approach to database performance tuning is to upgrade the internal database engine. In the widely popular MySQL and MongoDB databases, you can swap out the internal engine for a high-performance (free, open source) version from Tokutek, for example. The process of replacing the database engine is transparent to the application and doesn't require any changes to an infrastructure.

As a bonus, because of the way these new database engines index and write data, they also naturally optimize the database I/O stream to make better use of flash.

When faced with critical performance degradation, IT often is forced to make potentially huge investments with questionable payback. Instead of always reacting to performance issues with more infrastructure, look first at the range of tuning options above. Even better, explore some of these options proactively to avoid database performance issues in the first place.

About the author
Mike Matchett is a senior analyst and consultant at Taneja Group.

This was last published in April 2014

Dig Deeper on Configuration Management and DevOps

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

There is one more option: to actually optimize the database queries and the way the database engine works. There are techniques to do response time analysis, and tools that can help you understand the time each query takes, resources consumed, blocks and so son. One of the best tools is DPA from confio.com.

Another element of database performance is the network. If the network is slow, traffic between database tier and application tiers will lag naturally.
Cancel
This is something we could definitely stand to do better at. We have started some data archiving methods to help the issue. 
Cancel

-ADS BY GOOGLE

SearchDataCenter

SearchAWS

SearchSOA

SearchServerVirtualization

SearchCloudApplications

SearchCloudComputing

Close