brian jamroom team
Timeline documentation followers

(optional) MySQL optimizations for larger servers

brian » Documentation » guides » (optional) MySQL optimizations for larger servers

Table of Contents:


my.cnf settings for larger servers
Configuring the key_buffer for MyISAM tables
Configuring the max_allowed_packet size
Increasing MySQL's Query Cache size
Increase table_cache and tmp_table_size
Increase InnoDB buffer pool size
Save the changes and restart MySQL
Further reading on MySQL optimization

  • my.cnf settings for larger servers

    I've labeled this part of the guide optional, since we've been building our DigitalOcean setup on the smallest Droplet they have, which only has 512MB of RAM - the "default out of the box" setup for MySQL is fine for this size of a VPS server.

    However, if you have a Droplet that has more RAM (1GB and over), there are some tweaks you can make to the default MySQL config file to give the database more room to cache and work - which in turn makes Jamroom faster.

    The MySQL configuration file is called "my.cnf", and in our Ubuntu 12.04 install can be found at:

    /etc/mysql/my.cnf

    We're going to edit this file now:

    pico /etc/mysql/my.cnf

    And modify a few of the settings to give MySQL more room to run.
  • Remember - if you are only on a 512MB Droplet I would recommend NOT doing these modifications, as we really want the extra RAM to be dedicated to Apache web server and PHP processing.

  • Configuring the key_buffer for MyISAM tables

    Probably the most important setting for MySQL MyISAM tables is what is called the "key_buffer" - while it only applies to MyISAM tables (Jamroom does use some MyISAM tables), we definitely want to change it from it's default 8MB to something much larger.

    I would recommend setting it to around 128MB for every 1 Gig of RAM in your server - so on a 2 Gig Droplet you can set this to 256 megabytes:

    key_buffer = 256M

    Jamroom uses MyISAM tables for all datastore "key" tables - i.e. the tables that keep track of the item ID's that have been created. The key_buffer plays a crucial role in how quickly items can be created in the datastore key table, so it's good to give it some space.

  • Configuring the max_allowed_packet size

    The max_allowed_packet setting in MySQL tells MySQL how large the largest Query or result set can be. While 99.9% of Jamroom queries are very small, if you are running a very large Jamroom there are some sub queries that can contain very long lists of profile_id's (for privacy checking), so we want to be sure we never run into any sort of wall with regards to the max allowed packet size.

    We're going to set the Max Allowed Packet size to 32 megabytes:

    max_allowed_packet = 32M

    You could probably do 8M or 16M, but it doesn't hurt to have it set to a larger value - MySQL does not set aside any memory to "hold" it in any way.

  • Increasing MySQL's Query Cache size

    Increasing MySQL's Query Cache size
    Next up we want to increase MySQL Query Cache to something a little larger than the default value. MySQL's Query Cache is used to cache the OUTPUT of a query (the entire result set), so we want to take advantage of it if we can.

    #
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 32M

    Here we've set the query cache to 32 megabytes, and we only cache a result set if it is less than 1 megabyte in size (which all JR queries should be).

  • Increase table_cache and tmp_table_size

    The Jamroom DataStore functions do their best to "force" MySQL to create as many tmp tables as it can in memory (i.e. my limiting all tables to no BLOB/TEXT columns and max length of 512 for VARCHAR columns), but sometimes no matter what you do MySQL is going to create a tmp table on disk (which is 100 times slower than creating it in RAM).

    The good news is that DigitalOcean Droplets are all SSD (solid state disk) based - for random read/write access they are much faster than "normal" hard drives, and they really help with MySQL tmp table creation.

    We are going to change both the table_cache and tmp_table_size values to somthing a little higher (the table_cache caches the table definition which speeds up the MySQL query planner while the tmp_table_size/max_heap_table_size settings define how much memory can be used for creating tmp tables):

    I would recommend 64M for every 1 Gig of System RAM - so a 2 Gig Droplet would use 128M, a 4 Gig 256M and so on:

    table_cache          = 1536
# for a 2 Gig RAM droplet
tmp_table_size       = 128M
max_heap_table_size  = 128M

    We also have set our table_cache up to 1536 which is a good number for the number of tables that Jamroom can have open at any given time.

  • Increase InnoDB buffer pool size

    Next, we want to increase InnoDB's buffer pool size, which defaults to only 8MB, which is too small for a larger server.

    I would recommend setting the buffer pool size to 1/8th of your system RAM - so on a 4G RAM system it would be:

    innodb_buffer_pool_size = 512M
innodb_flush_method     = O_DIRECT

    The inndob_flush_method is a small performance improvement for Linux based systems to use a faster file access method:

    http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

  • Save the changes and restart MySQL

    When you've finished making these changes, save the my.cnf file and restart MySQL so the changes will take effect:

    service mysql restart

  • Further reading on MySQL optimization

    There are entire industries that have been built up around database optimization, and there are thousands and thousands of online documents and guides about optimizing MySQL.

    The items I've outlined here are really just the tip of the iceberg when it comes to tweaking MySQL. If you are interested, I would highly recommend reading more on the internals of how MySQL works and is configured:

    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

    There is an absolute ton of small details and information available to help you fine tune your server.

    I would also recommend the MySQL Performance Blog:

    http://www.mysqlperformanceblog.com/

    From the company that brings you the Percona server (a high performance version of MySQL) there's a lot of really good information on the site.

Tags