So we have recently moved one of our DBs onto newer hardware. The original platform was 32 bit dual CPU Intel. The new environment is 64bit 2 x Dual Core AMD. The other variable is that we have gone from 5.0.27 to 5.0.37.
So here’s the issue. The DB server appears to be fine, except now there s a particularly bad query that’s making more of an impact on the system this query hasn’t changed during this migration. The query results in using filesort to local disk, causing these mini I/O explosions in /tmp and high sys utilization/kernel time. All our settings are roughly the same. Here are the relevent settings that I know of:
max_heap_table_size | 792723456
tmp_table_size | 792723456
query_alloc_block_size | 8192
query_cache_limit | 4194304
query_cache_min_res_unit | 4096
query_cache_size | 134217728
query_cache_type | ON
query_cache_wlock_invalidate | OFF
query_prealloc_size | 8192
key_buffer_size | 792723456
sort_buffer_size | 67108848
So my question is this, why wouldn’t this query be using memory for this filesort in the new environment?
By the way, both machines have ext3 based /tmps…no tmpfs at play here.
Sorry if I’m a bit vague or lack details you need to help me with this…I’m not a DB guy ) – just let me know what you need!
-Dan