MariaDB is forked from MySQL, MariaDB has been known to be a drop-in replacement that brings enhancements and performance optimizations over MySQL. This tutorial will show you how to install, optimize and tune on your server-side.

Step 1: Download/Install MariaDB

This is straightforward, if you don’t have installed yet simply run the following. If you already have installed then good, move to the next step directly. Remember to always verify the contents of a remote script before executing.

wget https://raw.githubusercontent.com/sayem314/MariaDB-Installer-for-Linux/master/install-mariadb.sh && bash install-mariadb.sh && rm install-mariadb.sh

On Debian/Ubuntu it would prompt for entering a root password. I would recommend choosing a strong/complex password.

Step 2: Securing MariaDB

Finish the installation by running mysql_secure_installation script to address several security concerns in a default MariaDB installation:

/usr/bin/mysql_secure_installation

In order to log into MariaDB to secure it, we’ll need the current password for the root user. If you’ve just installed MariaDB, and you haven’t set the root password yet, then the default password will be blank, so you should just press enter to continue.

Enter current password for root (enter for none):

OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorization.

Set root password? [Y/n] y

New password: MyPassword

Re-enter new password: MyPassword

Password updated successfully!

Reloading privilege tables..

… Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove the anonymous login before moving into a production environment.

Remove anonymous users? [Y/n] y

… Success!

Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess the root password over the network.

Disallow root login remotely? [Y/n] y

… Success!

By default, MariaDB comes with a database named ‘test’ that anyone can access. This is intended for testing only and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] y

– Dropping test database…

… Success!

– Removing privileges on test database…

… Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y

… Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

Now, you have to restart MariaDB.

service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

That’s it. To log in to MariaDB as the root user:

mysql -u root -p

When prompted, enter the root password you assigned when the mysql_secure_installation script was run.

Step 3: Tuning/Optimization

Tuning/Optimization are about utilizing as many resources as possible, not reducing resource utilization. No one buys a Ferrari and modifies it to go slower! A well-tuned MySQL server can perform 2-3x better than a poorly tuned MySQL server. Optimizing MySQL/MariaDB is very important to get right the first time. There are really only few settings that need to be tuned/changed. This post will walk you through the steps for determining if MySQL/MariaDB needs to be tuned, and if it does how to tune the settings.

This is how MySQL/MariaDB works on the server-side:

• Server A: Lots of CPU power and good storage system.

E5-2650v3

8GB RAM

SATA RAID 10 with 4 disks

10GB of MySQL data

• Server B: Decent CPU, not a ton of cores, lots of RAM, basic storage system.

E3-1270v3

16GB RAM

SATA RAID 1 with 2 disks

10GB of MySQL data

The question is which server will perform better? You might think that because the E5 box has a lot of cores, costs a lot of money and has RAID 10 it’s better. However, if you run some Sysbench OLTP read tests on these boxes Server B will more than likely to crush server A. Why? RAM, that’s why.

Here is what happens if I send a SELECT query to the server A:

Step 1) Server gets my request, goes to find my data, there’s a 4/10 chance that it’s in RAM. Just so happens my data is not in RAM.

Step 2) Server sends my disk a request for my data, CPU sends the request really quickly, and then waits for the array to return my data.

Step 3) My disks took their time getting my data, let’s say they took 20ms (really long).

Step 4) My CPU fiddles it’s thumbs waiting on my data to be returned from the disks (into RAM). And finally gets the results and sends to me.

Here is what happens if I send a SELECT query to the server B:

Step 1) Server gets my request, goes to find my data, 100% chance it’s in RAM. Great!

Step 2) It takes less than 1ms for the RAM to find my data and return it to the CPU

Step 3) CPU sends the data to me.

Basically the server B could theoretically serve more than 10 requests in the amount of time the server A takes to return one request. There are four main areas to focus on in terms of server performance, RAM, CPU, DISK and NETWORK. RAM is the most important factor for MySQL performance. The fastest CPU will not help to speed up your database if it does not have correct amount of RAM. There has always been a severe bottleneck between the CPU and Harddrive, the solution to that is RAM. Reading data out of RAM is significantly faster than reading data from an SATA harddrive, or even an SSD. Because of this we want to try and fit as much data into RAM as possible to speed up response times.

CPU performance is ONLY a factor if you have very low IO WAIT and you are correctly utilizing RAM. If you notice that your CPU is constantly around 90%-100% then you may want to look into a CPU upgrade. In general, MySQL/MariaDB prefers faster cores instead of more cores.

How much RAM do I need?

Let’s say you have one database that totals 6GB in size. You would want at least 6GB of RAM for this database. You also need RAM for the OS and things like that so 8GB of RAM should be sufficient.

If you have 30GB of databases then, you will want at least 30GB of RAM. Obviously this can sometimes be cost prohibitive, but the fact is that you will want to put as much of your data into RAM as possible. This is the most important thing to improve MySQL/MariaDB performance. If you don’t have sufficient RAM on your server MySQL/MariaDB will perform poorly regardless of the my.cnf settings you try to change.

By utilizing as much RAM as possible you are not only making response time faster, you are also freeing up the servers DISK so that it can attend to other things, like writing data. This also makes your CPU much more efficient since it does not have to wait for the slow disk to process its request for data.

You can use below script to get MySQL/MariaDB memory usage, or memory usage for any other running process. Create a file named mem.sh, paste in the contents below.

#!/bin/bash

ps -C $1 -O rss | awk ‘{ count ++; sum += $2 }; END {count –; print “Number of processes =”,count; print “Memory usage per process =”,sum/1024/count, “MB”; print “Total memory usage =”, sum/1024, “MB” ;};’

Run chmod +x mem.sh then run ./mem.sh mysqld to get MySQL/MariaDB memory usage. You can also use this for other apps like apache, php or anything else.

Changes you should make on MySQL/MariaDB configuration file described below. Your configuration file (my.cnf) is located on /etc/my.cnf or /etc/mysql/my.cnf. If you have a 512MB server then I would recommend you keep the default settings.

innodb_buffer_pool_size

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, it’s an important aspect of MySQL/MariaDB tuning.

key_buffer_size

To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory.

innodb_log_file_size

For servers with SSDs you can raise this to 128MB, 256MB, or even 2GB. Keep in mind that the larger the logs are the longer a recovery might take. For spinning HDDs this is a problem since they are already slow, so recovery could take a long time. If you have SSDs, which is much faster, recovery would take only few more seconds or minutes if you set this really high.

## 160M for 1GB RAM, 450M for 2GB RAM 2G for 4GB RAM and 5G for 8GB RAM

innodb_buffer_pool_size = 64M

## 96M for 2GB RAM, 256M for 4GB RAM and 768M for 8GB RAM

key_buffer = 64M

## Changing this setting requires you to stop MySQL

innodb_log_file_size = 128M

If you have 256MB VPS then, you can lower default value slightly, this might decrease performance, but it would keep your VPS stable. I would not recommend you to install MariaDB on a system with less than 256MB RAM. You can use Percona Tools to get an optimized configuration files for your server.

Thank you to @sayem314 on LowEndTalk for contributing this tutorial!

• Author
• Recent Posts

SysAdmin

Latest posts by SysAdmin (see all)

• MikroVPS Kft – Hungarian Xen VPS with 10Gbit/s Uplinks - November 10, 2016
• Swissnode – Switzerland VPS Offer - November 3, 2016
• IO Zoom – DDoS Protected VPS in Los Angeles, Chicago and Miami - November 2, 2016