the two most important variables for tuning mysql server performance are
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 &
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
Command-Line Format --key_buffer_size=#
System Variable Name key_buffer_size
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type integer
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.
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
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;