MariaDB is a free and open-source relational database management system (RDBMS) that is based on MySQL. It is a popular choice for web applications, e-commerce platforms, and other high-traffic applications/websites.
Like most software, MariaDB can benefit from performance tuning. By adjusting certain settings and configurations, you can improve the speed and responsiveness of your database, which can lead to a better user experience and improved overall performance of your application. Optimized database performance also can help reduce hosting costs by getting the most out of the hardware that is available within your existing infrastructure.
In this article, we will discuss some of the most important aspects of tuning MariaDB for high performance. We will cover topics such as:
Hardware Optimization
The first step to tuning MariaDB for high performance is to optimize your hardware. This means ensuring that you have enough RAM, CPU cores, and storage space to handle the demands of your database workload.
Use More RAM and Faster RAM
The amount of physical RAM that MariaDB has access to directly affects its performance. More RAM means that more data can be stored in memory, which means that the processor has to access the slower disk storage less frequently. This translates to significantly improved performance.
Faster RAM also improves database performance because it allows the database to access data in memory more quickly. This is because faster RAM has a shorter access time, which means that the processor can read data from memory much faster.
In high traffic workloads, it is not always possible to store the entire dataset in memory. However, the minimum level of memory needed for high traffic workloads should be enough physical memory to house all of your table indexes. This will keep the most actively used data up front for easier access.
For example, if your database overall on-disk size is 200 GB, and the sum total of all your indexes is 92 GB, you will want at least 92 GB of physical RAM devoted to those indexes. You may also need additional RAM for other system needs, so you should aim for 96 GB or more of physical RAM.
Use Faster Disks
It is typically not possible to store the entire contents of your database directly in memory without spending some obscene amounts of cash. However, money aside, hardware has its physical limits and even the best databases require reading and writing to a physical disk for persistent storage. So disk speed is also important for database performance. If you are using slower disks, consider upgrading to faster disks. The following is the generic upgrade path for common disk types from slowest to fastest.
- HDD (Hard Disk Drive) is the oldest and slowest type of disk drive. It uses spinning disks to store data, which limits its performance.
- SDD (Solid State Drive) is a newer type of disk drive that uses flash memory to store data. This makes it much faster than HDDs, but it is also more expensive.
- NVMe SSD (Non-Volatile Memory Express Solid State Drive) is the fastest type of disk drive available. It uses a newer interface than SATA SSDs, which allows it to achieve even higher speeds.
- High Performance Hardware RAIDs are arrays of multiple disks that are configured to work together to provide increased performance and redundancy. They can be significantly faster than single drives, especially for random read and write operations.
Disk Type | Read Speed | Write Speed |
HDD | 120-150 MB/s | 80-100 MB/s |
SATA SSD | 500-550 MB/s | 400-450 MB/s |
NVMe SSD | 3,000-3,500 MB/s | 2,500-3,000 MB/s |
Hardware RAID | Up to 10,000 MB/s | Up to 7,000 MB/s |
Use More CPU Cores
Simply put, a server with more CPU cores can process more queries at the same time. So more CPU cores directly translates in to faster performance for your high-traffic websites and applications. Here are some of the benefits of using more CPU cores:
- Faster query processing: When multiple cores work at the same time, they can process queries more quickly. This is because each core can handle a different query, which can significantly reduce the overall processing time.
- Improved scalability: A server with more CPU cores can handle more traffic without experiencing performance bottlenecks. This is important for websites and applications that experience sudden spikes in traffic.
- Reduced latency: Latency is the time it takes for a server to respond to a request. A server with more CPU cores can typically respond to requests more quickly, which can improve the user experience.
When considering the number of CPU cores for your infrastructure, take these metrics into account:
- The number of queries you need to process: The more queries you need to process, the more CPU cores you will need.
- The type of queries you need to process: Some queries are more CPU-intensive than others. If you need to process a lot of CPU-intensive queries, you will need a server with more CPU cores.
- The budget you have: Servers with more CPU cores can be more expensive. You need to make sure that the cost of the server is justified by the performance benefits.
- Consult a DBA (Database Administrator): If you are unsure how many CPU cores you need, you should consult with a server administrator. They can help you determine the right number of CPU cores for your needs.
Use A Database Cluster
A database cluster is a group of database servers that are connected together to work as a single unit. This can improve MariaDB performance in a number of ways:
- Load balancing: A database cluster can distribute database queries across multiple servers, which can help to improve performance during periods of high load.
- Fault tolerance: If one server in a database cluster fails, the other servers can continue to operate, which can help to improve availability.
- Scalability: A database cluster can be scaled up or down as needed, which can help to improve performance and cost-efficiency.
Here are some specific examples of how using a database cluster can improve MariaDB performance:
- If you have a website that experiences spikes in traffic, a database cluster can help to ensure that your website remains responsive even during these periods.
- If you have a database that is used by multiple applications, a database cluster can help to improve performance by distributing queries across multiple servers.
- If you have a database that is growing rapidly, a database cluster can help to scale your database without affecting performance.
Overall, using a database cluster can be a great way to improve MariaDB performance, availability, and scalability. Here are some of the most popular database clusters for MariaDB:
- MariaDB Galera Cluster: This is a multi-master cluster that provides high availability and scalability.
- MariaDB MaxScale: This is a load balancer that can be used to distribute queries across multiple MariaDB servers.
- MariaDB SkySQL: This is a managed MariaDB service that provides high availability, scalability, and security.
Operating System Optimization
In addition to optimizing your hardware, you should also optimize your operating system for best MariaDB performance. This includes the following items:
- Running the latest version of your operating system. This will ensure that you have the latest performance improvements and bug fixes.
- Installing any relevant performance patches or updates. These updates can often improve the performance of your operating system, which can in turn improve the performance of MariaDB.
- Disabling unnecessary services and applications. These services and applications can consume resources and slow down your database. You can use the
ps
command to list all of the services and applications that are running on your server, and then disable any that you don’t need. - Tuning your operating system settings. There are a number of operating system settings that can affect the performance of MariaDB. You can consult your operating system documentation to learn how to tune these settings.
Here are some additional tips for optimizing your operating system for MariaDB performance:
- Use a high-performance file system. The file system that you use to store your MariaDB data can have a significant impact on performance. If you are using a low-performance file system, you may see a significant improvement in performance by switching to a high-performance file system, such as XFS or ZFS.
- Use a dedicated MariaDB server. If you are running other applications on the same server as MariaDB, you may want to consider using a dedicated MariaDB server. This will ensure that MariaDB has all of the resources that it needs to perform at its best.
- Monitor your performance. It is important to monitor your performance so that you can identify any bottlenecks. You can use the
top
command to view a real-time overview of your system resources, and thesar
command to collect detailed performance statistics.
By following these tips, you can optimize your operating system for best MariaDB performance. This will help to ensure that your database is running as efficiently and safely as possible.
Indexing
Indexes are a critical way to improve table performance in MariaDB. They allow the database to quickly find the rows that match a given criteria, without having to scan the entire table. This can have a significant impact on the performance of queries that involve joins, WHERE
clauses, and ORDER BY
clauses.
However, indexes also have a performance impact on writes. When a new row is inserted into a table with an index, the index must be updated as well. This can add some overhead to the insert operation.
The overall impact of indexing on MariaDB performance depends on the specific workload. If the workload is mostly reads, then indexes will generally improve performance. However, if the workload is mostly writes, then indexes may have a negative impact on performance.
Here are some general guidelines for using indexes in MariaDB:
- Only create indexes on columns that are frequently used in queries.
- Avoid creating indexes on columns that are frequently updated.
- Keep indexes as small as possible.
- Rebuild indexes periodically to keep them up-to-date.
By following these guidelines, you can optimize your MariaDB tables for maximum performance.
Here are some additional things to keep in mind about indexing in MariaDB:
- The more indexes a table has, the slower the
INSERT
operations will be. - The size of an index can also affect performance. Larger indexes will take longer to scan, which can slow down
SELECT
operations. - It is important to monitor the performance of your queries after you create indexes. If you notice that the performance of your writes has decreased, you may need to remove some of the indexes.
Overall, indexes are a powerful tool for improving the performance of MariaDB tables. However, it is important to use them wisely to avoid any negative performance impact.
Query Optimization
Query optimization is the process of rewriting queries to improve their performance. This can be done by using more efficient algorithms, avoiding unnecessary operations, and using indexes effectively.
There are a number of factors that can affect the performance of a query, including the size of the database, the complexity of the query, and the hardware configuration of the server. However, even simple queries can be improved by careful optimization.
Some common techniques for query optimization include:
- Using indexes to speed up lookups.
- Simplifying the query by removing unnecessary clauses.
- Using more efficient algorithms for joins and other operations.
- Avoiding full table scans.
- Using the right data types for columns.
The MySQL Query Optimizer is a complex piece of software, and it is important to understand how it works in order to optimize your queries effectively. Likewise, its important to understand the Differences Between the MySQL and MariaDB Query Optimizer.
By following these techniques and using the available tools, you can improve the performance of your queries and make your database more responsive.
Table Optimization
In addition to optimizing your queries and indexes, you can also improve the performance of your MariaDB database by optimizing your tables. This means removing unnecessary data, defragmenting your tables, and using the correct storage engine for your data.
Remove Unnecessary Data
One of the easiest and most overlooked ways to improve the performance of your tables is to remove unnecessary data. This can be done by deleting old or unused data, or by archiving data that you no longer need.
For example, if you have a table that contains a lot of data that is no longer needed, you can delete that data to free up space and improve performance. You can also archive the data to a different location, such as an external hard drive or a cloud storage service.
You can visualize this concept pretty easily by considering what items you would take on a week-long vacation with the family. You definitely cannot take the entire house with you, so you have to keep only enough that you don’t overload the vehicle to the point that it cannot move or you cannot operate inside it.
Defragment Tables
Another way to improve the performance of your tables is to defragment them. This means rearranging the data in your tables so that it is stored in contiguous blocks. This can help to improve the speed of queries that access large amounts of data.
You can defragment your tables using the OPTIMIZE TABLE
command. For example, to defragment the users
table, you would use the following command:
OPTIMIZE TABLE users;
Use The Correct Storage Engine
The storage engine that you use for your tables can also have a significant impact on performance. Some storage engines are better suited for certain types of data than others. Afterall, when packing up to move, you don’t throw a couple of ties into a refrigerator box and expect it to hold up. Likewise, you don’t disassemble your bicycle to fit it in the car when you have a perfectly suitable bike rack on top of the vehicle.
For example, if you have a table that contains a lot of small, frequently updated records, you should use InnoDB storage engine. InnoDB is a transactional storage engine that is designed to provide good performance for these types of tables.
If you have a table that contains a lot of large, infrequently updated records, you should use the MyISAM storage engine. MyISAM is a non-transactional storage engine that is designed to provide good performance for these types of tables.
There are several other storage engines available in MariaDB that are designed to meet specific use-cases and MariaDB documentation has a great article for helping understand and select from one of these storage engines: MariaDB: Choosing the Right Storage Engine
Memory Allocation
MariaDB uses memory to store various data structures, such as the query cache, the table cache, and the buffer pool. By allocating the correct amount of memory to these data structures, you can improve the performance of your database. Please consider the following when allocating memory:
- The size of your database
- The number of concurrent connections
- The types of queries that are being run
Query Cache
The Query Cache stores the results of recently executed queries. This can improve performance by reducing the number of times that the database has to re-execute the same query.
The size of the query cache should be set to a value that is large enough to store the results of the most frequently executed queries. However, it is important to avoid allocating too much memory as this can impact the performance of other data structures.
Note: Due to Limitations, Query Cache cannot be enabled in certain environments.
Table Cache
The table cache stores information about the tables in your database. This information includes the table’s structure, the location of the table’s data on disk, and the most recently used data in the table.
The size of the table cache should be set to a value that is large enough to store information about all of the tables in your database. However, it is important to not allocate too much memory to the table cache, as this can impact the performance of other data structures.
Buffer Pools
The buffer pool stores the data pages of your database in memory. This has significant performance benefits by reducing the number of times that the database has to read data from those clunky disk drives.
The size of the buffer pool should be set to a value that is large enough to store the most frequently accessed data pages in your database. However, it is important to not allocate too much memory to the buffer pool, as this can impact the performance of other data structures.
Memory Tuning Tools
The best way to tune memory allocation for your MariaDB database is to experiment with different values and see what works best for your specific application. You can use the following tools to monitor the performance of your database and track the impact of changes to memory allocation:
- The
SHOW GLOBAL STATUS;
command - The
sysbench
tool - The
MySQLTuner
tool
System Variables
MariaDB has a number of system variables that can affect performance. By adjusting these variables, you can fine-tune the performance of your database.
System variables can affect performance in a number of ways. For example, some system variables can control the size of the database’s cache, which can affect how quickly queries are executed. Other system variables can control the number of concurrent connections that the database can handle, which can affect the overall throughput of the database.
The best way to find the right system variables to adjust is to monitor the performance of your database. You can use a tool like MySQL Workbench or MariaDB Monitor to collect performance metrics. Once you have a good understanding of your database’s performance, you can start to adjust the system variables to see if they improve performance.
Below are some system variables that commonly affect performance in MariaDB. It is important to monitor the performance of your database after making changes to system variables to make sure that they have the desired effect. Also adjust only one variable at a time else it will be difficult to determine which one(s) are causing an undesired outcome.
INNODB_BUFFER_POOL_SIZE
This variable controls the size of the database’s buffer pool. The buffer pool is a cache that stores data pages from the database files. A larger buffer pool can improve performance by reducing the number of times that the database has to read data from the disk.
INNODB_FLUSH_LOG_AT_TRX_COMMIT
This variable controls whether or not the database flushes the transaction log to disk at every commit. Flushing the transaction log to disk can improve performance by reducing the number of times that the database has to read the transaction log from disk. However, it can also reduce the durability of the database, as it means that there is a longer period of time between when a transaction is committed and when it is guaranteed to be durable.
MAX_ALLOWED_PACKET
This variable defines the maximum size of a packet that can be sent between the client and the server. The default value of this variable is 16MB. However, if you are working with large datasets or queries, you may need to increase the value of this variable. This is important for performance because it can prevent the server from dropping large packets. If the value of this variable is too low, the server may drop large packets, which can cause performance problems.
MAX_CONNECTIONS
This variable controls the maximum number of concurrent connections that the database can handle. A higher value for this variable can improve performance by allowing more users to access the database at the same time. However, it can also increase the load on the database server, so it is important to set this value appropriately.
QUERY_CACHE_SIZE
This variable controls the size of the database’s query cache. The query cache is a cache that stores the results of recently executed queries. A larger query cache can improve performance by reducing the number of times that the database has to execute the same query.
THREAD_CACHE_SIZE
This variable controls the number of threads that the database can cache. A larger thread cache can improve performance by reducing the number of times that the database has to create new threads.
These are just a few of the many system variables that can affect performance. The specific variables that you need to adjust will depend on your specific configuration and the types of queries that are being run.
Benchmarking
One of the best ways to tune MariaDB for high performance is to benchmark your database. This means running a series of tests to measure the performance of your database under different conditions. There are a number of different tools that you can use to benchmark your database, such as:
sysbench
: This is a popular open-source benchmarking tool that can be used to measure the performance of a variety of different database systems.- Database Workbench: This is a graphical tool that can be used to benchmark MySQL and MariaDB databases.
- Percona Toolkit: This is a set of tools that can be used to benchmark MySQL and MariaDB databases, as well as to monitor and tune their performance.
Once you have chosen a benchmarking tool, you need to decide what kind of tests you want to run. Some common benchmarks include:
- Throughput: This measures the number of queries that can be executed per second.
- Latency: This measures the time it takes to execute a single query.
- Memory usage: This measures the amount of memory that the database is using.
- CPU usage: This measures the amount of CPU that the database is using.
Once you have run your benchmarks, you need to analyze the results to see where performance can be improved. You can then use the information from your benchmarks to make changes to your configuration and settings.
Here are some tips for benchmarking your MariaDB database:
- Use a consistent workload. This means running the same set of queries each time you run a benchmark.
- Run the benchmarks multiple times. This will help to ensure that the results are accurate.
- Use a variety of different benchmarks. This will give you a more complete picture of the performance of your database.
- Compare the results of your benchmarks to the results of other databases. This will help you to see how your database is performing relative to others.
Conclusion
MariaDB is a powerful and scalable database that can be used for a variety of applications. However, in order to achieve high performance, it is important to tune the database properly. This article has discussed some of the most important factors to consider when tuning MariaDB, including:
- Hardware configuration: The hardware that MariaDB is running on can have a significant impact on performance. It is important to ensure that the database has enough RAM, CPU, and disk I/O bandwidth to meet the demands of the application.
- Database configuration: The configuration of the database itself can also affect performance. Some important settings to consider include the buffer pool size, the query cache size, and the
MAX_ALLOWED_PACKET
value. - Application design: The way that the application is designed can also impact performance. For example, using efficient queries and avoiding unnecessary joins can help to improve performance.
By following the tips in this article, you can tune your MariaDB database for high performance. This will help to ensure that your application is able to handle even the most demanding workloads.
NameHero
If you are having trouble with your database performance, reach out to us via phone, chat, email, or ticket and one of our Everyday Heroes will work with you to find the right balance of cost and performance for your critical applications.
Jason Potter is a Senior Linux Systems Administrator & Technical Writer with more than 20 years experience providing technical support to customers and has a passion for writing competent and thorough technical documentation at all skill levels.
Leave a Reply