Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / Linux / CentOS / MariaDB: Installation and Performance Optimization

April 6, 2020 CentOSLinuxQuestions and Answers

MariaDB: Installation and Performance Optimization

In this article I will consider the installation, basic configuration and performance optimization of the database server MariaDB on Linux CentOS 7. At the end of the article I’ll show some examples of MariaDB configuration files so that you will be able to choose the best parameters for your db server.

Install and Configure MariaDB on CentOS - Step By Step Tutorail

Contents:
  • Install MariaDB on CentOS
  • Securing MariaDB Server
  • MariaDB Connection Test
  • MariaDB Config File – my.cnf
  • Performance Optimization & Tunning for MariaDB
  • Basic MariaDB Shell Commands

Install MariaDB on CentOS

Recently, MariaDB has been added to the standard CentOS 7 base repository, but its version in the repository is 5.5. This version is not up-to-date, has some performance problems and doesn’t provide full text search in InnoDB. To install the current version of MariaDB, you first need to add the mariadb.org developer repository.

Install the nano editor using yum:

yum install nano -y

Then open the repository file to edit it:

nano /etc/yum.repos.d/mariadb.repo

Add the following text there:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save the mariadb.repo config file and install the MariaDB server and client:

yum --disablerepo=AppStream install MariaDB-server MariaDB-client -y

Installation is complete, now you need to add the mariadb service to startup:

systemctl start mariadb
systemctl enable mariadb

Check the service status:

systemctl status mariadb

systemctl status mariadb - checking mariadb service state

The mariadb service is active and running, so you can go on with the configuration.

Securing MariaDB Server

After we have installed and run the MariaDB server, we can move on to security settings. Run the built-in script:

/usr/bin/mysql_secure_installation

First of all, it will prompt you to enter the root password (after the initial installation of mariadb no password has been set), so we press ENTER and set a complex password in the next step. Then you can just press ENTER up to the end of the scenario execution. Anonymous users, a remote root login and test tables will be deleted, and privileges will be updated.

To connect remotely to mariadb server, you must create some rules for Linux firewall using iptables:

iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables-save > /etc/sysconfig/iptables

Optionally, you can allow outbound connections to remote MariaDB databases:

iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

MariaDB Connection Test

You need to make sure that MariaDB has been successfully installed.

Let’s connect to the DB server using the built-in mysqladmin tool:

mysqladmin version

The command returns the following:

mysqladmin Ver 9.1 Distrib 10.4.7-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.4.7-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 59 min 35 sec
Threads: 10 Questions: 24 Slow queries: 0 Opens: 28 Flush tables: 1 Open tab

It means that MariaDB has been installed successfully, the database is up and running.

You can connect to the mariadb server console to run SQL commands interactively:

mysql -u root -p

MariaDB Config File – my.cnf

Usually, after MariaDB is installed, I add my standard configuration to the config file /etc/my.cnf that works on most servers and has not caused any problems so far. Clear my.cnf and add the following text to it:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
max_allowed_packet = 128M
sql_mode = ""
log-error = /var/log/mysql-error.log
# Cache parameters
query_cache_size = 16M
table_open_cache = 4096
thread_cache_size = 16
key_buffer_size = 8M
thread_stack = 256K
join_buffer_size = 2M
sort_buffer_size = 2M
# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 32M
tmp_table_size = 32M
# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_use_native_aio = 0
transaction-isolation = READ-COMMITTED
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
skip-name-resolve
[mysqldump]
quick
quote-names
max_allowed_packet = 128M
default-character-set = utf8
[mysql]
[isamchk]
key_buffer = 16M
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

Let’s dwell on the main my.cnf parameters in more detail:

  • datadir is the directory the DB files are stored in;
  • tmpdir is the directory to store temporary files;
  • skip-name-resolve disables DNS name resolving;
  • max_allowed_packet is the maximum package size allowed. If you DB is using blob fields, its value cannot be less than the size of the largest field;
  • max_connections is the maximum number of opened connections; the parameter sets how many clients can work with the mariadb server simultaneously;
  • # Cache parameters section includes everything related to the query cache. It is not recommended to set too high values since your DB server will consume more resources;
  • # InnoDB parameters section includes everything related to the innodb tables;
  • innodb_buffer_pool_size is the cache buffer for data and indexes. If 1-2 projects are located on your server, set the value to 70-80% of available RAM;
  • innodb_flush_method set the value O_DIRECT for Linux, it will disable OS-level caching;
  • innodb_flush_log_at_trx_commit this parameter affects the write speed of innoDB tables. Take this parameter seriously: if you set 0 here, you will have higher performance, but the risk of data loss increases. I prefer to set the value 2 here, since I have not seen any obvious DB server performance gain, while security is a priority.

Performance Optimization & Tunning for MariaDB

I would like to add that you do not need to copy my configuration file: every server and every project need their own parameters. I advise to use some scripts that check MariaDB configuration automatically and provide some recommendations on the server optimization.

To optimize your mariadb parameters you can use the Tuning-Primer.sh script. First, install additional tools:

yum install bc net-tools -y

Download the optimization script:

wget https://launchpadlibrarian.net/78745738/tuning-primer.sh

Assign the execute permissions to a .sh file:

chmod +x tuning-primer.sh

Run the script:

./tuning-primer.sh

After running, the script will show you the useful information you need to know. The most important things will be highlighted in red. These are the things that are recommended to be fixed.

For example, the script showed that the number of connections is much less than I have set.

mariadb tuning bash script

You can change this parameter both in my.cnf file and in the console. I have set a lower value (10) and the script approved it:

optimizing mariadb performance

Using these checks, you can make the performance of your mariadb server perfect.

I would like to note that the recommended DB server continuous operating time is at least 48 hours, then the information will be more accurate and you will be able to tune your server.

You can add a section to activate a slow query log. It will help to analyze the performance of your projects. Add the following to mysqld section:

slow_query_log = 1 # enable the log of slow queries
long_query_time = 5 # set the time in seconds
slow_query_log_file = /var/log/slow-query.log # the name of your slow query log file and the path to it
log_queries_not_using_indexes # whether to write queries that do not use indexes to the log file

Also you must create log files for MariaDB and a slow query log errors:

touch /var/log/mysql-error.log
touch /var/log/slow-query.log

Restart the db service:

systemctl restart mariadb

You can view your slow query log if you open /var/log/slow-query.log or check it in real time:

tail -f /var/log/slow-query.log

Thus, you will be able to analyze the queries your project sends to your DB and conduct an audit.

If you change any DB settings, check your configuration for errors. After making any changes to the my.cnf settings, you need to check the configuration for errors:

systemctl status mariadb -l

Basic MariaDB Shell Commands

The main cli commands an administrator has to use often in mariadb console are shown below.

To locally log on to your MariaDB console, run this command:

mysql -u root -p

To remotely connect to your MariaDB server:

mysql -u root -p -h 192.168.1.20

Useful MariaDB (MySQL) commands:

create database db1; — creates a DB with the name db1

show databases; — displays the list of DBs on server

use db1; — log on to a DB with the name db1

show tables; — lists all tables in the current database

create user 'project'@'localhost' identified by '$sup#erP@ss1'; — creates a user (project) and sets its password to $sup#erP@ss1

grant all privileges on database_name.* to project@'localhost'; — grants full privileges to user project

flush privileges; — updates all privileges

show processlist; — view active DB connections. You can also use this command:

show status where `variable_name` = 'Threads_connected';

In your mysql console, you can view or change some variables, like:

SHOW VARIABLES LIKE 'max_error_count';

mariadb admin console commands

To change:

SET max_error_count=256;

mariadb set parameters via cli

In conclusion I would like to say that when you configure a DB server you must consider the projects to be located on the server. What is applicable for minor projects will be definitely not suitable for a large one. Try, experiment and always view your logs. In the next article we’ll show how to provide high availability and increase app performance by using replication between MariaDB database servers.

0 comment
1
Facebook Twitter Google + Pinterest
previous post
Control Panel Mail App Not Found on Windows 10
next post
Using Visual Studio Code Instead of PowerShell ISE

Related Reading

Enable Internet Explorer (IE) Compatibility Mode in Microsoft...

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Stop Automatic Upgrade to Windows 11?

January 18, 2023

Fix: Windows Needs Your Current Credentials Pop-up Message

January 18, 2023

Adding Trusted Root Certificates on Linux

January 9, 2023

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023
  • Adding Trusted Root Certificates on Linux

    January 9, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Configure MariaDB Master-Master/Slave Replication?
  • How to Mount Google Drive or OneDrive in Linux?
  • KVM: How to Expand or Shrink a Virtual Machine Disk Size?
  • Adding VLAN Interface in CentOS/Fedora/RHEL
  • Hyper-V Boot Error: The Image’s Hash and Certificate Are not Allowed
  • Configuring High Performance NGINX and PHP-FPM Web Server
  • Install and Configure SNMP on RHEL/CentOS/Fedor
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top