3

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!

Improve this question
2
  • That tuning primer script seems to think you have 16GB, not 8GB.
    – Ladadadada
    Jan 30, 2012 at 15:37
  • with Higher version of MySQL InnoDB is Default and perform more better than the MyISAM so why you are going with MyISAM if it is not the restriction to use the MyISAM.The Higher Version of MySQL(5.5) Perform really well they have built-in plugins and provide better Performance.
    – Abdul Manaf
    Feb 9, 2012 at 5:17

3 Answers 3

Reset to default
10

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.

Improve this answer
1
  • If you need any other help regarding the MySQL server setting Please let me know.
    – Abdul Manaf
    Feb 9, 2012 at 5:18
2

Actually, Drupal (even 6.x) prefers to run on InnoDB. MyISAM is the default, yes, but that's simply because of a conscious effort to support hosters that don't offer InnoDB--see http://drupal.org/node/301362 . Consequently, I'd very strongly encourage you to reconsider your exclusive use of MyISAM.

One caveat does apply to this: the search_* tables use FULLTEXT indexes, which are not compatible with InnoDB. Leave those MyISAM, or better yet, replace their functionality with something like Apache Solr.

If you do use mysqltuner.pl after converting to InnoDB, unless it has been recently updated, it won't bother to tell you to set innodb_buffer_pool, which is the biggest performance key for InnoDB.

The conversion process from MyISAM to InnoDB is lossless and relatively quick; it's just a simple ALTER TABLEsometableENGINE=InnoDB. Be sure to have set innodb_buffer_pool before you do this or you may be in for a long wait!

Lastly, I wanted to get in a quick mention that a hosting provider with experience with large-scale Drupal deployments would take care of most or all of these steps for you. Consider talking to BlackMesh--for whom I work--or another host active in the Drupal community. They will likely be able to offer additional Drupal-specific support and scaling advice as you continue to grow your site.

Good luck!

Improve this answer
1

  1. Yes, you want to run it under real load, not fake load. The numbers are about how things have performed in the past, so if that isn't like the real load, they won't help as much as they might.

  2. Just do what mysqltuner tells you to.

If you don't have time to learn the details of tuning the product, and want to just follow the best practices, mysqltuner is for you! It does exactly that: capture best practice recommendations, and tell you what to change based on your real use.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .