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:
Email Marketing – Mostly InnoDB – Some MyISAM
Survey Software – MyISAM
Knowledge Management – Mostly MyISAM – Some InnoDB
Live Chat – Mostly InnoDB – Some MyISAM
Help Desk – 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:
These are modest values. They can be doubled or more based on the amount of RAM on your server.
For InnoDB tables:
The buffer size should be 25-50% of your server’s RAM, if not more. Intensive programs benefit a lot from extra RAM.
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