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]
