6 ms·
I don't know why people think this. In the specific example of country, in fact I _have_ tested using a country class with hard coded maps of country codes to a
by dotancohen 8y ago
I don't know why people think this. In the specific example of country, in fact I _have_ tested using a country class with hard coded maps of country codes to arrays of data. Even so, benchmarking showed the single database call with a join (MySQL 5.1 or 5.5 I think) to be faster as I was already hitting the database anyway.
Don't prematurely optimize away your database's flexibility (managed content) before testing a properly-coded query.
- toast0 8y agoI've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I've seen a lot of cases where just moving sorting to the frontend took a 3 second query down to 10ms and sorting on the frontend wasn't just a few ms too either. There were too many sorts available in the UI to add matching indexes in the db. If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends.
- kthejoker2 8y agoWhat about analytics, hosting dimensional data outside your DB sounds terrible.
- dirkgently 8y ago> I've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I too have seen front end of an application do a lot of dumb stuff with state of my requests. The point is, don't confuse PEBKAC with an issue with a tool.
- mmt 8y ago> If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends. This may only true for data manipulation that's necessarily [1] more CPU-intensive/bound than I/O-intensive. It's difficult to imagine a situation where that would actually be true, however, in the context of a manipulation like a join. Given how much faster CPU power has increase relative to I/O, over the entire history of computing, CPU on database servers has been progressively less of an issue. This isn't to say that CPU power is infinite, and I've seen naive analyses attribute attribute performance problems to inadequate CPU power, even when it's an I/O issue masquerading as (system, not user) CPU time. In that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. See also Amdahl's Law and the Fallacies of Distributed Computing (in particular the ones about bandwidth and latency). [1] as opposed to merely as-implemented, such as due to PEBKAC, as a sibling comment points out [2] e.g. why ZFS might have wonderful features, but harware RAID cards can offload a remarkably significant pressure from the CPU and main memory, when it matters most
- scarface74 8y agoIn that respect, it's hard to scale a database server, in that now-rare and undervalued-by-programmers Ops knowledge of things like hardware and I/O and offloading [2] but that difficulty doesn't actually go away by moving the data around in a partly-distributed or even fully distributed system. It just drives up the cost. In a cloud environment - which is relevant since he was using AWS - it’s much cheaper and easier to autoscale app servers than database servers based on load. It could also be conceivable cheaper if you’re doing non time sensitive online analytics processing instead of something that requires a real time response. On AWS, you could set autoscaling and use spot instances to scale more when the rates are cheaper for compute. Also, when scaling up a database, there is more of a potential for wasting money because of idle capacity when the resources aren’t needed. It’s painless to remove an app VM out of service. Yes I know you can also use DynamoDB, but it’s non relational, the cost of adding additional global indexes and paying for extra read and write capacity gets expensive. There’s also Serverless Aurora that auto scales but it would also get really expensive fast.
- mmt 8y ago> which is relevant since he was using AWS Of course, in that case, one is so limited by Amazon's specific hardware choices, if not by virtualization [1]. The OC didn't specify AWS but did mention MySQL, instead of Postgres, which further limits ones ability to implement basic performance optimizations, like separating data and index I/O [2]. > it’s much cheaper and easier to autoscale app servers than database servers based on load Yet I'm still not convinced this is true if the "load" is actually I/O-bound rather than CPU-bound [3]. It may be easier to scale (especially autoscale), but, with cloud providers, that doesn't necessarily mean cheaper. In practice, distributed computing never scales as good as linearly, and people tend not to even measure for diminishing returns or how Amdahl's Law affects them. > It could also be conceivable cheaper if you’re doing non time sensitive online analytics processing instead of something that requires a real time response. I believe GCP has instituted single-instance pricing that's essentially pay-what-you-use in terms of number of cores, so that's even possible with a database server on that provider, but, again, only if the issue is CPU, which I maintain it generally isn't. > Also, when scaling up a database, there is more of a potential for wasting money because of idle capacity when the resources aren’t needed. I recall this sort of thing being the original argument for virtualization, when it first came out, but it only held water if one was grossly over-paying for the resources to begin with (which most "enterprise" shops certainly were, and still are). To me, it's an argument for why, beyond modest size, buying cloud infrastructure is just over-paying. > paying for extra read and write capacity gets expensive > Serverless Aurora that auto scales but it would also get really expensive fast Par for the course for any cloud services, in that they may seem convenient or "auto scale" but the price premium is tremendous, especially at scale. How do they compare with paying for that capacity over EBS? [1] Yes, I know they now have "bare metal" instances, or is it still only instance (singular)? [2] Not that it matters much if one is limited to 10Gb/s or even 40Gb/s (if you can get it) EBS volumes. [3] Which the OC's sort very likely would be but the OP's join would not. To that extent, perhaps the OC's whole premise is a red herring, because I doubt anyone is suggesting not offloading to the app an ORDER BY that's just the last step before presentation to the user.
- dotancohen 8y ago> just moving sorting to the frontend took a 3 second query down to 10ms I've seen this too, but the problem can often be optimized in SQL as well. For instance, if you expect that only one User will match a specific username, then adding LIMIT 1 to the query will instruct the DB to return after finding a single hit, which can significantly shorten query time. But then adding an ORDER BY clause will cause (at least MySQL) to parse the entire table anyway, even though you've got the LIMIT clause. This is just an example, one really needs to know their database and their application to make these types of decisions. But the premature optimization applies to SQL as well, if the results can be sorted/filtered/transformed in application memory better than in the database so be it.