Happy Holidays!

Optimize MariaDB: tips to deal with load spikes

by | Last updated Aug 27, 2021 | Community, Servers

optimize mariadb

Load spikes? We give you some tips to optimize MariaDB

This 2020 is perfect to check our beloved Pandora FMS server and precisely a very important component is the database (BD). As our loyal readers already know (and if you visit us for the first time you can read the introduction to architecture on our Wiki), MariaDB is one of the databases chosen by Pandora FMS to keep all your information about your device monitoring: Let’s see how to optimize MariaDB!

I am a big fan of GNU/Debian, specifically its GNU/Ubuntu distribution, but for Pandora FMS CentOS is officially recommended due to its high development stability. No worries, here you have the first tip: install MariaDB on CentOS 7.

Our second tip, as an introduction to today’s topic, is the number of devices you must monitor. For one hundred or more, apart from recommending you Pandora FMS Enterprise Version, you should use a High-Availability Database Cluster or simply HA. The following short video introduces the subject. If you want to learn more, read the article in our blog specific on this topic.

Please accept marketing cookies to watch this video.

Whether you use a single server, several or many of them, you will always have in each instance a database managed by MariaDB stoically withstanding load spikes.

Who uses MariaDB?

Before optimizing MariaDB to receive load spikes, let’s see a short history… it’s short, I promise!

Wikipedia®, Google®, WordPress®, among many others use MariaDB for mission-critical tasks. There you have it, I told you the story was short, right? Ok, I know, you need to know a little bit more.

You may already know this: MariaDB is the fork of MySQL® but with a totally free license (GNU General Public License). It’s been developed and maintained since 2012 by MariaDB Foundation (MariaDB), and one of its golden clients is MariaDB Corporation.

optimize mariadb 1

Caption: MariaDB Foundation https://mariadb.org/about/#logos-and-badges

A MariaDB bronze client is Percona.

Since 2013, Google® hires engineers to work at MariaDB. Other companies have followed suit, or donated billions of dollars to the foundation.

Michael Widenius is one of the creators of MySQL. “My” is the name, in Finnish, of his oldest daughter and “Maria” is the name of his youngest daughter and the storage engine that was born long before MariaDB. To differentiate it, they later renamed it Aria. MyISAM is the counterpart of Aria.

optimize mariadb 2

Caption: Michael ”Monty” Widenius
https://commons.wikimedia.org/wiki/File:Michael_%E2%80%9DMonty%E2%80%9D_Widenius_at_MariaDB%E2%80%99s_Developers_Unconference_2019_in_New_York_City_04.jpg

Of course, due to all of this, MySQL and MariaDB are like sisters both in theory and in practice, but hold on, there are important differences!

Right at the time of writing this article, MariaDB is in its stable version 10.3.21. Since version 10.3.7, it has set itself far apart from the use and development of InnoDB.

InnoDB in turn replaced MyISAM as the default storage engine for BD tables in both MySQL and MariaDB.

There are many database storage engines, even Facebook® has its own that makes the most out of the use of solid state hard drives. There is another extremely fast one that is only stored in RAM memory and when you turn off the computer all data is erased. Others have only academic interest. Here we will talk a lot about InnoDB.

Load spikes

A load spike, in computing, takes place either due to our interaction and/or human activities or due to unforeseen events. The very fact that we all go to work at 7:30 in the morning every day creates a load spike for any computer system, for example. We must learn to live with load spikes, deal with them, or at least mitigate them to the best of our ability. Let’s see how to improve MariaDB for that purpose.

Tips

  • Maximum number of connections (“max_connections”): one hundred and fifty-one by default. Some recommend raising it to 750 or even 1000. I think that we obviously should raise it but without losing sight of the hardware and/or virtual resources we have. So I don’t really set a specific number. Limits: minimum 10, maximum 100 thousand).
  • Thread_cache_size: The role of these threads is to stay in memory for up to five minutes in order to be reused if possible. It is related to the previous point, the default value is 256. If we raise that one to more than 256, this one rises automatically to the same value. You could first try the maximum number of connections at 256 as a first step and evaluate the performance, although I would dare to say between 150% and 200% the value of the maximum number of connections. Limits: minimum 0, maximum 16384.
  • Thread Pool: I mention it because activating it spoils the last tip. This feature is useful if we know in advance that we will have lots of short queries that will involve extra calculation work for the processor. Although Pandora FMS uses more the CPU to limit the data from the last 30 days and the rest it does is receiving and saving, which is why I consider that these threads are not any more useful in our case.
  • Sync Binlog (Synchronization binary log): this would be enough for a whole article. Essentially it means sacrificing a bit of speed by ordering the operating system to write on disk after every transaction. That way, you prevent data from being lost from synchronization to database replicas. Since the default value is zero (it is in the hands of the operating system), if you increase the value to one, which is the slowest, you have to pay attention to increasing it further if you see things go too slow.
  • InnoDB optimization: As I mentioned, InnoDB is very important and MariaDB uses XtraDB, a Percona-improved version for InnoDBD. At programming language level, you will not see XtraDB, since it identifies itself as InnoDB. Yes, quite a headache. However, it shares common features, and the first advice of this group to optimize MariaDB is to modify the InnoDB Buffer Pool Size. Again it depends on your hardware or virtualized resources, but it is always good to set it up to a maximum of 80% of the memory, as long as you do not have other software running on the same server and all your tables are InnoDB. Limits: minimum 5 megabytes, by default 128 megabytes. If you assign more than 2 gigabytes the number of instances must be increased (divide the pool into logical units), maximum 8192… petabytes!
  • InnoDB Log File Size: depending on the value you set to the reserve fund, you will have to assign half or less. By setting a larger size for this log file, it reduces writing at the expense of increasing the recovery time if there is a failure (for example a power cut, which is quite common, or someone shutting down the server manually). Limits: minimum 1 megabyte, 48 megabytes by default, maximum 512 gigabytes.
  • InnoDB Log Buffer Size: Here I recommend a figure, 64 megabytes, which reduces disk writing and reading. Limits: minimum 256 kilobytes, 16 megabytes by default, maximum 4096 megabytes.
  • InnoDB Log Flush Interval: here there is nothing to change, its default value is one. But if your power supply is completely reliable, you can change it to two to increase performance. I repeat, if you are really confident that the Uninterruptible Power System (UPS) won’t disappoint you!
  • InnodB IO capacity: this last tip to optimize MariaDB is better described with the storage hardware you have, although it is an approximation because you actually have to do specialized tests on each particular device. It is measured in operations per second (IOPS) and has a default value of 200 units, a value suitable for hard drives of 10,000 RPM. You must lower it to 100 (one hundred) for discs of less than 7,200 RPM. With solid state hard drives you can take it to 1,000 (thousand IOPS)!

    • Before finishing this article about optimizing MariaDB for load spikes, remember Pandora FMS is a flexible monitoring software, capable of monitoring devices, infrastructures, applications, services and business processes.

    Want to know more about what Pandora FMS has to offer you? Find out by entering here.

    Or if you have to monitor more than 100 devices, you can also enjoy a FREE 30-day Pandora FMS Enterprise TRIAL. Get it here.

    And remember that if you have a small number of devices to monitor you can use the OpenSource version of Pandora FMS. Find out more here.

Shares