36

This is a canonical question about capacity planning for databases.

Related:

I'm looking to create a canonical question of tools and methods of capacity planning for databases. This is intended to be a canonical question.

Obviously, the general workflow is:

  • Put your scenario in place
  • Add monitoring
  • Add traffic
  • Evaluate results
  • Remediate based on results
  • Rinse, repeat until reasonably happy

Please feel free to describe different tools and techniques for different web-servers, frameworks, etc., as well as best-practices.

1
  • A Database is almost never a stand alone system. It is to be seen in the major context of, often big, application servers in front of them. DBs are the backend data devices. So when testing load, you have to consider that.
    – Nils
    Nov 20, 2016 at 12:19

2 Answers 2

24

Disk & RAM Capacity Planning

Planning disk and memory capacity for a database server is a black art. More is better. Faster is better.

As general guidelines I offer the following:

  • You want more disk space than you'll EVER need.
    Take your best estimate of how much disk space you'll need for the next 3-5 years, then double it.
  • You'll want enough RAM to hold your database indexes in memory, handle your biggest query at least two times over, and still have enough room left over for a healthy OS disk cache.
    Index size will depends on your database, and everything else depends heavily on your data set and query/database structure. I'll offer up "At least 2x the size of your largest table" as a suggestion, but note that this suggestion breaks down on really large data warehousing operations where the largest table can be tens or hundreds of gigabytes.

Every database vendor has some instructions on performance tuning your disk/memory/OS kernel -- Spend some time with this documentation prior to deployment. It will help.


Workload Benchmarking and Capacity Planning

Assuming you haven't deployed yet…

Many database systems ship with Benchmarking Tools -- For example, PostgreSQL ships with pgBench.
These tools should be your first stop in benchmarking database performance. If possible you should run them on all new database servers to get a feel for "how much work" the database server can do.

Armed now with a raw benchmark that is ABSOLUTELY MEANINGLESS let's consider a more realistic approach to benchmarking: Load your database schema and write a program which populates it with dummy data, then run your application's queries against that data.
This benchmarks three important things: 1. The database server (hardware) 2. The database server (software) 3. Your database design, and how it interacts with (1) and (2) above.

Note that this requires a lot more effort than simple pre-built benchmarks like pgBench: You need to write some code to do the populating, and you may need to write some code to do the queries & report execution time.
This kind of testing is also substantially more accurate: Since you are working with your schema and queries you can see how they will perform, and it offers you the opportunity to profile and improve your database/queries.

The results of these benchmarks are an idealized view of your database. To be safe assume that you will only achieve 50-70% of this performance in your production environment (the rest being a cushion that will allow you to handle unexpected growth, hardware failures, workload changes, etc.).


It's too late! It's in production!

Once your systems are in production it's really too late to "benchmark" -- You can turn on query logging/timing briefly and see how long things take to execute, and you can run some "stress test" queries against large data sets during off hours. You can also look at the system's CPU, RAM and I/O (disk bandwidth) utilization to get an idea of how heavily loaded it is.
Unfortunately all these things will do is give you an idea of what the system is doing, and a vague concept of how close to saturation it is.
That brings us to…


Ongoing Monitoring

All the benchmarks in the world won't help you if your system is suddenly seeing new/different usage patterns.
For better or worse database deployments aren't static: Your developers will change things, your data set will grow (they never seem to shrink), and your users will somehow create insane combinations of events you never predicted in testing.

In order to do proper capacity planning for your database you will need to implement some kind of performance monitoring to alert you when database performance is no longer meeting your expectations. At that point you can consider remedial actions (new hardware, DB schema or query changes to optimize resource use, etc.).


Note: This is a very high level and generic guide to sizing your database hardware and figuring out how much abuse it can take. If you are still unsure about how to determine if a specific system meets your needs you should speak to a database expert.
There is also a Stack Exchange site specifically dedicated to database management: dba.stackexchange.com. Search their question archive or browse the tags specific to your database engine for further advice on performance tuning.

3
  • 1
    In addition to that, nowadays, you can use SSDs for swap/on disk operations. That will speedup queries that use large temporary tables on the disks. So, adding more SSDs is generally a very good idea.
    – Peter
    Sep 7, 2012 at 17:10
  • 2
    @Peter I wouldn't recommend SSDs for swap space (if you are actively swapping there is a very high churn rate), though with a big enough SSD and good wear leveling the disk may last the life of the machine. I have seen SSDs used for temp table space with good results.
    – voretaq7
    Sep 7, 2012 at 18:09
  • 1
    Please note that this advice in the comments about SSD's is now 7 years old. Every storage that holds a database on your database server should be an SSD in 2019 or later. Feb 27, 2019 at 0:23
1

Generally you need realistic use-cases to test performance. A best practice is to involve application developers and end users.

Record what they are typically doing, parametrize it (content, number of concurrent actions) for each use-case.

Then build up the client side. A single physical machine is often not enough to build up production load.

Then fire it up, ecaluate, enhance and test again.

You will be surprised where bottlnecks rise.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .