Category: All posts
Aug 01, 2023
Posted by
Grant Godeke
Testing performance improvements (or really anything related to your production database) can be a daunting task. Performance improvements, in particular, are especially challenging—you must test them first to ensure that the change produces a performance improvement. At a minimum, when changing something in our production database, we would like to make sure that our change doesn’t actually degrade performance before we apply these changes.
Thankfully, this is one area in which operating a managed database in the cloud can help since we can leverage the flexibility of a cloud infrastructure to do performance testing (or testing of any kind) rapidly and safely.
In this blog post, we’ll offer you some strategies to make testing easier from the perspective of Timescale (30-day free trial, no credit card required!)—but even if you’re not a Timescale customer, we hope you find advice in this blog post that you can directly apply to your own environment.
Let’s start from the beginning: what to look for if your database service is not performing as expected?
Database performance is highly dependent on your use case—that’s why having direct access to a technical support team is so valuable. On a high level, we usually recommend our customers look at two areas when they see performance issues:
The first possibility is that you’re maxing out your database CPU or memory. One possible performance improvement to test would be changing the configuration of your service to see if, for example, adding more memory and CPU improves the performance.
As we’ll see later, this is super convenient to test in Timescale using forks.
Another type of performance improvement may require configuring your database parameters more optimally. In the case of Timescale, you can start by following this advice:
These potential improvements can also be tested using forks in Timescale.
Alright: before we move on with our advice on testing for performance, let’s pause for one second. What are these “forks” that we keep talking about?
Modern cloud databases give us one gift: flexibility. In Timescale particularly, there’s a way in which you can run all tests fast and easy: you can create copies of your production database in one click, changing their configuration as you please. These copies of your production database (which we call forks) will replicate your production data up to the time you’re forking while staying completely independent of your production service. And since Timescale allows you to change your configuration in place, you can use that same fork to try out the different configurations, or you can create a fork for each configuration you’d like to test.
And the best part? This is also cost-efficient because you’ll only be charged for the time your services are running.
Apart from how easy and cost-efficient they are to use, there are a few more benefits of testing on forks:
Later in this post, we will tell you step-by-step how to apply forking for testing. But now, let’s get back to our testing strategy.
So, you’d like to improve your performance; you may have some hypotheses that you’d like to test; and if you’re using Timescale, now you know that you can use forks to run the tests easily. The next step is to design and write your tests. Here are some things to consider:
You might be thinking, “Well, my performance is bad; that’s why I’m reading this.”
Fair enough! Let’s dig deeper, though. In what scenarios is your performance bad? And in the same vein, what is the impact of this performance degradation on your application?
Isolating the problem will give you cues on how to solve it. If you are handling multiple scenarios, try picking the single scenario that’s particularly painful (i.e., the one with the most impact). Start your testing there and refine your hypothesis.
Now that you have defined a specific problematic scenario, investigate a bit into what your database looks like when the problem arises. By this, we mean analyzing traffic patterns; looking into queries being executed at this time; maybe peeking at your scheduled jobs, and so on.
The intent of doing this should be to design a testing suite that can mimic your problematic behavior as accurately as possible, so you can be sure you’re addressing it properly. If you aren’t sure where the problem lies, try to answer more general questions. For example: “Do I have a write-heavy or read-heavy workload?”
One tip: pg_stat_statements may help you identify problems. You may be able to look back at a time when you saw a performance degradation issue, and look through some of the statements executed during that time frame. If you find any, hang onto them! They’ll be models for part of your test suite. You’ll find information on how to use pg_stat_statements here and here.
Once you have identified a specific problematic scenario, it’s also helpful to consider whether that’s a standard occurrence for our database or if it’s an anomaly. If it’s an anomaly, you should also consider throwing in some tests for your more general workload to ensure that you won’t be sacrificing standard performance for a situation that happens less frequently.
Now that you’ve identified which tests to run, it’s time to get at it and write your tests.
I have good news, and I have bad news. The good news is if you have already identified some particularly problematic scenarios, you can use those as inspiration for writing your tests. The bad news is that—well, there is no real “silver bullet” here: you’ll have to write the tests yourself. That said, some tooling in the amazing PostgreSQL ecosystem can help you with this!
A common one is pgbench. Pgbench accepts user-defined scripts, which is handy as it is possible to write scripts based on your problem scenario to benchmark. Pgbench offers nice configuration options, like changing the number of clients. And for time-series workloads, there are some specific tools—including Timescale’s own Time Series Benchmarking Suite and AWS’s TSBench. Depending on your situation, choose a tool that works well for you.
These tools provide default scripts for testing, but we strongly recommend writing custom scripts instead. The whole point of this is to test production data safely, right? Off-the-shelf scripts won’t allow you to do that. Again, pg_stat_statements is your friend here to identify some troublesome queries!
As we said earlier, Timescale’s forks make it highly convenient to run tests. If you’re using Timescale, these are the exact steps we recommend you take for testing as easily, cost-effectively, and safely as possible:
Follow the advice in the previous section to design and prepare your tests.
For this example, we’ll assume that you’re using pgbench. Pgbench is available in the contrib directory of your PostgreSQL distribution. You can download it if you haven’t already by downloading the postgres-contrib subpackage using a tool like apt-get. You can see more information about the modules contained in the contrib directory in the official PostgreSQL docs.
We highly recommend opening up the official pgbench docs to learn about the tool!
Testing twice is good for redundancy, and creating two forks right off the bat will ensure that you have two testing databases ready with the same data loaded into them. We recommend forking your production database first (or whichever database you’re trying to improve performance on), and then “forking the fork.” If you create a second fork from the production database again, consider that the data in it will deviate from the first by whatever operations ran on the production database in the interim.
Pro tip: compute and storage are decoupled in Timescale, meaning that they’re billed independently. If you don’t want to pay for compute on both forks while you are testing, you can simply pause the fork you aren’t using, so you only pay for storage during that time. You can resume this fork anytime.
We will start by running the tests in this first fork and then follow the same steps on the second one. This will allow you to compare both results to see if they’re reproducible.
Note: Throughout this, we will be using placeholders like [your-host] that you should replace with your information. If you’re using Timescale, you can find this information in the Connection Info section of the service you’re trying to test. If you’re using another service, also make sure to replace “tsdb” with the name of your database.
Make sure pgbench is successfully installed on our local machine by running a version check:
Pgbench -V
Check your connection. In Timescale, you can easily find these values in the Connection info pane of your service. You’ll then be prompted for your password to connect to the database.
Pgbench --host=[your-host] --port=[your-port] --username=[your-username] tsdb
If you have an uninitialized database, you may get an output like this, which is expected:
pgbench (14.1, server 12.12 (Ubuntu 12.12-1.pgdg22.04+1))
pgbench: fatal: could not count number of branches: ERROR: relation "pgbench_branches" does not exist
LINE 1: select count(*) from pgbench_branches
pgbench: Perhaps you need to do initialization ("pgbench -i") in database "tsdb"
If you’d like to initialize your database with pgbench’s default test suite, you can add a -i flag to your arguments to initialize the database. Note that this will load data onto your database! This would look like this:
Pgbench -i --host=[your-host] --port=[your-port] --username=[your-username] tsdb
Make sure we are in the same directory as our testing suite, and then run our selected tests:
Pgbench -f [filename] --host=[your-host] --port=[your-post] --username=[your-username] tsdb
You should get a sample output of something like this:
pgbench (14.1, server 12.12 (Ubuntu 12.12-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 295.077 ms
initial connection time = 361.402 ms
tps = 3.388945 (without initial connection time)
Rather than copy and paste this for each test you do, you can always append the results to a .txt file if needed by running something like:
Pgbench -f [filename] -host=[your-host] --port=[your-post] --username=[your-username] tsdb >> results.txt
Keep in mind that pgbench has a lot of options for you to adjust depending on your use case. Examples of this include --clients to specify the number of --clients and connect to force a new connection with each transaction rather than at the beginning and end.
Compare the results of the control and the test. If satisfied with the results, you can apply them to your production database.
A quick reminder: if those changes imply changing your service configuration (e.g., assigning more compute to it), you can resize your service in place in Timescale using the Resources tab in the Operations pane.
If your tests don’t improve performance, it’s time to test the next hypothesis!
When you are done testing a fork, you can delete it. On Timescale, you are only charged for the duration of the fork. If you would like to keep it for reference but aren’t performing any active testing, you can always pause the fork, and you will only then pay for storage.
The big challenges around testing performance improvements lie in identifying the problem scenario and writing good test cases. Once you do that, you’ll be able to easily (and safely!) try out your hypothesis using forks in Timescale. This will allow you to spend more time on what's more meaningful and particular to your use case (i.e., thinking of hypotheses and designing experiments for them) rather than wasting time manually spinning up new databases to test on.
If you haven’t tried Timescale yet, you can use it completely for free for 30 days—no credit card required! Create an account here and experiment.