Tune MySQL and MariaDB through their .ini configuration file

Tune MySQL and MariaDB through their .ini configuration file. On Windows the file is named my.ini and on Linux/Unix/Apple the file is named my.cnf. the files are interchangeable. Both have limitations when used with large amounts of memory in 32 bit operating systems and all the limitations go away in 64 bit operating systems.

The following table shows some of the problem indicators.

Slow queries

There are a million potential reasons for slow queries. Poor SQL. Lack of indexes. Every one of your perfect queries can be slowed down by something else backing up the processing queue and the things jammed in the processing queue can be blocked by something else.

Check your request first. Work out what indexes are required for the request then ensure the indexes exist. If you are selecting data based on a column, you need an index on the column.

The next check is to find things you are not checking in the SQL. If you read a lot of records from the database then select a smaller number in the code, can you improve the SQL to select fewer rows?

MyISAM or InnoDB

MySQL, MariaDB, and other MySQL alternatives offer multiple storage engines. Each storage engine handles a specific table type. MyIsam and InnoDB are the two most popular table types. MyISAM is faster for some types of reads but does not have transactions. InnoDB was often slower than MyISAM because people did not know how to use it or tune it. Today InnoDB is the default in MySQL, MariaDB, and Drupal.

If you have more than one Web site on your server, or you have an old database carried through several upgrades, you have MyISAM in some tables. Either you tune for both MyISAM and InnoDB or you convert all your MyISAM to InnoDB.

Settings for MyISAM

Work through the MyISAM settings first because several of the settings apply to all table types.

The following table shows a variety of parameters with recommendations for various size systems. The smallest, Default, is the value you get if you do not specify anything and your Web hosting supplier has not changed the settings. The default is useless for everything. The 64 MB size is a trivial increase specified in the minimum configuration file supplied with MySQL and MariaDB. The 64 MB size is useless for everything.

The 512 MB size is the minimum you would use for a modern computer and the minimum you should use for a VPS running any modern content management system. The settings assume the database is allocated somewhere between a quarter and half the memory.

The 1 GB and 4 GB sizes are the 512 MB size expanded for a VPS of 1 GB or 4 GB. Again the assumption is the database has a significant share of memory.

See MySQL performance tips for a description of the parameters.

Look in the configuration file for a section labelled [mysqld]. The critical settings are in that section.

table_open_cache

table_open_cache was named table_cache in MySQL prior to MySQL 5.1.3. When you are updating an old MySQL to a new MySQL, make sure the parameter name is changed.

Look in PHPMyAdmin under Status. Look for Open tables then Opened tables. Opened tables is higher than Open tables. A table may be opened several times in different connections and queries. There is a max_connections setting defaulting to 100, enough for most Web sites. If you have 50 tables, you could have 50 tables open in each of 100 connections, or 5000 open tables. Adjust table_open_cache up to bring the two numbers closer. Try doubling table_open_cache once a day for a few days to see the effect.

tmp_table_size

max_heap_table_size and tmp_table_size work together to limit the use of memory to store temporary tables. max_heap_table_size defaults to 16M. tmp_table_size is either not set or is set to 32M on a lot of the sites I work on. MySQL will use the smaller of the two. When the limit is hit, temporary tables will be written to disk instead of memory. Drupal pushes this setting to the limit. The instant you start using Drupal Views to access Drupal Fields, you skyrocket past the defaults.

There should be a setting to make MySQL drop one and use only the other setting. For now, you have to set both settings to the same value. Start at 16M for a day then double it each day until the number of temporary files drops down.

How many is low? You probably have at least one cron job that work through big tables and creates a massive result. If the cron job runs once per hour, you will have 24 temporary tables on disk every day. A bigger number, say 5000, indicates a lack of memory for smaller database requests and should be fixed. Of course, 5000 tables on disk might mean that each cron job is running 200 massive table processing jobs, in which case you are stuck with providing more memory.

What you do not want is a View on your home page that reads a huge table to produce a giant temporary table. 300000 visits to your home page will result in 300000 temporary tables written to disk. If you have deep database and code knowledge, you might set the table size to something small then hunt down and kill everything that places massive amounts of data in temporary tables. Efficient Views and SQL is worth more than the memory because it also reduces disk and processing overheads.

Settings for InnoDB

Use the 64 bit version of your operating system. The 32 bit versions can hit internal limits now when you expand any memory allocation.