I have been asked this numerous times ever since I posted my InnoDB sample configuration files, Do I have sample configuration parameters for a MyISAM setup? We are seeing less and less people use MyISAM, but it is still popular ( especially in prepackaged form i.e. wordpress). So i figured why not adjust my sample InnoDB configs and make them suitable for MyISAM.
The biggest limitation to MySIAM is used to be the default key buffer only can could only be sized up to 4GB ( This was fixed in 5.0.52) . While you can create separate key buffers and assign indexes to them, it’s not very common ( Common as in present in low-end shops who need 5 minute dba help) in part because I think people do not fully understand it and you do have to plan for it. Setting up secondary caches is a database/application specific setup, it can not really be generalized, so I am not going detailing adding additional key caches here. My assumption is if you at the point of adding multiple key caches, you probably should not be copying your config of someone’s blog:)
As for the configs themselves they should look very similar to the InnoDB configs I published. In fact I change very little between the InnoDB and MyISAM configs.
Let me publish my disclaimers here. These are not the optimal settings for all environments, they are just meant to be starting points. The intended audience here are those developers and sysadmins who are DBA’s for 5 minuts at a time. They want a config that’s better then the default, “set it and forget it”. I may have missed a couple parms, so feel free to comment and I will update as needed.
Let get down to it:
|
32 bit system 2GB of memory Dedicated DB Box All MyISAM [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 128 table_cache = 512 key_buffer = 768M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=16M max_heap_table_size=16M query_cache_size=64M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=3 log-error=/mysql/error.log myisam_recover = force,backup myisam_sort_buffer_size=128M skip-innodb |
32 bit system 4GB of memory Dedicated DB Box All MyISAM [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 256 table_cache = 1024 key_buffer = 1500M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=32M max_heap_table_size=32M query_cache_size=128M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=3 log-error=/mysql/error.log myisam_recover = force,backup myisam_sort_buffer_size=256M skip-innodb |
64 bit system 8GB+ of memory Dedicated DB Box All MyISAM [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /mysql/ datadir = /data01/data tmpdir = /tmp thread_cache_size = 256 table_cache = 1024 key_buffer = 4000M sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K max_allowed_packet = 1M tmp_table_size=64M max_heap_table_size=64M query_cache_size=128M query_cache_type=1 log_output=FILE slow_query_log_file=/mysql/slow1.log slow_query_log=1 long_query_time=3 log-error=/mysql/error.log myisam_recover = force,backup myisam_sort_buffer_size=512M skip-innodb |
On machines with more then 8GB, feel free to increase the key buffer accordingly.
I am probably being overly cautious on some of these, but these are not supposed to be optimal… they are supposed to be better then default. Adjust as needed.