6 ms·

PlanetScale Insights: Advanced query monitoring

82 points by arjunnarayan 4y ago

joshstrange 4y ago
This is a great addition to the PlanetScale offerings. One of the scariest things with PS was "I have no clue how many rows are read/written" and usage-based plans are hard if you don't think in the terms they they charge in. It was just a metric I had never tracked or done much work on. That might be telling of myself and my DB management skillset (or lack there of). So far my usage has been minuscule in comparison to the PS limits. That said, I had been hoping for better tools to identify "problem queries" early instead of just when the billing cycle comes up and so I'm happy to see work in that direction. Just a random anecdote about PlanetScale: A few weeks ago I realized their databases did not have the timezone database loaded into them and it wasn't something I could do myself. I needed this so I could do `CONVERT_TZ` to convert from UTC to the user's TZ (for report aggregation). I reached out to support and in about a week they had added it to their roadmap, shipped it, and turned on the new feature for my DBs. They have been a joy to work with so far and I encourage you give them a shot, especially if you are on Aurora Serverless (V1 or V2).
- Scarbutt 4y ago
  The lack of FKs is a turn off for most apps.
  - joshstrange 4y ago
    I can understand that, for myself I don't miss them. An index on the column is just fine for me and personally I prefer to manage that in the application layer (I know I'm probably not in the majority). Lack of FKs is what allows for their scaling technique (using Vitess) from what I understand. I'll say for my use it's not been an issue but I do understand that migrating an app that does need/use them might be hard/impossible without other big changes in the code.
    - ithrow 4y ago
      Looks like a must-have thing that is also missing from planetscale is point-in-time recovery.
      - KwisaksHaderach 4y ago
        PiTR is like the primary reason for us using a managed DB, it's indeed a weird and fatal omission from a managed DB offering.
        derekperkins 4y ago
        Vitess supports PITR, so I'm sure it'll land in Planetscale sooner or later
  - datalopers 4y ago
    Database-enforced FKs are an overrated crutch anyway. Apply the third normal form, utilize transactions correctly, and you won't ever need them.
    - derekperkins 4y ago
      Strongly disagree. They allow you to declaratively say what data is allowed. No system where devs write code is going to be infallible, no matter how much people want it to be true. Show me a database without FKs and I'll show you a database with orphaned rows and insistent data. That being said, depending on the data you're working with, you may be fine with that trade-off.
      - deleted 4y ago
        [deleted]
    - vvern 4y ago
      > utilize transactions correctly This seems hard to do in Vitess or PlanetScale given the technically READ UNCOMMITTED isolation [1] when cross shard, and, in scaled deployments, will still require experimental 2PC [2] cross shard transaction. So, like, yeah, if you had serializable isolation, then transactions might save you so long as your code isn't buggy, but, literally the reason the system doesn't implement them is because it doesn't have isolated transactions. [1]: https://news.ycombinator.com/item?id=22170416#22177783 https://news.ycombinator.com/item?id=22170416#22177783 [2]: https://vitess.io/docs/13.0/reference/features/two-phase-commit/ https://vitess.io/docs/13.0/reference/features/two-phase-com...
      - derekperkins 4y ago
        In a production environment with well architected sharding, that rarely comes up in practice. It will be designed so that 99% of your operations happen on a single shard, both for performance and for transactional guarantees. That's often a customer/tenant id, and it's rare in practice that you will be performing cross-customer transactions.
  - deleted 4y ago
    [deleted]
  - derekperkins 4y ago
    Vitess, the underlying tech, only disallows FKs if you use online schema changes. They have to be on the same logical shard, and it just uses standard MySQL FKs. Hopefully Planetscale allows them in the future, as long as you're willing to give up OSC. We've been running Vitess for years with that trade-off and it works great.
    - vvern 4y ago
      > They have to be on the same logical shard This is a pretty major limitation. Part of the reason to use Vitess is to scale out. It is often very valuable to have a small number of root elements in a star schema and to have foreign keys which ladder up to them.
      - derekperkins 4y ago
        That's a different problem also solvable with Vitess. For those smaller tables, you can define them as a reference table, then have the same rows on every shard, so you can continue to have FKs to all of those. https://vitess.io/docs/13.0/user-guides/vschema-guide/advanced-vschema/#reference-tables https://vitess.io/docs/13.0/user-guides/vschema-guide/advanc... When you're choosing your sharding keys, you want to design it where the bulk of your operations happen on a single shard, often a tenant/customer id. That guarantees that all customer data lands on a single shard, with FKs across every table you want. We're running on ~40 shards across 6 keyspaces, and there are very few cases where we can't use FKs.
- throwusawayus 4y ago
  tz setup is done with executing a single script. not surprising they could fix quickly. bigger surprise is they forgot to do this before youre support request. generally this is table stakes for managed DB this is fourth day in a row of planetscale ads^H^H^H blog posts being on hn front page. as i mentioned on yesterdays thread, innodb_rows_read is known to be buggy. regardless, by design it includes cached rows. terrible thing to base billing on. real cloud providers base it on i/o instead since this is more reasonable metric of "use" planetscale's fork of mysql-server adds only a single commit, which exposes rows_read in an extra place. this from company that keeps talking about "building a database" https://github.com/planetscale/mysql-server https://github.com/planetscale/mysql-server
  - derekperkins 4y ago
    "real cloud providers" most definitely charge based on rows read/written. Many startups / side projects choose the on-demand billing model because they don't want a fixed $x / mo when they don't need it. Some of them also have pre-provisioned options, and it seems likely that Planetscale will probably end up doing something similar. https://aws.amazon.com/dynamodb/pricing/ https://aws.amazon.com/dynamodb/pricing/ https://firebase.google.com/docs/firestore/pricing https://firebase.google.com/docs/firestore/pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing
    - throwusawayus 4y ago
      i was talking about managed sql databases your first two examples are nosql. third example charges by data size processed, not by rows! rows is weird metric since some tables have tiny rows, some have huge
      - derekperkins 4y ago
        The point is that the market has shown there is a huge appetite for alternative database billing models other than a fixed cost per month. From my limited personal interactions, I'm aware of 10-20 developers who are using Planetscale in large part because of their billing model. They would have never considered a SQL database before because of the fixed cost. Those nosql options (probably the most popular in the world) also have the issue that row sizes are different, and if you're super cost conscious, you can change your architecture to take advantage of it. For example with Planetscale, you could store a lot more in JSON columns instead of other tables to reduce costs if that was your primary objective. Is your frustration that you'd like to use Planetscale or a managed Vitess, but you are worried about locking yourself into a pricing model that you don't think will work for you?
- gopalv 4y ago
  > That said, I had been hoping for better tools to identify "problem queries" early instead of just when the billing cycle comes up and so I'm happy to see work in that direction I see the documentation and graphs, but can't find if PlanetScale provides a query interface into this data. Single user systems do okay with reports like the ones I see in the link, where you can actually go in and drill-down into specific details in there. It would much more awesome for the DBA style user if the reporting data was actually just loaded into another database/table with join schemas to query the data exactly as the report did. That of course, assumes the DBA is consultant type who is dropped in to fix cost overruns rather than the app developer going over their queries again. In my last job, I built something of this sort (Hive has a protobuf SerDe + a special table named sys.query_data), so that I could connect up a CDSW Jupyter notebook and narrow down queries with a python program + a loop. Of course, the queries themselves were also customer-paid queries, but it was much more flexible + a bunch of canned reports did most of the work when moving it across customers. But before it was baked-in into Hive/CDW, it was actually a syslog parser which fed into a sqlite db which is almost exactly the same (but mostly intended at solving txn locking/conflict checking across hundreds of queries touching the same informatica audit log table).
gtCameron 4y ago
We moved from Aurora Serverless v1 to PlanetScale a couple months ago and love it so far. Tools like this are super helpful for someone like me who is a full stack developer with limited DB expertise to keep our platform running smoothly. Their team is awesome, I requested a couple features in the CLI and they were there within a few hours. Support is responsive and the sales team was super helpful getting everything running and migrated.

82 points by arjunnarayan 4y ago

This is a great addition to the PlanetScale offerings. One of the scariest things with PS was "I have no clue how many rows are read/written" and usage-based plans are hard if you don't think in the terms they they charge in. It was just a metric I had never tracked or done much work on. That might be telling of myself and my DB management skillset (or lack there of). So far my usage has been minuscule in comparison to the PS limits. That said, I had been hoping for better tools to identify "problem queries" early instead of just when the billing cycle comes up and so I'm happy to see work in that direction. Just a random anecdote about PlanetScale: A few weeks ago I realized their databases did not have the timezone database loaded into them and it wasn't something I could do myself. I needed this so I could do `CONVERT_TZ` to convert from UTC to the user's TZ (for report aggregation). I reached out to support and in about a week they had added it to their roadmap, shipped it, and turned on the new feature for my DBs. They have been a joy to work with so far and I encourage you give them a shot, especially if you are on Aurora Serverless (V1 or V2).

Scarbutt 4y ago
The lack of FKs is a turn off for most apps.
- joshstrange 4y ago
  I can understand that, for myself I don't miss them. An index on the column is just fine for me and personally I prefer to manage that in the application layer (I know I'm probably not in the majority). Lack of FKs is what allows for their scaling technique (using Vitess) from what I understand. I'll say for my use it's not been an issue but I do understand that migrating an app that does need/use them might be hard/impossible without other big changes in the code.
  - ithrow 4y ago
    Looks like a must-have thing that is also missing from planetscale is point-in-time recovery.
    - KwisaksHaderach 4y ago
      PiTR is like the primary reason for us using a managed DB, it's indeed a weird and fatal omission from a managed DB offering.
      - derekperkins 4y ago
        Vitess supports PITR, so I'm sure it'll land in Planetscale sooner or later
- datalopers 4y ago
  Database-enforced FKs are an overrated crutch anyway. Apply the third normal form, utilize transactions correctly, and you won't ever need them.
  - derekperkins 4y ago
    Strongly disagree. They allow you to declaratively say what data is allowed. No system where devs write code is going to be infallible, no matter how much people want it to be true. Show me a database without FKs and I'll show you a database with orphaned rows and insistent data. That being said, depending on the data you're working with, you may be fine with that trade-off.
    - deleted 4y ago
      [deleted]
  - vvern 4y ago
    > utilize transactions correctly This seems hard to do in Vitess or PlanetScale given the technically READ UNCOMMITTED isolation [1] when cross shard, and, in scaled deployments, will still require experimental 2PC [2] cross shard transaction. So, like, yeah, if you had serializable isolation, then transactions might save you so long as your code isn't buggy, but, literally the reason the system doesn't implement them is because it doesn't have isolated transactions. [1]: https://news.ycombinator.com/item?id=22170416#22177783 https://news.ycombinator.com/item?id=22170416#22177783 [2]: https://vitess.io/docs/13.0/reference/features/two-phase-commit/ https://vitess.io/docs/13.0/reference/features/two-phase-com...
    - derekperkins 4y ago
      In a production environment with well architected sharding, that rarely comes up in practice. It will be designed so that 99% of your operations happen on a single shard, both for performance and for transactional guarantees. That's often a customer/tenant id, and it's rare in practice that you will be performing cross-customer transactions.
- deleted 4y ago
  [deleted]
- derekperkins 4y ago
  Vitess, the underlying tech, only disallows FKs if you use online schema changes. They have to be on the same logical shard, and it just uses standard MySQL FKs. Hopefully Planetscale allows them in the future, as long as you're willing to give up OSC. We've been running Vitess for years with that trade-off and it works great.
  - vvern 4y ago
    > They have to be on the same logical shard This is a pretty major limitation. Part of the reason to use Vitess is to scale out. It is often very valuable to have a small number of root elements in a star schema and to have foreign keys which ladder up to them.
    - derekperkins 4y ago
      That's a different problem also solvable with Vitess. For those smaller tables, you can define them as a reference table, then have the same rows on every shard, so you can continue to have FKs to all of those. https://vitess.io/docs/13.0/user-guides/vschema-guide/advanced-vschema/#reference-tables https://vitess.io/docs/13.0/user-guides/vschema-guide/advanc... When you're choosing your sharding keys, you want to design it where the bulk of your operations happen on a single shard, often a tenant/customer id. That guarantees that all customer data lands on a single shard, with FKs across every table you want. We're running on ~40 shards across 6 keyspaces, and there are very few cases where we can't use FKs.
throwusawayus 4y ago
tz setup is done with executing a single script. not surprising they could fix quickly. bigger surprise is they forgot to do this before youre support request. generally this is table stakes for managed DB this is fourth day in a row of planetscale ads^H^H^H blog posts being on hn front page. as i mentioned on yesterdays thread, innodb_rows_read is known to be buggy. regardless, by design it includes cached rows. terrible thing to base billing on. real cloud providers base it on i/o instead since this is more reasonable metric of "use" planetscale's fork of mysql-server adds only a single commit, which exposes rows_read in an extra place. this from company that keeps talking about "building a database" https://github.com/planetscale/mysql-server https://github.com/planetscale/mysql-server
- derekperkins 4y ago
  "real cloud providers" most definitely charge based on rows read/written. Many startups / side projects choose the on-demand billing model because they don't want a fixed $x / mo when they don't need it. Some of them also have pre-provisioned options, and it seems likely that Planetscale will probably end up doing something similar. https://aws.amazon.com/dynamodb/pricing/ https://aws.amazon.com/dynamodb/pricing/ https://firebase.google.com/docs/firestore/pricing https://firebase.google.com/docs/firestore/pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing
  - throwusawayus 4y ago
    i was talking about managed sql databases your first two examples are nosql. third example charges by data size processed, not by rows! rows is weird metric since some tables have tiny rows, some have huge
    - derekperkins 4y ago
      The point is that the market has shown there is a huge appetite for alternative database billing models other than a fixed cost per month. From my limited personal interactions, I'm aware of 10-20 developers who are using Planetscale in large part because of their billing model. They would have never considered a SQL database before because of the fixed cost. Those nosql options (probably the most popular in the world) also have the issue that row sizes are different, and if you're super cost conscious, you can change your architecture to take advantage of it. For example with Planetscale, you could store a lot more in JSON columns instead of other tables to reduce costs if that was your primary objective. Is your frustration that you'd like to use Planetscale or a managed Vitess, but you are worried about locking yourself into a pricing model that you don't think will work for you?
gopalv 4y ago
> That said, I had been hoping for better tools to identify "problem queries" early instead of just when the billing cycle comes up and so I'm happy to see work in that direction I see the documentation and graphs, but can't find if PlanetScale provides a query interface into this data. Single user systems do okay with reports like the ones I see in the link, where you can actually go in and drill-down into specific details in there. It would much more awesome for the DBA style user if the reporting data was actually just loaded into another database/table with join schemas to query the data exactly as the report did. That of course, assumes the DBA is consultant type who is dropped in to fix cost overruns rather than the app developer going over their queries again. In my last job, I built something of this sort (Hive has a protobuf SerDe + a special table named sys.query_data), so that I could connect up a CDSW Jupyter notebook and narrow down queries with a python program + a loop. Of course, the queries themselves were also customer-paid queries, but it was much more flexible + a bunch of canned reports did most of the work when moving it across customers. But before it was baked-in into Hive/CDW, it was actually a syslog parser which fed into a sqlite db which is almost exactly the same (but mostly intended at solving txn locking/conflict checking across hundreds of queries touching the same informatica audit log table).

gtCameron 4y ago

We moved from Aurora Serverless v1 to PlanetScale a couple months ago and love it so far. Tools like this are super helpful for someone like me who is a full stack developer with limited DB expertise to keep our platform running smoothly. Their team is awesome, I requested a couple features in the CLI and they were there within a few hours. Support is responsive and the sales team was super helpful getting everything running and migrated.