Introduction to MariaDB performance tuning
MariaDB is an open-source database community; the performance tuning of MariaDB is used to increase the throughput of query execution. The performance tuning is based on different factors such as hardware and system optimization, I/O scheduler, increasing the open file limit, etc.; it also has many factors. Performance of databases or systems is an essential thing in software, or we can say any type of application as well as it also depends on the CPU performance. When we consider the memory factor in performance tuning, it is the most important factor for databases. It helps us adjust the server system variables, which comes under hardware optimization.
Syntax
optimize [no write operation to binlog | local] specified table [, specified table name 2]… [wait or no wait]
Explanation
In the above syntax, we use to optimize table command to increase the MariaDB database’s performance. Here we use specified table names as shown in the above syntax. Basically, optimize table it has two functions: it can be either used for defragmentation of the table or used for the InnoDB index that is dependent on the operation. In the above syntax, we also mention that if any write operation is performed or not, if performed, then it goes in wait state or either no wait state as shown.
How to perform performance tuning in MariaDB?
Let’s see how we can perform performance tuning in MariaDB as follows.
Performance tuning is depending on the following factor as follows.
1. Hardware Optimization
If we have a good quality of hardware, then we can easily increase the performance of databases. So we can improve the following hardware as follows.
Memory
Memory is a very important factor of performance tuning in which we adjust the server system variable. We can use more slots of RAM on the motherboard that increase the bus frequency, and there will be more efficiency between RAM and CPU; we can use the highest size of RAM.
Disks
Fast access of disk is not possible, or we can say it is critical because of data location or wecan say that where the data resides. The disk’s main point is to seek time, so select a disk with low seek time for fast access, and we can easily move the data.
Fast Ethernet
In Ethernet, we calculate the internet bandwidth.
CPU
We can say every time the processor gives fast results, the result of the processor depends on the situation or operations. If we need to send more data at that, we see the speed of the processor, so we can count the size of the cache, speed of bus and core count.
2. We can setting Disk I/O scheduler for MariaDB performance
When a user needs access to the same area on the disk, then the I/O scheduler optimizes the disk access in similar areas. The disk drive speed increases because the disk drive only goes to one location to find what it’s looking for, so response time is important. We have different tools to manage the I/O performance of MariaDB, such as noop and deadline. Both tools basically schedule the operation of the disk drive.
File system Optimizations
Basically, the filesystem is not an important factor of MariaDB performance. Currently, we have Linux filesystems such as ext4 and XFS. This filesystem is included in the Linux kernel, and it is widely supported and available on most Linux distributions.
3. Optimization and Indexes
Essentials of an Index
Consider we have a table, and we need to return some specific value without indexes, so there is only one way to return the specified value that goes through every row until we find the matching specified value. Now imagine we have a thousand records, so it is possible to see each and every row on the database; this is the inefficient consideration of speed. So the solution to this problem is that we can sort all records alphabetically by using order by clause, then we can get quick results, but it is not possible to sort all records. The best solution is to create an index as per our requirement.
Started with Indexes
We have different indexes such as primary key, unique indexes, plain indexes, and full-text indexes.
Primary Key
The primary key contains the unique key and cannot be null; it always has a unique value. Each table has only one primary key. In InnoDB, tables contain the primary key as a suffix.
4. Query Optimization
Basically, the optimizer is larger cost-based, and we always try to select the optimal plan for a query. Sometimes we don’t have sufficient information to select a perfect plan for query execution, so in this case, MariaDB provides different hints. We can use Join Order as well as we can also use specific indexes for the where clause.
5. Server System Variable
MariaDB has many different system variables that can be changed as per requirement.
6. Threads, Caches and Buffers
The Thread, caches and Buffers are used to improve the performance of MariaDB.
Examples
To enable InnoDB file – per – table by using the following statement.
Innodb_file_per_table= 1
Explanation
In the above statement, we use the InnoDB database engine to improve the performance of MariaDB. InnoDB is the default engine for MariaDB, so the question is how we can make MariaDB more efficient. All tables use a .idb file, and if you truncate that table at that time, we can reclaim that space by using enable InnoDB parameter that means we can change my.cnf file, as shown in the following screenshot.
Example: Max connection.
set global max_connections := 600;
Explanation
In the above example, max_connection is used to tell our server how many connections are permitted. Here we allowed 600 connections, as shown in the above statement. This is helpful to increase the performance of the MariaDB server. End result of the above statement we illustrate by using the following screenshot.
Example: Thread_cache_size
To improve the MaraiDB server’s performance, we can set the thread_cache_size to a high number, so first find the hit rate of the cache by using the following statement, then we set the size to thread_cache_size.
Show status like ‘Threads_created’;
The output of the above statement as shown in the below screenshot.
After that, we can find the hit rate of connection by using the following statement.
show status like ‘Connections’;
The output of the above statement as shown in the below screenshot.
Now set the size of the thread by using the following statement.
set global thread_cache_size = 20;
Explanation
In the above example, we set thread_cache_size to increase the performance of MariaDB.The output of the above statement as shown in the below screenshot.
Conclusion
We hope from this article you have understood about the MariaDB Performance Tuning. From this article, we have learned the basic syntax of Performance Tuning, and we also see different examples of select databases. From this article, we learned how and when we use MariaDB Performance Tuning.
Recommended Articles
This is a guide to MariaDB performance tuning. Here we discuss How to perform performance tuning in MariaDB along with the examples and explanation. You may also have a look at the following articles to learn more –
