Database Query Optimization: When Hardware Should be Upgraded

One question that consultants are frequently asked is whether it is better to focus on upgrading hardware or optimizing software.

Here, we’ll analyze this question from the consultants’ point of view and understand why they suggest a hardware upgrade instead of software or database query optimization.

5 Questions to Ask before Making a Recommendation

Generally, consultants ask their clients the following questions before they suggest anything -

Question 1 - What is the state of the hardware?

In many cases, the hardware is inadequate, which is why upgrading it before hiring professional services is often much cheaper. However, some people may still prefer an optimized system, even if it will be run in seriously lacking hardware and prove more expensive.

If you require a significant performance boost but lack the time for major application changes, it may be more favorable to opt for a hardware boost in the long run.

Question 2 - Is current hardware usage balanced ?

Suppose you are conducting SQL performance monitoring for your organization’s database. You have five servers out of which one is overloaded and the remaining four are nearly idle. This is where it is more important, and sensible, to maintain a proper balance instead of blindly going for more hardware.

Of course, some of the applications may prove difficult to balance. Yet the number of cases with users suffering because of wrong balance, despite having reasonable sharded or replicated architecture, is astonishing. Furthermore, balancing could be a simple operations act or need certain application switches, depending on the situation.

Question 3 - Are there any fluctuations in hardware use?

Certain performance related issues can be unavoidable, such as during nightly backup or in case of a running cron job. In situations like these, it is better and more convenient to even out the usage rather than opt for a hardware upgrade.

Question 4 - What is the state of MySQL, Queries, and Architecture?

A hardware upgrade is not the first thing that consultants advise, especially if the user can triple performance with a basic my.cnf modification. For optimal performance, however, there is a need to see how easily the consultant and the client can balance everything through tasks like query performance tuning.

For instance, adding an index is simple, as is changing the statement in your own application. On the other hand, doing the same via third party applications is difficult, particularly for close source. Major schema modifications, caching, sharding, etc. could get complicated or remain relatively simple, depending on the specific case.

The larger your application will turn out to be the better you’ll want its performance to be on application level.

Question 5 - What do you hope to achieve with the hardware upgrade?

Having a goal is just as important here as it is in the case of software optimization. Memory is inserted to prevent disk IO and quicken lookups, and for database query optimization, you add indexes to prevent a complete table scan and improve the speed of the related query.

The aim of application performance optimization is to improve throughput, speed up statement execution. In a similar manner, these should be kept in mind when you consider upgrading your hardware -

• Quicker random IO,
• Enhanced caching,
• Rapid execution

Such factors will help you keep things in perspective in order to comprehend what needs to be done, such as opting for a new SSD, or upgrading memory and CPUs. You will have to resolve certain balancing questions on the way, but having a goal before you begin will help you achieve better performance in a more satisfactory manner.