I believe you seeing the symptoms of an issue with Windows 2003 requiring contiguous memory and this causes the running processes including Analysis Service to trim their Working Set memory. With large amounts of memory allocated this trim process can take a significant amount of time to complete, while this is running new allocations will be blocked, causing the process to stall.
This issue is fixed in Windows 2008 onwards this support article is for SQL Server but has all the links to the relevant pages http://support.microsoft.com/kb/918483 in particular this quote
In Windows Server 2008, the allocation of physically contiguous memory
is greatly enhanced. Requests to allocate contiguous memory are much
more likely to succeed because the memory manager now dynamically
replaces pages, typically without trimming the working set or
performing I/O operations. In addition, many more types of pages—such
as kernel stacks and file system metadata pages, among others—are now
candidates for replacement. Consequently, more contiguous memory is
generally available at any given time. In addition, the cost to obtain
such allocations is greatly reduced.
I believe that with this issue you will either have to upgrade to Windows 2008 onwards or test granting Lock Pages In Memory to the Analysis Services account. I have also had issues with memory allocation on Windows 2003 which requires a reboot to clear.
This SQLCAT blog compares the changes between Windows Server 2003 & 2008.
This blog mentions some of the issues of LPIM with Analysis Services (near the end)
Also this knowledge base article on working set trimming causing performance problems and this article shows how to Identifying Drivers That Allocate Contiguous Memory
The real problem is all the I/O that is needed in creating indexes on huge tables.
When the index is being created, does SHOW PROCESSLIST say "Repair by key_buffer" or "by sorting"? Sorting is better for large tables.
Are you running only MyISAM? If so, make these changes:
innodb_buffer_pool_size = 0
key_buffer_size = 3G
However, if you switched to InnoDB and a new enough version of MySQL, you could CREATE/DROP indexes "online" -- that is, without recreating the table (as happens in MyISAM). This would significantly decrease the I/O, hence cut back on the interference with the other queries.
A 5 second query using SSDs and RAID-10? Yikes! What is the query doing? Please show us the query, together with SHOW CREATE TABLE. It can possibly be sped up to be sub-second.
You say it is not I/O -- would you share the details of the evidence.
Yes, into "Multiple Key Caches".
query_cache_size=256M
is too high. Don't go above about 50M, so as to avoid lots of overhead in "pruning".
Summary:
- Change some settings;
- Switch to InnoDB;
- Speed up the 5-25s query.
Best Answer
Query cache can be combined with memory storage but it'll only make sense if don't have many updates (as your cached results will be no longer actual after your change your data).
Basically query cache stores results of your SELECT queries (if run again, data is retrieved faster), key buffer size stores indices (helps to use them more efficiently, i.e. to retrieve data quicker) and you said you know what memory storage is.
All three options you mentioned can be used together, but the real efficiency depends on your usage pattern. But hey won't do any harm when combined (by that I mean assigning bigger values to cache and buffer while using memory storage) as long as you have enough memory.
P.S. As you probably reckon yourself, your setup screams you're using a wrong tool. I can imagine situation where it is still necessary, e.g. using legacy / unchangeable code that depends on MySQL but otherwise consider redesigning and using other data storages besides MySQL, permanent or temporary like memcache.