Database Server Specs - Go Big or Go Home?
Bill Rehm, ATS - Database Lead
There’s a big upgrade coming up and it’s time to buy new hardware. The business wants to build a server that can handle anything, and you’ve got a decent budget to work with. Since you have the money you just load that bad boy up with all the CPU and RAM you can afford. That’ll make it run faster than ever!
Another scenario is that you have performance problems and queries are just not moving through very fast. If you double the RAM and CPU that will make everything run at 2x speed!
It often seems like adding more resources to a database is a good thing. Unfortunately, you can easily reach a point of diminishing returns and even poor performance. If you already have poor performance, there might not be any amount of memory and processors you can add to make it run faster.
Let’s just look at the money side of it first. Is it worth it to empty your bank account and stack CPU and RAM to the ceiling? The truth is – not really. There does come a point when you’re just wasting money.
Putting everything in memory is great, but disk I/O is not a bad thing. Eventually the database has to write to disk anyway. CPUs are the same way. Having 64 cores is pretty awesome, but if your database activity only ever uses 4 or 8 cores then there’s a lot of power out there that will never get used.
It’s especially painful to the bottom line if your systems are in the cloud. Every extra CPU and GB of RAM costs money every second forever. There’s no sense in paying money for resources you don’t need.
What about increasing performance? Contrary to what seems like common sense, all that extra bandwidth can make processing slower. Loading data in memory certainly speeds up retrieval times on the front end, but RAM is used in many ways in a database.
Databases use memory as a holding area for instructions on how to execute queries. When a new query comes in, the database looks through the memory to see if it already has instructions for that query. If it finds a match it can immediately run the query. If there’s no match, it will examine the query to determine how best to execute it, then save that information to memory for the next time it comes through.
That means if there is a boatload of memory the database can store endless amounts of execution plans. Good, right? Not really. If there’s too much memory the database will spend more time looking for an execution plan than if it had just created a new one from scratch. This makes your database slower rather than faster.
The amount of physical resources also changes what kind of plan the database uses to execute queries. With a reasonable setup a query might execute in an efficient way, but with vast resources it can actually choose a less efficient execution plan that takes many times longer.
If your database is already performing slowly, you need to find out why before you begin making changes. Even the beefiest database can be brought to its knees by a single badly designed query. Third-party reporting tools can also cause slowdowns especially if you leave it up to the end users to design their own custom reports.
If you think you need more physical resources, find out why first. Don’t add memory or processors just because you can. The database might not need it. Use the built-in database performance advisors to help you make that decision. Take an in-depth look in to what’s running slow on your database. It could be a badly designed application, a query, or an indifferent reporting tool that is blocking other sessions. Database locks, blocks, and deadlocks are rarely solved by throwing bandwidth at the problem.
There’s really no simple answer to improving performance on a database. Well, it could be simple. You might just need more memory. The key is to be sure that’s what you need. Don’t react – plan for a solution, otherwise you may be weeks, months, or even years down the road troubleshooting a problem only to find out you’ve been going at it all wrong.
If you have any questions about databases, please email us at inquiries@getgsi.com or click Contact Us.