So I stand corrected :) Sorry for the mis-information. Thanks Mark!!
Keith ----- Original Message ----- From: "Mark Leith" <[EMAIL PROTECTED]> To: "Baron Schwartz" <[EMAIL PROTECTED]> Cc: "Michael Dykman" <[EMAIL PROTECTED]>, "Gu Lei(Tech)" <[EMAIL PROTECTED]>, "Jen mlists" <[EMAIL PROTECTED]>, mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:51:19 PM (GMT-0500) America/New_York Subject: Re: run out of memory Baron Schwartz wrote: > Mark Leith wrote: >> And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, >> rather than a full 4G. > > What are the practical memory limits for 64-bit binaries? I have > heard that MySQL's indexing code is only 32-bit safe anyway, and I > assume for example the MyISAM key buffers can still only be 4 GiB in a > 64-bit version. Is this true of all storage engines? Are there any > other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
