Optimized----3 based on MySQL status. Key_buffer_size

View the various status values that the MySQL server is running:

Show global status;

3. Key_buffer_size

Key_buffer_size is to set the size of the MyISAM table index engine cache space, which has the greatest impact on MyISAM table performance.

To view the settings:

mysql>'key_buffer_size';  +-----------------+----------+| Variable_name   | Value in     Set (0.10 sec)

The result shows that 512MB of memory is allocated to Key_buffer_size

To view usage status:

Global ' key_read% ' ; +-------------------+-------------+| Variable_name     | Value       |+-------------------+-------------+| key_read_requests | 27813678764 | | Key_reads         | 6798830 in      Set (0.00 sec)

A total of 27,813,678,764 index read requests, 6,798,830 requests were not found in memory, directly read the index from the hard disk.

To calculate the probability of an index miss cache:

Key_cache_miss_rate = Key_reads/key_read_request * 100%

Key_cache_miss_rate under 0.1% is very good (every 1000 requests have a direct read hard disk), if under 0.01%, it means that key_buffer_size allocated too much, can be appropriately reduced.

Global ' key_blocks_u% ' ; +-------------------+-------+| Variable_name     | Value |+-------------------+-------+| key_blocks_unused | 0     | | key_blocks_used in    Set (0.00 sec)

Key_blocks_unused: Number of unused cache clusters (blocks).

Key_blocks_used: The maximum number of blocks ever used.

As a result, all caches are used, either by adding key_buffer_size or by over-indexing, which fills up the cache.

The more ideal settings are:

Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Optimized----3 based on MySQL status. Key_buffer_size