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.
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?
<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?