Building a PostgreSQL load tester
blog.lawrencejones.dev
blog.lawrencejones.dev
There is also pg_badger which can run parallel jobs:
https://github.com/darold/pgbadger
pg_badger is also used by nancy, an benchmark experiment driver, so you can replay your logs, then change something, like add an index, and replay them again and nancy will show you to differential performance:
pgBadger is the second component to the tool this article is about. The idea is to replay the original production logs and capture the target clusters logs while you do it. After the benchmark is complete you can pass the logs into pgBadger to understand what happened with the performance, and compare the report to one produced for production.
I hadn't come across nancy before though, this looks cool! For replay of real logs nancy appears to use the original pgreplay (https://github.com/laurenz/pgreplay) so it would have been subject to the same issue that motivated building pgreplay-go, but I bet providing an option to switch between the tools would be quite easy.
There is pgbench, of course that does exist, but that probably doesn't fit your use case as it's a load tester with a contrived schema, data access pattern, queries, etc. Load testing the way you use a database is a harder problem than just doing so generically.
But here's a question: were the logs really the best option for getting that "seed" data? I wonder if some of the work done facilitating logical replication could be harnessed for such a task without some of the noise that comes with logs. I don't know that this is possible and it may simply be too intrusive, especially if you're just trying to work with existing history; but if possible and not too much admin overhead seems like this would be an easier way to accomplish something like getting a load testing data set.
It seemed to us like they were. The database under test belongs to a large monolithic app that has long reads, heavy write batch jobs and is serving API traffic. My team and I struggled to find an alternative that we didn't feel was too fake, that we could really have confidence in.
Even though the preparation of the benchmark loses some realism, it's still close enough to real life that it gives you great signal on how things might degrade. Running this replay against a new cluster and simultaneously verifying the performance of a critical endpoint was the gold standard for verifying we were safe to move to the new hardware.
Whether this is all true for others, I'm really not sure. We were willing to invest the time because this was so critical for us, but it might not make sense for other people to take the same approach.
> I wonder if some of the work done facilitating logical replication
Perhaps? You could capture writes, but the majority of our traffic were reads which would have been unreported by logical replication.
> getting a load testing data set
Anecdotally, capturing the logs seemed quite easy. Just turn Postgres logging up to max and shove them on a different disk. That said, if we weren't in GCP with quick disk snapshotting then things would be a lot more awkward!
Ah yes... of course.... forget I mentioned it... I do a lot of ERP type work so am in a very similar boat most of the time, but I'm currently doing the information architecture for a green field development project and am very focused on some other priorities, so a little work induced myopia on my part.
Michel, I'm sure you mean pgbench, since you've added its support to Nancy CLI (thank you!) :) It's easy to mix though, since we embed both (pgbadger is for log analysis when during experiments we are "okay" to turn the full query logging on -- it's a great tool being developed for years; it can produce JSON, so it's machine/integration-friendly).
And yes, it's quite convenient in many cases to use pgbench, when logs collections is not an option due to some reason -- instead of workload replay we can use workload simulation, based on underdtanding how every query group from pg_stat_statements looks like (first of all, the main important thing is % of calls in overall picture). It's good that pgbench allows to set "weights" for every workload "piece", using multiple -f options and @weght (for example, "pgbench -f tx1.sql@20 -f tx2.sql@80", etc). We use this approach a lot.
But I also believe in workload replay approach. Cool to see pgreplay-go, we'll definitely check it out and try!
"Database experiments" is a very important and underestimated topic. Database change management methodologies (for open source products) are very weak compared to CI/CD, and other tech areas -- I hate to see how changes are being made without proper preparations and thorough testing. Improvements are definitely needed and it's great to see new tools!
I still have two concerns when simulating a workload in order to test configurations / DDL changes:
- Processing the logs implies generating all for every query that arrives. While this may be acceptable for some cases, it is unacceptable for high volume sites. Logging every query may bring the db down. PostgreSQL needs a different mechanism to report this information, like a streamed UDP of query performance metrics.
- Independently, this only reproduces the read workload, but what about the write one? Ideally, you should combine both. Write one may be very well captured and replayed using logical decoding / replication. But still would need a lot of coordination / tooling with the read replaying tool to achieve the desired effect.
All in all, complex problems but very important and interesting :)
Anyone have nice patterns for this around MySQL?
Otherwise seems like a nice tool, I might give it a try in the future :)
ok yes - it is possible that Safeway groceries has not used Postgres to build their business, or that they have been very active and productive contributors to the project, based on the vast business resources they have accumulated over sixty years.. but.. how about it.. a load tester would kind of be useful .. now, wouldn't it. Maybe Safeway is mocking the Postgres project, not the data .. no clue.. rumor only