Categories
Code

Installing MariaDB on Ubuntu 20.4

I’m setting up a new Database Cluster for work, we’re setting up servers, each on a seperate physical host and we’re going to configure MariaDB 10.6 (current release) on them, with master/slave/slave replication behind MariaDB’s MaxScale load balancing and health monitoring.
This will allow automatic switching of the master it fails.

  • Server Setup
  • MariaDB Installation
  • Performance Turning Options
  • Allow network connections from other servers
  • Disable DNS/Host-name reverse validation
  • Max Connections
  • Thread Cache Size
  • Starting, Stoping and checking mySQL’s Status

Server Setup:

The three Database Servers are all Ubuntu 20.04.3 LTS (Focal Fossa). For those who are interested here are the specs (Identical on each)

CPU: 40 Cores
RAM: 20GB
Storage OS: 20GB
Storage Data: 200GB
OS: Ubuntu 20.04.3
Network: Static IP 10.0.1.7x 10G Fibre

All three servers are identical, are fresh installations and are fully up-to-date.

MaridaDB Installation:

Installing Packages:

sudo apt update
sudo apt upgrade
sudo apt install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
5sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mariadb.mirror.liquidtelecom.com/repo/10.6/ubuntu bionic main'

sudo apt update
sudo apt install mariadb-server mariadb-client

Securing mySQL:

sudo mysql_secure_installation

Enter current password for root (enter for none):  # ENTER
Switch to unix_socket authentication [Y/n]         # n
Change the root password? [Y/n]                    # n
Remove anonymous users? [Y/n]                      # y
Disallow root login remotely? [Y/n]                # y
Remove test database and access to it? [Y/n]       # y

Checking the Installation:

sudo mysql

SELECT VERSION();
+--------------------------------------+
| VERSION()                            |
+--------------------------------------+
| 10.6.4-MariaDB-1:10.6.4+maria~bionic |
+--------------------------------------+
1 row in set (0.000 sec)

Performance Turning Options

Now, ever server, and use case is different, but here are the options I’m going to adjust the following setting to meet my requirements.

Edit the MariaDB Config file (I’m going to replicate this across all three servers):

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

1: Allow network connections from other servers.

By default mySQL only allows connections from it’s own IP (127.0.0.1), I’m going to set this to the 4 IP’s in this cluster, and a backup server.
Change the following line:

bind-address            = 127.0.0.1

You can either open up to all IP’s Addresses by setting this to 0.0.0.0 or comment it out by prepending a #.

If you want only 2 or more specific IPs, you will then have to use a firewall.

For MySql version 8.0.13 and above, you can specify a list of comma-separated IP addresses. MariaDB does not yet support this.

# bind-address            = 127.0.0.1

2: Disable DNS/Host-name reverse validation

I only allow connections from specific servers using local IP Addresses, on every connection the server will do a reverse lookup on that to check the DNS name of the server.

I don’t need this as it add’s unnecessary overhead.
Remove the # comment on this line:

# skip-name-resolve
skip-name-resolve

3: Max Connections

This really depends on your traffic, we’re using this for an application that during certain times of the year our traffic spikes, I’m talking we average ~500 visitors a day for 9 months of the year, then ~40,000/day for the peak 3 months.

# max_connections       = 100
max_connections         = 4000

The InnoDB engine has a buffer pool used for caching data and indexes in memory. This helps MySQL/MariaDB queries be executed significantly faster.
As these are dedicated database servers, I can assign a high value here, for dedicated, I suggest around assigning 70% of your RAM to the innodb_buffer_pool_size value.

#innodb_buffer_pool_size = 100M
innodb_buffer_pool_size = 15G

4: Thread Cache Size

The thread_cache_size directive sets the amount of threads that the servers cache.
As each client connects then disconnects, their threads are cached – if they are less than the thread_cache_size. Further requests are completed by using the threads stored in the cache.

To improve performance you can set the thread_cache_size based on the following calculations, I’m replacing production servers so I’m running these on our existing servers:

sudo mysql

mysql> show status like 'Threads_created';
mysql> show status like 'Connections';
MariaDB [(none)]> show status like 'Threads_created';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| Threads_created | 1706000 |
+-----------------+---------+
1 row in set (0.002 sec)

MariaDB [(none)]> show status like 'Connections';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Connections   | 10454214 |
+---------------+----------+
1 row in set (0.002 sec)

Now use the following formula to calculate the thread_cache_size percentage:

100 - ((Threads_created / Connections) * 100)
100 - ((1706000 / 10454214) * 100) = 83.6812217542

I’m going to set my thread_cache_size to 88

# thread_cache_size      = 16
thread_cache_size      = 88

You can also dynamically change the  thread_cache_size value without having to restart the MySQL service:

sudo mysql

set global thread_cache_size = 88;

Starting, Stoping and checking mySQL’s Status:

As with anything, you will need to stop and start mySQL now, and test your servers. run some benchmarking, before and after.

# Stop mySQL
sudo service mysql stop

# Start mySQL
sudo service mysql start

# Restart mySQL
sudo service mysql restart

# Check mySQL's status
sudo service mysql status

Leave a Reply

Your email address will not be published. Required fields are marked *