MySQL Bugs: #3827: MyISAM refuses to cache more than ~1,688,000 key blocks

Bug #3827	MyISAM refuses to cache more than ~1,688,000 key blocks
Submitted:	19 May 2004 2:49	Modified:	23 Sep 2005 6:45
Reporter:	Alex Seropian	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.18-standard-log	OS:	MacOS (Mac OS X Server 10.3.3)
Assigned to:		CPU Architecture:	Any

Description:
| Key_blocks_used          | 1687734   |

It won't seem to go over 1687734. I have constantly growing indexed
tables, so it's not a matter of all the keys being in memory. I've
restarted the server, and it still stops at that number. my.cnf has
key_buffer = 3072M, and indeed:

| key_buffer_size                 | 3221225472

top says mysql is only using about 1.8G. Key_reads *is* increasing, even
though Key_blocks_used is not.

I tried increasing key_buffer to 4095M, and Key_blocks_used now stops at 1688253. Again, Key_reads 
increases with no change in Key_blocks_used.

How to repeat:
Increase key_buffer_size to 3GB or more. Create MyISAM table with large (>~1.8GB) index, and cause all 
of that index to be read into memory (SUM() on an indexed colum seems to work nicely.) 
Key_blocks_used will stop incrementing well before key_buffer_size.

It looks like we're unable to use beyond approx. 1.75GB of memory although in apple's docs they state 
that there is up to 4GB of addressable space allocated to a process.  They do also state that the each 
process has it's own 32 bit virtual address space.

If this is something that needs support from Apple, I'd be more than happy to donate a code-level 
Technical Support Incident ( http://developer.apple.com/faq/techsupport.html ) to get it resolved.

I have done some inquiries and analysis and 1.8 Gb is a maximum value that is allocatable for a single chunk of memory on 10.3.

This is something done by trial and error and is not documented anywhere.

It was even possible to build a binary on G5 to allocate more, but as soon as 1.8 Gb limit is passed, server was killed by kernel.

This will be fixed when Apple releases 64-bit version of OS.

Mac OS X 10.4 "Tiger" is fully 64-bit; this bug should be re-opened.

Do you mean, the same behaviour (MyISAM refuses to cache more than ...) is observed on Mac OS X 10.4 "Tiger"  even when mysqld binary is compiled in 64-bit mode ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".