MariaDB Performance Tuning
Guide

MariaDB is a major player in the open-source database world, originally developed as a fork of MySQL. It's known for its reliability and feature-rich environment powering countless applications with efficiency and speed. rich set of features that make it a go-to choice for many applications. To keep your MariaDB server running at its best, fine-tuning is essential. In this guide, we'll cover practical tips to optimize your setup, like adjusting configuration settings, using effective indexing strategies, and monitoring performance metrics.

To effectively tune MariaDB, it's important that you get familiar with its detailed documentation. This will help you better understand configuration settings and how they impact MariaDB’s performance. Check out these resources to learn more:

• MariaDB Reference Manual
• MariaDB Server Documentation Library
• Comprehensive list of variables that impact database performance from Releem

Not all settings have a profound impact on performance. But for those few key variables that do, you want to learn how to configure them properly according to your specific workload requiremnts and available hardware.

The following resources cover a wide range of topics, including important variables, how connections impact performance, and InnoDB optimization help:

• Configuring MariaDB for Optimal Performance
• InnoDB Performance Optimization Basics

When you are comfortable with MariaDB documentation and configuration best practices, you can turn to your monitoring data as a source of valuable information on your servers health. Monitoring tools allow you to monitor key performance metrics and figure out where improvements can be made.

• Zabbix is an open-source tool for monitoring networks, servers, cloud environments, applications, and services. It's highly secure and scalable.
• Prometheus is an open-source monitoring software known for its simplicity and powerful visualization tools.
• Percona Monitoring and Management focuses on improving database performance and data security, offering useful insights and tools for DBAs.
• Releem is perfect for monitoring MySQL and MariaDB databases. Releem offers a user-friendly solution with automatic configuration recommendations to improve performance and query optimization assistance.

Keeping tabs on CPU usage reveals how much processing power your MariaDB server is using. If you notice consistently high CPU usage, it might mean your server is handling a heavy load or there's room for improving your queries and configuration. You could also upgrade your hardware.

Watching memory usage gives you insight into how much RAM your MariaDB server is consuming. If memory usage is nearing its limit, performance can take a hit. Seeing swap usage means your system is compensating with disk space, a clear sign that more RAM might be needed.

Disk I/O metrics show how quickly data is read from and written to the disk. High disk I/O can slow things down, especially for large databases or heavy transaction volumes. If you see high read/write latency, your storage might be lagging behind, while high throughput suggests frequent large data transfers.

Tracking active connections helps you manage your MariaDB server's load. A high number of connections can lead to resource contention, which may slow down your server. Connection errors could indicate configuration problems or that the server is reaching its resource limits.

Error rates are useful for spotting and fixing issues with your MariaDB server, like failed queries or connection problems. A sudden spike in errors can signal deeper issues that need immediate attention, while recurring specific errors might point to configuration or coding problems that need to be addressed.

Before you can start making performance optimizations your server needs to be running for a minimum of 24 hours. At that point, there’s enough data to check the status of server variables.
Run the SHOW GLOBAL STATUS; command to pull up various performance metrics and look for outliers that could benefit from some tweaking.

With Releem, you can easily monitor 19 important metrics with health checks across the 3 categories listed below – cache performance metrics, database efficiency metrics, and temporary data metrics:

By analyzing hit rates and fragmentation for specific cache types—such as thread, table, MyISAM, and InnoDB—you can uncover bottlenecks and optimization opportunities. Tracking these metrics allows you to fine-tune your caching strategy, guaranteeing rapid data access and optimal cache utilization. Key metrics include:

• Thread Cache Hit Ratio
• Thread Cache Ratio
• MyISAM Cache Hit Rate
• InnoDB Cache Hit Rate
• Table Cache Hit Rate
• QCache Fragmentation

By examining write ratios, you can understand the balance between data writes and reads, which sheds light on your database's ability to handle modifications. Monitoring log file sizes helps you detect inefficiencies in data logging and storage, as rapid growth might signal underlying issues. Sort merge passes reveal the efficiency of data sorting operations – a higher count may indicate the need for better memory management or indexing. Key metrics include:

• MyISAM Key Write Ratio
• InnoDB Log File Size
• Sort Merge Passes Ratio
• Flushing Logs

Monitoring the creation and management of temporary data during operations highlights inefficiencies in query execution or storage systems. For example, high levels of Temporary Disk Data – the amount written to disk during queries – can signal the need for configuration tweaks to better manage this data.

Since MariaDB is a fork of MySQL, it shares a very similar architecture. This means most scripts that work for MySQL also work with MariaDB. The following tools can be used to extract configuration recommendations by analyzing your current configuration and metrics:

• MySQLTuner – This Perl script offers performance suggestions and identifies potential security issues.

• Tuning-Primer Script – This script provides recommendations based on "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..." commands. Though the original is no longer maintained, a fully supported version for MariaDB is available on GitHub.

• Percona Toolkit – A suite of open-source command-line tools that simplify database management, allowing you to focus on more strategic tasks.

• Mysqlreport – Converts SHOW STATUS data into a user-friendly report, giving a clear snapshot of your database performance. From MariaDB 10.4.6 onward, mysqlreport is linked to mariadb-report, and from 10.5.2, mysqlreport is the primary tool.

At the core of MariaDB tuning is the calculation of the best values for various performance parameters. This process can be time-consuming and complex. With Releem, DBAs can avoid all the associated manual work of these calculations. Releem automates the tuning process from start to finish, handling the optimizing for you. All you have to do is apply the recommended configuration.

• thread_cache_size: Controls the number of threads to be cached for reuse.
• query_cache_type: Determines the type of query caching mechanism used.
• query_cache_size: Sets the size of the query cache in bytes.
• query_cache_limit: Defines the maximum size of a single query that can be cached.
• query_cache_min_res_unit: Specifies the minimum result size in bytes for caching.
• key_buffer_size: Sets the size of the buffer used for index blocks in MyISAM tables.
• max_allowed_packet: Defines the maximum size of a packet that can be sent between the client and server.
• max_heap_table_size: Determines the maximum size of a heap table in bytes.
• tmp_table_size: Sets the maximum size of internal in-memory temporary tables.
• innodb_file_per_table: Controls whether InnoDB creates a separate file for each table.
• sort_buffer_size: Specifies the buffer size for sorting operations.
• read_rnd_buffer_size: Sets the buffer size for random read operations.
• bulk_insert_buffer_size: Controls the buffer size for bulk insert operations.
• myisam_sort_buffer_size: Specifies the buffer size for sorting MyISAM indexes during repair.
• innodb_buffer_pool_chunk_size: Determines the size of each chunk in the InnoDB buffer pool.
• join_buffer_size: Sets the buffer size for join operations.
• table_open_cache: Controls the number of open tables that can be cached.
• table_definition_cache: Determines the number of table definitions to be cached.
• innodb_flush_log_at_trx_commit: Controls when logs are flushed during a transaction commit.
• innodb_log_buffer_size: Specifies the size of the InnoDB log buffer. innodb_write_io_threads: Sets the number of I/O threads for writing to the InnoDB buffer pool.
• innodb_read_io_threads: Sets the number of I/O threads for reading from the InnoDB buffer pool.
• innodb_flush_method: Determines the method used for flushing data to InnoDB data files.
• innodb_thread_concurrency: Controls the number of user threads allowed inside InnoDB concurrently.
• optimizer_search_depth: Specifies the depth of the search tree for the query optimizer.
• innodb_purge_threads: Sets the number of threads used for purging operations in InnoDB.
• thread_handling: Determines how threads are managed by the server.
• max_connections: Controls the maximum number of concurrent connections to the server.
• innodb_buffer_pool_size: Sets the size of the InnoDB buffer pool. innodb_log_file_size: Specifies the size of the InnoDB log file.
• thread_pool_size: Determines the number of threads in the thread pool.

Optimizing SQL queries is also critical for overall MariaDB performance. Inefficient queries can lead to high resource usage and slow response times.

MariaDB offers a helpful tool called the EXPLAIN statement, which reveals exactly how your queries are processed It provides a step-by-step breakdown, showing which parts of the query are most resource-intensive. This helps you determine if your queries are efficient or could use some work.

The Slow Query Log records queries that exceed a specified duration to execute. For example, if you set the long_query_time to 2 seconds, the log will contain all queries that take more than 2 seconds to complete.

To start using the Slow Query Log, you need to enable it. Edit your my.cnf or my.ini file to add the following:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2

Focus on the queries that appear most often in the log since they likely affect performance the most. Pay attention to execution times to see which queries are the slowest. Also, review the number of rows processed to gauge how much they are taxing your database resources.

While the Slow Query Log helps in identifying slow queries, Releem’s Query Analytics feature goes further by providing a comprehensive view of your MariaDB server's query performance. It automatically detects inefficient queries that consume excessive resources, such as CPU, memory, and disk I/O. The Query Analytics feature ranks your top queries based on execution time and total load, offering detailed insights into which queries need optimization.

With real-time monitoring and an intuitive interface, Releem simplifies the process of query optimization, saving you valuable time and effort by allowing you to quickly identify and address performance issues.

After reviewing your database's performance and deciding which variables need changes, it's time to set up a new configuration file with these adjustments:

1. Create a backup of your existing configuration file. This acts as a safeguard if you need to revert to the original settings.
2. Open the configuration file (my.cnf) in a text editor.
3. Modify the configuration settings based on your performance needs, manual calculations, or recommendations from tools like Releem.
4. Save the updated configuration file with the .cnf extension in the appropriate directory so that MariaDB can access and apply the changes.

After preparing your new configuration file, you need to restart the MariaDB server to activate the changes:

• On Linux, use the command sudo systemctl restart mariadb.
• On Windows, open the Services management console (services.msc), find MariaDB or MySQL, right-click it, and select Restart.

Releem simplifies the often complex and time-consuming process of MariaDB optimization for database administrators. Instead of manually sifting through performance metrics and configuration settings, Releem automatically analyzes your server workload, database usage patterns, and overall system health. It pinpoints specific areas where performance is lagging, such as inefficient queries or suboptimal configuration settings.