Sort buffers and tables on disk, MySQL/InnoDB

Question

I have the following type of query frequently running:

# Time: 120322 13:03:29
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 17.039286  Lock_time: 0.000141 Rows_sent: 24  Rows_examined: 1471904
SET timestamp=1332417809;
select cast(cast(someplaceen0_.datePlaced as date) as datetime) as col_0_0_,     count(distinct someplaceen0_.user) as col_1_0_, lower(someplaceen0_.deviceId) as     col_2_0_, someplaceen0_.country as col_3_0_ from SomePlaceEntry someplaceen0_ where someplaceen0_.datePlaced between '2011-12-17 00:00:00.0' and '2012-03-16 00:00:00.0' group by col_0_0_ , lower(someplaceen0_.deviceId) , someplaceen0_.country;

This particular database is pretty much doing that query after a bunch of inserts at given intervals, and Im now trying tweak my.cnf accordingly.

Engine: InnoDB

RAM: 12G, single purpose.

• No TEXT or BLOB, still temporary tables on disk. My understanding is that this should never have to happen, given that max_heap_table_size and tmp_table_size are high enough. Is that correct?

• Perhaps joins and sort buffers shall be increased, how can I see how filled they have ever been?

mysqltuner output:

<snip>
[!!] Sorts requiring temporary tables: 175% (49 temp sorts / 28 sorts)
[OK] Temporary tables created on disk: 0% (132 on disk / 20K total)

Variables to adjust:
    query_cache_limit (> 4M, or use smaller result sets)
    sort_buffer_size (> 4M)
    read_rnd_buffer_size (> 1M)
    table_cache (> 128)

Relevant my.cnf config:

sort_buffer_size = 4M
read_rnd_buffer_size = 1M
max_heap_table_size = 128M
tmp_table_size = 128M

What's your general recommendation in terms of config here?

Answer 1

If you are still seeing on-disk temporary tables with your max_heap_table_size and tmp_table_size set that high, then chances are you have a handful of queries with GROUP BY/ORDER BY (or a complex join) that are not using indexes effectively and are generating an enormous amount of intermediate data or you have TEXT/BLOB columns somewhere. Since you said you don't have the latter, I'm guessing it's the former.

You have a few options

1. Find the queries causing the problem. You can use pt-query-digest to help summarize the slow query log and make it easier to track down which queries are going to disk.
2. Ignore it. This represents less than 1% of your queries. Unless it's causing you an actual problem, don't worry about it.
3. If you can't ignore it, keep bumping up max_heap_table_size and tmp_table_size until the problem goes away. I wouldn't recommend this.

Keep in mind that MySQL pushes large temporary tables to disk for good reason. Eventually, you risk pushing other data out of memory (or worse, running the server out of memory) in order to process a handful of queries with very large data sets. Those settings are per table, so if you have any level of concurrency, you could get into trouble very quickly. I run in production with tmp_table_size/max_heap_table_size at 768MB, but I have 140GB of RAM to work with. Your headroom is much smaller with only 12GB.