Как увеличить максимальное количество подключений в postgres?

Введение

Вопрос с потреблением памяти mysql при работе в bitrixenv я уже разбирал отдельно некоторое время назад — где хранятся настройки mysql. Рекомендую с ней ознакомиться, так как там информация напрямую относящаяся к текущей теме оптимизации использования памяти сайта на bitrix при работе в bitrixenv.

Разработчики bitrixenv упростили работу системных администраторов по настройке сервера, внедрив службу bvat, которая автоматически при запуске сервера подбирает оптимальные параметры следующих служб:

• mysql
• apache
• nginx
• php

Настройки будут зависеть от количества доступной оперативной памяти. В целом, это неплохой шаг, который упрощает начальную настройку сервера. Чаще всего конфигурация служб получается адекватной и подохдящей для типовых сайтов.

В моем случае стандартные настройки перестали подходить. На сервере время от времени появлялась нехватка оперативной памяти. Приходил OOM Killer (OOM — Out of memory) и грохал mysql сервер, так как он потреблял больше всего оперативной памяти. Какое-то время все работало нормально, потом провторялось то же самое.

Мое внимание привлекли события из мониторинга Zabbix, такие как Lack of available memory on server. Посмотрел график и все сразу стало ясно, еще до подключения к серверу

Зашел на сервер, посмотрел системный лог. Увидел там вот это:

kernel: Out of memory: Kill process 7382 (mysqld) score 431 or sacrifice child
kernel: Killed process 7382 (mysqld) total-vm:3967860kB, anon-rss:1942144kB, file-rss:0kB, shmem-rss:0kB
systemd: mysqld.service: main process exited, code=killed, status=9/KILL
systemd: Unit mysqld.service entered failed state.
systemd: mysqld.service failed.
systemd: mysqld.service holdoff time over, scheduling restart.
systemd: Stopped MySQL Server.
systemd: Starting MySQL Server...
systemd: Started MySQL Server.

Первое, что я сделал — увеличил swap раздел до объема всей оперативной памяти. До этого он был размером в 1G. Это сразу помогло и предотвратило регулярный приход OOM Killer. А я стал спокойно разбираться, что делать дальше.

План дальнейшей настройки сервера для стабильной работы сайта на bitrix следующий:

1. Определяем основных потребителей оперативной памяти.
2. Распределяем всю свободную память между ними.
3. Убеждаемся, что под нагрузкой все работает корректно, всем хватает памяти, OOM Killer не приходит.

Изменение стандартных настроек BitrixVM

Как я уже говорил, служба bvat автоматически регулирует некоторые настройки стандартных служб bitrixenv. Чтобы применять наши настройки, нужно их указывать в отдельных конфигурационных файлах.

• MySQL — /etc/mysql/conf.d/z_bx_custom.cnf
• Apache — /etc/httpd/bx/custom/z_bx_custom.conf
• nginx — /etc/nginx/bx/conf/z_bx_custom.conf
• PHP — /etc/php.d/z_bx_custom.ini

А вот общий список всех основных конфигурационных файлов bitrixenv:

• /etc/php.d/bitrixenv.ini — основные настройки php
• /etc/httpd/bx/conf/prefork.conf — параметры модуля Apache — MPM prefork;
• /etc/php.d/z_bx_custom.ini — пользовательские настройки PHP;
• /etc/httpd/bx/custom/z_bx_custom.conf — пользовательские настройки Apache;
• /etc/mysql/conf.d/z_bx_custom.cnf — пользовательские настройки MySQL;
• /etc/nginx/bx/conf/z_bx_custom.conf -пользовательские настройки nginx;
• /etc/nginx/bx/conf/push-im_settings.conf — настройки nginx-push-stream-module.

Описание:

Итак, у меня есть настройка, где устройство arduino подключено к ноутбуку через последовательный кабель USB, а ноутбук подключен к Интернету.

На компьютере python script (transit.py) работает непрерывно и прослушивает COM-порт, анализирует полученные данные и пересылку в файл (insert.php) на удаленном сервере (бесплатный хостинг-сайт)
Смотрите код, чтобы узнать, как это работает…

Тогда есть insert.PHP скрипт, который получает эти данные (все же почти каждую секунду), анализирует его и сохраняет в базе данных mySql.
Это, однако, не единственный файл, который требует подключения mySql, поэтому я включаю connect.php в начале каждого такого файла.

Настройка nginx для сайта bitrix

В самом nginx в bitrixenv настраивать для производительности особо нечего. Он работает в качестве proxy сервера для apache. С помощью proxy_pass он перенаправляет все динамические запросы, а сам отдает только статику. В таком режиме работы он потребляет минимум ресурсов и оптимизировать в нем нечего. Если вам все же интересно разобраться в настройках nginx, то читайте мою отдельную подробную статью.

Отдельной настройки требует только модуль Push and Pull, если он у вас используется. Его конфигурация располагается в файле /etc/nginx/bx/conf/push-im_settings.conf. В контексте данной статьи нас интересует только параметр push_stream_shared_memory_size, который отвечает за использование оперативной памяти.

В принципе, дефолтного значения 256 Мб обычно хватает, хотя по сути это небольшие цифры. Но имейте ввиду, что если свободной памяти совсем нет, то можно подрезать этот параметр.

1 ответ

159

Всякий раз, когда вы увеличиваете и , имейте в виду, что настройка этих параметров не приводит к тому, что запросы ведут себя лучше. Фактически это приводит к тому, что неэффективные запросы ведут себя хуже, чем раньше. При каких обстоятельствах?

Когда запрос выполняет объединение или сортировку (через ) без преимущества индекса, в памяти должна быть сформирована таблица temp. Это увеличит .

Что делать, если таблица temp растет до количества байтов в и требует больше места? Происходит следующая последовательность событий:

• Обработка запросов должна прекратиться.
• Создайте временную таблицу на диске
• Переносить содержимое временной таблицы на основе памяти в временную таблицу на основе диска
• Отбросить временную таблицу на основе памяти
• Обработка запросов продолжается с использованием временной таблицы на диске

Этот процесс увеличивает

Зная эти механизмы, давайте исследовать, что произошло в каждом случае

Это может легко произойти, если в запросах, которые раньше выполнялись, были сохранены кешированные результаты в ОЗУ. Это избавит вас от необходимости обрабатывать запрос с самого начала и не воссоздавать те же самые большие временные таблицы.

Это не удивительно. Это просто объясняет тот факт, что есть запросы, требующие временные таблицы. Сначала они были созданы в оперативной памяти. Это просто указывает на наличие запросов, которые не присоединяются хорошо (возможно, слишком мало) или неиндексированные столбцы или столбцы с временной таблицей (возможно, слишком мало).

Это тоже не удивительно. Если есть достаточно вызовов для одного и того же запроса с одинаковыми значениями, сортировки и объединения могут быть вытеснены выполнением запросов из ранее кэшированных результатов.

РЕКОМЕНДАЦИЯ

В свете этих вещей, вот что можно было бы скорректировать:

• Создание индексов, которые будут использоваться
  • в соединениях через eq_ref
  • сортирует по индексу в порядке

Общая цель должна заключаться в том, чтобы предотвратить создание темповой таблицы как можно больше. Простое увеличение и позволяет использовать неэффективные запросы и таблицы, которые не имеют надлежащего индексации, работают amok.

01 фев. 2013, в 18:29
Поделиться

Ещё вопросы

• 111MySQL против PostgreSQL для веб-приложений
• 88Как отфильтровать результаты SQL в сквозном отношении
• 73Почему целое число без знака недоступно в PostgreSQL?
• 71Postgres 9,1 против Mysql 5,6 InnoDB?
• 64Оперативная проверка синтаксиса SQL в соответствии с несколькими базами данных
• 63Как вы пишете нечувствительный к регистру запрос для MySQL и Postgres?
• 60PostgreSQL GROUP BY отличается от MySQL?
• 62Простые случайные образцы из базы данных SQL

Оптимизация php под bitrix

Из настроек php я бы обратил внимание на следующие параметры:

• memory_limit — максимальное количетсво памяти на выполнение php скрипта;
• sendmail_path — управляет параметрами отправки сообщений, хотя к теме текущей статьи и не имеет отношение;
• post_max_size — максимальный размер данных для всего POST запроса;
• upload_max_filesize — максимальный размер файла для загрузки через POST запрос;
• max_execution_time — максимальное время в секундах, в течение которого скрипт должен полностью загрузиться.

Так или иначе, эти параметры, кроме sendmail, влияют на производительнойсть сервера и потребление памяти. Не ставьте эти значения слишком большими без особой надобности. Я бы для начала выставил в 256 Мб и увеличивал по мере необходимости. Да, 256 Мб это и так очень много, но сайт на bitrix требует высоких значений этих параметров для корректной работы. 256 мб это общая рекомендация для дефолтных значений.

Свои параметры php вы можете размещать в отдельном конфиге, который не будет перетираться bitrixenv — /etc/php.d/z_bx_custom.ini. После изменения настроек надо перезапускать apache для применения.

Оптимизация настроек apache в bitrixenv

Дальше переходим ко второму основному потребителю оперативной памяти на сервере с сайтом на bitrix — apache. Ему, как и для mysql, служба bvat автоматически выставляет некоторые настройки. Она хранятся в файле /etc/httpd/bx/conf/prefork.conf. Нас будут интересовать настройки, касающиеся количества запущенных процессов.

Чтобы узнать, количество запущенных процессов httpd, обслуживающих работу bitrix сайта, введите в консоли сервера команду:

# ps ax | grep httpd | wc -l

Вы получите число, на 2 больше, чем указано в приведенном конфиге, в параметрах модуля mpm_prefork. В моем случае bvat выставлял максимально возможное количество процессов httpd равное 60, но для меня это было слишком много, сервер не тянул такое количество процессов. Я его уменьшил до 30.

Как вы понимаете, в зависимости от bitrix сайта, один процесс httpd будет использовать разное количество памяти, поэтому автоматически невозможно выставить этот параметр корректно для всех сайтов. В данном случае, дефолтный параметр мне не подошел, поэтому я создал свой файл настроек httpd — /etc/httpd/bx/custom/z_bx_custom.conf.

Привожу скриншотом, потому что движок сайта проглатывает все строки в угловых скобках. Не получается выложить полностью конфиг в текстовом виде. В общем случае, вам надо посмотреть, сколько у вас занимает памяти один процесс httpd и рассчитать максимальное количество процессов, которое потянет ваш сервер.

Посмотреть, сколькло памяти занимает один процесс httpd можно в htop или с помощью команды:

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

Будет один основной процесс, который занимает больше всего памяти и дальше его форки, которые потребляют примерно одинаково. На них и ориентируйтесь. У меня основной процесс потребляет 500 Мб и 30 форков по 100 Мб. В сумме получается 3.5 Гб.

Итого в пике у меня 6.5 Гб использует mysql и 3.5 Гб использует httpd, итого 10 Гб из доступных 12-ти. На практике, свободной памяти обычно больше, чем 2 Гб, так как mysql чаще всего потребляет ниже максимального предела.

Оптимизация настроек Mysql

На подопытном сервере имеется 12 Гб оперативной памяти. Я решил половину этой памяти отдать под mysql. Приступим к тюнингу конфигурации mysql. В общем случае достаточно будет одного параметра, который в основном отвечает за потребление памяти:

innodb_buffer_pool_size = 4G

В моем случае этого было недостаточно. Я решил более внимательно подойти к настройке mysql. Нашел неплохой инструмент — MySQLTuner, который анализируя работу mysql, выдает некоторые рекомендации по настройке. Сам я не разбираюсь в тонкой настройке mysql, поэтому решил довериться утилите. Судя по отзывам, она неплоха и доверять ей можно, если сам не разбираешься в теме. Забегая вперед скажу, что с помощью этого тюнера я настроил mysql на стабильную работу с фиксированным потребелением памяти. Проблем с этим сервером с тех пор не возникало.

Итак, копируем себе на сервер сам скрипт:

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

Запускаем его:

# perl mysqltuner.pl

Для того, чтобы рекомендации получились более эффективные, служба mysql должна поработать у вас несколько дней. Если накануне перезапускали ее, а я это делал, то рекомендую через несколько дней зайти и еще раз прогнать тесты. Будут новые советы по конфигу.

Для оптимизации потребления памяти, достаточно будет прогнать скрипт в любое время. Я вам рекомендую внимательно изучить его возможности. Подробно на них я сейчас не буду останавливаться, а рассмотрю только то, что касается памяти. Помимо прочего, вы увидите следующую информацию.

У меня уже все оптимизировано под потребленее не более примерно 6 Гб памяти. Расскажу, какие параметры за это отвечают. Как уже сказал ранее, это параметр innodb_buffer_pool_size. В общем случае для mysql сревера рекомендуют указывать этот параметр равный 80% доступной памяти сервера. Но это в том случае, если у вас кроме mysql на этом сервере ничего не крутится. А у нас там полно других служб, поэтому нам такой совет не подходит.

Дальше нам нужно выяснить, сколько памяти занимает thread (процесс, который порождает соединение) и в соотвествии с этим выставить предел числа подключений. Размер thread равен сумме следующих парметров — read_buffer_size + sort_buffer_size + join_buffer_size.

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

С такими настройками максимальное потребление памяти службой mysql не будет превышать 6.8 Гб, о чем подсказывает вывод mysqltuner. Конкретно моему сайту 70 подключений к mysql достаточно. До этого поставил 50, были сообщения о нехватке подключений. На своем сервере выбирайте параметры сами, у меня не копируйте.

 Maximum possible memory usage: 6.8G (58.69% of installed RAM)

На практике так и получилось. Через несколько дней я зашел и прогнал еще раз проверку, которая показала, что реально использование памяти не вышло за эти пределы. Плюс, подредактировал некоторые параметры.

Советы по изменению параметров даются в заключительной секции mysqltuner — Variables to adjust. Не буду приводить свои рекомендации, так как они будут актуальны только для конкретного сервера. Советую посмотреть все рекомендации, почитать описание параметров и попробовать применить их у себя. Слепо не надо менять то, что там советуют.

Приведу список основных параметров mysql, которые влияют на производительность и на которые надо в первую очередь обращать внимание:

• max_connections
• log_bin
• table_open_cache_size
• table_definitions_cache_size
• open_files_limit
• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_flush_log_at_trx_commin
• innodb_flush_method=O_DIRECT

Список взял отсюда. Очень полезная статья, рекомендую.

What is an InnoDB Buffer Pool?

InnoDB buffer pool is the memory space that holds many in-memory data structures of InnoDB, buffers, caches, indexes and even row-data. is the MySQL configuration parameter that specifies the amount of memory allocated to the InnoDB buffer pool by MySQL. This is one of the most important settings in the MySQL hosting configuration and should be configured based on the available system RAM.

In this post, we’ll walk you through two approaches of setting your InnoDB buffer pool size value, examine the pros and cons of those practices, and also propose a unique method to arrive at an optimum value based on the size of your system RAM.

1 ответ

1

Лучший ответ

MySQL принимает новые подключения, но не может обрабатывать все запросы. Количество ожидающих соединений просто накапливается, пока их не будет.

Проблема не в том, что у MySQL, это боты, которые плохо себя ведут. Вам, вероятно, не нужны все те боты, которые сканируют весь ваш сайт каждый раз. К счастью, у вас есть контроль над ними.

Шаг 1: Создайте и запретите все боты, кроме тех, которые вам нужны.

Шаг 2: Создайте карту сайта. Установка последнего измененного времени каждой страницы означает, что боты будут попадать только на измененные страницы вашего сайта. Динамическое создание файла Sitemap (запрос к базе данных) можно с помощью библиотеки PHP: thepixeldeveloper/sitemap.

В этом примере мы предполагаем, что у вас есть база данных с таблицей . Таблица имеет столбцы и .

Вы можете использовать правило перезаписи в Apache (или аналогично на другом HTTP-сервере), чтобы переписать на .

Этого должно быть достаточно, хотя могут быть и боты, которые не уважают robots.txt. Обнаруживать и блокировать их (с помощью IP и/или агента пользователя) в конфигурации вашего сервера HTTP

Также рассмотрим следующее:

Максимальные соединения есть, поэтому ваш сервер не перегружает. Вы должны выполнить тестовый тест, чтобы определить максимальное количество параллельных запросов, которые может обрабатывать ваше приложение. Затем уменьшите это число на 20% и установите максимальное значение как в вашем веб-сервере HTTP, так и в конфигурации MySQL.

Это означает, что ваш сервер даст хороший ответ , прежде чем он перегрузится. Это заставит (хорошо себя вести) ботов сдаться и повторить попытку позже, что означает, что ваша система будет восстанавливаться без ручного внимания.

Ваш script должен также выйти с правильным ответом HTTP.

28 фев. 2017, в 10:52
Поделиться

Ещё вопросы

• 2291Почему я не должен использовать функции mysql_ * в PHP?
• 931Справка — Что означает эта ошибка в PHP?
• 285JSON кодирует результаты MySQL
• 282Хорошая PHP ORM библиотека?
• 224Предупреждение: mysql_connect (): Нет такого файла или каталога (пытается подключиться через unix: ///tmp/mysql.sock) в
• 260Лучший тип поля базы данных для URL
• 144MySQL против MySQLi при использовании PHP
• 19WooCommerce: поиск товаров в базе данных
• 60Как использовать несколько баз данных в Laravel
• 69PHP + PDO + MySQL: как вернуть целочисленные и числовые столбцы из MySQL как целые и числовые значения в PHP?

Заключение

После оптимизации всех указанных выше параметров в bitrixenv, потребление памяти сервером стабилизировалось. Bitrix сайт стал работать ровно с предсказуемой производительностью без неожиданных тормозов и падений.

На этом у меня все по теме оптимизации настроек сервера под bitrix. Система интересная и многогранная. Всегда любопытно заглянуть под капот bitrixenv. Как по мне, сделано неплохо, хотя и доставляет хлопот при разборе каких-то иницидентов.

В целом считаю, что в общем случае, все сделано удобно и функционально для быстрого запуска bitrix сайта. Справится даже неподготовленный человек, а конкретно какой-нибудь программист. Он бы запарился настраивать эту связку самостоятельно, а тут все из коробки работает. Но вот если возникают проблемы, то разобраться бывает не всегда просто.

Следующим этапом жду появление docker сборок с bitrixenv внутри. Либо один общий образ, либо набор через docker-compose. Это было бы логичное продолжение развития в свете популярности контейнеров и микросервисов.

Онлайн курс Основы сетевых технологий

Теоретический курс с самыми базовыми знаниями по сетям. Курс подходит и начинающим, и людям с опытом. Практикующим системным администраторам курс поможет упорядочить знания и восполнить пробелы. А те, кто только входит в профессию, получат на курсе базовые знания и навыки, без воды и избыточной теории. После обучения вы сможете ответить на вопросы:

• На каком уровне модели OSI могут работать коммутаторы;
• Как лучше организовать работу сети организации с множеством отделов;
• Для чего и как использовать технологию VLAN;
• Для чего сервера стоит выносить в DMZ;
• Как организовать объединение филиалов и удаленный доступ сотрудников по vpn;
• и многое другое.

Уже знаете ответы на вопросы выше? Или сомневаетесь? Попробуйте пройти тест по основам сетевых технологий. Всего 53 вопроса, в один цикл теста входит 10 вопросов в случайном порядке. Поэтому тест можно проходить несколько раз без потери интереса. Бесплатно и без регистрации. Все подробности на странице .