Re: large file operations exhaust memory

[Tomas Singliar]

Thanks for the fun reading. I'll catch up with it soon. Actually, after I loaded the raw data in bulk, the idea was to do few full scan queries to generate subsets that would be hit often, and index those. That's what I meant by 'materialized query'. The nature of the data is such that the finest time granularity matters, so summarizing in a simple way is hard.

And the small buffer is a remnant of testing the hypothesis that it was Bug #36705 "key_buffer_size of >= 2G allocates all available virtual memory on 64-bit windows" resurrected. I've set back to a more reasonable value.

We actually use partitioning that makes sense wrt the query workloads, so it isn't quite as bad as it looks.

But my problem appears to lie deeper than MySQL. I sniff something around write caching by Windows, RAID driver or something close to it. Any large file write operation, not just MySQL, seems to drive memory usage unreasonably high (e.g. from 4 to 12GB). It clears a short while after the operation completes. I suspect that files which reopen within a timeout never get cleared from the cache. Large read operations are unaffected.

So I still do my indexing partition-by-partition with myisamchk... pain in the rear.



Edited 2 time(s). Last edit at 07/04/2012 02:30AM by Tomas Singliar.