MariaDB Optimization & Performance Tuning Guide

1. Swappiness Configuration

Swappiness is a critical kernel parameter that controls how aggressively the Linux kernel swaps memory pages from RAM to disk. For database servers like MariaDB, proper swappiness configuration is essential to prevent service crashes and maintain optimal performance.

Recommended Values

Checking Current Swappiness

To verify your current swappiness configuration, use the following command:

sudo sysctl -a | grep vm.swappiness

Expected output:

vm.swappiness = 1

Modifying Swappiness

To permanently change the swappiness value:

1. Open the custom overrides configuration file:

   sudo nano /etc/sysctl.d/custom-overrides.conf

2. Locate or add the swappiness directive:

   vm.swappiness = 1

3. Change the value as needed (e.g., to 5 for NVMe systems)
4. Save the file and exit the editor
5. Reboot the server:

   sudo reboot

6. After reboot, verify the new setting:

   sudo sysctl -a | grep vm.swappiness

2. Understanding Schemas and Databases in MySQL/MariaDB

In MySQL and MariaDB, the terms "schema" and "database" are often used interchangeably, but they have subtle technical differences that are important to understand.

Viewing Databases

To list all databases in your MariaDB installation:

sudo mysql
MariaDB [(none)]> SHOW DATABASES;

Sample output showing default databases:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3. Performance Schema Configuration

The Performance Schema is a powerful feature in MariaDB that collects and provides access to performance-related data, enabling database administrators to monitor and optimize server performance.

Performance Schema vs SYS Schema

Enabling Performance Schema

MariaDB includes both Performance Schema and SYS Schema by default, but the Performance Schema needs to be enabled in the configuration.

1. Navigate to the configuration directory:

   cd /etc/mysql/mariadb.conf.d

2. Create a backup of the configuration file:

   sudo cp 50-server.cnf 50-server.cnf.back

3. Open the configuration file:

   sudo nano 50-server.cnf

4. Locate the [mysqld] section and add the following directives under the # * Temp dir comment:

   # Enable Performance Schema
   performance_schema = ON
   performance-schema-instrument = 'stage/%=ON'
   performance-schema-consumer-events-stages-current = ON
   performance-schema-consumer-events-stages-history = ON
   performance-schema-consumer-events-stages-history-long = ON

5. Save the file and exit (Ctrl+X, then Y, then Enter)
6. Restart MariaDB to apply changes:

   sudo systemctl restart mariadb

Creating a Bash Alias for Quick Restart

To simplify frequent MariaDB restarts during configuration, create a bash alias:

nano ~/.bash_aliases

Add the following line:

alias mariare='sudo systemctl restart mariadb'

Save, exit, and reload your shell:

exit
# Log back in

Now you can restart MariaDB simply by typing:

mariare

4. Query Cache Management

The MySQL Query Cache is a reserved memory area within the MySQL daemon designed to store query results for instant retrieval when the same query is executed again.

Why Query Cache is Disabled by Default

Checking Query Cache Status

To verify query cache support and status:

sudo mysql
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';

Expected output:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

To check if the query cache is enabled:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';

Key variable to check:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

MariaDB Console Tips

5. Hostname Resolution Optimization

When a new client connects to MySQL/MariaDB, the server creates a new thread to handle the request. By default, this thread performs a time-consuming DNS resolution process that negatively impacts performance.

Disabling Hostname Resolution

To eliminate this overhead, configure MariaDB to use only IP addresses:

1. Navigate to the configuration directory:

   cd /etc/mysql/mariadb.conf.d

2. Open the server configuration file:

   sudo nano 50-server.cnf

3. Locate the commented directive (usually under the [mysqld] section after the Performance Schema directives):

   # skip-name-resolve

4. Uncomment it by removing the hash symbol:

   skip-name-resolve

5. Save and exit (Ctrl+X, Y, Enter)
6. Restart MariaDB:

   mariare

6. Log File Management

MariaDB log files can accumulate rapidly and consume significant disk space, especially on smaller servers. Uncontrolled log growth can lead to MariaDB becoming unresponsive or even causing server crashes.

Binary Log Retention

The expire_logs_days parameter controls how long MariaDB retains binary log files. By default, this is set to 10 days, which can be excessive for smaller servers.

Configuring Log Retention

Follow these steps to reduce log retention from 10 days to 3 days:

1. Log into MariaDB:

   sudo mysql

2. Check the current setting:

   MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';

   Expected output:

   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | expire_logs_days | 10    |
   +------------------+-------+

3. Set the new value (runtime change):

   MariaDB [(none)]> SET GLOBAL expire_logs_days = 3;

4. Flush binary logs to rotate and apply changes:

   MariaDB [(none)]> FLUSH BINARY LOGS;

   Important: Flushing binary logs ensures proper rotation and prevents corruption of MySQL transactional logs.
5. Exit MariaDB:

   MariaDB [(none)]> EXIT;

Making the Change Permanent

To ensure this setting persists across server reboots:

1. Open the configuration file:

   sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

2. Search for the directive (Ctrl+W, then type "expire"):

   Ctrl+W
   expire

3. Locate the line:

   expire_logs_days = 10

4. Change it to:

   expire_logs_days = 3

5. Save and exit (Ctrl+X, Y, Enter)
6. Restart MariaDB:

   mariare

7. Verify the change:

   sudo mysql
   MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';

   Expected output:

   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | expire_logs_days | 3     |
   +------------------+-------+

7. InnoDB Storage Engine Optimization

InnoDB is the default and recommended storage engine for MariaDB, providing a balance of high reliability and high performance. Proper configuration of InnoDB parameters is crucial for optimal database performance.

What is a Storage Engine?

A database storage engine is the underlying software component that a database management system uses to create, read, update, and delete (CRUD) data from a database. InnoDB specifically excels at handling concurrent transactions and maintaining data integrity.

Critical InnoDB Parameters

Configuration Formula Summary

Correct Procedure for Changing InnoDB Settings

Step-by-Step Configuration

Step 1: Check Current Values

sudo mysql
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer%';

Expected default output:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

Check log file size:

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log%';

Default output:

+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 100663296|
+---------------------------+----------+

EXIT;

Step 2: STOP MariaDB Service

sudo systemctl stop mariadb

Step 3: Modify Configuration

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Search for the InnoDB section (Ctrl+W, then type "innodb"):

Ctrl+W
innodb

You'll find a commented example:

# innodb_buffer_pool_size = 8G

Below this, add your custom values. For a 1GB server:

innodb_buffer_pool_size = 800M
innodb_log_file_size = 200M

Save and exit (Ctrl+X, Y, Enter)

Step 4: START MariaDB Service

sudo systemctl start mariadb

Step 5: Verify New Values

sudo mysql
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer%';
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log%';
EXIT;

Monitoring Server Resources

It's essential to monitor your server's RAM usage to ensure your InnoDB configuration is appropriate:

htop

In htop:

• Monitor the "Mem" bar to see total RAM usage
• Ensure sufficient RAM remains available for the operating system and other services
• Press 'Q' to quit htop

Summary & Best Practices

Configuration Checklist

Key Takeaways