Poor database performance manifests itself as that noticeable (and sometimes intolerable) lag that occurs when navigating, querying, or submitting data within a web portal. There are several causes that facilitate that, from software to hardware. On the software side, the database could be poorly designed in terms of the data structures it employs, the way data is organized, or just inefficient coding. On the hardware side, there is throughput and IOPS, reflecting the network’s ability to process requests and deliver data back to the clients. Optimal performance only results when software can generate the largest possible workload and the hardware can “keep it moving” it with minimal latency.
Database Performance Versus Health
Another way to divide up the responsibilities within a database management system (DBMS) is in terms of database health and database performance. Performance is a timed measurement of how well the database software executes queries and creates and updates records. Health refers to the hardware’s ability to run the database software optimally and includes storage performance and CPU and RAM utilization. Both are clearly important, but as a starting point in any diagnostic, establishing a healthy network for the software to run on should be the prerequisite for achieving good performance.
Targeting Network Latency
The major culprit to eliminate from hardware is network latency, and the place to begin is within the storage system. If an environment has a SAN, then look at the performance of the SAN. If it has an internal storage array, then look at the storage media. In either case, the big question is the same: Are you still running your data servers on spinning HDDs? If yes, rest assured… THERE is your latency problem. HDDs are notorious for long read/write times which have no ability to keep up with the modern processors of the day… or even yesterday. As a result, the rate of input/output (IOPS) across the storage system suffers because every request gets queued up while waiting for the read/write heads to locate data on the spinning disk. Switching to a modern storage solution that intelligently uses SSDs will eliminate that latency and you can expect to see IOPS across storage suddenly explode.
What You Can Expect
Once you unleash the IOPS potential of your storage system, you can expect to see dramatic performance increases. What is dramatic? One use case involved a company’s inventory report that previously required three hours to run. On the new (flash enabled) storage array, the same report completed in approximately 15 minutes. Other tests involving database queries yielded results that exceeded expectations by 75%. How is that possible? Because the speed issue in a DBMS is rarely about the CPU or RAM… it’s about IOPS.
But My Database Needs the Capacity of HDDs…
While SSDs offer incomparably better IOPS performance than spinning HDDs, that performance still comes at a price. Actually, two prices. The first is the higher cost per terabyte, and the other is the reduced capacity of SSDs in comparison to HDDs. It’s worth noting that larger capacity SSDs (currently up to 60TB) are always being developed, so we could see the capacity argument come to an end someday soon, but you can be sure that you will still have to pay top dollar for the privilege. In the meantime, the best of both worlds can be found in modern hybrid storage arrays that use intelligent data tiering to keep hot data in flash storage and cold data archived in the extensive storage capacity of the HDDs. Win-Win.
Look at Storage First
As mentioned earlier, there can be multiple reasons why a database system could underperform, and none of those should be summarily dismissed. The recommendation here is that you investigate the obvious causes first. More times than not, any type of sluggish performance across a network is caused by low IOPS performance across the storage system. IOPS is the primary driver of database performance.
To learn more about modern storage technology that is available to drive your database management system and empower your IT team, contact the IT professionals at CNS Partners. We help manufacturing businesses improve the performance of their IT systems while enabling business growth.
For an in depth about how to diagnose and solve network performance issues, download our eBook.
