Mysql – Best the.cnf configuration for a 8GB MySQL server with MyISAM use only

drupalmyisamMySQLperformancePHP

I am preparing a large Drupal 6.x based installation and have setup a dedicated MySQL server with 8GB of RAM. Drupal 6.x uses only MyISAM engine and there will be around 500 concurrent users using the site through two web nodes.

Here is a dump of mysql tuning primer analysis, I took it early in the morning as I am not sure if this should be run during high usage or not: http://pastie.org/3279741.

I am not very experienced in mysql configuration, so I am looking for help in determining the right configuration for my.cnf file so that I can sustain the amount of load. I need this on a pretty urgent basis so not looking for resources for understanding (at least not now!).

Thanks in advance!

Best Answer

I don't know the drupal. But as you asked you need dedicated MyIsam MySql configuration. For MyIsam i have some recommendations as

key_buffer_size

Size of the Key Buffer, used to cache index blocks for MyISAM tables. Do not set it larger than 30% of your available memory, as some memory is also required by the OS to cache rows. Even if you're not using MyISAM tables, you should still set it to 8-64M as it will also be used for internal temporary disk tables.So You should set it from 2GB to 3GB.

read_buffer_size

Size of the buffer used for doing full table scans of MyISAM tables. Allocated per thread, if a full scan is needed..You should set it to 8MB to 16MB .

read_rnd_buffer_size

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. You can improve ORDER BY performance a lot, if set this to a high value. Allocated per thread, when needed.

bulk_insert_buffer_size

MyISAM uses special tree-like cache to make bulk inserts (that is, INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) faster. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 will disable this optimisation. Do not set it larger than "key_buffer_size" for optimal performance. This buffer is allocated when a bulk insert is detected.Set it to 512 MB to 1GB.

myisam_sort_buffer_size

This buffer is allocated when MySQL needs to rebuild the index in REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE into an empty table. It is allocated per thread so be careful with large settings.So You should set it to 2M

myisam_max_sort_file_size

The maximum size of the temporary file MySQL is allowed to use while recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. If the file-size would be bigger than this, the index will be created through the key cache (which is slower).Set it to as you need.

myisam_repair_threads

If a table has more than one index, MyISAM can use more than one thread to repair them by sorting in parallel. This makes sense if you have multiple CPUs and plenty of memory.Set it to 1.

myisam_recover

Automatically check and repair not properly closed MyISAM tables.

Related Topic