12 ms·
How I Reduced My DB Server Load by 80%
- tyingq 9y agoTldr: Your ORM might generate heavyweight queries. Edit: in this example, case insensitive uniqueness validation in Activerecord
- xfour 9y agoExactly why ORMs are a bad idea. I've always wondered whether ORMs help or harm. I feel like the one reason to use it is if you have a development team that isn't capable of writing SQL which in itself is bad.
- jackmott 9y agoit can be nice to have the whole stack type checked, so you know the query is good as you write it. There are a few languages/libraries where you can do this while also writing SQL directly, so you can kind of get the best of both worlds. Like F# sql type providers, where you can get compile time (or editor time) checks of your queries against the DB or a schema.
- stevenou 9y agoI think ORMs are there to make developing easier - but doesn't obviate the need for the user to understand how the underlying database works. For one based on my understanding of their use-case, could've used a validation :on => :create only. Likewise, if case-insensitivity is needed, they can enforce that on record insertion or, if they're using something else like MySQL, just use case insensitive collation. The fact that I know how to write SQL doesn't mean it's always easier/faster/better than using an ORM...
- megous 9y ago> I think ORMs are there to make developing easier Only for object oriented apps, where you want to tightly link behavior to your objects. For other kinds of app architectures, there may be no relational/object oriented mismatch to smooth out.
- jbverschoor 9y agoSounds like stating that we should all use assembler.
- bdcravens 9y agoNo, you could write raw SQL (we do this alot in our Rails app) or perhaps push out your abstractions to stored procedures (not a good option IMO, but maybe better than letting my framework come up with the best SQL)
- wyc 9y agoORMs are great for prototyping new business systems. The performance is not great, but when your priority is to quickly model business logic into something functioning, there are few better tools for it. However, if you have well-defined requirements and are expecting high-volume traffic from the get-go, then it could be wise to skip the ORM.
- porker 9y ago> Exactly why ORMs are a bad idea I'm not a full-out fan for ORMs but... this is not an ORM issue. It's an issue with an ORM of the ActiveRecord variety that pushes database-logic back to code level for some strange reason. The ORM I'm most familiar with (Doctrine2, Object Mapper pattern) doesn't do this. It does other weird stuff, but not this.
- Semiapies 9y agoJust so. SQLAlchemy and many other ORMs allow you to simply define an index. ActiveRecord always reimplemented far too much of the database. I'm not sure whether that was due to the state of MySQL functionality at the time or what.
- esaym 9y agoWell the problem is, if you don't use an ORM, you'll invent one yourself...only poorly. And new hires will end up taking a ton of time to learn this "custom" orm framework of yours. And there are many bad, or simply, "too simple" ORMs out there that really don't help you much. I haven't really found one better than Perl's DBIx::Class (though outside of Java, Python, Ruby, I have haven't really looked). Case in point, I recently found this little gem[0] allowing easy correlated subqueries. I basically took a webpage that was loading in over 2 minutes, and reduced it to about 100ms and the outputted SQL was about 2 pages long (from about half a page of custom resultset orm code). I used several correlated subqueries to sort of pivot part of a table (well several tables actually). The original author of the code I was working on was fetching entire tables of data, with each row fetching (joining) to another entire table (it did this in several levels really) just to sum some values. This was all valid ORM code (even with handy 'if' statements checking every row id to do in ORM join (yes he didn't even use a dang where clause!)) but it showed a true lack of knowledge of the ORM at hand and even SQL in general. Nonetheless, I saved the day :) [0] https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/ https://blog.afoolishmanifesto.com/posts/introducing-dbix-cl...
- joncrocks 9y agoORMs try and solve a hard problem, and the devil is in the detail. https://martinfowler.com/bliki/OrmHate.html https://martinfowler.com/bliki/OrmHate.html
- marcosdumay 9y agoIt surely is hard. But is it worth solving? The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity. If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model. All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.
- zip1234 9y agoORMs are a great idea and can save a lot of time and prevent a lot of errors. However, they are not a silver bullet. If you have performance bottlenecks you still need to know SQL and should be looking at the SQL that the ORM generates. There is also a 'mixed-mode' ORM or a lightweight ORM such as Dapper. Dapper allows you to write the queries in SQL but it makes it trivially easy to convert those to actual objects and allow you to parameterize them.
- StavrosK 9y agoI don't really understand why people throw the baby out with the bathwater like that. What's the problem of using an ORM for 99% of your queries and doing the other 1% in raw SQL? Many ORMs (like Django's) even help you by mapping the results back into models automatically[1]. [1] https://docs.djangoproject.com/en/1.11/topics/db/sql/#mapping-query-fields-to-model-fields https://docs.djangoproject.com/en/1.11/topics/db/sql/#mappin...
- dpark 9y agoI use something like Dapper on one of my services. I don’t consider it “throwing the baby out with the bath water”. My experience with ORMs is that they waste far more time debugging odd behavior and working around quirks and poor generated queries than they save in coding effort. I’ve never felt that ORMs actually saves me that much effort except for the boilerplate of deserializing into objects. Everything else I feel is a net negative. Writing SQL generally isn’t that hard. For the cases where it is hard, ORM generally does a poor job anyway.
- zaptheimpaler 9y agoThe search for the holy grail continues..
- weberc2 9y agoORMs aren't a bad idea; they just aren't a drop-in replacement for in-memory data structures for traditional imperative languages. They drop in rather well for functional languages though. Also, many ORMs are simply flat-footed, but we shouldn't fault ORMs generally any more than we should call C "slow" because some compiler is particularly bad.
- FLUX-YOU 9y agoSQL strings in source code are worse than ORMs, even if you move it out to a resources file of some kind. No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source. If you don't do that, you're dropping all of the productivity that syntax checkers do for you and risk losing productivity to typos or other dumb mistakes.
- Amezarak 9y agoMaybe I'm doing it wrong, but I like to keep simple queries in an ORM, and then execute anything complicated as a stored procedure.
- FLUX-YOU 9y agoNo, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure versioning and deployments of procedures. Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.
- jandrese 9y agoSometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language. Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.
- Amezarak 9y agoWell, it depends. At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made. Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server. But either way, I wouldn't dump a monster like that right in source code - that's crazy!
- maxxxxx 9y agoI think ORMs are very nice if you take the time to sometimes profile your DB and also look at the SQL it generates. You still can optimize slow queries by hand writing SQL. I definitely prefer an ORM codebase over one with direct SQL statements. Best in my view is to abstract all database access into a separate layer. A little more work but much easier to maintain and tune if needed.
- meritt 9y agoORMs aren't the problem, they're simply a tool. Developers who never bothered to learn SQL, indexing, relational theory or schema design are the problem. Incidentally, this aversion to learning is also how we ended up with MongoDB.
- tynpeddler 9y agoORM's let you write a lot less code since you don't have to do the object-relational mapping yourself. That's just one of several reasons I usually recommend them. The issue is, as you've pointed out, that people sometime use ORM's as an excuse to not understand SQL, which is a terrible idea. But the bottom line is that you have to optimize all your database calls, including ones implemented with ORM.
- dalore 9y agoWell this actually had nothing to do with using an ORM. If he put that same validation into an SQL call and ran it on update it would have been the same effect.
- lostboys67 9y agoIf your in that position run away and join the foreign legion to forget the "horror". All web developers ought to know the basics of SQL ie how to SELECT INSERT and UPDATE
- brasetvik 9y agoNote that Postgres supports functional indexes, and the case in the post (a `lower(column)=`-clause not being able to utilise the column's index) is the example used in the documentation: https://www.postgresql.org/docs/current/static/indexes-expressional.html https://www.postgresql.org/docs/current/static/indexes-expre...
- brightball 9y agoWas coming here to post that. Being able to create indexes based on the results of a function is the solution to so many problems. You can even index the result of an XPath function on a huge, compressed XML document.
- jaggederest 9y agoPartial functional indexes are amazing. You can create an index that not only elides execution of the function, but automatically only includes the subset where that function would be relevant. Makes the index smaller, to boot. create index repo_name_lower on repos(LOWER(name)) where name IS NOT NULL;
- combatentropy 9y agoPartial unique-constraint indexes are useful too
- DigitalJack 9y agoI really like all these debug and analysis reports coming into HN lately.
- djaychela 9y agoYeah - I read some of them, and while I don't necessarily 'get' all of it, there's a lot to be learned in all these things, and it's so good that people spend the time to cover in depth what the issues were and how to fix them.
- Entangled 9y agoThose who ignore CODD will learn it the hard way, forcefully.
- joshribakoff 9y agoThat just defines what relational is, it doesn't prescribe it as a best practice. There are use cases where its ill advised, like EAV.
- lo_fye 9y agoTL;DR - spend time configuring it, write efficient code, and understand what your 3rd party code is actually doing.
- sidlls 9y ago> Rarely do we consider how one query or a series of queries could interact to slow down the whole site. That doesn't seem right to me. It's almost always something to consider when designing the data model. Maybe I'm being uncharitable, but this seems to me to be equivalent to claiming we rarely consider the use of an algorithm or interactions between algorithms and data structures when writing some code. I mean, for toys that's fine, but I wouldn't defer this discussion for something I intended for public use.
- alkz 9y agoTL;DR guy has a scheduled job which puts load the db, removes a query which normally runs for 1.9ms instead of implementing rate limiting
- tkyjonathan 9y agoI do this everyday for the past 10 years.. This is my bread and butter. http://www.jonathanlevin.co.uk http://www.jonathanlevin.co.uk
- petergeoghegan 9y ago> It turns out it was coming from this line in my model. > This innocuous little line was responsible for 80% of > my total database load. This validates call is Rails > attempting to ensure that no two Repo records get > created with the same username and name. Instead of > enforcing the consistency in the database, it put a > before commit hook onto the object and it’s querying > the database before we create a new repo to make sure > there aren’t any duplicates. I still can't believe that Rails even attempts this. It's simply not possible to do this kind of enforcement in a race-free manner using SELECT statements with Postgres.
- combatentropy 9y agoThis story supports my growing theory that you should put as much of your app's rules in the database as you can. There were three problems with having the rule in Rails: 1. The need for an index was easily overlooked. 2. The rule would be bypassed if a different app used the same database. 3. The rule wasn't even foolproof. Only a database constraint would guarantee uniqueness when two users are saving at the same instant. The problem is, SQL is hard. We should not forget how much a programmer must learn. For example: Ruby, Rails, Linux command line, HTML, CSS, JavaScript, vi, how to exit vi, etc. Each of these takes years to master. SQL is especially SQuirreLy. However, it can't possibly be worse than learning the myriad JavaScript frameworks and complicated server-build tools that are completely optional for 99% of us. My advice: Don't do a SPA. Spend your time on SQL instead :D
- Retra 9y agoMaybe I'm crazy, but I thought the whole point of putting validation and constraint capabilities into RDBMSs was because it was more efficient to do so, and that this has been pretty well understood for like... eons. If you didn't want to write SQL and use constraints, then why would you even use a database (over just plain files)? That's what they are designed for. RDBMS developers are probably all sitting around giggling (nihilistically) over experiences with users who think their database is slow on account of their failure to actually use it in the way it was optimized to be used.
- bobwaycott 9y agoI believe the primary driver here is the overwhelming reliance upon ORMs when building anything with your average web framework—be it Python, Ruby, JavaScript, or whatever. Sure, these ORMs may, and usually do, offer ways to write and execute queries directly in SQL, but a vast majority of current developers seems to always reach for the ORM. Now, for simple CRUD apps, sure, that’ll help get you going quickly without having to think outside your chosen language. But this then snowballs into never thinking outside your chosen language. When ORMs are the average developer’s introduction and sole interface to using databases, we wind up with all the StackOverflow questions about how to write query X in ORM Y, when it could have been written as an SQL statement, sproc, or function, and executed directly (and probably more efficiently). It’s both amusing and sad when I see or hear of someone being unable to interact with their db directly. I can only suspect they actually believe the ORM is the way to interact with their db. Oh, they may know SQL exists, but that’s about it. This problematic state of affairs is only compounded by the fact that ORMs don’t properly and easily handle taking constraint, validation, and other data-focused application logic and actually writing it into the schema (some ORMs are better/worse at this than others, of course). They all seem to just default to letting as much of it be application-level code as possible. This then leaves new developers with the impression this is where such code belongs, so they never experience anything telling them it should be anywhere else. It’d be great to see more education offered within ORM docs, but the really important stuff seems to often be there only if you already know what you’re looking for. I’ve seen so many models defined over the years without indexes on the most obvious columns that are queried a bajillion times. Or maybe you can set a unique constraint on certain fields without a bunch of effort—sqlalchemy is decent at this in the Python world—but then you have to have unique validators for forms, rather than relying on the db to complain about a violation of the the constraint, and the ORM catching it in a friendly way devs can bubble up when needed. Oh, and then some insist on adding client-side validation of that same constraint. So, you take a constraint that matters at the db level, and is already easily handled at the db level, and people are writing client and server validation to enforce a condition the db is already supposed to be enforcing. There’s madness here. Maybe it’s just me, though.
- throw2016 9y agoRails is a pretty popular framework and widely used. How come something like this was not caught earlier, presumably its affects all Rails applications. Why would anyone code this kind of inefficiency instead of using inbuilt constraints. Has the code been reviewed, tested? Too many questions. It's surprising given how popular Rails was and still is that something which should have been caught in the early days of Rails is discovered now years later. Aren't all the production apps seeing this? Didn't Twitter see this? The real concern is a lot of highly promoted technologies in HN do not get the proper technical scrutiny that one should take for granted in a technical forum and increasingly hype is conflated to quality.
- cletus 9y agoI've seen a litany of these kinds of posts and I'm always amazed by two things when I see them posted on HN: 1. A cadre of diehards can't wait to post how amazing Postgress is or would be for whatever it is the OP is doing (as an aside, why isn't Postgres more popular if it's so amazing?); and 2. How averse people are to actual SQL. Years ago I dealt with this crap in the Java world back when Hibernate and the like were all the rage. I was always amazed at how much confirmation bias there seemed to be. People decided these ORMs were amazing and then completely ignored all the bugs introduced by this layer and effort spent trying to figure out what the ORM was doing and how to make it do the right thing. Back in the day I always liked a Java data mapper framework called iBatis (now dead, replaced by Mybatis it seems), which was pretty simple. Write some SQL in an XML file and call that SQL from your Java code. It was parameterized (so no SQL injection issues) and you could still do some funky things with discriminated types and the like. Plus, analytics were super easy because you knew how often each query was called and how long it took. Also, you could easily EXPLAIN PLAN those queries if you even had to (usually needed indexes were obvious). Compare this to the auto-generated SQL from the likes of Hibernate. ugh. I've come to the conclusion that people have this tendency to decide X is bad and then go completely out of their way to avoid X. You see it with SQL and ORMs. It largely explains (IMHO) thing slike Javascript and GWT. At least half the time "X is bad" really means "I don't understand X and I don't want to learn it". Joel Spolsky's "leaky abstractions" is good and time-honoured advice. Take the Hibernate example. Once you bought into that framework you had to do all your data access that way or you broke the caching. That's mostly bad. People also overestimate their needs. They rush to create Hadoop clusters and distributed NoSQL solutions because, you know, relational DBs can't keep up with their "Big Data" (which means, millions of rows) when in fact you can dump billions of rows into a single MySQL instance.
- rapind 9y agoI liked iBatis quite a bit too. Basically mapping hand bombed SQL to functions instead of objects (using XML ugh, but that was the norm at the time). Hibernate was just horrible and I had to watch my entire team adopt it (EJB 1 era).
- kitd 9y ago+1 for MyBatis. Good library that. If you're looking for something similar these days, JDBI [1] is great too. [1] - http://jdbi.org/ http://jdbi.org/
- ozim 9y agoFor all those ORM's are bad people: `I’ve been seeing that stray 30s+ spike in request time daily for months, maybe years. I never bothered to dig in because I thought it would be too much trouble to track down. It also only happened once a day, so the impact to users was pretty minimal.`