4 ms·

> When faced with a technical problem it’s often better to use an existing tool than jump into writing one yourself. Having benchmarked Postgres clusters before

> When faced with a technical problem it’s often better to use an existing tool than jump into writing one yourself. Having benchmarked Postgres clusters before, I was already familiar with a tool called pgreplay that I thought could do the job. There is also pg_badger which can run parallel jobs: https://github.com/darold/pgbadger 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: https://gitlab.com/postgres-ai-team/nancy https://gitlab.com/postgres-ai-team/nancy

lawrjone 8y ago
Hey, author here! Perhaps I'm missing something, but I think pgBadger is just for log analysis, no? 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 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.
- sbuttgereit 8y ago
  I came here to basically state the same point or ask if there was a way to apply pgBadger, the log analysis tool, to this problem. I use pgBadger quite a lot, but not for your use case. Perhaps you could take the generalized versions of queries that emerge from pgBadger analysis along with query counts and fake something together, but there are significant flaws with that approach, such as sequencing, transaction relationships, etc. anyway.... 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.
  - lawrjone 8y ago
    > were the logs really the best option for getting that "seed" data? 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!
    - sbuttgereit 8y ago
      > Perhaps? You could capture writes, but the majority of our traffic were reads which would have been unreported by logical replication. 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.
- michelpp 8y ago
  Ah you're right, i got them confused in my memory. I did not write nancy so i'm not entirely familiar with it's guts, but found it VERY useful for replaying workloads while messing with indexes to find the best performance. Maybe you can plug your pgreplay-go into nancy as an option as your said. I sent them a PR to add pgbench support so I'm sure they're open to new features like that.
nik-postgres 8y ago
/* Co-author of Nancy CLI is here. */ 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!

lawrjone 8y ago

Hey, author here! Perhaps I'm missing something, but I think pgBadger is just for log analysis, no? 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 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.

sbuttgereit 8y ago
I came here to basically state the same point or ask if there was a way to apply pgBadger, the log analysis tool, to this problem. I use pgBadger quite a lot, but not for your use case. Perhaps you could take the generalized versions of queries that emerge from pgBadger analysis along with query counts and fake something together, but there are significant flaws with that approach, such as sequencing, transaction relationships, etc. anyway.... 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.
- lawrjone 8y ago
  > were the logs really the best option for getting that "seed" data? 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!
  - sbuttgereit 8y ago
    > Perhaps? You could capture writes, but the majority of our traffic were reads which would have been unreported by logical replication. 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.
michelpp 8y ago
Ah you're right, i got them confused in my memory. I did not write nancy so i'm not entirely familiar with it's guts, but found it VERY useful for replaying workloads while messing with indexes to find the best performance. Maybe you can plug your pgreplay-go into nancy as an option as your said. I sent them a PR to add pgbench support so I'm sure they're open to new features like that.

nik-postgres 8y ago

/* Co-author of Nancy CLI is here. */ 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!