OLTP Optimization Cheat Sheets for dbdevs and DBAs

posted September 5, 2017 by "No Bugs" Hare, translated by Sergey Ignatchenko

	Author:	“No Bugs” Hare Follow:
	Job Title:	Sarcastic Architect
	Hobbies:	Thinking Aloud, Arguing with Managers, Annoying HRs, Calling a Spade a Spade, Keeping Tongue in Cheek

Execution Plans with no index, single-column index, and multi-column index

[rabbit_ddmog vol=”7″ chap=”Chapter 27(c) from “beta” Volume VII”]

After we discussed some trivial and not-so-trivial aspects of DBMS execution plans in two previous posts (1,2) – we can summarize some of generic (and actually very basic) things to be kept in mind when optimizing your DB. NB: DON’T expect anything novel here, it is just a list of things which every DBA should know by heart; still – it is easy to forget about one of these things very easily. In addition, it is VERY desirable for database developers to remember at least about half of these things – but from what I’ve seen, even in 2017 such dbdevs-who-know-this-stuff, are extremely rare <very-sad-face />.

Another NB: we’re still speaking about OLTP; that is – a database with LOTS of updates (a typical OLTP DB can be anywhere between 20-80 and 80-20 split between reads and writes, while analytical ones can easily have 100x more reads than writes).

General DB Cheat Sheet for dbdevs and DBAs

Before even starting to discuss optimizations, we MUST ensure that a few very basic things are complied with:

1NF A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.— Wikipedia —Every table has a primary key (PK); this is a prerequisite to having your table to comply with First Normal Form (1NF). Seriously, if you have trouble agreeing with this concept – you’ll have LOTS of trouble with relational databases in general.
You DO write your SQL by hand (not allowing a “magical” 3^rd-party tool such as ORM to generate it for you¹).
You DO have your SQL transactions aligned with business transactions (and consistency requirements). For example – for a bank we MUST NOT have separate transactions “take money from user account” and “add money to user account”; instead – we should have transactions such as “transfer money from user A to user B” (or “transfer money from user A to our own account” <wink />).
You DO use prepared statements (it is necessary both to avoid injections, and to improve performance).

¹ Such generated SQL will usually work at logical level; the problem is that as soon as we’re speaking about billion-row-tables – as a Big Fat Rule of Thumb™, such generated SQL won’t work fast enough

OLTP Optimization Cheat Sheet for dbdevs and DBAs

As it was already noted above – in spite of practice-which-is-common-for-certain-companies where database developers just write SQL, and then it comes to DBAs to make it work fast – I insist that

Database developers and DBAs have joint responsibility for the database performance.

Otherwise, it leads to developers-who-don’t-care-about-performance writing outright atrocious SQL statements – which cannot possibly be optimized. Or, looking at the same thing from a bit different angle – we can think about the “joint responsibility” statement above as of incarnation of DevOps (~=”collaboration between development and operations”).

With this in mind, here goes a short list of the points which IMNSHO both database developers and DBAs should keep in mind:

DO think in terms of execution plans
DO make 100% sure that you DO understand execution plans
“Have I already mentioned that you DO need to understand execution plans?Have I already mentioned that you DO need to understand execution plans?
- More seriously – for any query is executed frequently enough, we have to be sure that an “Ideal Execution Plan” for such a query doesn’t have any O(N) with N being in millions of rows; in fact, as a rule of thumb for OLTP database O(N) with N>=1000 spells as “trouble” (though if it happens rarely, it may fly), and O(N) for N>=100’000 qualifies as a “disaster”. For more discussion on “Ideal Execution Plans” and big-O asymptotic – see [[TODO]] section above.
- If we can optimize big-O asymptotic – as a Very Big Fat Rule of Thumb™, such an optimization will trump any other optimization, so optimizing big-O behaviors is the very first thing we should try
Indexes are the most important thing to enable “Ideal Execution Plans”. However:
- DO understand which indexes are necessary to execute your queries, yourself
  - Sure, it is not possible to be right 100% of the time – but with practice, you can reach 80-90% accuracy, and it already tends to help a Damn Lot™
  - DO keep in mind that two indexes by (A) and by (B) are not the same as index by (A,B) (it might be crazy, but I’ve heard a claim that “we have (A) and (B) so we don’t need (A,B)” from a real-world dbdev). OTOH, see also below about b-tree index (A,B) acting as an index on (A)
- DON’T rely on advice from magical “index advisors” (“wizards”, etc.). While they often list that-index-you-need among those recommended, I have yet to see a single “index wizard” which wouldn’t advise to create a dozen of indexes (most of them with non-key columns) where one will suffice.² With regards to “why one index is better” – see the next item.
- “DO keep in mind that maintaining indexes has its own costDO keep in mind that maintaining indexes has its own cost (both in terms of additional load, and in terms of size – the latter including very valuable cache size(!)).
  - As a rule of thumb – that one additional index which you MAY really need to optimize big-O asymptotic of your query, is not too bad. There are dozens of not-exactly-necessary indexes – in particular, those one falling under “the law of diminishing returns” which needs to be avoided.
  - DO keep in mind that in most cases, b-tree index by (A,B) can be used pretty much as the index by (A). As a result:
    - If you have both index by (A) and another index by (A,B) – consider dropping the one by (A)
    - if you already have an index by (A), converting it to another index by (A,B)³ is often a very viable option.
  - At this level (while we’re thinking in terms of big-O optimizations) – DON’T think about stuff such as clustered indexes or non-key columns in indexes (leave this stuff to later stages and to DBAs).

DO avoid functions-which-can-be-replaced-with-range-restrictions, in your queries. For example – DON’T use something like WHERE YEAR(some_field) == 2017 (unless SQL compiler is unusually smart, it is very likely to cause full index scan instead of range index scan – and the difference can be like 1000x easily); use something like WHERE some_field >= ‘2017-01-01 00:00:00’ AND some_field < ‘2018-01-01 00:00:00’⁴
- If you still happen to need a function in your index (not in an example such as above, where it can be avoided) – DO consider index on function. Keep in mind though, that even if supported by your RDBMS, such indexes usually cannot be reused for other queries, so use them even more sparingly than usual ones.
DO avoid indexes on columns which have different-selectivity-for-different-values-in-the-index. While quite a few modern RDBMS try to handle it (by using value-level stats for such columns) – it is cumbersome and tends to be rather fragile. Symptoms of such indexes are having some kind of ‘type’ column, with number of rows being vastly different for different ‘types’. Overall, very often such tables and indexes exhibit database design problems.⁵
DO avoid foreign keys. Yes, I know it is a kind of fallacy in theoretical world – but still all the seriously-loaded-DBs-I’ve-seen – avoided them for performance reasons.
- It means that enforcing constraints (these and many others) belong to app-level. TBH, I do NOT see it as a serious practical problem (in particular, because enforcing-FK-like-constraints tends to represent only a tiny portion of the consistency-rules-to-be-enforced-by-app-level-anyway).

² sure, one hand-picked index-re-used-by-multiple-queries will probably be a few percent slower than wizard-suggested index-optimized-for-each-query; however – if we take into account costs of updating each index on each write (and as mentioned above – OLTP has lots of writes), advantages of a single hand-picked index become obvious

³ actually – creating a new index, recompiling all the prepared statements, and dropping an old index.

⁴ While it is not the point here, I strongly prefer using half-open interval with strict ‘<’ on the upper side, to a much-less-strict BETWEEN ‘2017-01-01 00:00:00’ AND ‘2017-12-31 23:59:59.999’ or something.

⁵ one exception I know might be a global audit table – though even there, design-wise it is better to have several specific audit tables (with a global audit ID), see also the discussion on audit tables in Vol. VI chapter on Databases.

OLTP Optimization Cheat Sheet (DBAs only)

“While I am arguing for dbdevs to avoid outright-inefficient SQL statements – I am sure that cluttering their mind with relatively-minor details would push it too farWhile I am arguing for dbdevs to avoid outright-inefficient SQL statements – I am sure that cluttering their mind with relatively-minor details would push it too far (in particular – beyong magic 7+-2 cognitive limit). As a result, I am all for further optimizations to be performed by DBAs; in other words – I see that dbdevs should enable optimizations (by providing reasonable statements to start with) and DBAs should perform those optimizations.

For DBAs, at least the following list should be kept in mind:

DO run RUNSTATS/ANALYZE/UPDATE STATISTICS on regular basis (normally – daily)
If there is a situation when that “Ideal Execution Plan” discussed above is not picked up by your SQL compiler – DO use hints to force it (this also includes de-facto hints such as adding “OR 1=0” to WHERE clause).
- If nothing else helps – DO consider denormalization to improve performance. For a discussion of examples when denormalization can help – see, for example, [http://www.vertabelo.com/blog/technical-articles/denormalization-when-why-and-how] (unfortunately, denormalization examples are never simple, so discussing them won’t really fit into this book).
  - Keep in mind that with each denormalization, app-level gets yet another responsibility for maintaining DB consistency.
- In extreme cases, you may be tempted to manipulate stats to achieve desired result. I’ve seen it working, but it is sooooo fragile, that it should be used as a Really Last Resort™.
DO consider “clustered” indexes
- Note that “clustered” indexes can also be considered under our “C++/Java/Knuth approximations” (in case of a clustered index, we can say that our table is no longer a list, but std::map<>/TreeMap/tree, with all the other indexes referring to the table by index, that’s it).
- Beware: costs of updating a clustered index might be significantly higher than that of the non-clustered one
- On a plus side – pretty often, clustered index is a Good Thing™ for those tables with PK being something like time, incremental ID, etc. (i.e. for a pretty much any historical table) – and having very few indexes (this may vary). In such cases, costs of updating a clustered index may be even lower than for the non-clustered index, plus locality during range index scans will be usually significantly better.
DO consider non-key fields in indexes
- The idea here is to avoid the need for reading table pages while performing range index scans; the same thing can also be observed in our “C++/Java/Knuth approximations” (and while big-O asymptotic won’t change – we will be able to avoid referring from index to table data).
- They can help quite a bit (though not as much as improving your big-O asymptotic) – but beware of including too-long non-key fields (especially VARCHARS); this may cause index becoming too large and using too much of valuable cache space.
DO consider hash indexes.
- Keep in mind that hash indexes cannot be used for Range Index Scans.
- NB: departure from our “approximations”. Note that while in theory, complexity difference between hash index and b-tree index is a difference between O(1) and O(log(N)) – in practice, b-tree index won’t really have more than 6 levels of pages even for a billion-row table. This, in turn, means that we can say that b-tree index is merely 6x slower than hash index – so it is actually also O(1). On the other hand:
  - 6x is also considerable.
  - for a completely cached DB role of hash indexes tends to increase further (as we’re no longer completely dominated by page read costs)
- Overall – if you won’t need to range scan (never ever) – then hash indexes DO have an advantage.
DO consider converting some of your statements into stored procedures
- Personally, I don’t really like storage procedures because of them causing very severe Vendor-Lock-In (and from my experience, careful use of prepared statements allows to achieve comparable performance while being MUCH more DB-independent). Still, I do recognize stored procedures as a legitimate optimization technique (especially when it can be demonstrated that they do help performance-wise); also – with heterogeneous app-level replication (as will be discussed in [[TODO]] section below), Vendor-Lock-In won’t prevent you from using a different DB for your reporting/analytical replicas, so it won’t be that
- Keep in mind that even for stored procedures, we DO need to use prepared statements to call them.
“DO optimize your DB physical layoutDO optimize your DB physical layout (and underlying hardware). In particular:
- DO dedicate one completely-isolated RAID array (ideally – even the whole RAID card) to your DB logs
- DO use BBWC RAID (or NVMe) at least for that-RAID-which-handles-DB-logs
  - No, simple SSD-sitting-on-SATA won’t provide the-best-possible-performance (though BBWC RAID over such SSDs will).
- DO keep an eye on your DB caches.
  - DO ensure that you’re NOT double-caching (once at DB level and another time at OS level). In general – whenever possible, DO prefer DB-level caching.
  - DO try to cache your whole OLTP DB (we’ll discuss how to limit its size in [[TODO]] section below)
  - If caching your whole DB isn’t feasible – DO consider separating your caches for different tables (and then give more caching for tables such as USERS, and less caching for historical tables where it won’t do much good). In addition to improved overall caching of the all-important USERS table, this will also allow to prevent “cache poisoning” from scanning a historical table from affecting your USERS cache.
- Wherever possible – DO use DB-level containers (even if they’re lying on top of OS files); this tends to reduce OS-level fragmentation greatly.
  - If DB-level containers are not supported by your RDBMS – DO pay special attention to the underlying file system. DO use that-filesystem-which-is-recommended-by-your-RDBMS-vendor.
- DO consider reorganizing your tables and/or indexes (unfortunately, they do degrade with time <sad-face />). Note that the need and frequency of required reorgs heavily depends on specifics of your RDBMS.
  - While doing it – DO consider parameters such as PCTFREE or FILL FACTOR; while they’re unlikely the need to eliminate reorgs completely, they may help to make reorgs less frequent. Be ready to experiment (this is one thing which is very difficult to get right at the first attempt; on the other hand – penalties for doing it not-so-perfectly are rather mild too <phew />).
- When populating data into a non-operational table⁶ – DO consider dropping index before populating, and creating it later (believe it or not, but it will be significantly faster⁷).

⁶ This is often necessary even in an operational DB – for example, during migrations etc.

⁷ I’ve seen “up to 10x faster”

TL;DR for OLTP DB Optimization 101

This concludes our discussion on “OLTP DB Optimization 101”. In the next section, we’ll proceed to “OLTP DB Optimization 102” – including things such as app-level caches and app-level heterogeneous replication (which in turn allows to truncate our OLTP DB so it fits into 100G or so – and 100G can be fully-cached these days rather easily).

[[To Be Continued…

This concludes beta Chapter 27(c) from the upcoming book “Development and Deployment of Multiplayer Online Games (from social games to MMOFPS, with social games in between)”.

Stay tuned for beta Chapter 27(d), where we’ll continue our discussion into less-known ways for OLTP optimization, including app-level caches and heterogeneous asynchronous replicas]]

Acknowledgement

Cartoons by Sergey Gordeev from Gordeev Animation Graphics, Prague.

ITHare (16)

Comments

Vlad Mihalcea says

September 11, 2017 at 7:27 am

[[EDITED BY ITHARE: CLICKABLE LINKS REPLACED WITH NON-CLICKABLE TO AVOID LINK SPAMMING]]

You DO write your SQL by hand (not allowing a “magical” 3rd-party tool such as ORM to generate it for you).

As long as you log all JDBC statements [https://vladmihalcea.com/2016/05/03/the-best-way-to-log-jdbc-statements/], as well as asserting the numbe rof statements being generated [https://vladmihalcea.com/2016/05/03/the-best-way-to-log-jdbc-statements/] , you are fine with an ORM tool As well.

As I explained in my book, High-Performance Java Persistence [https://leanpub.com/high-performance-java-persistence/read#leanpub-auto-why-jpa-and-hibernate-matter], ORMs are very good for writing data in OLTP systems because:

– they can enable batch updates without any change the code base
– you get many application-level concurrency control mechanisms like optimistic locking which allow you to prevent lost updates when your logical transaction spans over multiple DB physical transactions
– the DB connection acquisition can be delayed towards the end of the transaction boundary, therefore reducing the Transaction response time and increasing the throughput.

As for reading data, the Hibernate Developer Advocate says that Native SQL is a Magic Wand [https://vladmihalcea.com/2017/01/18/the-jpa-entitymanager-createnativequery-is-a-magic-wand/].

Reply
- "No Bugs" Hare says
  
  September 11, 2017 at 1:13 pm
  
  > As I explained in my book, […], ORMs are very good for writing data in OLTP systems because…
  
  Well, as I have explained in _my_ book 😉 – based on my experiences with stock exchanges and games, I yet to see one single ORM which produces anywhere-reasonable SQL (and yet to see an ORM-based system performing 10B real-world transactions/year on one single 4S/4U box); in particular – all-the-ORMs-I-know (starting from EJB 1.0 Entity Beans) are suffering from the N+1 query problem 🙁 (actually, I wrote a technical note to IBM management back in 2000 or so exactly because of EJB Entity Beans suffering from N+1). Also – ORMs make pretty much zero sense for DB-App-sitting-between-server-side-logic-and-DB (and I have to insist that it is The Only Right Way(tm) to implement separation between app-level developers and DB developers).
  
  In other words – while I DO agree that business-logic developers should NOT write SQL themselves, I say that the conversion between business-logic requests (such as “move $X from account of user A to user B”) and SQL should be done not by ORM (which has no clue about those things we’re dealing with), but by a separate team of the DB developers+DBAs. For serious systems (hundreds of developers, many billions transactions/year), this approach has been observed to be pretty much unbeatable by each and every metric out there (including both development speed, and operational performance). BTW, as for development speed – isolation at business-logic requests such as above, tends to isolate developers from DB by orders of magnitude better than any ORM – while preserving all-important properties such as request atomicity.
  
  > As long as you log all JDBC statements, as well as asserting the numbe rof statements being generated, you are fine with an ORM tool As well.
  
  With anywhere-seriously loaded OLTP, we’re speaking about tens of millions transactions per day, and a natural question arises: who is going to read all those logs?
  
  > they can enable batch updates without any change the code base
  
  The same effect can be achieved by an SQL bindings compiler, discussed in http://ithare.com/oltp-compiling-sql-bindings/ . In other words – it is perfectly possible to combine SQL statements into one single transaction, even if they’re written by hand :-).
  
  > you get many application-level concurrency control mechanisms like optimistic locking which allow you to prevent lost updates when your logical transaction spans over multiple DB physical transactions
  
  I took a look at your book, and TBH, I don’t see how these mechanisms are relevant to OLTP. To start with, “executing a multi-request long conversation” (which is the context for discussion of “Application-level concurrency control” in your book) is a thing which should never ever occur in OLTP, period (I never even felt a need to have such a thing in OLTP – including serious real-world systems; each interaction is atomic and self-sufficient, that’s it – plain and simple). Similar inapplicability arguments apply to other ORM trickery – including Maps-for-JOINs (JOINs, as a Big Fat Rule of Thumb, are not common for OLTP, not to mention that remapping may make sense only to large resultsets, but large resultsets should never ever happen in OLTP to start with), “first-level caching” which can possibly work only for really-large transactions (which are out of question for OLTP), and “second-level caching” (which is just plain inconsistent – and which is completely unacceptable for most of OLTP loads).
  
  In contrast, there ARE ACID-compliant ways of improving OLTP performance (such as coherent app-level caching, based on single-DB-writing-connection), but they have absolutely nothing to do with ORM (or more generally, with SQL generation), see http://ithare.com/gradual-oltp-db-development-from-zero-to-10-billion-transactions-per-year-and-beyond/ . With proper tuning along these lines – real-world OLTP transactions (affecting 10+ rows each) were seen to take hundreds-of-microseconds on average(!); I daresay that reaching this kind of performance for ORM-based real-world systems is hopelessly out of reach.
  
  > the DB connection acquisition can be delayed towards the end of the transaction boundary, therefore reducing the Transaction response time and increasing the throughput.
  
  Once again, this seems to apply only to multi-request interactions, which should be outlawed for any serious OLTP, that’s it. This is not to mention that if you’re speaking about efficiency of “connection acquisition”, you’re clearly doing something wrong performance-wise :-).
  
  Overall – if RedHat can demonstrate a real-world system using Hibernate, which takes hundreds-of-microseconds-per-multi-row-writing-transaction – I will consider it. Until that point – sorry, I’ll stick to hand-written SQL… That’s not to mention that Java, however surprising it might sound for some of the developers, is certainly not the only way to write database apps.
  
  Reply
  - Vlad Mihalcea says
    
    September 11, 2017 at 1:45 pm
    
    [[EDITED BY ITHARE: CLICKABLE LINKS REPLACED WITH NON-CLICKABLE TO AVOID LINK SPAMMING]]
    
    Well, as I have explained in _my_ book 😉 – based on my experiences with stock exchanges and games, I yet to see one single ORM which produces anywhere-reasonable SQL (and yet to see an ORM-based system performing 10B real-world transactions/year on one single 4S/4U box). Also – ORMs make pretty much zero sense for DB-App-sitting-between-server-side-logic-and-DB (and I have to insist that it is The Only Right Way(tm) to implement separation between app-level developers and DB developers).
    
    But not everything is stock exchange or banks. Based on your observations,
    it might be that nobody would use MySQL, since, in a bank, you typically find Oracle, SQL Server or DB2.
    But then, there are many other types of applications using databases out there,
    like web apps or enterprise applications that work just fine with JPA or Hibernate.
    
    Hibernate has a market share of 70% for Java-based projects.
    So, not only that it makes sense to use it, but lots of projects actually do so.
    
    In other words – while I DO agree that business-logic developers should NOT write SQL themselves,
    I say that the conversion between business-logic requests (such as “move $X from account of user A to user B”) and SQL should be done not by ORM (which has no clue about those things we’re dealing with), but by a separate team of the DB developers+DBAs.
    
    The ORM is just a tool. It does not do anything by itself. It does only what you tell it to do.
    What you call team of the DB developers+DBAs is what I call DevOps,
    and as long as the Devs make sure that the ORM produces the right queries, there’s no reason why you should not use it.
    
    For serious systems (hundreds of developers, many billions transactions/year),
    this approach has been observed to be pretty much unbeatable by each and every metric out there (including both development speed, and operational performance).
    
    DevOps is one thing. ORM is another.
    
    it is perfectly possible to combine SQL statements into one single transaction, even if they’re written by hand :-).
    
    Well, that’s the whole point of having transaction boundaries.
    You want to enroll all statements that form the single atomic unit of work into one DB transaction.
    
    I took a look at your book, and TBH, I don’t see how these mechanisms are relevant to OLTP.
    
    To start with, “executing a multi-request long conversation” (which is the context for discussion of “Application-level concurrency control” in your book) is a thing which should never ever occur in OLTP, period (I never even felt a need to have such a thing in OLTP – including serious real-world systems; each interaction is atomic, that’s it – plain and simple).
    
    This topic is descriebed in this blog post[https://vladmihalcea.com/2014/09/22/preventing-lost-updates-in-long-conversations/] as well.
    It’s about the “lost update” anomaly in a typical web flow.
    While it might not be an issue for some applications,
    it can be a serious problem for others.
    
    Similar inapplicability arguments apply to other ORM trickery – including Maps-for-JOINs (JOINs, as a Big Fat Rule of Thumb,
    are not common for OLTP, not to mention that remapping may make sense only to large resultsets,
    but large resultsets should never ever happen in OLTP to start with),
    and “second-level caching” (which is just plain inconsistent – and which is completely unacceptable for most of OLTP loads).
    
    We never advise people do to that. That’s also explaained in this article [https://vladmihalcea.com/2017/03/29/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/].
    
    “second-level caching” (which is just plain inconsistent – and which is completely unacceptable for most of OLTP loads).
    
    Second-level caching has two strategies which offer a highly consistent cache solution.
    It’s suitable to reduce the workload on the Primary Node in a Primary-Follower replication topology.
    While increasing read load on Followers is easy, scaling the Primary node is not.
    
    In contrast, there ARE ACID-compliant ways of improving OLTP performance (such as coherent app-level caching, based on single-DB-writing-connection),
    but they have absolutely nothing to do with ORM (or more generally, with SQL generation),
    see http://ithare.com/gradual-oltp-db-development-from-zero-to-10-billion-transactions-per-year-and-beyond/ .
    
    With proper tuning along these lines – real-world OLTP transactions (affecting 10+ rows each) were seen to take hundreds-of-microseconds on average(!);
    I daresay that reaching this kind of performance for ORM-based real-world systems is hopelessly out of reach.
    
    What if the ORM executes the same queries you’d have written manually?
    It’s just a matter of using it properly.
    
    Once again, this seems to apply only to multi-request interactions,
    which should be outlawed for any serious OLTP, that’s it.
    
    Nope, it’s not. It about any DB transaction.
    Unless all the code is embedded in stored procedures,
    you need to open the DB connection somewhere in your data access layer.
    The more you can delay it, the better.
    If you open the DB connection, issue a query, and then do something else that takes 100 ms,
    it means that the current running transaction just took an extra 100ms.
    
    This is not to mention that if you’re speaking about the efficiency of “connection acquisition”,
    you’re clearly doing something wrong performance-wise :-).
    
    Which I was not. Connection acquisition is related to connection pooling, not ORM.
    
    Overall – if RedHat can demonstrate the real-world system using Hibernate, and taking hundreds-of-microseconds-per-writing-transaction (with commit) – I will consider it. Until that point – sorry, I’ll stick to hand-written SQL…
    
    This is a survey from 2015, issued by DZone.
    Again, the market share for Hibernate is around 70%.
    
    Now, one example that comes to my mind, if you’re using FitBit, you’re using Hibernate.
    
    Reply
    - "No Bugs" Hare says
      
      September 11, 2017 at 2:14 pm
      
      > nobody would use MySQL
      
      For OLTP – TBH, using MySQL is indeed a mistake :-).
      
      > But then, there are many other types of applications using databases out there,
      
      Sure – but I’m speaking about very specific type of databases – specifically about OLTP. WordPress which runs this site, is not OLTP; and news site, even as large as CNN, is neither; for such 99.99%-read-occasional-write systems there MIGHT be some space for ORMs (though even there I would oppose ORM, on the grounds of improper layering; still it will be indeed more difficult to oppose ORM there), but OLTPs (with 80-20 to 20-80 read-write split) are very different.
      
      > Hibernate has a market share of 70% for Java-based projects.
      > So, not only that it makes sense to use it, but lots of projects actually do so.
      
      “millions of flies can’t be wrong” line of argument doesn’t really fly on this site, sorry…
      
      > DevOps is one thing. ORM is another.
      
      Exactly. And what I was speaking about here – is not about DevOps, but about proper API layering, which ORM is lacking (to understand what-I-mean – please look for “DB Server API” in http://ithare.com/ultimate-db-heresy-single-db-connection-part-i-performance-part-ii-scalability-to-follow/ ). In other words – if you’re forcing your developers to understand BOTH OO representation AND SQL – it will contribute to cognitive overload (exceeding magic 7+-2 number, with pretty bad results).
      
      > It’s about the “lost update” anomaly in a typical web flow.
      
      This “problem” you’re referring to in your post (which is actually an ages-old “mid-air collision”), has a well-known-for-decades bullet-proof solution (and a perfectly stateless-at-the-DB-side one too) – and certainly doesn’t require any ORM magic. Trying to solve it by making the transaction stateful (especially with pessimistic locking) – is the fallacy of the scale I didn’t encounter for years…
      
      > What if the ORM executes the same queries you’d have written manually?
      
      Well, when Hibernate does produce _exactly_the_same_queries_ as a team-of-DB-gurus-produces-after-spending-many-man-years-on-the-queries (among other things, providing hints to the SQL compiler to wrestle it into using optimal execution plans) – feel free to come back, we’ll have another conversation 🙂 .
      
      Reply
      - Vlad Mihalcea says
        
        September 11, 2017 at 2:30 pm
        
        One thing puzzles me more than the debate about to ORM or not.
        
        Why do you think that an application that uses MySQL (Facebook, Twitter, Instagram, LinkedIn, etc) is not OLTP?
        
        According to the definition, OLTP is about high concurrency, short transactions, affecting a relatively small subset of data.
        
        How come that a system like Facebook or Twitter which make heavy use of MySQL is not OLTP enough?
      - "No Bugs" Hare says
        
        September 11, 2017 at 2:43 pm
        
        In the very beginning of my post, I gave my own definition of OLTP: “we’re still speaking about OLTP; that is – a database with LOTS of updates (a typical OLTP DB can be anywhere between 20-80 and 80-20 split between reads and writes, while analytical ones can easily have 100x more reads than writes).”
        
        And being consistent with my definition, I didn’t say that Facebook etc. is not OLTP (though they don’t require ACID, which simplifies their life greatly). As for them using MySQL – for their type of non-ACID-compliant database processing, MySQL is certainly the best solution price-wise. On the other hand, ACID-compliant OLTP databases (which are actually required for _any_ kind of processing with money involved, including games with artifacts-worth-thousands-of-real-world-dollars such as WoW) – MySQL is IMO not that good choice (more specifically – MyISAM is completely out of question, and InnoDB, while not that bad, is IMO still not mature enough at this point). BTW, both Facebook and Twitter are processing mere hundreds of billions transactions/year – pretty much nothing for a non-ACID-compliant trivially-shardable database 🙂 .
Vlad Mihalcea says

September 11, 2017 at 2:53 pm

InnoDB is ACID-compliant, and from 5.7, it switched to proper Consistency too (disallowing improper values). It’s been rather stable since 5.1/5.2.

Although not as common as Oracle in the Banking Industry, there are also some banks running MySQL.

What’s interesting is that Square uses PostgreSQL and MySQL and they handle money and have high concurrency requirements as well.

Reply
- Vlad Mihalcea says
  
  September 11, 2017 at 3:01 pm
  
  Also, it would be wrong to call Oracle ACID-compliant because it does not provide Serializability at all. Snapshot Isolation still allows write-skew phenomenon.
  
  So, from this perspective, PostgreSQL and the InnoDB engine are closer to ACID than Oracle.
  
  Reply
  - "No Bugs" Hare says
    
    September 12, 2017 at 4:23 am
    
    Serialization isolation level for OLTP? Gimme a break, even with MVCC and Oracle’s implementation in mind it won’t fly. Even RR is out of question for any serious work (though I _heard_ of some banks using RR); most of the time – it is good ol’ Read Committed. As a result – the whole point is completely moot for any practical purposes.
    
    In real-world, things such as “ability to add a column to a billion-row table in milliseconds” (which MySQL is lacking) are MUCH more important…
    
    BTW, as for MySQL’s “standard compliance” – I remember times when MySQL was claiming that they’re the most standard-compliant DB in existence (measured in mystical percentage of compliance, with charts and tables to make it look solid); it was at those times when they had only MyISAM = “absolutely no ACID in sight” (!!); since that point, I am _extremely_ suspicious of any compliance claims coming from MySQL.
    
    Reply
    - Vlad Mihalcea says
      
      September 12, 2017 at 5:25 am
      
      How about Google Spanner? Not only that it provides Serializability, but it also provides Linearizability as well, therefore Strict Serializability while being globally distributed.
      
      Times have changed. As for MyISAM, you really have to use it explicitly as, by default, MySQL uses InnoDB. For heavy write-based workloads, the Facebook MyRocks DB engine is also a good alternative too.
      
      Reply
      - "No Bugs" Hare says
        
        September 12, 2017 at 7:18 am
        
        Google Spanner does look interesting, however:
        – I am certainly not convinced by their latencies. Single-digit-milliseconds they’re boasting (actually, if looking at their own article on it, it is more like “up to 100ms for as much as 1% of the transactions”), are actually pretty bad in my world (as I told, it is not too difficult to get to hundreds-of-microseconds range, and for a fraction of price).
        – As for claimed scalability-out-of-the-box – as with any stuff which relies on two-phase commits (which in turn inevitably means _distributed_ locks(!)), it is bound to suck big time :-(. Hence, some app-level Shared-Nothing will still be necessary (and Shared-Nothing stuff tends to be more latency-sensitive even if not going into full-scale single-writing-DB-connection).
        – And their pricing, while it is not TOO bad ($2K/month for 3 nodes with 100G of storage), is not really good either compared to rented servers.
        – As for other often-neglected features (such as “how much time does ADD COLUMN on a billion-row table take?”), and for “how long it will take to iron bugs out?” – we’ll have to wait until it starts working in the real-world.
        
        My take on it: “looks relatively interesting, though (a) high latencies will hurt my favorite single-writing-connection OLTP which enables ultra-high performance and real Shared-Nothing scalability without dealing with transaction isolation, and (b) in any case, it will take a loooong while until such a system becomes production-ready”. If somebody could run tests comparing Google Spanner with, say, DB/2 or Oracle on a 4S/4U box under on a load such as TPC-E, it would be really interesting.
- "No Bugs" Hare says
  
  September 12, 2017 at 4:43 am
  
  As for banks running MySQL – such references don’t qualify as a real argument; did you know that eBay was running for a long while without understanding what a transaction is (!!)? Still, it is not the reason to run financial stuff without transactions :-).
  
  That being said – MySQL MIGHT work, but TBH, for any serious ACID-compliant OLTP work my personal order of preference (among _usable_for_this_purpose_ DBs) would go as DB/2 (shines ONLY for OLTP, not for reporting, leave alone analytics) – Oracle – MSSQL – Postgre (would rate them higher if they had hints) – MySQL (still usable, but will cause much more trouble than necessary). See also http://ithare.com/choosing-rdmbs-for-oltp-db/ .
  
  BTW, as for “high concurrency requirement” – such requirements don’t exist in real-world; the only real requirement is “we need to handle XX million transactions/day”, which can be satisfied without concurrency (and without relying on isolation levels too :-)). This is especially true for small operations such as Square (which handles just a bit more than 1M transactions per day, less than half a billion per year, peanuts).
  
  Reply
  - Vlad Mihalcea says
    
    September 12, 2017 at 5:32 am
    
    I don’t disagree with you here. Investing billions of dollars into Oracle, SQL Server of DB2, it really shows. It’s indeed amazing how much processing you can do with these three commercial DBs, as opposed to their open-source alternatives.
    
    The entire StackOverflow (Top 50 Internet sites) runs with just 2 SQL Server nodes and it handles 500 million queries per day.
    
    Reply
    - "No Bugs" Hare says
      
      September 12, 2017 at 6:49 am
      
      Yes, though I have to say that handling _queries_ (especially as trivially cacheable as those by StackOverflow) is quite simple – the real problem is how to handle _writes_.
      
      Reply
      - Vlad Mihalcea says
        
        September 12, 2017 at 8:27 am
        
        I talked to one of the StackOverflow de developers during Devoxx France and I assumed they were using the MVCC-based engine with TempDB, but he said that would be overkill for them.
        
        So, because of the relaxed consistency expectations of a Q&A site, they use the Read Uncommitted SQL Server isolation level. It’s always about tradeoffs.
        
        As for writes, the vast majority of traffic is read based, and they use both Redis to cache all sorts of data and ElasticSearch for full-text queries. However, more complex queries, go to SQL Server, which not only that can cope with it, but the average response time is less than 20 ms.
      - "No Bugs" Hare says
        
        September 12, 2017 at 10:43 am
        
        Yes, this is pretty much the way you build Web2.0 systems – most of which are quite simple by design :-). Building a complicated system (even if it is also web-based, such as a Zynga game) is a very different story though… The key problem of such complicated systems is that read-only caches don’t cut it anymore, and introducing some kind of non-recoverable-from-DB state (often expressed as a write-back cache) is not trivial 🙁 . For a discussion on Zynga-like systems – see http://ithare.com/chapter-via-server-side-mmo-architecture-naive-and-classical-deployment-architectures/ (NB: it is rather sketchy on the web site, and significantly more elaborated in upcoming Vol.III of “Development&Deployment of Multiplayer Online Games”).