Bitrix vm my cnf

Below I will give the most complete and correct guide on how to set up remote access to the mysql database on the Bitrix virtual machine (vm bitrix), which is located on the Centos 7 server. Therefore, I will give the commands below specifically for this operating system

I occasionally work with Bitrix sites that work in a ready-made web environment from developers. Today I will share information about where they are stored and how to change mysql settings on the server with bitrix env. Many simple things become very complex if you are not familiar with the nuances of how this environment works.

Purpose of the article

Once again, I had to tinker with setting up Bitrixenv and the site on it. At some point, the bitrix site began to pour 500 errors on some operations. According to the logs, it was clear that there was not enough memory for some scripts to work, although there was enough before. I had to investigate and optimize the memory consumption of the bitrix site.

Kubernetes Online Course — for developers, administrators, technical leaders who want to learn the modern Kubernetes microservices platform. The most complete Russian-language course on highly demanded and well-paid skills. The course is not for beginners — you need to pass an entrance test.

Introduction

I already analyzed the issue of mysql memory consumption when working in bitrixenv separately some time ago — where mysql settings are stored. I recommend that you familiarize yourself with it, since there is information directly related to the current topic of optimizing the use of site memory on bitrix when working in bitrixenv.

The developers of bitrixenv have simplified the work of system administrators in setting up the server by introducing the bvat service, which automatically selects the optimal settings for the following services when the server starts:

The settings will depend on the amount of available RAM. All in all, this is a nice step that simplifies the initial server setup. Most often, the configuration of services turns out to be adequate and suitable for typical sites.

In my case, the standard settings no longer fit. The server ran out of RAM from time to time. OOM Killer (OOM — Out of memory) came and crashed the mysql server, as it consumed the most RAM. For a while everything worked fine, then the same thing happened again.

Zabbix monitoring events caught my attention, such as Lack of available memory on server. I looked at the graph and everything immediately became clear, even before connecting to the server.

I went to the server, looked at the system log. I saw this there:

The first thing I did was to increase the swap partition to the size of all the RAM. Before that, it was 1G in size. This immediately helped and prevented the regular arrival of OOM Killer. And I began to calmly figure out what to do next.

The plan for further setting up the server for the stable operation of the site on bitrix is ​​as follows:

PHP optimization for bitrix

From the php settings, I would pay attention to the following parameters:

Anyway, these options, apart from sendmail, affect server performance and memory consumption. Don’t set these values ​​too high unless absolutely necessary. I would start with 256 MB and increase as needed. Yes, 256 MB is already a lot, but a site on bitrix requires high values ​​​​of these parameters for correct operation. 256 MB is the general recommendation for default values.

You can place your php parameters in a separate config that will not be overwritten by bitrixenv — /etc/php.d/z_bx_custom.ini. After changing the settings, you need to restart apache to apply.

Setting up nginx for the bitrix site

In nginx itself, in bitrixenv, there is nothing special to configure for performance. It works as a proxy server for apache. With the help of proxy_pass, it redirects all dynamic requests, and itself gives only static requests. In this mode of operation, it consumes a minimum of resources and there is nothing to optimize in it. If you are still interested in understanding the nginx settings, then read my separate detailed article.

In principle, the default value of 256 MB is usually enough, although in fact these are small numbers. But keep in mind that if there is no free memory at all, then you can trim this parameter.

How to change mysql settings in bitrixenv

In order to manually change some parameters in mysql that will not change dynamically, you need to use the /etc/mysql/conf.d/z_bx_custom.cnf file. The main parameter that causes mysql to take up all the RAM is innodb_buffer_pool_size. First of all, you should reassign it. Make somewhere in a third of real RAM. The rest of the parameters must be dealt with separately. I did not waste time on this until I temporarily gave the server the initial amount of memory of 16GB. In the near future I will completely update the server along with bitrix env and see if the glitch with the fact that the memory is determined incorrectly has disappeared. If not, I will manually set the parameters for the real RAM of the server.

Setting up a remote connection to the mysql database

We do it on the server where the database is located

Here the database will listen to all ip addresses for remote connection

Check if there is a line containing skip-networking, comment it out by adding # at the beginning of the line.

Next, in the terminal, restart the database for the changes to take effect, using the service mysqld restart command for Centos 7

Check if port 3306 is open or open with the command nmap -p 3306 xx.xx.xx.xx (where xx.xx.xx.xx is the ip of the server on which we are setting up the database)
If the nmap command is not installed, install yum install nmap

Granting access to a user from a remote computer

We also do this on the server where the database is hosted

On the virtual machine, the /root/.my.cnf file contains root access to the mysql database

Connect in the linux terminal to the MySql database with the command mysql -uroot -p and enter the root password

You can exit the database with the exit command;

Firewall setup

Set the resolution with the command
iptables -I INPUT 1 -p tcp —dport 3306 -j ACCEPT

But this is not safe, so we make a command with setting a specific ip from which you can connect

iptables -I INPUT 1 -s yy.yy.y.yy -p tcp —dport 3306 -j ACCEPT

Pay attention to the presence of the number 1 in the line, this means that this permission will be placed at the beginning of the iptables table in the Chain INPUT block

You can check the existence of a line with the command iptables -nL —line-numbers

If the line is not at the beginning, delete it with the command iptables -D INPUT line, where line is the line number and execute the command written above

4 Connection test

In the terminal of the server from which the request is made to the database, enter the command mysql -u bitrix1 -h xx.xx.xx.xx -p and enter the password

If all the steps are completed correctly, you should be able to connect to the base

It is necessary to register access to the remote database in Bitrix

To do this, in the files /home/bitrix/www/bitrix.settings.php and /home/bitrix/www/bitrix/php_interface/dbconn.php, specify new connection data to the database in special blocks

$DBHost = «xx.xx.xx.xx»;
$DBLogin = «bitrix1»;
$DBPassword = «password»;
$DBName = «dbname»;

Where are mysql settings in bitrix env

First, it was necessary to check where mysql settings are stored on the server with bitrixenv. By analogy with the rest of the settings (php, apache, nginx), I was looking forward to a long search and was not mistaken. As a result, I found out that they are stored in the /etc/mysql/conf.d/bvat.cnf file. But it is not enough to know where they are stored. As it turned out, this file is generated dynamically every time the server starts, depending on the available RAM. That’s when I realized what the problem was. I’ll tell you about everything in order.

Bitrix env has a service called bvat. It starts when the server boots up via /etc/init.d/bvat. This service determines the amount of RAM on the server and, depending on this, changes some settings of the web environment. In particular mysql, php, apache. You can look at this script to understand what it does. In short, it runs the /etc/ansible/library/bx_perf script, which connects some variables and generates new configs. He logs his work in the /opt/webdir/logs/bvat.log file.

In my case, bvat did not change mysql config. I checked the documentation for it on the Bitrix website. It is clearly stated that it runs when the system boots. I ran those checks from the script with my hands that should change exactly the mysql config. I deleted the config, but bvat invariably created a new config, based on the fact that the server has 16GB of memory. This is how it was at the time of the initial installation. But over time, the hypervisor was loaded and the memory was made dynamic, reducing the maximum available.

At some point, there was not enough memory for everyone on the hypervisor, and he began to distribute it among virtual machines. Specifically, the experimental server began to get less memory than 16 GB, but all configs were sharpened for this amount. Because of this, the server did not have enough memory and it began to go into swap and restart abnormally memory-consuming services.

When I understood everything, I made static memory for the virtual machine (less than 16GB) and rebooted. But mysql config didn’t change. There is clearly some glitch here. bvat still got 16GB of memory from somewhere and drew a config based on them. I have looked at the whole script. It uses several memory checks. I looked at the main one, through free -m, it shows the correct value, which is less than 16GB, but bvat takes a different number from somewhere. I did not deal with this, as there is a bug that is most likely either already fixed or will be fixed after some update.

Errors that may occur

This error may occur in paragraph 4 of the text above. This means that the firewall does not allow

So you have entered incorrect access to the remote database

Connect to address xx

When using the telnet xx.xx.xx.xx 3306 command to check the connection, this error may pop up
It means the firewall is not configured correctly.
To install the command, execute in the terminal yum install telnet

Optimization of apache settings in bitrixenv

Next, we move on to the second main consumer of RAM on the server with a site on bitrix — apache. To him, as well as for mysql, the bvat service automatically exposes some settings. They are stored in the /etc/httpd/bx/conf/prefork.conf file. We will be interested in the settings regarding the number of running processes.

To find out the number of running httpd processes serving the work of the bitrix site, enter the command in the server console:

You will get a number, 2 more than specified in the given config, in the parameters of the mpm_prefork module. In my case, bvat set the maximum possible number of httpd processes to 60, but for me it was too much, the server did not pull such a number of processes. I reduced it to 30.

As you understand, depending on the site’s bitrix, one httpd process will use a different amount of memory, so it’s impossible to automatically set this parameter correctly for all sites. In this case, the default setting did not suit me, so I created my own httpd configuration file — /etc/httpd/bx/custom/z_bx_custom.conf.

I’m including a screenshot because the site’s engine swallows all the lines in angle brackets. It is not possible to lay out the complete config in text form. In general, you need to look at how much memory one httpd process takes up and calculate the maximum number of processes that your server will pull.

You can see how much memory one httpd process takes in htop or with the command:

# ps -o vsz,rss,cmd —pid $(pgrep httpd)

There will be one main process that takes up the most memory and then its forks that consume about the same. Focus on them. My main process consumes 500 MB and 30 forks of 100 MB each. The total is 3.5 GB.

In total, at the peak I have 6.5 GB using mysql and 3.5 GB using httpd, a total of 10 GB out of the available 12. In practice, free memory is usually more than 2 GB, since mysql most often consumes below the maximum limit.

Checking who takes up all the memory on the server

I encountered unexpected behavior of the server on which the site was running on Bitrix. For a long time it worked, taking up all available RAM. I received notifications about this from zabbix, but did not pay much attention to the server, since in general this is a normal situation when you have mysql and apache working together. For about half a year, it worked fine, and then it began to degrade greatly in performance. In general, real problems began.

I went to the server and began to figure out what was going on. I started by looking at who occupies the RAM.

# ps aux —sort -rss

#ps aux —sort -vsz

I was not surprised to see that mysql. The first thing I did was restart it and start watching. I saw this picture in zabbix.

Then the server ate the entire swap and killed the mysql process with a message in the system log:

kernel: Out of memory: Kill process 30609 (mysqld) score 97 or sacrifice child

Mysql restarted automatically and then everything continued in a circle. I should have dealt with him first.

Mysql settings optimization

The test server has 12 GB of RAM. I decided to give half of this memory to mysql. Let’s start tuning the mysql configuration. In the general case, one parameter will suffice, which is mainly responsible for memory consumption:

innodb_buffer_pool_size = 4G

In my case, this was not enough. I decided to take a closer look at setting up mysql. I found a good tool — MySQLTuner, which, by analyzing the work of mysql, gives some recommendations for tuning. I myself do not understand the fine tuning of mysql, so I decided to trust the utility. Judging by the reviews, she is not bad and you can trust her if you yourself do not understand the topic. Looking ahead, I will say that with the help of this tuner, I tuned mysql to stable operation with a fixed memory consumption. There have been no problems with this server since then.

So, copy the script itself to the server:

# wget http://mysqltuner.pl/ -O mysqltuner.pl

# perl mysqltuner.pl

In order for the recommendations to be more effective, the mysql service should work for you for several days. If it was restarted the day before, and I did it, then I recommend that you go in a few days and run the tests again. There will be new tips on the config.

To optimize memory consumption, it will be enough to run the script at any time. I recommend that you carefully study its capabilities. I will not dwell on them in detail now, but will consider only what concerns memory. Among other things, you will see the following information.

I already have everything optimized for consumption of no more than about 6 GB of memory. I’ll tell you what parameters are responsible for this. As I said earlier, this is the innodb_buffer_pool_size parameter. In general, for the mysql server, it is recommended to specify this parameter equal to 80% of the server’s available memory. But this is the case if you have nothing running on this server other than mysql. And we have a lot of other services there, so this advice does not suit us.

Next, we need to find out how much memory the thread (the process that creates the connection) takes and set the limit on the number of connections accordingly. The thread size is equal to the sum of the following parameters — read_buffer_size + sort_buffer_size + join_buffer_size.

The read_buffer_size parameter is set to 128 KB by default. I didn’t touch him. I initially set the other two according to the recommendations of mysqltuner, and set the max_connections value, which is responsible for the maximum number of connections, so that the sum of three buffers multiplied by the number of connections does not exceed 2 GB of memory. The server worked a little in this mode and it turned out that there were not enough exposed connections. Then I lowered join_buffer_size to 18 MB and increased the number of connections. In the end, I settled on these settings.

innodb_buffer_pool_size = 4G
sort_buffer_size = 18M
join_buffer_size = 18M
max_connections = 70

With these settings, the maximum memory consumption of the mysql service will not exceed 6.8 GB, as indicated by the output of mysqltuner. Specifically, for my site, 70 connections to mysql is enough. Before that, I set 50, there were reports of a lack of connections. On your server, choose the parameters yourself, do not copy from me.

In practice, it happened. A few days later, I went back and ran another check, which showed that the real memory usage had not gone beyond these limits. Plus, I edited some parameters.

Tips for changing parameters are given in the final section of mysqltuner — Variables to adjust. I will not give my recommendations, since they will be relevant only for a specific server. I advise you to look at all the recommendations, read the description of the parameters and try to apply them on your own. Blindly it is not necessary to change what is advised there.

Here is a list of the main mysql parameters that affect performance and which you should first of all pay attention to:

I took the list from here. Very helpful article, highly recommended.

As I said, the bvat service automatically adjusts some settings of the standard bitrixenv services. To apply our settings, you need to specify them in separate configuration files.

And here is a general list of all the main bitrixenv configuration files:

After optimizing all the above parameters in bitrixenv, the server’s memory consumption has stabilized. The Bitrix site began to work smoothly with predictable performance without unexpected brakes and crashes.

That’s all for me on the topic of optimizing server settings for bitrix. The system is interesting and multifaceted. It’s always interesting to look under the hood of bitrixenv. As for me, it was done well, although it causes trouble when parsing some incidents.

In general, I think that in general, everything is done conveniently and functionally for a quick launch of a bitrix site. Even an unprepared person, and specifically some programmer, will cope. He would be tired of setting up this bundle on his own, but here everything works out of the box. But if there are problems, then it is not always easy to figure it out.

The next step is waiting for the appearance of docker builds with bitrixenv inside. Either one shared image or a set via docker-compose. This would be a logical continuation of development in light of the popularity of containers and microservices.

If you have a desire to learn how to administer Linux-based systems, but you have never worked with them and are not familiar with them, then I recommend starting with the Linux for Beginners online course at OTUS. Course for beginners, for those who are not familiar with Linux. The price for the course is minimal (symbolic). Course and price information.

Did the article help? Subscribe to the author’s telegram channel

Announcements of all articles, plus a lot of other useful and interesting information that does not make it to the site.

I have an ambiguous attitude towards bitrixenv. On the one hand, it is convenient that everything is collected in one place, connected to each other and quickly installed and configured. But when you need to debug some problems, it takes many times more time than if you use a classic web server configured by yourself. Now I’m already well versed in bitrixenv, I solve problems quickly, but this is the first time I’ve encountered mysql. Usually there are problems with php, apache, nginx configs, with sending mail.

The logical conclusion at the end is that bitrixenv is not designed to work on a server with dynamic RAM. You must either disable bvat or set static memory to the virtual machine.