Realizing optimal database performance comes from the total sum of the parts involved. Any part that is not tuned properly can wreak havoc with performance. Just like a fine-tuned machine, a database relies on many individual elements, and each must work in concert to achieve the best efficiency.
Although it may sound straightforward, databases tend to be just parts of a larger overall IT solution. It becomes clear that performance is often tempered by compromise, dictated by budgets and interoperability concerns. For example, providing more bandwidth or CPU cycles to a database may negatively impact other IT solutions. Simply put, optimizing databases requires a holistic approach, one where all of the parts concerned are looked at independently and then judged as a whole.
An enterprise database consists of many hardware and software elements. First, of course, is the design of the database itself, which is tightly intertwined with the database engine, the hosting server and the primary network operating system that is in use. Other parts of the pie include network connectivity, the storage infrastructure, and the data protocols in use.
All those elements work together to deliver the data in the database to the endpoint requesting the data via an application. The application can be run from within the database environment, from an application server, or directly on the endpoint. Breaking down those individual elements proves to be the key to tuning a database. But bear in mind that some elements have a larger impact on performance than others, while some elements can’t be easily modified.
Examine database design first
One of the first places to look for performance gains is in the design of the database itself. Design elements, such as the use of full table scans, multiple indexes, views, joins, sub-queries, stored procedures and many other techniques can greatly improve the performance of a database, but require a high level of database design and understanding, usually best left to the initial programmers/designers.
Server hardware and OS elements are probably the next best place to look for performance improvements. However, there are some limitations to the amount of performance improvement realized, simply because the primary database server may also be responsible for other IT tasks or may require expensive hardware upgrades to facilitate improvements. What’s more, the native network OS is usually not easily changed or upgraded, requiring significant planning and time investment beforehand.
Attempting to improve database performance with server modifications can potentially open up a can of worms. Downtime of ancillary systems can wreak havoc on other processes. If there is a budget in place to explore server upgrade options, perhaps a better path to follow would be to migrate any databases over to a dedicated platform, such as Oracle’s Exadata or Exalogic
Exadata, Exalogic and database performance
Oracle’s Exadata product is a server appliance that combines hardware and software into a rack-mounted device, which functions as a dedicated machine optimized for analytic applications and data warehouses, supporting large data volumes and eliminating the delays caused by real-time read and writes of data. Exadata has evolved from V1 to V2 to X2-2 and X2-8. The X2-2 appliances sport six-core CPUs, 96GB RAM per database server, and 40GB InifiniBand SAN connectivity. The X2-2 maxes out at 14 storage servers, totaling 96 cores for the database servers and 768GB of RAM along with 5.3TB of flash and 100TB disk. Oracle’s Exadata x2-8 is an extension of the X2-2 product line, and offers more cores, more RAM and more storage
The primary advantages behind an Exadata implementation are increased performance and ease of management, which are enabled by integrating all of the primary elements of an Oracle database into a unified offering. In other words, Exadata combines CPU, Storage, OS, the database engine and connectivity into a singularly managed device. Performance is further enhanced as an element of design – the software and hardware components are specifically designed to maximize performance of a database.
Exalogic (officially named Oracle Exalogic Elastic Cloud) is another hardware/software/appliance offering from Oracle. However, Exalogic is very different than Exadata and is designed to serve a different purpose. Where Exadata is considered a database machine, Exalogic is an application machine and runs several other pieces of software beyond the native OS of Linux and a database engine. For example, an Exalogic solution may run middleware applications, as well as Weblogic, JRockit and HotSpot.
From a management and performance standpoint, Exalogic integrates all of the necessary components to support a large user base of web applications, with multiple Oracle databases on the backend. Ideally, an Exalogic-based solution removes busy Web applications and related databases from the core data center network. Exalogic and Exadata can solve performance problems and offer levels of performance rarely seen for large enterprises. Management ease should prove to be a benefit as well. However, either solution can be quite pricey.
One element that Exalogic and Exadata have in common is high performance storage subsystems, where access speed to hard disk storage is maximized. That offers an important clue to database performance. Simply put, high-speed storage solutions can only help to improve a database’s performance. With that in mind, DBAs should be able to find other products that enhance the performance of storage solutions, which will help to improve database performance, with SANs (Storage Area Networks) being a primary example.
There are a multitude of SAN vendors on the market, each of which offers its own twist on storage availability and performance. It will be those nuances that will dictate which SAN solution may be the best fit for improving the performance of any database. DBAs need to weigh elements such as flash storage, cache, spindle types and topology used for connections to determine what benefits each SAN solution offers.