Hibernate, MySQL Temporary Tables, and How we Cut our Write Operations in Half

Here at Turo, our database houses most things we need, from reservation information to driver feedback and more. However, about a year ago, our Amazon RDS database was dangerously close to maxing out our allowed write operations per second. We had exhausted other possibilities, and while looking at SHOW FULL PROCESSLIST we came upon some slow queries that included Using temporary, leading us to think that perhaps that may be making an impact. However, we had no way of knowing whether that was true.

MySQL Developer Reference: Temporary Tables

MySQL has some very defined reasons why it would write temporary tables to disk instead of memory (see MySQL Developer Reference). What caught my attention at first was that:

Users have no direct control over when this occurs.

This means there isn’t a configuration we can use to stop this from happening outright. Reading further, this document also says:

EXPLAIN will not necessarily say Using temporary for derived or materialized temporary tables.

For those of us who don’t know what EXPLAIN is — EXPLAIN is a MySQL keyword that developers can use to get insight into how a query will be handled by the optimizer (the thing that runs the query). This was unfortunate, since we would have to figure out a less straightforward way to keep track of when we were creating these tables. Further on it says:

When the server creates an internal temporary table (either in memory or on disk), it increments the Created_tmp_tables status variable. If the server creates the table on disk (either initially or converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

Which is helpful — we know where there is a point of truth that MySQL keeps track of. Moving on it says:

Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

- Presence of a BLOB or TEXT column in the table. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.

…

- The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

The first statement is the most interesting: presence of a BLOB or TEXT column in the table. This means that any query that would have saved a temporary table, AND includes a BLOB or TEXT type column, will write to disk, instead of in-memory. Further, if we use SHOW COLUMNS or DESCRIBE, it means the same goes: on-disk writes. That one is also important because it means when we run the queries to actually grab the Created_tmp_tables information, we are also creating an on disk temporary table.

From reading the documentation, I’ve concluded a couple of important things:

1. EXPLAIN is helpful in most cases, but could be deceptive in this one
2. There isn’t a configuration we can use to prevent this
3. The default for temporary tables is in memory which means that, if we can stop it from falling into the on disk cases, that could solve the problem
4. There is a definitive way to investigate this with MySQL’s INFORMATION_SCHEMA

Finding our BLOB and TEXT offenders

We saw above that many of the reasons for writing to disk involve the data types BLOB andTEXT—and, after investigating, it looked like this was a non-trivial issue.

The bad news was: we had over 70 tables that contained the TEXT or BLOB types. The good news was: not all of these tables were frequently used. There were only a handful of top offenders, all of which were called more than half a million times every half hour.

Having BLOB or TEXT columns in and of itself isn't so bad — some sorts of data require them. However, when you add the fact that it is likely some of these are used in some nasty queries — not so great.

I got 99 problems and the giga-query is one

Due to the constraints defined to make a temporary table, much less an on disk temporary table, I had a hunch that one of our most often run queries was likely to blame.

One of our core entities, that we query quite frequently, has a bunch of connections to other entities that are EAGERLY fetched, because of Hibernate’s implementation of @OneToOne LAZY relationships. (That is a whole other can of worms, and is chronicled elsewhere, I am sure). Hibernate also manages Criteria which is how we generally query the database. If we need to EAGERLY fetch relations to this core entity, it means that when we search for it, we also need to fetch the entities attached. This goes on and on. As a concrete example, if we have a relationship of A → B → C, then whenever we need A, we also need to grab C. Hibernate generates a query to grab all of these connections when you run getDetachedCriteria, and because of all of the connections in our model, we ended up with... drumroll… the giga-query.

select this_.id as id1_152_18_, this_.a as a_30_152_18_, this_.created as created2_152_18_, this_.b as b31_152_18_, this_.c as c_32_152_18_, this_.d as d3_152_18_, this_.e as e4_152_18_, this_.f as f33_152_18_, this_.g as g5_152_18_, this_.h as h6_152_18_, this_.i as i7_152_18_, this_.j as j8_152_18_, this_.k as k9_152_18_, this_.l as l10_152_18_, this_.m as m11_152_18_, this_.n as n12_152_18_, this_.o as 13_152_18_, this_.p as 14_152_18_, this_.q as q34_152_18_, this_.r as r35_152_18_, this_.s as s15_152_18_, this_.t as t16_152_18_, this_.u as u17_152_18_, this_.v as v18_152_18_, this_.w as w19_152_18_, this_.x as x20_152_18_, this_.y as y21_152_18_, this_.z as z22_152_18_, this_.aa as aa23_152_18_, this_.bb as bb24_152_18_, this_.cc as cc25_152_18_, this_.dd as dd26_152_18_, this_.ee as ee27_152_18_, this_.ff as ff28_152_18_, this_.gg as gg29_152_18_, this_.hh as hh36_152_18_, A5_.id as id1_155_0_, A5_.created as created2_155_0_, A5_.a as a7_155_0_, A5_.b as b3_155_0_, A5_.c as c4_155_0_, A5_.d as d8_155_0_, A5_.e as e9_155_0_, A5_.f as f10_155_0_, A5_.g as g11_155_0_, A5_.h as h12_155_0_, A5_.i as i5_155_0_, A5_.j as j6_155_0_, C6_.id as id1_83_1_, C6_.a as a11_83_1_, C6_.b as bc2_83_1_, C6_.c as cc3_83_1_, C6_.d as d_4_83_1_, C6_.e as e_5_83_1_, C6_.f as f_6_83_1_, C6_.g as g7_83_1_, C6_.h as hd8_83_1_, C6_.i as i12_83_1_, C6_.j as j9_83_1_, C6_.k as k10_83_1_, D7_.id as id1_89_2_, D7_.a as a2_89_2_, D7_.b as b3_89_2_, D7_.c as c4_89_2_, D7_.d as d5_89_2_, D7_.e as e15_89_2_, D7_.f as f6_89_2_, D7_.g as g7_89_2_, D7_.h as h8_89_2_, D7_.i as i9_89_2_, D7_.j as j10_89_2_, D7_.k as k11_89_2_, D7_.l as l12_89_2_, D7_.m as m13_89_2_, D7_.n as n14_89_2_, E8_.id as id1_74_3_, E8_.a as a2_74_3_, E8_.b as b3_74_3_, E8_.c as c4_74_3_, E8_.d as d5_74_3_, E8_.e as e6_74_3_, E8_.f as f7_74_3_, D9_.id as id1_89_4_, D9_.a as a2_89_4_, D9_.b as b3_89_4_, D9_.c as c4_89_4_, D9_.d as d5_89_4_, D9_.e as e15_89_4_, D9_.f as f6_89_4_, D9_.g as g7_89_4_, D9_.h as h8_89_4_, D9_.i as i9_89_4_, D9_.j as j10_89_4_, D9_.k as k11_89_4_, D9_.l as l12_89_4_, D9_.m as m13_89_4_, D9_.n as n14_89_4_, B3_.id as id1_155_5_, B3_.created as created2_155_5_, B3_.a as a7_155_5_, B3_.b as b3_155_5_, B3_.c as c4_155_5_, B3_.d as d8_155_5_, B3_.e as e9_155_5_, B3_.f as f10_155_5_, B3_.g as g11_155_5_, B3_.h as h12_155_5_, B3_.i as i5_155_5_, B3_.j as j6_155_5_, F11_.id as id1_44_6_, F11_.created as created2_44_6_, F11_.a as a3_44_6_, F11_.b as b4_44_6_, F11_.c as c5_44_6_, F11_.d as d22_44_6_, F11_.e as e6_44_6_, F11_.f as f7_44_6_, F11_.g as g8_44_6_, F11_.h as h9_44_6_, F11_.i as i10_44_6_, F11_.j as j11_44_6_, F11_.k as k12_44_6_, F11_.l as l13_44_6_, F11_.m as m14_44_6_, F11_.n as n15_44_6_, F11_.o as o16_44_6_, F11_.p as p17_44_6_, F11_.q as q18_44_6_, F11_.r as r19_44_6_, F11_.s as s20_44_6_, F11_.t as t21_44_6_, concat(F11_.u, ' ', F11_.v) as formula0_6_, G12_.id as id1_219_7_, G12_.created as created2_219_7_, G12_.a as a3_219_7_, G12_.b as b4_219_7_, G12_.c as c5_219_7_, G12_.d as d6_219_7_, G12_.e as e7_219_7_, G12_.f as f9_219_7_, G12_.g as g8_219_7_, H13_.id as id1_221_8_, H13_.created as created2_221_8_, H13_.a as a3_221_8_, H13_.b as b4_221_8_, H13_.c as c5_221_8_, H13_.d as d7_221_8_, H13_.e as e6_221_8_, I1_.id as id1_187_9_, I1_.created as created2_187_9_, I1_.modified as modified3_187_9_, I1_.a as a4_187_9_, I1_.b as b5_187_9_, I1_.c as c6_187_9_, I1_.d as d7_187_9_, I1_.e as e8_187_9_, I1_.f as f16_187_9_, I1_.g as g9_187_9_, I1_.h as h10_187_9_, I1_.i as i11_187_9_, I1_.j as j12_187_9_, I1_.k as k17_187_9_, I1_.l as l13_187_9_, I1_.m as m14_187_9_, I1_.n as n18_187_9_, I1_.o as o15_187_9_, J15_.id as id1_217_10_, J15_.created as created2_217_10_, J15_.a as a3_217_10_, J15_.b as b4_217_10_, J15_.c as c5_217_10_, J15_.d as d7_217_10_, J15_.e as e6_217_10_, F2_.id as id1_44_11_, F2_.created as created2_44_11_, F2_.modified as modified3_44_11_, F2_.a as a4_44_11_, F2_.b as b5_44_11_, F2_.c as c22_44_11_, F2_.d as d6_44_11_, F2_.e as e7_44_11_, F2_.f as f8_44_11_, F2_.g as g9_44_11_, F2_.h as h10_44_11_, F2_.i as i11_44_11_, F2_.j as j12_44_11_, F2_.k as k13_44_11_, F2_.l as l14_44_11_, F2_.m as m15_44_11_, F2_.n as n16_44_11_, F2_.o as o17_44_11_, F2_.p as p18_44_11_, F2_.q as q19_44_11_, F2_.r as r20_44_11_, F2_.s as s21_44_11_, concat(F2_.t, ' ', F2_.u) as formula0_11_, K17_.id as id1_63_12_, K17_.a as a2_63_12_, K17_.b as b3_63_12_, K17_.c as c4_63_12_, K17_.d as d5_63_12_, K17_.e as e6_63_12_, K17_.f as f7_63_12_, K17_.g as g8_63_12_, K17_.h as h9_63_12_, K17_.i as i10_63_12_, K17_.j as j11_63_12_, K17_.k as k12_63_12_, K17_.l as l13_63_12_, K17_.m as m14_63_12_, K17_.n as n15_63_12_, K17_.o as o16_63_12_, K17_.p as p17_63_12_, K17_.q as q18_63_12_, K17_.r as r19_63_12_, K17_.s as s20_63_12_, K17_.t as t21_63_12_, K17_.a as a22_63_12_, K17_.b as b23_63_12_, K17_.c as c24_63_12_, K17_.d as d25_63_12_, L18_.id as id1_222_13_, L18_.created as created2_222_13_, L18_.a as a5_222_13_, L18_.b as b3_222_13_, L18_.c as c4_222_13_, L18_.d as d6_222_13_, M19_.id as id1_92_14_, M19_.created as created2_92_14_, M19_.a as a8_92_14_, M19_.b as b3_92_14_, M19_.c as c4_92_14_, M19_.d as d5_92_14_, M19_.e as e6_92_14_, M19_.f as f7_92_14_, F20_.id as id1_44_15_, F20_.created as created2_44_15_, F20_.modified as modified3_44_15_, F20_.a as a4_44_15_, F20_.b as b5_44_15_, F20_.c as c22_44_15_, F20_.d as d6_44_15_, F20_.e as e7_44_15_, F20_.f as f8_44_15_, F20_.g as g9_44_15_, F20_.h as h10_44_15_, F20_.i as i11_44_15_, F20_.j as j12_44_15_, F20_.k as k13_44_15_, F20_.l as l14_44_15_, F20_.m as m15_44_15_, F20_.n as n16_44_15_, F20_.o as o17_44_15_, F20_.p as p18_44_15_, F20_.q as q19_44_15_, F20_.r as r20_44_15_, F20_.s as s21_44_15_, concat(F20_.t, ' ', F20_.u) as formula0_15_, I21_.id as id1_187_16_, I21_.created as created2_187_16_, I21_.modified as modified3_187_16_, I21_.a as a4_187_16_, I21_.b as b5_187_16_, I21_.c as c6_187_16_, I21_.d as d7_187_16_, I21_.e as e8_187_16_, I21_.f as f16_187_16_, I21_.g as g9_187_16_, I21_.h as h10_187_16_, I21_.i as i11_187_16_, I21_.j as j12_187_16_, I21_.k as k17_187_16_, I21_.l as l13_187_16_, I21_.m as m14_187_16_, I21_.n as n18_187_16_, I21_.o as o15_187_16_, F22_.id as id1_44_17_, F22_.created as created2_44_17_, F22_.modified as modified3_44_17_, F22_.a as a4_44_17_, F22_.b as b5_44_17_, F22_.c as c22_44_17_, F22_.d as d6_44_17_, F22_.e as e7_44_17_, F22_.f as f8_44_17_, F22_.g as g9_44_17_, F22_.h as h10_44_17_, F22_.i as i11_44_17_, F22_.j as j12_44_17_, F22_.k as k13_44_17_, F22_.l as l14_44_17_, F22_.m as m15_44_17_, F22_.n as n16_44_17_, F22_.o as o17_44_17_, F22_.p as p18_44_17_, F22_.q as q19_44_17_, F22_.r as r20_44_17_, F22_.s as s21_44_17_, concat(F22_.t, ' ', F22_.u) as formula0_17_ from tableA this_ left outer join tableB A5_ on this_.a=A5_.id left outer join tableC C6_ on A5_.d=C6_.id left outer join tableD D7_ on A5_.f=D7_.id left outer join tableE E8_ on D7_.e=E8_.id left outer join tableD D9_ on A5_.h=D9_.id inner join tableB B3_ on this_.b=B3_.id left outer join tableF F11_ on this_.c=F11_.id left outer join tableG G12_ on this_.f=G12_.id left outer join tableH H13_ on this_.q=H13_.id inner join tableI I1_ on this_.gg=I1_.id left outer join tableJ J15_ on I1_.f=J15_.id inner join tableF F2_ on I1_.k=F2_.id left outer join tableK K17_ on I1_.n=K17_.id left outer join tableL L18_ on this_.hh=L18_.id left outer join tableM M19_ on L18_.a=M19_.id left outer join tableF F20_ on M19_.a=F20_.id left outer join tableI I21_ on L18_.d=I21_.id left outer join tableF F22_ on I21_.k=F22_.id;

For those of us following along and vigorously scrolling past the enormous blob (ha) of text (haha), the giga query ended up doing 18 joins across 11 different tables for a SELECT statement, and since it was a Hibernate-generated query, there were only a handful of things we could do to fix that. It joined across tables that hadBLOB and TEXT types, and was large enough that there is a non-trivial chance it fell into one of those cases where we create temporary tables when we add conditionals to the base query, thus making it a prime suspect in our investigation.

Questions and Answers

I started with two concrete questions to answer:

1. Why are we using temporary tables?
2. Why are we writing to disk?

Both of those have to be satisfied for us to create a temporary table on disk, because, remember, the default is to create an in-memory temporary table. That means MySQL has to realize Ah, I want a temporary table in memory and then go Oh wait—can’t do that in memory, guess I’ll write to disk then.

There were two promising candidates for this to start off with, and I had a hunch that the answers were:

1. “the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue” was why were were creating temporary tables
2. “Presence of a BLOB or TEXT column in the table. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.” was why we were being forced to write to disk.

In order to test that, I moved into testing and debugging the giga query.

Investigation

I pared down the giga-query into a smaller, more manageable, version, so that I could isolate different aspects, and then tested against my two hypotheses.

Testing TEXT or BLOB presence and order by clauses

The interesting part here is that I ended up a scenario I couldn’t quite figure out: why the upper left hand corner case was returning an on disk temporary table.

Let’s play a game of spot the culprit:

<insert giga-query here> where entity_.foreign_id=84 and other_entity_.id=4 order by this_.id asc;

It only has one order by, and some fairly innocuous parameters to filter by. We know that TEXT or BLOB would cause it to write to disk if it was trying to create a temporary table, but there was no obvious reason why it would need to do so.

It doesn’t fall under the category of the ORDER BY culprit that I thought would be the cause, and nothing else really jumped out at me. It turns out, the entity_.foreign_id section is the one that was causing it. It get's weirder though. If you change that to be (for example) other_.b_id, it does not generate a temp table.

The Mysteries of MySQL Optimizer: EXPLAIN

I make no claim to understand the MySQL Optimizer in any useful detail. However, when looking at the different EXPLAIN plans around this query it seems that the optimizer chooses to join tables differently, based on the indices available. This means that we fall into the case of doing an ORDER BY on a table other than the first one in the join queue as detailed in the docs here:

Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

If nothing else, this means that, depending on how the optimizer thinks a query should be handled, it may or may not fall under some of the cases of creating an on disk temporary table.

The end of the story…for now

After conversations with the team, we decided that we would do two things: remove the LONGTEXT types from TableA, by getting rid of those columns, and disconnecting an entity that was being selected within the giga query. Upon further investigation, we realized that we didn’t need to remove the columns from TableA entirely, but simply stop selecting those values from within the query.

With the implementation of those changes, we saw a drastic reduction in on-disk temporary tables: from an average of about 1.7k per second to fewer than 30. This means we almost entirely removed our on disk temporary table usage by making a couple of simple changes.

In the end we achieved what we wanted: a 50% reduction in our write operations! (Pro Tip™ for future debuggers of write operations: use RDS performance insights, not CloudWatch metrics)

A quick note on Temporary Tables

There aren’t many times I say this and willingly move on, but: I still don’t understand everything. The elusive optimizer and derived tables being chief among them. What I do know is that temporary tables are not all evil, and that it may not be in our best interest to get rid of all instances of creating them. Many times, temporary tables speed up queries, and indices almost universally do. The reasons why temporary tables are created are largely difficult to pin down, and there is no magic bullet to prevent them from being created. The problem areas that we should focus on are the cases that force those (normally fine) temporary tables to write to disk since disk is both much slower than memory, and consumes a finite resource: write operations per second.

Special thanks to Chris Eager and Catherine Patchell for editing help.