12 ms·

I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a h

I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a hard challenge: but the reality is that most troubleshoot them in production, which means that: a) you need access to production, when you shouldn't; b) you run queries in production, potentially affecting -sometimes noticeably- production workloads. Joe provisions a database clone using ZFS snapshot (I believe LVM support was added recently) and gives you a full copy of the database for you, with real data. You can test the queries, explain plans, create indexes to test if you need. And when you are done, it goes away. With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning. Give it a try, it is a very good tool!

briffle 7y ago
If I'm reading this correctly, Joe uses docker running on the same host as your regular production db, and provisions a snapshot, and a new docker image to mount it. So yes, your production DB data is 'safe' but if your query uses lots of CPU, or lots of Disk IO, then its still going to fight the other images on the system for resources. IE, a Full Table scan of a 2TB table is going to affect the other docker systems running on this same server.
- samokhvalov 7y ago
  Joe uses Docker and ZFS (or LVM+ext4, alternatively) on a separate machine, where data is transferred, normally, from archives (optionally, being transferred constantly, if there is a small Postgres "sync" server with "restore_command" configured), stored on ZFS, periodically snapshotted and prepared (and at this point, we can anonymize it, see https://gitlab.com/postgres-ai/database-lab/-/blob/master/scripts/create_zfs_snapshot.sh#L173 https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc...), and snapshotted again. Such snapshots of prepared PGDATA can be done periodically. And from there, we do thin provisioning, in a few seconds. All this happens on a fully separated machine, not affecting production nodes by any means.
- deleted 7y ago
  [deleted]
dntbnmpls 7y ago
> but the reality is that most troubleshoot them in production I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers. > and gives you a full copy of the database for you, with real data Other than for small toy databases, I don't see how this would work for large databases. Not to mention the huge security issue. You can test, optimize, etc queries on non-production servers. If the problem is production specific, it most likely is more server specific and not query optimization related.
- geekpowa 7y ago
  Query plans are dependent upon actual data itself. Plan performance issues can be difficult to replicate in test envs. AFAICT, this product relies on LVM snapshots/copy on write : so is ideal diagnosing, trying out ideas on large databases.
- ahachete 7y ago
  > I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers. Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do. Query execution plans can change completely based on data volume and even with the data itself. Databases use detailed statistics about the data to influence the planner's decision. Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience. > Other than for small toy databases, I don't see how this would work for large databases. Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter. > You can test, optimize, etc queries on non-production servers. No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.
  - dntbnmpls 7y ago

briffle 7y ago

If I'm reading this correctly, Joe uses docker running on the same host as your regular production db, and provisions a snapshot, and a new docker image to mount it. So yes, your production DB data is 'safe' but if your query uses lots of CPU, or lots of Disk IO, then its still going to fight the other images on the system for resources. IE, a Full Table scan of a 2TB table is going to affect the other docker systems running on this same server.

samokhvalov 7y ago
Joe uses Docker and ZFS (or LVM+ext4, alternatively) on a separate machine, where data is transferred, normally, from archives (optionally, being transferred constantly, if there is a small Postgres "sync" server with "restore_command" configured), stored on ZFS, periodically snapshotted and prepared (and at this point, we can anonymize it, see https://gitlab.com/postgres-ai/database-lab/-/blob/master/scripts/create_zfs_snapshot.sh#L173 https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc...), and snapshotted again. Such snapshots of prepared PGDATA can be done periodically. And from there, we do thin provisioning, in a few seconds. All this happens on a fully separated machine, not affecting production nodes by any means.
deleted 7y ago
[deleted]

dntbnmpls 7y ago

> but the reality is that most troubleshoot them in production I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers. > and gives you a full copy of the database for you, with real data Other than for small toy databases, I don't see how this would work for large databases. Not to mention the huge security issue. You can test, optimize, etc queries on non-production servers. If the problem is production specific, it most likely is more server specific and not query optimization related.

geekpowa 7y ago
Query plans are dependent upon actual data itself. Plan performance issues can be difficult to replicate in test envs. AFAICT, this product relies on LVM snapshots/copy on write : so is ideal diagnosing, trying out ideas on large databases.
ahachete 7y ago
> I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers. Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do. Query execution plans can change completely based on data volume and even with the data itself. Databases use detailed statistics about the data to influence the planner's decision. Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience. > Other than for small toy databases, I don't see how this would work for large databases. Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter. > You can test, optimize, etc queries on non-production servers. No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.
- dntbnmpls 7y ago