mysql key_buffer_size


//=== https://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

the two most important variables for tuning mysql server performance are
* key_buffer_size
* table_cache.

"""...
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &

..."""



//=== https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

To see the values that a server will use based on its "compiled-in defaults" and any option files that it reads
$ mysqld --verbose --help

To see the values that a server will use based on its compiled-in defaults, "ignoring the settings in any option files,"
$ mysqld --no-defaults --verbose --help




* key_buffer_size

Command-Line Format --key_buffer_size=#
System Variable Name key_buffer_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type integer
Default 8388608
Min Value 8
Max Value 4294967295

Index blocks for MyISAM tables are buffered and are shared by all threads.
key_buffer_size is the size of the buffer used for index blocks.
The key buffer is also known as the key cache.

"""...
You can increase the value to get better index handling for all reads and multiple writes;
on a system ... using the MyISAM storage engine,
25% of the machine's total memory is an acceptable value for this variable.
..."""



--> http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

"""...
InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not
..."""

* Another major difference between MyISAM and InnoDB storage engine is how caching is done:

MyISAM --> key cache(key_buffer_size).
It only caches index pages from .MYI files.

InnoDB --> InnoDB Buffer Pool(Innodb_buffer_pool_size).
It caches data and index pages from the accessed InnoDB tables.



//=== show variables for the running mysql server
$ mysqladmin variables -uroot -p
$ mysqladmin extended-status -uroot -p

or login mysql server first
$ mysql -uroot -p

then
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;