MyISAM with key_buffer larger than 4 GB

In many applications, MyISAM can be used successfully if the proportion of write operations is only a small fraction of the read operations. As the tables grow, the 4 GB limitation in the key_buffer size caused performance issues and other strategies. Wait… did I miss something… From a recent comment to a post from Matt I re-read the MySQL documentation and realized I missed a big change that occurred with versions 5.0.52 and 5.1.23 (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size)

      As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms

So, key_buffer as large or even larger than 32 GB are possible. That is an important design consideration that needs to be taken into account when tuning a database.

About Yves Trudeau

I work as a senior consultant in the MySQL professional services team at Sun. My main areas of expertise are DRBD/Heartbeat and NDB Cluster. I am also involved in the WaffleGrid project.
This entry was posted in mysql, performance, yves. Bookmark the permalink.

5 Responses to MyISAM with key_buffer larger than 4 GB

  1. Yes, the >4GB key_buffer_size has been around for a while.

  2. This is helpful when you have lots of tables, with total indexes > 4GB. When you have one big table, the advantage may not be so big.

  3. Yves Trudeau says:

    Justin, I have many clients that have MyISAM tables with more than 25GB of index space. I think it can be useful even for large tables.

  4. Mark R says:

    Unfortunately the key buffer itself has a big fat mutex around it (one per key buffer, that is, just one unless you use multiple key buffers), which is taken any time anyone modifies the key buffer (which includes bringing pages in AND out of it, which is done by selects as well as updates).

    This means that operations which do a large amount of key buffer work effectively block up the entire server – even if they’re operating on an entirely different table.

    The example we see, is doing a lot of inserts with DELAY_KEY_WRITE on, then doing a flush table on that table. It effectively “stops the world” during the flush – no index on any table at all may be touched whatsoever.

  5. Yves Trudeau says:

    Mark, of course there is one mutex per key_buffer. The application I am thinking of is using a custom key_buffer on a mainly read-only table that has more that 25 GB of indices. The table is inserted only about once per day in a batch of many millions.