Rick,
here is paste, you wanted, I am not sure, if you can find out something. The queries, which I got problem with are usually simple, for example like:
# Query_time: 6.244268 Lock_time: 0.000045 Rows_sent: 40 Rows_examined: 1573295
SET timestamp=1321857164;
SELECT SQL_CACHE SQL_CALC_FOUND_ROWS a.*
FROM subs_search_cache a
WHERE SubEnabled = 1 ORDER BY SubAddDate Desc LIMIT 0,40;
in this query can be other parameters, like SubLanguageID, but it is not so good for using key also, different LIMIT also can occur. I know, it would help a little to remove SubEnabled = 0 to different table, but I am not sure, how much, I guess this query just sorts rows by SubAddDate and then give me back all SubEnabled = 1 rows - which should be faster than 6 seconds I think...
http://pastebin.com/yrQFgkaU
On DB box I am running mysql, redis and mongo...anyway, ram:
SYSTEM MEMORY INFORMATION:
mem_wire: 5590437888 ( 5331MB) [ 22%] Wired: disabled for paging out
mem_active: + 13272596480 ( 12657MB) [ 53%] Active: recently referenced
mem_inactive:+ 4569399296 ( 4357MB) [ 18%] Inactive: recently not referenced
mem_cache: + 1022099456 ( 974MB) [ 4%] Cached: almost avail. for allocation
mem_free: + 465461248 ( 443MB) [ 1%] Free: fully available for allocation
mem_gap_vm: + 43712512 ( 41MB) [ 0%] Memory gap: UNKNOWN
-------------- ------------ ----------- ------
mem_all: = 24963706880 ( 23807MB) [100%] Total real memory managed
mem_gap_sys: + 788332544 ( 751MB) Memory gap: Kernel?!
-------------- ------------ -----------
mem_phys: = 25752039424 ( 24559MB) Total real memory available
mem_gap_hw: + 17764352 ( 16MB) Memory gap: Segment Mappings?!
-------------- ------------ -----------
mem_hw: = 25769803776 ( 24576MB) Total real memory installed
SYSTEM MEMORY SUMMARY:
mem_used: 19712843776 ( 18799MB) [ 76%] Logically used memory
mem_avail: + 6056960000 ( 5776MB) [ 23%] Logically available memory
-------------- ------------ ----------- ------
mem_total: = 25769803776 ( 24576MB) [100%] Logically total memory
I write already sql queries with locking, so it should work better now, I will see soon.