Structured Query Language (SQL) is a special purpose programming language used to store, manipulate and retrieve data from databases.

Running MySQL at optimal settings for specific resources helps handle larger server loads and helps lower infrastructure costs. Generally, after tuning Apache to handle larger loads, it is beneficial to tune MySQL to allow additional connections.

What about MariaDB?

MariaDB is a fork of MySQL, intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL.

MariaDB intends to maintain high compatibility with MySQL, ensuring a drop-in replacement capability with library binary parity and exact matching with MySQL APIs and commands.

It includes a new storage engine, Aria, an alternative to MyISAM. It initially used XtraDB as the default storage engine, and switched back to InnoDB since version 10.2

Locating my.cnf

• On Linux systems the MySQL configuration file is usually stored in: /etc/mysql/my.cnf
• On DirectAdmin and cPanel based systems the MySQL configuration file is stored in: /etc/my.cnf
• On Windows systems the MySQL configuration file is usually stored in: C:\Windows\my.ini

# cp /etc/mysql/my.cnf ~/my.cnf.backup

In case you are unable to find my.cnf, you can also request MySQL for a list of all locations where it searches for my.cnf (or my.ini on Windows). It is not an SQL query though. Rather, execute:

# mysqladmin --help

or, prior to version 5.7 of MySQL:

# mysqld --help --verbose

In the very first lines you will find a message with a list of all my.cnf locations it looks for:

Default options are read from the following files in the given order:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

Or, on Windows:

Default options are read from the following files in the given order:
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

Restart MySQL

Best practice suggests that you make small changes, one at a time, and then monitor the server after each change. You should restart MySQL after each change:

For distributions using systemd:

systemctl restart mysqld

For distributions with different init systems:

service mysql restart

When changing values in the my.cnf file, be sure that the line you are changing hasn’t been commented out with the pound # prefix.

Storage Engines

InnoDB is a data storage engine used by default in MySQL database since version 5.5 replacing MyISAM. It has a strong focus on reliability and performance, and features row-level locking, foreign keys and transaction support. It follows the ACID (atomicity, consistency, isolation, durability) model which guarantees that all transactions are handled properly and error free.

MyISAM is the default storage engine for the MySQL relational database management system versions prior to 5.5. It is based on the older ISAM code but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support. Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

Aria is a storage engine for the MariaDB and MySQL relational database management systems. Its goal is to make a crash-safe alternative to MyISAM. It is not yet transactional but plans to add proper support for database transactions at some point in the future. The long-term goal is for Aria to be the default transactional and non-transactional storage engine for MariaDB. Aria is not shipped with MySQL or Percona Server, but is included in the TokuDB release of MariaDB

In this article we will be focusing exclusively on InnoDB. If you still make use of MyISAM it is strongly recommended that you convert your storage engine to InnoDB.

Optimizing Directives

join_buffer_size

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes.

Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

Change the MySQL config to log queries without indexes, so you will be able to find such queries and add indexes and/or modify the application that sends generates such ill queries. You should enable "log-queries-not-using-indexes" Then look for non-indexed joins in the slow query log.

There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that have large joins.

Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

Unlike many of the buffers that are allocated per-thread (i.e. per-connection), this one is allocated per-join-per-thread.

The maximum permissible setting for join_buffer_size and sort_buffer_size is 4GB–1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB–1 with a warning). However, such large values should never be used.

As a rule of thumb you should set 1M for each Gigabyte of used memory in MySQL. So 1M for 1GB, 2M for 2GB, 4M for 4GB

sort_buffer_size

Each session that needs to do a sort allocates a buffer of this size. This means that whenever a query needs to sort the rows, the value of this variable is used to limit the size that needs to be allocated. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.

The first thing you need to know is that sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread.

Second, internally in the OS usage independently of MySQL, there is a threshold > 256K. If the buffer is set to a value higher than 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but defaults to 256k. A buffer larger than 256K causes a 37x slowdown. This applies to all per session buffers, not just sort buffer. Now I have heard recently about this limit being 512K.

If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size.

In conclusion, On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload.

read_buffer_size

Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.

This option is also used in the following context for all search engines:

• For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
• For bulk insert into partitions.
• For caching results of nested queries.

and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.

The maximum permissible setting for read_buffer_size is 2GB.

read_rnd_buffer_size

The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size.

Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

The maximum permissible setting for read_rnd_buffer_size is 2GB. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.

However, you should take into account that this does not apply to MariaDB, especially when taking advantage of MRR. MariaDB uses mrr_buffer_size while MySQL uses read_buffer_size and read_rnd_buffer_size.

key_buffer_size

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).

A simple way to check the actual performance of the buffer is to examine four additional variables:

key_read_requests
key_reads
key_write_requests
key_writes

If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.

The maximum size of the key_buffer_size variable is 4 GB on 32 bit machines, and larger for 64 bit machines. MySQL recommends that you keep the key_buffer_size less than or equal to 25% of the RAM on your machine. This also depends on the other processes that use memory on the machine and it is wise to check if you consistently have 25% of free memory using the Linux command free.

Should you increase the key_buffer_size?

First, we must inspect some statistics on the key buffer. Login to your mysql admin tool:

mysql -u databaseuser -p

And then, use the following command when you get the mysql prompt:

mysql> SHOW STATUS LIKE "key%";
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 0         |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 663954641 |
| Key_reads              | 7451404   |
| Key_write_requests     | 2189484   |
| Key_writes             | 2159580   |
+------------------------+-----------+
7 rows in set (1.42 sec)

mysql>

The two variables of interest are key_reads and the key_read_requests.

• Key_reads: This variable represents the number of reads from disk instead of from the cache
• Key_read_requests: Represents the number of read requests to read a block from the cache

If your key_cache_hit_percentage is high, you don’t need to increase the key_buffer_size as it will not have much effect. In my case, the key_cache_hit_percentage is about 98% which is quite high. So increasing the key_buffer_size will not help much. But I already have a key_buffer_size of 256 megabytes.

See what hit percentage you get and if the value is close to 90% to 100%, you already have an optimized key_buffer_size. But if it is less, it may help immensely to increase the key buffer.

Note again, MySQL recommends that you keep the value of the buffer to 25% or less of your physical RAM size.

Before you decide to increase the key_buffer_size, you must inspect your free memory with free -m

max_user_connections

One of the common causes of downtime with MySQL is running out of connections.

Have you ever seen this error: ERROR 1040 (00000): Too many connections?

If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime, transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

One means of limiting use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected.

• If the user account has a nonzero max_user_connections resource limit, the session max_user_connections value is set to that limit
• Otherwise, the session max_user_connections value is set to the global value

The solution I see some people employ is just to increase max_connections to some very high number so MySQL “never” runs out of connections. This however can cause resource utilization problems – if a large number of connections become truly active it may use a lot of memory and cause the MySQL server to swap or be killed by OOM killer process, or cause very poor performance due to high contention.

There is a better solution: use different user accounts for different scripts and applications and implement resource limiting for them. Specifically set max_user_connections:

mysql> GRANT USAGE ON *.* TO 'batchjob1'@'localhost'
     ->     WITH MAX_USER_CONNECTIONS 10;

This approach (available since MySQL 5.0) has multiple benefits:

• Security: different user accounts with only required permissions make your system safer from development errors and more secure from intruders
• Preventing running out of Connections: if there is a bug or miss-configuration the application/script will run out of connections of course but it will be the only part of the system affected and all other applications will be able to use the database normally.
• Overload Protection: Additional numbers of connections limits how much queries you can run concurrently. Too much concurrency is often the cause of downtime and limiting it can reduce the impact of unexpected heavy queries running concurrently by the application.

In addition to configuring max_user_connections for given accounts you can set it globally in my.cnf as:

max_user_connections = 20

max_connections

The maximum permitted number of simultaneous client connections that are allowed from your application. By default, this value is set to 151.

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time.

Linux or Solaris should be able to support 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding.

Increase this number if you receive the following error: MySQL Error, Too many connections…

max_allowed_packet

The maximum size of one packet or any generated/intermediate string. The max_allowed_packet size is a session variable and is also a read only variable. In MySQL, you might run into error 1153: Got a packet bigger than ‘max_allowed_packet’ bytes when packet size is too small.

The largest possible packet that can be transmitted to or from a MySQL server or client is 1GB. The default value is 64MB.

In older versions of MySQL, the default value was 4 MB so you might take care of that in case you encounter ER_NET_PACKET_TOO_LARGE error.

You must increase the default value if you are using large BLOB (Binary Large OBject) columns or long strings. It should be as big as the largest BLOB you want to use. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple. Suggested value:

max_allowed_packet = 64M

or in bytes:

max_allowed_packet = 4194304;

To check what the present value of max_allowed_packet is, the command show variables is used:

mysql> show variables like 'max_allowed_packet';

output:

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.04 sec)

query_cache_size

MySQL provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over.

Increasing your query_cache_size will definitely have an impact on performance of a few high frequency queries.

The decision whether to increase the size of the variable query_cache_size can be easily made using a few diagnostic mysql sessions.

Should you increase query_cache_size?

First login to mysql command line prompt:

mysql -u databaseusername -p

This will prompt you for a password and then show you the mysql command prompt.

Use the following session to get some statistics on your query cache:

mysql> SHOW STATUS LIKE "qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 2618    |
| Qcache_free_memory      | 8089536 |
| Qcache_hits             | 216892  |
| Qcache_inserts          | 3331399 |
| Qcache_lowmem_prunes    | 540755  |
| Qcache_not_cached       | 11490   |
| Qcache_queries_in_cache | 4788    |
| Qcache_total_blocks     | 12430   |
+-------------------------+---------+
8 rows in set (0.00 sec)

Qcache_free_memory shows that currently there is about 8 MB of free memory from the available 16 MB. This means that about half of the query_cache_size is available for caching the outputs of more queries!

However, note that there is another variable called Qcache_lowmem_prunes which indicates how many times MySQL had to prune the query cache (removing some data) to make space for the outputs of other queries. This clearly indicates that increasing your query_cache_size will have a positive impact on performance. Try to increase the size of the cache till you get a very low value of Qcache_lowmem_prunes.

In this case, we have about 2 GB of available memory. We must first check how much free memory we have:

[~]# free -mt
             total       used       free     shared    buffers     cached
Mem:          2048       2043          4          0          0       1616
-/+ buffers/cache:        426       1621
Swap:         2048        717       1330
Total:        4096       2761       1334
[~]#

There is about 1.6 GB free! This indicates that we should use a higher value for the query_cache_size.

query_cache_type

When testing mysql performance with mysqltuner.pl and if the line “query cache efficiency” has a “0” on it, this means that you must turn on the query caching mechanism, because it is disabled (you are missing a query_cache_type=1 in your my.cnf file)

query_cache_limit

The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached.

Setting this value too high might prevent a lot of smaller queries to be cached. Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources.

skip-external-locking

external locking is disabled by default. You need to enable external locking:

• If you run multiple servers that use the same database directory (not recommended), each server must have external locking enabled.

• If you use myisamchk to perform table maintenance operations on MyISAM tables, you must either ensure that the server is not running, or that the server has external locking enabled so that it locks table files as necessary to coordinate with myisamchk for access to the tables. The same is true for use of myisampack to pack MyISAM tables.

External locking affects server performance because the server must sometimes wait for other processes before it can access tables.

External locking is unnecessary if you run a single server to access a given data directory (which is the usual case) and if no other programs such as myisamchk need to modify tables while the server is running. If you only read tables with other programs, external locking is not required, although myisamchk might report warnings if the server changes tables while myisamchk is reading them.

With external locking disabled, to use myisamchk, you must either stop the server while myisamchk executes or else lock and flush the tables before running myisamchk. To avoid this requirement, use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.

slow_query_log

MySQL reports how much time it took to execute a query.

Step 1: Enable slow_query_log, set a slow_query_log_file, and set the log_query_time in my.cnf:

nano /etc/my.cnf

In the [mysqld] section add/edit the following variables:

long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld.slow.query.log

log_query_time is set to 0 (zero). Any MySQL query taking more than 0 seconds will be logged.

Step 2: Create the file /var/log/mysqld.slow.query.log and set the system user mysql as the owner.

touch /var/log/mysqld.slow.query.log
chown mysql.mysql /var/log/mysqld.slow.query.log

Step 3: Restart MySQL server

/etc/init.d/mysqld restart

Step 4: Watch the slow query log using the tail command.

tail -f /var/log/mysqld.slow.query.log

A sample entry on the server looks like this:

[root@localhost ~]# tail -f /var/log/mysqld.slow.query.log 
SET timestamp=1255345490;
SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices"') LIMIT 1;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000273  Lock_time: 0.000104 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
SELECT * FROM organization_details;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000048  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
# administrator command: Quit;

table_open_cache

Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table.

Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.

The default is value is 64.

More information: https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/

table_open_cache_instances

This value limits the number of open tables cache instances.  Setting this variable would help improve scalability, and performance, which would reduce contention among sessions.

The open tables cache can be partitioned into several smaller cache instances of:
table_open_cache / table_open_cache_instances

A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)

A value of 8 or 16 is recommended on systems that routinely use 16 or more cores

table_definition_cache

Cache table definitions i.e. this is where CREATE TABLE is cached to speed up opening of tables and only one entry per table. It would be reasonable to increase the value if you have a large number of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.

Percona recommends  the formula below for databases with less than 50.000 tables:

Take note that the default value is based on the following formula capped to a limit of 2000:

MIN(400 + table_open_cache / 2, 2000)

So, in case you have larger number of tables compared to the default, then it’s reasonable you increase its value. Take into account that with InnoDB, this variable is used as a soft limit of the number of open table instances for the data dictionary cache. It will apply the LRU mechanism once it exceeds the current value of this variable.

The limit helps address situations in which significant amounts of memory would be used to cache rarely used table instances until the next server restart. Hence, parent and child table instances with foreign key relationships are not placed on the LRU list and could impose a higher than the limit defined by table_definition_cache and are not subject to eviction in memory during LRU.

Additionally, the table_definition_cache defines a soft limit for the number of innoDB file_per-table tablespaces that can be open at one time, which is also controlled by innodb_open_files and in fact, the highest setting between these variables is used, if both are set. If neither variable is set, table_definition_cache, which has a higher default value, is used.

If the number of open tablespace file handles exceeds the limit defined by table_definition_cache or innodb_open_files, the LRU mechanism searches the tablespace file LRU list for files that are fully flushed and are not currently being extended. This process is performed each time a new tablespace is opened. If there are no “inactive” tablespaces, no tablespace files are closed. So keep this in mind.

thread_cache_size

If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.

The easiest way to figure out the proper size is through watching the increments of the Threads_created status variable:

root@db01:~# mysqladmin -u root -p -r -i 1 ext | grep Threads_created
Enter password:
| Threads_created                          | 8929           |
| Threads_created                          | 21             |
| Threads_created                          | 17             |
| Threads_created                          | 29             |
| Threads_created                          | 13             |
| Threads_created                          | 20             |
| Threads_created                          | 22             |
| Threads_created                          | 34             |
| Threads_created                          | 11             |
| Threads_created                          | 15             |

Note: The first line of output shows the accumulated value since the database instance was last started and should not be used in calculations.

The cache size should be sufficiently large to reduce the increments to zero in most cases, however be sure to watch the statistics during peak hours as you always want to tune for the peak usage.

Based on the values from the example above, setting thread_cache_size to somewhere between 20 and 30 would make sense.

created_tmp_disk_tables

created_tmp_disk_tables are the number of implicit temporary tables on disk created while executing statements and “created_tmp_tables” are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

max_heap_table_size

max_heap_table_size is the maximum size in bytes user-created MEMORY tables are permitted to grow. This is helpful when your application is dealing with MEMORY storage engine tables. Setting the variable while the server is active has no effect on existing tables unless they are recreated or altered.

The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables. This variable is also in conjunction with tmp_table_size to limit the size of internal in-memory tables (this differs from the tables created explicitly as Engine=MEMORY as it only applies max_heap_table_size), whichever is smaller is applied between the two.

tmp_table_size

tmp_table_size is the largest size for temporary tables in-memory (not MEMORY tables) although if max_heap_table_size is smaller the lower limit will apply. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table.

You should increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have a decent amount of available memory space.

You can compare the number of internal on-disk temporary tables created with the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and the Created_tmp_tables variables.

Under what circumstances?

When a query performs a join or sort (via ORDER BY) without the benefit of an index, a temp table has to be formed in memory. This would increment Created_tmp_tables.

What if the temp table grows to the number of bytes in tmp_table_size and needs more space? The following sequence of events happens:

• Query processing must stop
• Create a temp table on disk
• Transfer the contents of the memory-based temp table into the disk-based temp table
• Drop in the memory-based temp table
• Query processing continue using the disk-based temp table

This process increments created_tmp_disk_tables

The overall goal should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.

The default wait_timeout variable is 28800 seconds, which is 8 hours. That's a lot.

Setting the wait_timeout value too low (e.g. 30, 60, 90) can result in "MySQL has gone away" error messages. So you'll have to decide for your configuration.

skip_name_resolve

MySQL server handles incoming connections by hostname resolution. By default, MySQL does not disable any hostname resolution which means it will perform a DNS lookups, and by chance, if DNS is slow, it could be the cause of awful performance to your database.

Consider turning this on if you do not need DNS resolution and take advantage of improving your MySQL performance when this DNS lookup is disabled. Take into account that this variable is not dynamic, therefore a server restart is required if you set this in your MySQL config file. You may optionally start mysqld daemon, passing --skip-name-resolve option to enable this.

Optimizing Directives - InnoDB

innodb_buffer_pool_size

innodb_buffer_pool_size is by far the most important option for Innodb performance and it must be set correctly. This variable tells MySQL how much memory it can use. Leaving the value to the default 8M greatly slows performance down in most cases. You will want to give all memory that you do not need for other services to the Innodb Buffer Pool.

This of course assumes your database is large enough that you need a large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough.

You also should account for growth of course. You need your buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces) because it does not only contain data pages – it also contain adaptive hash indexes, insert buffer, locks which also use additional memory.

Though it is not as critical – for most workloads if you have set your Innodb Buffer Pool 10% less than your database size you would not loose too much performance.

suggested query to find correct value:

First enter the following SQL query:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you the RIBPS (Recommended InnoDB Buffer Pool Size) based on all InnoDB Data and Indexes with an additional 60%.

For Example:

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

mysql>

With this output, you would set the following in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G

Next, service mysql restart

After the restart, run mysql for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you the number of pages of InnoDB data that reside in the InnoDB Buffer Pool.

You could just run the DataGB query right now rather than reconfiguring, restarting and waiting a week.

The value DataGB more closely resembles how big the InnoDB Buffer Pool should be.

innodb_buffer_pool_instances

For your concurrency workload, setting this variable can improve concurrency and reduce contention as different threads of read/write to cached pages. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum).

Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

Take note that this option takes effect only when innodb_buffer_pool_size >= 1GiB and its size is divided among the buffer pool instances.

innodb_flush_method

An important parameter that you should add to your my.cnf file is innodb_flush_method=O_DIRECT. The common use for O_DIRECT is when an application such as a database is doing its own caching in userspace. This will prevent the operating system from caching data that is already cached.

However, you should Avoid using O_DIRECT on EXT2, EXT3 and jfs non-journaling file systems – writes to the same file are serialized and cannot happen concurrently.

The serialization problem also becomes evident when sync_binlog = 1. Performance degrades significantly in both cases. Setting innodb_file_per_table appropriately helps to reduce the problem when using a non-journaling file system.

innodb_adaptive_flushing

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. Typically, this is enabled by default . This variable, when enabled, tries to be smarter about flushing more aggressively based on the number of dirty pages and the rate of transaction log growth.

innoDB multithreading

InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you must remember to activate a number of options.

Most people are unaware of these features and a quite satisfied with InnoDB just doing ACID-complaint transactions. If you tweak any of these options, you do so at your own peril.

In conclusion, InnoDB behaves well now in a multi CPU server given its default settings for multithreaded operations. Tweeking them takes great care and great patience.

In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrecy_tickets to open for a given MySQL instance setup.

Once you set innodb_thread_concurrency = 0, you can set innodb_read_io_threads and innodb_write_io_threads (see below) to the maximum value of 64. This should engage more cores:

innodb_io_capacity = 200
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

innodb_io_capacity

The innodb_io_capacity should be tuned to the number of IOPS your disk array can sustain. Or actually, the number of IOPS you want MySQL to consume (because you might not want that to be 100% of disk capacity). The default value is innodb_io_capacity = 200, which is recommended for HDD servers.

Careful adding this directive, it will effectively eliminate any optimization of IO consolidation if you have a low enough write workload (that is, dirty pages get flushed almost immediately, we might be better off without a transaction log in this case). It also can quickly starve data reads and writes to the transaction log if you set this too high.

innodb_file_per_table

innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

innodb_thread_concurrency

sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks.

It is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero) in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in. If you see concurrency issues, you can tune this variable.

innodb_concurrency_tickets

sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency

sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay

sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads

(both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. These can also be rendered useless by filesystems that don’t allow parallel writing to the same file by more than one thread (particularly if you have relatively few tables and/or use the global tablespaces)

innodb_replication_delay

imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold

allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

innodb_log_file_size

This variable is the log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. According to Vadim, a bigger log file size is better for performance, but it has a drawback (a significant one) that you need to worry about: the recovery time after a crash. You need to balance recovery time in the rare event of a crash recovery versus maximizing throughput during peak operations. This limitation can translate to a 20x longer crash recovery process!

To elaborate it, a larger value would be good for InnoDB transaction logs and are crucial for good and stable write performance. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. However, the recovery process is pretty slow once your database was abnormally shutdown (crash or killed, either OOM or accidental).

Ideally, you can have 1-2GiB in production but of course you can adjust this. Benchmarking this changes can be a great advantage to see how it performs especially during after a crash.

innodb_log_buffer_size

To save disk I/O, InnoDB’s writes the change data into lt’s log buffer and it uses the value of innodb_log_buffer_size having a default value of 8MiB.

This is beneficial especially for large transactions as it does not need to write the log of changes to disk before transaction commit. If your write traffic is too high (inserts, deletes, updates), making the buffer larger saves disk I/O.

innodb_flush_log_at_trx_commit

When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact.

Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improves performance if you can relax your ACID requirements, and can afford to lose transactions for the last second or two in case of OS crashes.

innodb_stats_on_metadata

This attempts to keep the percentage of dirty pages under control, and before the Innodb plugin, this was really the only way to tune dirty buffer flushing. However, I have seen servers with 3% dirty buffers and they are hitting their max checkpoint age. The way this increases dirty buffer flushing also doesn’t scale well on high io subsystems, it effectively just doubles the dirty buffer flushing per second when the % dirty pages exceeds this amount.

innodb_dedicated_server

This variable is new in MySQL 8.0 which is applied globally and requires a MySQL restart since it’s not a dynamic variable. However, as documentation states that this variable is desired to be enabled only if your MySQL is running on a dedicated server. Otherwise, do not enable this on a shared host or shares system resources with other applications.

When this is enabled, InnoDB will do an automatic configuration for the amount of memory detected for variables innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method. The downside only is that you cannot have the feasibility to apply your desired values on the detected variables mentioned.

Deprecated

innodb_additional_mem_pool_size

deprecated - do not use

innodb_use_sys_malloc

deprecated - do not use

thread_concurrency

deprecated and removed in MySQL 5.6.1

skip-innodb

InnoDB can no longer be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

Denormalization and Constraints

Denormalization is the process of improving read performance by adding redundant data or by grouping specific data.

For example, if you have a “product” table and a “category” table and every time you query the “products” table you also need to get the “category_name” of each product. In this case, you may use join to retrieve the “category_name.”

However, this means that every time a user will open a product page, a complicated join query will be executed. So you may consider adding the “category_name” in the “product” table. In spite of the redundant data, the improvement in read performance is generally worth it.

The denormalization approach may cause an out-dated “category_name” in the “product” table. So you need to define a foreign key constraint. Unfortunately, a “foreign key” will make the write performance a little bit slower because MySQL needs to check the constraints before writing the data. So it is important to determine what the best options are to improve your database performance.

MySQLTuner

MySQLtuner is an open source perl script that analyzes your MySQL performance and provides recommendations that help improve performance and stability.

1. Download the MySQLTuner script:
   wget http://mysqltuner.com/mysqltuner.pl
2. Change the scripts permissions to be executable:
   chmod +x mysqltuner.pl
3. Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:
   ./mysqltuner.pl
4. The script will return results similar to the output below:

>>  MySQLTuner 1.4.0 - Major Hayden 
         >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
         >>  Run with '--help' for additional options and output filtering
        Please enter your MySQL administrative login: root
        Please enter your MySQL administrative password:
        [OK] Currently running supported MySQL version 5.5.41-0+wheezy1
        [OK] Operating on 64-bit architecture

        -------- Storage Engine Statistics -------------------------------------------
        [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
        [--] Data in InnoDB tables: 1M (Tables: 11)
        [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
        [!!] Total fragmented tables: 11

        -------- Security Recommendations  -------------------------------------------
        [OK] All database users have passwords assigned

        -------- Performance Metrics -------------------------------------------------
        [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
        [--] Reads / Writes: 100% / 0%
        [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
        [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
        [OK] Slow queries: 0% (0/113)
        [OK] Highest usage of available connections: 0% (1/151)
        [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
        [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
        [OK] Query cache prunes per day: 0
        [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
        [OK] Thread cache hit rate: 97% (1 created / 42 connections)
        [OK] Table cache hit rate: 24% (52 open / 215 opened)
        [OK] Open file limit used: 4% (48/1K)
        [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
        [OK] InnoDB buffer pool / data size: 128.0M/1.2M
        [OK] InnoDB log waits: 0
        -------- Recommendations -----------------------------------------------------
        General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            Enable the slow query log to troubleshoot bad queries
        Variables to adjust:
            query_cache_limit (> 1M, or use smaller result sets)

You should carefully read the output, especially the recommendations at the end. It shows which variables should be adjusted in the [mysqld] section of your my.cnf

If you are unsure about optimizing your database performance on your own, following MySQLTuner’s suggestions is one of the safer ways to do so.