MariaDB Best practices for Optimization & Tuning

InnoDB file-per-table

InnoDB Buffer Pool Size

Max Connections

Disable Swap In MySQL

Tune Your Table Cache

How to deal Query Cache

Tune/Increase Open Files Limit

InnoDB file-per-table

After setting this parameter all the tables will have their own .idb file on server

innodb_file_per_table=1

MariaDB 10.x, innodb_file_per_table=1 is a default setting. if so your new InnoDB tables and those converted from MyISAM to InnoDB will already have separate tablespaces.

if you are using MariaDB 5.5, innodb_file_per_table=0 by default. 

To start using individual tablespaces, run SET GLOBAL innodb_file_per_table = 1;

To make the option permanent, add it to the cnf file.

InnoDB Buffer Pool Size

We can set the parameter to 60 percent of your memory

The default value of Buffer pool size is 8MB and you can change this value by adding the following line in your my.cnf file

innodb_buffer_pool_size = 1G

Disable Swap In MySQL  

Max Connections

Tune Your Table Cache 

SHOW GLOBAL STATUS LIKE 'Open%table%';

For table_open_cache, it shall be the total number of your tables

 but it's best you add more depending on the type of queries you serve since temporary tables shall be cached as well.

 For example, if you have 500 tables, it would be reasonable you start with 1500. 

While your table_open_cache_instances, start setting it to 8. 

This can improve scalability by reducing contention among sessions, 

the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances.

Dealing with Query Cache

Preferred option -  We think that disabling the query cache to improve the performance of MariaDB is the preferred option. 

You need to make sure that query_cache_type=OFF and query_cache_size=0 so that the query cache is completely disabled. 

In contrast to MySQL, MariaDB still supports query cache and doesn’t plan to withdraw support for it anytime soon.

There are those who think that using query cache gives them performance benefits,

but as this post from Percona demonstrates, 

an enabled query cache increases overhead and reduces server performance.

If you want to use query cache, ensure that you monitor it by running 

SHOW GLOBAL STATUS LIKE ‘Qcache%’;. 

Qcache_inserts reports on how many queries have been added to the query cache,

Qcache_hits shows how many have made use of it, and 

Qcache_lowmem_prunes contains the number of queries that have been dropped because of insufficient memory. 

Over time, using query cache may cause it to become fragmented.

A high Qcache_free_blocks to Qcache_total_blocks ratio may point to increased fragmentation. 

To defragment it, run FLUSH QUERY CACHE. This will defragment the query cache without dropping any queries and improve MariaDB performance.

Tune/Increase Open Files Limit

To ensure good server performance, the total number of client connections, database files, and log files must not exceed the maximum file descriptor limit on the operating system (ulimit -n). Linux systems limit the number of file descriptors that any one process may open to 1,024 per process. On active database servers (especially production ones) it can easily reach the default system limit.

To increase this, edit /etc/security/limits.conf and specify or add the following:

1 mysql soft nofile 65535

2

3 mysql hard nofile 65535

 

This requires a system restart. Afterwards, you can confirm by running the following:

1 $ ulimit -Sn

2

3 65535

4

5 $ ulimit -Hn

6

7 65535

 

 

Optionally, you can set this via mysqld_safe if you are starting the mysqld process thru mysqld_safe,

1 [mysqld_safe]

2

3 open_files_limit=4294967295

or if you are using systemd,

1 sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF

2 

3 [Service]

4

5 LimitNOFILE=infinity

6

7 EOF

8 

9 sudo systemctl daemon-reload

Increase Open Files Limit

 

Increase Open Files Limit

To ensure good server performance, the total number of client connections, database files, and log files must not exceed the maximum file descriptor limit on the operating system (ulimit -n). Linux systems limit the number of file descriptors that any one process may open to 1,024 per process. On active database servers (especially production ones) it can easily reach the default system limit.

To increase this, edit /etc/security/limits.conf and specify or add the following:

1 2 3

mysql soft nofile 65535   mysql hard nofile 65535

This requires a system restart. Afterwards, you can confirm by running the following:

1 2 3 4 5 6 7

$ ulimit -Sn   65535   $ ulimit -Hn   65535

Optionally, you can set this via mysqld_safe if you are starting the mysqld process thru mysqld_safe,

1 2 3

[mysqld_safe]   open_files_limit=4294967295

or if you are using systemd,

1 2 3 4 5 6 7 8 9 10 11

sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF   [Service]       LimitNOFILE=infinity   EOF   sudo systemctl daemon-reload

Max Connections - mariadb/mysql

 

Max Connections

Max connection parameter in MySQL shows how many concurrent connections can be initiated on your MariaDB server. default is 151.

First thing to decide is what new maximum value you want to set for max_connections

Considerations to take into account when increasing the number of MySQL/MariaDB connections.

The maximum number which can be supported by the system will depend on:

1)The amount of available RAM.

2)How much RAM each connection takes (simple queries will require less RAM than more labor-intensive connections).

3)The acceptable response time.

  According to the MySQL documentation, most Linux systems should be able to support 500-1000 connections without difficulty.

Systems that get too busy can return the too_many_connections error.

When the number of threads_connected exceeds the max_connections server variable, it's time to make a change.

Viewing the threads_connected status variable shows only the current number of connections,

but it's more useful to see what the value has peaked at, and this is shown by the max_used_connections status variable.

This error may be a symptom of slow queries and other bottlenecks,

 but if the system is running smoothly this can be addressed by increasing the value of max_connections.

What Is The Source Of The Database Connections?

Use the ‘SHOW processlist’  SQL command to show you which threads are currently running.

It will, for instance, provide the following details:

• User – The MySQL user who issued the statement.
• Host – The hostname of the client issuing the statement.
• Command – The SQL command that is executed.

The ‘show processlist SQL command:

SHOW processlist

How to change max_connections

The max_connections variable will need to be changed in two places:

1. Update the my.cnf file, so that the new value is used if the MySQL/MariaDB server is restarted.
2. Use the SET GLOBAL command to update the value on the running MySQL/MariaDB server.

Fortunately, by using this method, you will not need to restart MySQL/MariaDB, and therefore will not need to experience any downtime.

Show the Current max_connections Value

To see the current number of max_connections log in to the MySQL/MariaDB command line client with the command:

mysql -u root -p

Use the command:

SHOW variables;

This will output a list of all of the variables which are set for MySQL/MariaDB. Scroll up through the list to find the value for max_connections.

Update my.cnf

Open the file /etc/my.cnf for editing with the command:

sudo nano /etc/my.cnf

Directly beneath the first line:

[mysqld]

Add a line:

max_connections=[desired new maximum number]

For example, to set max_connections to 200, the first two lines of the file will read:

[mysqld]  max_connections=200

Save and exit the file.

SET GLOBAL

Log in to the MySQL/MariaDB command line client with the command:

mysql -u root -p

Set the new max_connections value with the command:

SET GLOBAL max_connections=[desired new maximum number];

For example, to set max_connections to 200, the command is:

SET GLOBAL max_connections=200;

Exit MySQL/MariaDB with the command:

quit;