Tuning mySQL – Because by default it’s not even close to tuned.

Basic tuning of the mySQL is accomplished in the /etc/my.cnf file. If you want to get all geeky and into this reference the seminal document over on the mysql dev site. This should result in a speed increase in your system.  It certainly has in my system running mySQL 5.x.

The information below is expressed as a set of ratios that begins with your system RAM and then works from there.

innodb_buffer_pool_size = $SYSTEMRAM/2
innodb_additional_mem_pool_size = $innodb_buffer_pool_size/20
innodb_log_file_size = $innodb_buffer_pool_size/4
innodb_log_buffer_size = $innodb_buffer_pool_size/50 or a minimum value of 8MB

Note bene: Changing your log file size can results in a mySQL refusing to start.  Simply remove these files from you mysql data directory and they will be created on the next startup.

2 thoughts on “Tuning mySQL – Because by default it’s not even close to tuned.

  1. have a look at mysqltuner.pl for setting up memory and mysqlreport for query types and where the server is spending alot of time.

    you setup if find for myisam tables. Later versions of mysql 5.1 and better run much faster with innodb.

    Cheers
    Steve

    • “you setup if find for myisam tables. Later versions of mysql 5.1 and better run much faster with innodb. ”

      Did I type that!!! sorry … thinking about two things at once.

      Your setup would be fine for the default myisam table engine. Later versions of mysql, 5.1 and higher run much faster with innodb.

      innodb needs a different set of memory options in the /etc/my.cnf file.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>