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:

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:

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

A trial is worth a thousand words.
Get started today, no credit card required.

 
 
  • Gregory

    “1-2-All – Mostly InnoDB – Some MyISAM”

    Wait, but in my installation of 12all ALL tables are MyISAM.
    I checked the installation script. All tables declared as MyISAM.

    So, why you wrote “mostly innoDB”?
    Should I convert them to innoDB? Which tables?

    • Different versions may have slightly different setups. If all the tables are in MyISAM you should optimize for MyISAM on the server. 1-2-All 5.0 will almost be all INNODB

      Sorry about the confusion

      • Gregory

        Ok, I undestand: MyISAM -> optimize for MyISAM.

        But I have problems with performance and just wonder, if I use innoDB, can I have better performance?

        I never know than 12all have different versions.
        I use ver. 4.80 is that versions you talking about?

        • You wouldn’t likely see a big difference with changing the table type. You would be better off changing the buffer size and what not for MyISAM

  • Quero

    Awefull innodb-parameters! Got extreme wins of performance! Big thx!

  • FYI – for those of you using cPanel on your server, the file path to the my.cnf file is /usr/local/cpanel/whostmgr/my.cnf and not /etc/my.cnf

    took me a long time to figure that out (and I’m posting this here in case I ever have to refer to it again too!)

  • Jonix

    Stephan, that’s not true, for mysql, the correct my.cnf for websites IS /etc/my.cnf .
    /usr/local/cpanel/whostmgr/my.cnf or /root/my.cnf, have nothing to do with the real configuration of mysql (/etc/my.cnf).

  • www.gelder.tk

    I think i ‘ll try it out