Don’t Forget To Optimize MySQL

I often encounter situations where someone is dedicating their time to server specs yet overlooking some of the most basic optimization options available.  One of these basic optimization options include looking into your MySQL configuration and actually setting it to properly use the resources available on your server.

All ActiveCampaign products utilize a MySQL database.  The database is a critical part of the application and your MySQL configuration can result in your software being lightning fast or can force limited speed upon your applications.

Types of tables

Before you start to optimize MySQL you will need to know the basics.  There are two types of tables we use for our applications.  MyISAM and InnoDB.  While the differences are important – we will focus on configuration for this article.  There are different configuration options for both types of tables.  Thus the configuration you set your MySQL to may depend on the product(s) you are using.  For example, if you are only using 1-2-All you would want to optimize MySQL for InnoDB and you do not need to use as many resources for MyISAM.

Here is a list of table types for a couple of our products:

1-2-All – Mostly InnoDB – Some MyISAM

iSalient -  MyISAM

KnowledgeBuilder – Mostly MyISAM – Some InnoDB

TrioLive – Mostly InnoDB – Some MyISAM

SupportTrio – Mostly MyISAM – Some InnoDB

What to edit

For the basic MySQL configuration options you will want to edit the MySQL configuration file.  This file is called my.cnf or my.ini.

On a linux box you will usually find the file at: /etc/my.cnf

On a windows box you will usually find the file in your MySQL folder and the file name will be my.ini

Specific Optimization Tips

For MyISAM tables:

bulk_insert_buffer_size=32M
join_buffer_size=4M
key_buffer_size=128M
max_allowed_packet=32M
query_cache_limit=4M
read_buffer_size=1M
read_rnd_buffer_size=2M
sort_buffer_size=8M
table_cache=128
tmp_table_size=32M

These are modest values. They can be doubled or more based on the amount of RAM on your server.

For InnoDB tables:

innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=0

The buffer size should be 25-50% of your server’s RAM, if not more. Intensive programs benefit a lot from extra RAM.

Dig deeper

The information provided here is only scratching the surface of what you can do with MySQL configuration options.  If you have a host or system administrator they should be able to assist with such configuration changes.  For additional reading you may want to view the MySQL Performance Blog