7 ms·

"our idea is that developers should know about details such as schemas and indexes (the Parse engineers strongly agreed in hindsight)." Many engineers I have w

• nodamage 10y ago
  > At this very moment, I'm working on a classical DBMS, searching through around 200 million tuples within the order of 2 seconds Just curious, does this kind of performance require dedicated hardware or is it possible on platforms like AWS?
  • edejong 10y ago
    This is on a AWS RDS instance, everything is in (DBMS) cache, db.m4.large.
    • geocar 10y ago
      You should revisit your schema. That sounds much too slow. q)a:(200000000?200000000),-42 /pick some random numbers q)\t a?last a 303 303msec for a linear scan of 200m ints on an i5 laptop means you should be able to do better than 2sec for a "smart" query.
      • philliphaydon 10y ago
        It seems slow to me too... We have tables with > billion records In RDS / SQLServer and return results in <1 second...
        • felixge 10y ago
          And that's for a sequential scan over all of those tuples? That'd be very impressive. Does SQLServer support parallel execution? Or how come it be so fast?
          • philliphaydon 10y ago
            > Does SQLServer support parallel execution? For a long time as far as I know?
            • felixge 10y ago
              Cool. I'm not familiar with it, that's why I ask :). Anyway, is the performance you mentioned for a scan over the entire table? E.g. to do an aggregate?
              • philliphaydon 10y ago
                Honestly don't remember, I'm working on new stuff in PostgreSQL now. I know a count of the whole table takes about a minute tho. But the filter > group by was ~1s as the grouping was done on about < 50 rows of the filtered result. Without knowing the original query above was using it's speculation on why it was slow and mine was fast. My argument is just that SQL Server isn't magically slow. MySQL/PostgreSQL/SQL Server are super fast. And if you don't massage the database it can be super slow too.
                • felixge 10y ago
                  Thanks. > I know a count of the whole table takes about a minute tho. But the filter > group by was ~1s as the grouping was done on about < 50 rows of the filtered result. Yeah, that makes sense. I'm running some queries in production that take a 150 million row table, filter it down to ~100-300k rows which are then aggregated/grouped. This usually takes < 1s. However, if I'd try to do a count(*) on the table, that'd be around a minute as well. > Without knowing the original query above was using it's speculation on why it was slow and mine was fast. My argument is just that SQL Server isn't magically slow. MySQL/PostgreSQL/SQL Server are super fast. And if you don't massage the database it can be super slow too. Yeah. The query / query plan will be needed to go more in-depth on these kind of discussions. The amount of disk vs memory hits during execution obviously as well. Anyway, I just wanted to understand if SQL Server was e.g. an order of magnitude faster than Postgres when scanning a large number of tuples. But I guess the answer to that is: probably not.
    • xref 10y ago
      Just to clarify, the whole db fits into 8gb of ram you're saying?
• pvg 10y ago
  Why non-replicated? Do you mean non-sharded?
  • edejong 10y ago
    I actually meant clustered. Replication is of course a good idea, but distributed query processing, esp. in combination with transaction processing is often too complicated to get the job done.
    • pvg 10y ago
      Close enough.
    • jo909 10y ago
      I agree, but how do you argue for that? I work with a customer where every application has to have a dedicated environment, running on VMs in a seperate VPC on a private cloud. The default DB is a galera cluster with 3 nodes, even for the most simple application. The argument for it is that cloud VMs are expected to fail, so everything has to be setup to tolerate instance failure without downtime. All I have to counter it is the supposedly higher risk of data loss in a complicated split brain, the unspecific "more complicated" etc. While the risk of instance failure is very real, that feels a bit theoretical and vague.
      • falcolas 10y ago
        > how do you argue for that 0 downtime and 0 data loss is impossible, both theoretically and practically. Even a Galara cluster is going to have downtime if a node fails (timeouts expiring, etc.). Really, it comes down to what trade-offs is the company willing to accept: the potential for stale reads for high availability, slow/aborted writes for high consistency, downtime for reduced complexity, etc. A few notes about Galara: its performance going to become severely degraded if one of the nodes decides it needs to be rebuilt - both that node and the donor node will effectively be out of the loop, with the network link between them saturated for the duration. That degraded state doesn't require the node to go down, it can happen spontaneously. Galara also imposes limits on write frequency and location - if you're doing many writes, you don't want to split those writes between nodes, since every write has to look at every other pending transaction on every node before it can occur. An automated RDB master failover can occur in under 30 seconds, easily. You can also typically run master-master without a risk of split brain by specifying even and odd primary keys. > cloud VMs are expected to fail Yup, but that timeframe is usually on the order of months or years. And if you use externally backed disk stores (at the cost of read/write speed) you can be back up in a matter of minutes with a single master. Even a full restore from backups can typically be done in 10-15 minutes; constrained mostly by your network speed.
• DivineTraube 10y ago
  > Some problems are caused by DBMS to backend and backend-to-frontend network utilization, which is generally not something a specialized DBMS can solve. I absolutely agree. RDBMSs are the kings of one-size-fits-most. In the NoSQL ecosystem, MongoDB starts growing into this role. Of course, many performance problems are not caused by the backend but rather the network (an average website makes 108 HTTP requests) or the frontend (Angular2 without AOT, I'm looking at you). We took an end-to-end approach to tackle this problem: 1. As objects, files and queries are fetched via a REST API, we can make them cachable at the HTTP level to transparently use browser caches, forward proxies, ISP interception proxies, CDNs and reverse proxies. 2. In order to guarantee cache coherence, we invalidate queries and objects in our CDNs and reverse proxy caches when the change using an Apache Storm-based real-time query matching pipeline. 3. To keep browser up-to-date we ship a Bloom filter to the client indicating any data that has a TTL that is non-expired but was recently written. This happens inside the SDK. Optionally client can subscribe to queries via websockets (as in Firebase or Meteor). The above scheme is the core of Baqend and quite effective in optimizing end-to-end web performance. Coupled with HTTP/2 this gives us a performance edge of >10x compared to Firebase and others. The central lesson learned for us is that it's not enough to focus on optimizing one thing (e.g. database performance) when the actual user-perceived problems arise at a different level of the stack.
  • akanet 10y ago
    I think the comparison to Firebase is a bit disingenuous. Their core offering is real-time synchronization of large, arbitrary data structures. From perusing Baqend briefly, it seems like your architecture is much more poll-based.
    • ako 10y ago
      The streaming queries functionality seems to fill this need: https://www.baqend.com/guide/#streaming-queries https://www.baqend.com/guide/#streaming-queries ?
      • inlined 10y ago
        I work on Firebase now, though not the DB team anymore. I'd love more data on how the DB is being outperformed if you're willing to share. Was it write throughout? Read throughout? Latency? Were you using small or large object sizes? Were indexes and ACLs used in both cases? Are you sure your firebase layout used best practices? Have you run the test lately to verify whether the findings are still true?
• wsh91 10y ago
  It seems like you're suggesting that "classical" DBMS are inherently relational. Pardon me if I've misread you but, respectfully, as awesome as relational databases are, you haven't offered any reason why they should be your default versus some kind of NoSQL store. Insofar as your concern is simplicity, which you say you prefer (understandably!), it's actually just as likely the simplest option will be non-relational. There's also nothing inherently specialized about a non-relational store; in particular, hierarchical and key-value stores have at least as much maturity in production as relational ones (cf. IMS and dbm, respectively). (Sorry for the pedantry, but I both work on a cloud document-oriented database and recently watched a bunch of collegiate developers struggle to set up MySQL when their queries could easily have been handled by a simpler system.)
  • fabian2k 10y ago
    I'd argue that the power of a classical relational database makes it easier to handle for someone without in-depth knowledge of databases than many NoSQL databases. Of course, if all you need is to look up values by keys, a key-value store is a much simpler solution. But the moment you need more complicated or just different kinds of queries than you expected, NoSQL can be rather difficult. You can get a lot of complicated queries done with mediocre SQL knowledge and a general-purpose RDBMS, but trying to use a more specialized database outside the intended use can be pretty painful. I don't have all that much experience with NoSQL databases, but my impression was always that it is more important there to understand the limitations of the database than with something like Postgres that is more forgiving. They might be simpler to get started with, but much harder to use correctly.
  • edejong 10y ago
    The following reasons come to mind wrt RDBMS: - When properly normalized, future modifications to the relational model will often be much simpler. Transforming hierarchical data stores or document-based ones often involves changing much of the code which calls the database - Query optimizers are often performing work which is difficult to implement yourself. - When properly called, query composition allows more computation to be done close to the data. - Knowledge of RDBMs is widespread allowing a low barrier to entry (is also a huge disadvantage, since it brings inexperienced people to use a sophisticated tool, leading to overgeneralized bad press) - Index management in relational model is often simpler and logical - Many RDBMs have a proven track-record of stability, code quality and feature completeness. Take a look at PostgreSQL, for example: GIN / GIST / PostGIS / date-time / windowing functions / streaming results. Also, the extensibility of postgres is astonishing, the community is warm and welcoming to beginners. Obviously, I could make a similar list of disadvantages of RDBMs. It's just my experience that the advantages mostly outweigh the disadvantages.
• pmelendez 10y ago
  "searching through around 200 million tuples within the order of 2 seconds (partially stored columnar using psql array types and GIN indexes). Not for a second did I consider using any kind of specialized database, nor optimized algorithms, except for some application-level caching. " -- That's cool and all, but for some real-time applications, retrieving in the order of seconds is unacceptable. DBMS has their earned place in the backend stack but I found that statement of covering 98% of the cases a bit of a stretch.
  • edejong 10y ago
    Of course, this is an extreme example. Most of our front-end is able to serve quite complicated data within 500 milliseconds. Some queries are over a longer time-span (3 months, 0.7 million truck-movement-legs, +/- 200M links), so a 2 second wait-time (including a spinner) is sufficient.
• zzzcpan 10y ago
  You are assuming that 95% of the jobs will remain the same throughout their entire lifetimes. Which is not true and I'm sure many would prefer for a database not to become a liability in case they happen to attract a lot of users, which requires more availability than a classical DBMSes can provide. The problem is more about constrains required to scale and achieve high-availabily and low-latency, rather than a database choice. But classical DBMSes lack those constrains and you may and probably will end up with a design that just cannot be scaled without removing and redesigning a lot of things that users rely on, and also with engineers that never learned how to do that, so you may end up in an urgent need of a distributed systems specialist too. Scary. You can avoid that by using systems designed for high-availability from the very beginning, like Riak. Same goes for blobs and filesystems, using POSIX API instead of an object storage is going to haunt you later on, because in your designs you may end up relying on things that ultimately cannot scale well and guaranty latency, as gitlab learned the hard way.
  • dhd415 10y ago
    I'd say YAGNI applies here since 95% of systems will never need to scale beyond the capabilities of a properly-configured RDBMS. Ensuring consistency and durability with a non-relational, distributed datastore generally requires a lot more detailed understanding of its internals than with a RDBMS, so choosing one of those rather than a RDBMS is going to impose costs that should be deferred until they're absolutely necessary.
    • oblio 10y ago
      Plus it's waaay more likely that bugs in application code will haunt you even more. Especially the lack of things such as foreign keys and other constraints. Schemas can be straight jackets, but in many situations they're actually closer to life jackets.
      • gerbilly 10y ago
        Totally agree that schemas are lifejackets. Carfully modelled data can outlive the application it was originally written for. Poorly structured data, on the other hand, where the schema and consistency is enforced in code is much harder to migrate. Maybe our problem is that we write systems with a disposable point of view? We don't even think the system will be around long enough to require migration.
• sandGorgon 10y ago
  > At this very moment, I'm working on a classical DBMS, searching through around 200 million tuples within the order of 2 seconds (partially stored columnar using psql array types and GIN indexes). hi, this comment was timely. I have been asking myself what to use to persist a 10 million node graph. There has been a lot of marketing around using Hbase (obscene to setup and maintain) and Cassandra (much better.. but paywalled). My main db is postgresql and we use jsonb quite extensively. The problem is that there is a lot of statements that "a single huge jsonb table in postgresql will never scale. you will have made a horrible mistake". Seems you have ample experience on this front - whats your take?
  • mhluongo 10y ago
    Depends heavily on your data access patterns. If you need eg recursive queries or pattern matching, you might be better served by a graph database.
  • edejong 10y ago
    hi sandGorgon. I agree with mhluongo on this. If you want to do things like transitive closure or connected components, you fall in the 2% section above. However, if you want to encode graphs for message systems, a simple table with parent-id might do just fine. Wrt jsonb: if you only do key->jsonb tables, you might want to consider another kind of system. Nevertheless, we've got millions of trajectories, connected to millions of annotations (links to trajectories with semi-structured jsonb), and combined with some GIN indexes, it works pretty well.