MySQL Configuration

An optimized MySQL configuration can have a significant impact on your database’s efficiency and performance. Default settings are designed for general use, not for maximizing the capabilities of your specific environment or workload. They are a starting point, sure, but to really get the most out of your database, you need to adjust key parameters.

Let’s dive in and see how a little customization can go a long way.

The out-of-the-box configuration of MySQL prioritizes broad compatibility over-optimized performance, making it a suitable starting point for a wide range of applications. However, this one-size-fits-all approach often falls short of meeting the specific demands of your workload, leaving much room for improvement.

By adjusting your configuration settings – you have various options to purposefully align your database's functionality with your workload requirements and operational goals. Here's why this is critical:

• Resource Utilization – Default settings can leave hardware capabilities underutilized. Custom configurations allow you to align MySQL's use of CPU, memory, and disk I/O with the actual capacity of your hardware.
• Latency – Configuring query cache and temporary table variables can dramatically decrease response times for data retrieval and complex queries. This directly translates to faster application performance and a better end-user experience.
• Scalability – Fine-tuning parameters like max_connections and table_open_cache can help your database scale to meet demand without compromising on performance. This means your system remains stable and responsive even as your user base expands or during unexpected spikes in traffic.
• Reliability and Availability – Properly configured servers are less prone to bottlenecks and failures. Adjustments in settings like innodb_log_file_size and innodb_flush_log_at_trx_commit can improve the reliability of data transactions and recovery mechanisms, guaranteeing high service availability.

Consider an e-commerce platform for creating online shops, marketplaces, and B2B apps – like Aimeos Laravel. We tested the performance of Laravel on an AWS EC2 instance (c5.xlarge) running Debian 11, with Apache as the web server and MariaDB 10.5 in its default configuration, managing a database size of 500 MB. We then tuned the configuration, changing nothing else, to monitor the impacts on performance. The results are impossible to ignore.

These performance enhancements can have profound real-world implications. A more responsive and efficient e-commerce platform can elevate the shopping experience. This leads to increased sales and customer retention.

If you are interested in seeing the results or exploring our other application performance tests, click here.

At the core of every MySQL database server lies its configuration file, either named my.cnf or my.ini, based on your operating system. This isn't just any file – it's the blueprint that dictates your server's behavior, from how fast it processes queries to how securely it locks away data. Understanding where this file lives, and how to speak its language, is key for database administrators (DBAs) and developers aiming to fine-tune their database systems.

The location of MySQL's configuration files can vary depending on your operating system, installation choices, and MySQL version. Here's a general guide to their locations:
Unix/Linux

• /etc/mysql/my.cnf
• /etc/my.cnf
• /usr/local/mysql/etc/my.cnf

MacOS

• Typically found at /usr/local/mysql/my.cnf

Windows

• Located within the MySQL installation directory, often C:\Program Files\MySQL\MySQL Server X.X\my.ini, where "X.X" denotes the version number.

It's important to note that MySQL might read multiple configuration files at startup, with server-wide settings in one file and more detailed settings in another. The server processes these files in a specific sequence, allowing configurations in later files to override those set in earlier ones.

MySQL's configuration files are intuitively organized into sections and parameters:

• Sections are marked by square brackets [ ], such as [mysqld] for server-specific settings.
• Parameters within these sections follow a key-value format, like innodb_buffer_pool_size=1G, which allocates 1 gigabyte of memory to the InnoDB buffer pool.

Here's a simplified configuration snippet as an example:

Here's a step-by-step guide to editing and applying changes to your MySQL server configurations:

Locate MySQL's configuration file (my.cnf or my.ini) using the paths provided earlier or by searching your system.

Before diving into any modifications, you’ll want to protect your existing MySQL setup. This protects your server in the event that your configuration changes cause unintended performance issues, service interruptions, or other problems. You can easily roll back to the backup file, where the MySQL configuration was known to be stable.

Either duplicate the configuration file to a secure directory or append a recognizable suffix to the original file's name, such as .backup or .orig. This approach not only protects your current setup but also provides a quick reference point for comparing changes should you need to troubleshoot or further refine your configurations.

Open the configuration file with a text editor you're comfortable with (such as Vim, Nano, or Notepad++). Make the desired changes carefully.

This is the most challenging part of the process. It requires a deep understanding of which variables influence specific behaviors and, critically, determining the optimal values for these variables to achieve the desired performance enhancements.

Remember to follow the syntax and structure rules, with parameters set in a key-value format.

After editing the configuration file, save your changes and close the editor. To apply the changes, you will need to restart the MySQL server. The command to restart MySQL varies depending on your system. Restarting your server will temporarily disrupt database access, so plan this step during off-peak hours.

Post-edit, most changes require restarting the MySQL server to become active. Yet, some parameters are dynamic, allowing on-the-fly adjustments with commands like
SET GLOBAL max_connections = 500;

Always verify which settings are dynamic to avoid unnecessary restarts.

Once the MySQL server is back up, verify that your changes have been applied successfully.
You can do this by checking the server's status or using commands like
SHOW VARIABLES LIKE 'innodb_buffer_pool_size' ;

to confirm specific configurations.

Experimentation is a part of optimizing MySQL, but it comes with risks. Implementing a change that degrades performance necessitates a quick rollback. To roll back your server, locate your backup configuration file.

Replace the current configuration file with the backup by copying the backup file back to the original location, thus overwriting the modified configuration file. After replacing the configuration file with the backup, restart your server.

NOTE: If you made changes directly in the database using commands like SET GLOBAL, note that these changes might not persist through a restart if they weren't added to the configuration file. In this case, you might not need to revert these changes manually.

For those seeking to bypass the complexity and hassle of manual tuning, Releem offers a sophisticated, straightforward, and user-friendly solution, with automated tuning options to help your database reach peak performance. This automated solution not only saves time but also introduces a level of precision and adaptability that manual adjustments can rarely match.

Releem gathers crucial performance metrics, like MySQL latency, queries per second (QPS), MySQL slow queries and CPU Utilization, to then leverage this data to recommend and implement configuration changes aimed at boosting performance.

The beauty of Releem lies in its ability to make these adjustments with just a click of a button – eliminating the need for you to comb through and change each setting manually.

Even with careful monitoring and planning, new database configurations can sometimes result in unexpected challenges. Releem makes returning to your previous settings incredibly easy and quick – if things don’t go as anticipated.

With just a single click, you can undo the most recent configuration changes. There’s no need for you to handle and create backup configuration files on your own.

Releem goes beyond the basics to fine-tune an extensive set of 41 key parameters (10 in the free tier), covering a broad spectrum of MySQL's functionality to improve your performance across the board. This extensive list optimizes everything from memory usage and query processing to connection handling.

Depending on the version of MariaDB/MySQL/Percona, Releem recommends values for the following variables:

Memory

• bulk_insert_buffer_size - The size of the cache tree in bytes per thread for MyISAM bulk inserts.
• innodb_adaptive_flushing_lwm - The percentage of the InnoDB buffer pool below which no adaptive flushing occurs.
• innodb_autoextend_increment - The increment size (in MB) for extending the size of an autoextending InnoDB system tablespace file when it becomes full.
• innodb_buffer_pool_chunk_size [Free tier] - Defines the chunk size for InnoDB buffer pool resizing operations.
• innodb_buffer_pool_instances [Free tier] - The number of regions that the InnoDB buffer pool is divided into.
• innodb_buffer_pool_size [Free tier] - The size in bytes of the memory area where InnoDB caches data.
• innodb_change_buffer_max_size - The maximum size of the change buffer as a percentage of the InnoDB buffer pool.
• innodb_change_buffering - Controls which types of operations are buffered in the InnoDB change buffer to enhance performance.
• innodb_max_dirty_pages_pct [Free tier] - Specifies the target percentage of dirty (modified but not yet written to disk) pages in the InnoDB buffer pool.
• 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.
• key_buffer_size [Free tier] - Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
• max_heap_table_size [Free tier] - The maximum size to which user-created MEMORY tables are permitted to grow.
• myisam_sort_buffer_size - The size of the buffer that is allocated when sorting MyISAM indexes.
• read_rnd_buffer_size - The size of memory for reads from MyISAM tables, and, for any storage engine.
• sort_buffer_size - The size in bytes of the memory for sort operations.
• tmp_table_size [Free tier] - The maximum size of internal in-memory temporary tables

Connections

• max_allowed_packet - The maximum size of one packet or any generated/intermediate string.
• max_connections [Free tier] - The maximum permitted number of simultaneous client connections.

Logs

• innodb_log_buffer_size - Size in bytes of the buffer for writing InnoDB redo log files to disk.
• innodb_log_file_size [Free tier] - The size in bytes of each log file in a log group.
• innodb_log_files_in_group - The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.
• innodb_redo_log_capacity [Free tier] - The size of the redo log files.

Cache

• Table_open_cache - The number of open tables for all threads.
• query_cache_limit - Do not cache results that are larger than this number of bytes.
• query_cache_min_res_unit - The minimum size (in bytes) for blocks allocated by the query cache.
• query_cache_size - The amount of memory allocated for caching query results.
• table_definition_cache - The number of table definitions (from .frm files) that can be stored in the definition cache.

Disk

• innodb_file_per_table - When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default.
• innodb_flush_log_at_trx_commit - Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
• innodb_flush_method - Defines the method used to flush data to InnoDB data files and log files, which can affect I/O Throughput.
• innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
• innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.

Threads

• innodb_page_cleaners - Number of page cleaner threads.
• innodb_purge_threads - Number of background threads dedicated to InnoDB purge operations.
• innodb_thread_concurrency - Defines the maximum number of threads permitted inside of InnoDB.
• thread_cache_size - The count of threads the server should cache for reuse.
• thread_handling - The thread-handling model used by the server for connection threads.
• thread_pool_size - The number of thread groups in the thread pool, which determines how many statements can execute simultaneously.
• thread_stack [Free tier] - The size of stack for each thread

Others

• optimizer_search_depth - Maximum depth of search performed by the query optimizer.
• transaction_prealloc_size [Free tier] - The size of memory block preallocated when a transaction is started.

If you want to deepen your understanding and enhance your skills in MySQL optimization, Releem has a wealth of resources available. Within our Help Center, you'll find detailed documentation covering more than 44 variables that are key to performance tuning, and we're always adding more.

We also have a comprehensive MySQL Performance Tuning Guide that covers eight essential steps for practical and effective MySQL tuning.