4 ms·

A small warning for folks. I once was responsible for migrating a legacy business app to Azure, and the app had a local MSSQL server co-running with the app (t

A small warning for folks. I once was responsible for migrating a legacy business app to Azure, and the app had a local MSSQL server co-running with the app (the same pattern that Litestream is using). As have been mentioned below, the app had been developed assuming the local access (and thus <1ms latency), so it had a ton of N+1 everywhere. This made it almost impossible to migrate/transition to another configuration. So, if this style of app hosting doesn't take off and you're at all worried about this being a dead end storage once you reach a certain scale, I'd recommend not doing this, otherwise your options will be very limited. Then again - I bet you could get very very far on a single box, so maybe it'd be a non factor! :)

inerte 1y ago
I used to work on a product where the app server and database were in the same rack - so similar low latency. But the product was successful, so our N+1 would generate thousands of queries and 1ms would become >500ms or more easily. Every other month we would look at New Relic and find some slow spot. It was a Rails app, therefore easy to get into the N+1 but also somewhat easy to fix.
- 1-more 1y ago
  For our rails app we actually added tests asserting no N+1s in our controller tests. Think a test setup with 1 post vs 10 posts (via factorybot) and you could do an assertion that the DB query count was not different between the two. A useful technique for any Railsheads reading this!
  - simonw 1y ago
    That's a good trick. In Django world I like pytest-django's django_assert_max_num_queries fixture: https://pytest-django.readthedocs.io/en/latest/helpers.html#django-assert-max-num-queries https://pytest-django.readthedocs.io/en/latest/helpers.html#... def test_homepage_queries(django_assert_max_num_queries, client): with django_assert_max_num_queries(10): assert client.get("/").status_code == 200 Or django_assert_num_queries to assert an exact number.
  - jasonwatkinspdx 1y ago
    Way back in the prehistoric era of Rails I just wrote a like 5 line monkey punch to ActiveRecord that would kill mongrel if queries per request went above a limit. Probably some of the most valuable code I've ever written on a per LOC basis lol. But anyhow, merging that into a new project was always a fun day. But on the other side of the cleanup the app stops falling down due to memory leaks.
metadat 1y ago
What is N+1?
- simonw 1y ago
  The thing where your app displays 20 stories in the homepage, but for each story it runs an extra query to fetch the author, and another to fetch the tags. It's usually a big problem for database performance because each query carries additional overhead for the network round trip to the database server. SQLite queries are effectively a C function call accessing data on local disk so this is much less of an issue - there's an article about that in the SQLite docs here: https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
- genpfault 1y ago
  https://stackoverflow.com/questions/97197 https://stackoverflow.com/questions/97197
- Scubabear68 1y ago
  Object Relational Mapping (ORM) tools, which focus on mapping between code based objects and SQL tables, often suffer from what is called the N+1 problem. A naive ORM setup will often end up doing a 1 query to get a list of object it needs, and then perform N queries, one per object, usually fetching each object individually by ID or key. So for example, if you wanted to see “all TVs by Samsung” on a consumer site, it would do 1 query to figure out the set of items that match, and then if say 200 items matched, it would do 200 queries to get those individual items. ORMs are better at avoiding it these days, depending on the ORM or language, but it still can happen.
  - OJFord 1y ago
    I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.
    - whizzter 1y ago
Quarrelsome 1y ago
I mean, that's not much of a trade off given that it seems that what you're saying is that using such a service might just show you how shit your code actually is. Its not its fault. :)
rco8786 1y ago
Bad query practices are always going to bite you eventually. I would not call that a shortcoming of this approach
- laurencerowe 1y ago
  It's not a bad query practice in SQLite! https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
masterj 1y ago
> I bet you could get very very far on a single box, With single instances topping out at 20+ TBs of RAM and hundreds of cores, I think this is likely very under-explored as an option Even more if you combine this with cell-based architecture, splitting on users / tenants instead of splitting the service itself.
- immibis 1y ago
  Single instance is underappreciated in general. There's a used server reseller near me, and sometimes I check their online catalogue out of curiosity. For only $1000ish I could have some few generations old box with dual socket 32-core chips and 1TB of RAM. I don't have any purpose for which I'd need that, but it's surprisingly cheap if I did. And things can scale up from there. AWS will charge you the same per month that it costs to get one of your own forever - not counting electricity or hard drives.
  - switz 1y ago
    I run my entire business on a single OVH box that costs roughly $45/month. It has plenty of headroom for growth. The hardest part is getting comfortable with k8s (still worth it for a single node!) but I’ve never had more uptime and resiliency than I do now. I was spending upwards of $800/mo on AWS a few years ago with way less stability and speed. I could set up two nodes for availability, but it wouldn’t really gain me much. Downtime in my industry is expected, and my downtime is rarely related to my web services (externalities). In a worst case scenario, I could have the whole platform back up in under 6 hours on a new box. Maybe even faster.
    - ahoog42 1y ago
      any notes or pointers on how to get comfortable with k8? For a simple nodejs app I was looking down the pm2 route but I wonder of learning k8 is just more future proof.
      - lillecarl 1y ago
        Use K3s in cluster mode, start doing. Cluster mode uses etcd instead of kine, kine is not good. Configure the init flags to disable all controllers and other doodads, deploy them yourself with Helm. Helm sucks to work with but someone has already gone through the pain for you. AI is GREAT at K8s since K8s has GREAT docs which has been trained on. A good mental model is good: It's an API with a bunch of control loops

inerte 1y ago

I used to work on a product where the app server and database were in the same rack - so similar low latency. But the product was successful, so our N+1 would generate thousands of queries and 1ms would become >500ms or more easily. Every other month we would look at New Relic and find some slow spot. It was a Rails app, therefore easy to get into the N+1 but also somewhat easy to fix.

1-more 1y ago
For our rails app we actually added tests asserting no N+1s in our controller tests. Think a test setup with 1 post vs 10 posts (via factorybot) and you could do an assertion that the DB query count was not different between the two. A useful technique for any Railsheads reading this!
- simonw 1y ago
  That's a good trick. In Django world I like pytest-django's django_assert_max_num_queries fixture: https://pytest-django.readthedocs.io/en/latest/helpers.html#django-assert-max-num-queries https://pytest-django.readthedocs.io/en/latest/helpers.html#... def test_homepage_queries(django_assert_max_num_queries, client): with django_assert_max_num_queries(10): assert client.get("/").status_code == 200 Or django_assert_num_queries to assert an exact number.
- jasonwatkinspdx 1y ago
  Way back in the prehistoric era of Rails I just wrote a like 5 line monkey punch to ActiveRecord that would kill mongrel if queries per request went above a limit. Probably some of the most valuable code I've ever written on a per LOC basis lol. But anyhow, merging that into a new project was always a fun day. But on the other side of the cleanup the app stops falling down due to memory leaks.

metadat 1y ago

What is N+1?

simonw 1y ago
The thing where your app displays 20 stories in the homepage, but for each story it runs an extra query to fetch the author, and another to fetch the tags. It's usually a big problem for database performance because each query carries additional overhead for the network round trip to the database server. SQLite queries are effectively a C function call accessing data on local disk so this is much less of an issue - there's an article about that in the SQLite docs here: https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
genpfault 1y ago
https://stackoverflow.com/questions/97197 https://stackoverflow.com/questions/97197
Scubabear68 1y ago
Object Relational Mapping (ORM) tools, which focus on mapping between code based objects and SQL tables, often suffer from what is called the N+1 problem. A naive ORM setup will often end up doing a 1 query to get a list of object it needs, and then perform N queries, one per object, usually fetching each object individually by ID or key. So for example, if you wanted to see “all TVs by Samsung” on a consumer site, it would do 1 query to figure out the set of items that match, and then if say 200 items matched, it would do 200 queries to get those individual items. ORMs are better at avoiding it these days, depending on the ORM or language, but it still can happen.
- OJFord 1y ago
  I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.
  - whizzter 1y ago

Quarrelsome 1y ago

I mean, that's not much of a trade off given that it seems that what you're saying is that using such a service might just show you how shit your code actually is. Its not its fault. :)

rco8786 1y ago

Bad query practices are always going to bite you eventually. I would not call that a shortcoming of this approach

laurencerowe 1y ago
It's not a bad query practice in SQLite! https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html

masterj 1y ago

> I bet you could get very very far on a single box, With single instances topping out at 20+ TBs of RAM and hundreds of cores, I think this is likely very under-explored as an option Even more if you combine this with cell-based architecture, splitting on users / tenants instead of splitting the service itself.

immibis 1y ago
Single instance is underappreciated in general. There's a used server reseller near me, and sometimes I check their online catalogue out of curiosity. For only $1000ish I could have some few generations old box with dual socket 32-core chips and 1TB of RAM. I don't have any purpose for which I'd need that, but it's surprisingly cheap if I did. And things can scale up from there. AWS will charge you the same per month that it costs to get one of your own forever - not counting electricity or hard drives.
- switz 1y ago
  I run my entire business on a single OVH box that costs roughly $45/month. It has plenty of headroom for growth. The hardest part is getting comfortable with k8s (still worth it for a single node!) but I’ve never had more uptime and resiliency than I do now. I was spending upwards of $800/mo on AWS a few years ago with way less stability and speed. I could set up two nodes for availability, but it wouldn’t really gain me much. Downtime in my industry is expected, and my downtime is rarely related to my web services (externalities). In a worst case scenario, I could have the whole platform back up in under 6 hours on a new box. Maybe even faster.
  - ahoog42 1y ago
    any notes or pointers on how to get comfortable with k8? For a simple nodejs app I was looking down the pm2 route but I wonder of learning k8 is just more future proof.
    - lillecarl 1y ago
      Use K3s in cluster mode, start doing. Cluster mode uses etcd instead of kine, kine is not good. Configure the init flags to disable all controllers and other doodads, deploy them yourself with Helm. Helm sucks to work with but someone has already gone through the pain for you. AI is GREAT at K8s since K8s has GREAT docs which has been trained on. A good mental model is good: It's an API with a bunch of control loops