MySQL
my.ini most important values: key_buffer_size table_cache
Submitted by chris on January 8, 2006 - 11:28When tuning MySQL, the two most important variables to configure are
key_buffer_size and table_cache. You should first feel confident that
you have these set appropriately before trying to optimize any other variables.
Ideally, key_buffer_size will be large enough to contain all the indexes
(i.e. at least the total size of all .MYI files on the server) of your MyISAM tables.
MySQL recommendations (which appears to be on the conservative side...):
256MB system: key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M
128MB system: key_buffer_size=16M sort_buffer_size=1M
How to determine, if you need to increase or decrease key_buffer_size:
Look at the "key_reads" (actual reads from the disk) and "key_read_requests" status variables.
MySQL recommendations (which appears to be on the conservative side...):
256MB system: key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M
128MB system: key_buffer_size=16M sort_buffer_size=1M
How to determine, if you need to increase or decrease key_buffer_size:
Look at the "key_reads" (actual reads from the disk) and "key_read_requests" status variables.
tmp_table_size sort_buffer_size read_buffer_size
Submitted by chris on January 8, 2006 - 14:43If you have complex queries sort_buffer_size and tmp_table_size
are likely to be very important.
If the space required to build the temporary MyISAM table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory.
To determine a good value for tmp_table_size:
Compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. You want to increase tmp_table_size if Created_tmp_tables is not a lot larger then Created_tmp_disk_tables (like in the order of 20x: 5%).
If the space required to build the temporary MyISAM table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory.
To determine a good value for tmp_table_size:
Compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. You want to increase tmp_table_size if Created_tmp_tables is not a lot larger then Created_tmp_disk_tables (like in the order of 20x: 5%).