5 Minute DBA MyISAM Example Config Files

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.

This entry was posted in 5 minute dba, Matt, mysql. Bookmark the permalink.

4 Responses to 5 Minute DBA MyISAM Example Config Files

  1. erkules says:

    key_buffer_size: As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms
    (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size)
    Works here:)

  2. Mark R says:

    In my opinion, you should not run MySQL on a 32-bit system. It’s just too risky. Running out of address space causes the process to quit, leaving many MyISAM tables in need of repair.

    Also you’ve not put in max_connections, which is vital to ensure it won’t run out of address space.

    I’d say no new significant sized installation should be done on a 32-bit box, and existing ones should be migrated as soon as convenient (of course there’s a lot of QA work associated with that)

  3. Matthew Yonkovit says:

    Erkules,

    Thanks… shows you how much I work with MyISAM now days. Learn something new all the time.

  4. Pingback: MyISAM with key_buffer larger than 4 GB » Big DBA Head!