How not to structure database-backed web apps: performance bugs in the wild

Post by blattimwind » Thu, Jun 28, 2018, 2:17 PM UTC

acutesoftware wrote: post #138

I need a very good reason before using any external library in an attempt to keep the total code base as clean as possible. It is just too easy to be rushed and bring in a heap of code, so I prefer to use SQL instead of ORM's. All access to the database is done in a single module and they are wrapped in functions like below def get_table_as_list(user_id, cols, tbl, where_clause, params_as_list, conn_str, order_by="1"…

Post by psadauskas » Thu, Jun 28, 2018, 2:19 PM UTC

unilynx wrote: post #121

Pretty cool to read. We built a (currently proprietary) CMS with our own scripting language, and instead of going the ORM way, we merged basic SQL into the language itself. We did that mostly to eliminate sending raw strings to databases (and all the injection risks and complexity that comes with it) but it does allow a few extra optimisations because the compiler can look at both the query and the language using it.…

Post by dmitriid » Thu, Jun 28, 2018, 2:21 PM UTC

EGreg wrote: post #141

olavk wrote: post #128

Earlier quoted context omitted.

So you have to use sharding because your databases have limited capacity. But you can just join in the app, because the app have unlimited memory?

The app doesn’t hold the entire database at a time. The app simply does the following: 1) Get the root record(s) from id(s) 2) See what related records it needs, combine them into a list of ids, partition list by shard 3) Ask each shard for the corresponding records 4) Repeat from 2 if necessary 5) Return this whole tree / graph to the user Graph databases can do this in O(1) instead of O(log N) lookups. Relational j…

Post by memset » Thu, Jun 28, 2018, 2:23 PM UTC

blattimwind wrote: post #196

latch wrote: post #23

I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (messa…

0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)

Post by nicktrocado92 » Thu, Jun 28, 2018, 2:25 PM UTC

blattimwind wrote: post #196

latch wrote: post #23

I've worked on moderately busy backend platforms (~10K-20k rps handled on a ~4 e5-2650 and aiming for 5ms 95p response times). It greatly depends on what you're doing, but for the majority of systems which are read heavy (and that most certainly includes "dynamic" sites like Amazon or Wikipedia), I hold to two major beliefs: 1 - Have very long TTLs on your internal cache servers with a way to proactively purge (messa…

0 - Caching antipattern 101: key = calculate_cache_key() if not cache.has(key): data = expensive_calculation() cache.store(key, data) else: data = cache.get(key)