EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB performance tuning
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB performance tuning

MariaDB performance tuning

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

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,697 ratings)

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.

MariaDB performance tuning output 1

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.

MariaDB performance tuning output 2

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.

MariaDB performance tuning output 3

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.

output 4

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.

output 5

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 –

  1. MariaDB MaxScale
  2. MariaDB Timezone
  3. MariaDB Grant All Privileges
  4. MariaDB AUTO_INCREMENT
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more