Databases Reference
In-Depth Information
The MyISAM key block size
The key block size is important (especially for write-intensive workloads) because of
the way it causes MyISAM, the operating system cache, and the filesystem to interact.
If the key block size is too small, you might encounter
read-around writes
, which are
writes that the operating system cannot perform without first reading some data from
the disk. Here's how a read-around write happens, assuming the operating system's
page size is 4 KB (typically true on the x86 architecture) and the key block size is 1 KB:
1. MyISAM requests a 1 KB key block from disk.
2. The operating system reads 4 KB of data from the disk and caches it, then passes
the desired 1 KB of data to MyISAM.
3. The operating system discards the cached data in favor of some other data.
4. MyISAM modifies the 1 KB key block and asks the operating system to write it
back to disk.
5. The operating system reads the same 4 KB of data from the disk into the operating
system cache, modifies the 1 KB that MyISAM changed, and writes the entire 4 KB
back to disk.
The read-around write happened in step 5, when MyISAM asked the operating system
to write only part of a 4 KB page. If MyISAM's block size had matched the operating
system's, the disk read in step 5 could have been avoided.
6
Unfortunately, in MySQL 5.0 and earlier there's no way to configure the key block
size. However, in MySQL 5.1 and later you can avoid read-around writes by making
MyISAM's key block size the same as the operating system's. The
myisam_block_size
variable controls the key block size. You can also specify the size for each key with the
KEY_BLOCK_SIZE
option in a
CREATE TABLE
or
CREATE INDEX
statement, but because all
keys are stored in the same file, you really need all of them to have blocks as large as
or larger than the operating system's to avoid alignment issues that could still cause
read-around writes. (For example, if one key has 1 KB blocks and another has 4 KB
blocks, the 4 KB block boundaries might not match the operating system's page
boundaries.)
The Thread Cache
The thread cache holds threads that aren't currently associated with a connection but
are ready to serve new connections. When there's a thread in the cache and a new
connection is created, MySQL removes the thread from the cache and gives it to the
new connection. When the connection is closed, MySQL places the thread back into
6. Theoretically, if you could ensure that the original 4 KB of data was still in the operating system's cache,
the read wouldn't be needed. However, you have no control over which blocks the operating system
decides to keep in its cache. You can find out which blocks are in the cache with the
fincore
tool, available
