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.
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
- 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.
- 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.
- 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.
- 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.
- 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.
