Mysql max connections windows

Как изменить лимиты в MySQL/MariaDB

Обновлено: 19.08.2021
Опубликовано: 26.08.2016

Тематические термины: MySQL, MariaDB.

По умолчанию, настройки лимитов СУБД могут оказаться не оптимальными. В некоторых случаях, это может привести к накапливанию очередей и низкой производительности приложений или, вовсе, привести к ошибке «MySQL server has gone away» — необходимо увеличить стандартные значения.

В рамках данной инструкции мы рассмотрим следующие лимиты:

Просмотр текущих значений

Выполняется в оболочке mysql — для подключения вводим:

mysql -uroot -p

Посмотреть значений выполняется командой:

> SHOW VARIABLES WHERE `variable_name`='<Имя параметра>’;

Примеры более конкретных запросов будут рассмотрены ниже.

Принцип настройки

Для применения настройки можно выполнить запрос SQL со следующим синтаксисом:

SET GLOBAL <имя параметра> = <значение>;

Данная настройка будет использоваться до перезапуска СУБД. Чтобы применить ее навсегда, необходимо отредактировать конфигурационный файл. Местоположение данного файла может зависеть от версии MySQL/MariaDB. Возможные варианты:

• /etc/my.cnf
• /etc/my.cnf.d/server.cnf

В конфигурационном файле мы должны найти раздел [mysqld] и добавить в него значение для нужной нам опции:

<имя параметра> = <значение>

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

systemctl restart mysql || systemctl restart mariadb

* в некоторых системах перезагрузка сервера баз данных выполняется командой service mysql restart или service mysqld restart или service mysql-server restart

Это приведет к перебою в работе СУБД.

Настройка max_connections

Конфигурационный файл:

[mysqld]
…
max_connections = 500

* в данном примере мы разрешим 500 одновременных подключений к MySQL. При превышении данного значения будет отображаться ошибка too many connections.

Задать текущее значение в SQL:

> SET GLOBAL max_connections = 500;

Посмотреть текущее значение:

> SHOW VARIABLES WHERE `variable_name`=’max_connections’; 

Оптимальное значение

Для данного лимита нет золотого стандарта — маленькое значение может привести к выстраиванию очередей запросов, большое — к перегрузке серверного оборудования. Правильнее всего постоянно наблюдать за значениями max_connections и threads_connected и определить для себя свой, так называемый, Best Practices.

На первое время, для сервера можно поставить лимит в 200-300 подключений.

Опция max_user_connections

В отличие от max_connections, данная опция задает лимит для подключений конкретному клиенту.

Конфигурационный файл:

[mysqld]
…
max_connections = 500
max_user_connections = 50

* в данном примере сервер будет принимать 500 одновременных подключений, но от каждого клиента, максимум — 50.

Задать текущее значение в SQL:

> SET GLOBAL max_user_connections = 50;

Посмотреть текущее значение:

> SHOW VARIABLES WHERE `variable_name`=’max_user_connections’; 

Лимит запросов wait_timeout 

Конфигурационный файл:

[mysqld]
…
wait_timeout = 1200

* в данном примере устанавливаем лимит на 20 минут.

Задать текущее значение в SQL:

> SET GLOBAL wait_timeout = 1200;

Посмотреть текущее значение:

> SHOW VARIABLES WHERE `variable_name`=’wait_timeout’; 

Максимальный размер пакета max_allowed_packet

Конфигурационный файл:

[mysqld]
…
max_allowed_packet = 128M

Задать текущее значение в SQL:

> SET GLOBAL max_allowed_packet = 134217728;

Посмотреть текущее значение:

> SHOW VARIABLES WHERE `variable_name`=’max_allowed_packet’;

Время ожидания транзакции innodb_lock_wait_timeout

Конфигурационный файл:

[mysqld]
…
innodb_lock_wait_timeout = 500

Задать текущее значение в SQL:

> SET GLOBAL innodb_lock_wait_timeout = 500;

Посмотреть текущее значение:

> SHOW VARIABLES WHERE `variable_name`=’innodb_lock_wait_timeout’; 

MySQL is an open-source relational database management system that is widely used for web applications. It allows multiple users to access and manipulate data simultaneously, and it uses a system variable called “max_connections” to limit the number of connections that can be made to the database server.

Max_connections is a configuration parameter in MySQL that determines the maximum number of concurrent connections that can be established with the database server. This value is set at server startup and can be modified dynamically. The default value is usually set to 151, but this can vary depending on the server’s configuration.

In this article, we will discuss how to check and update the max_connections value in MySQL.

Checking the Current max_connections Value

To check the current max_connections value, you can run the following command in MySQL:

SHOW VARIABLES LIKE 'max_connections'; 

Output
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

This command will display the current value of max_connections. If you are using a MySQL client, such as MySQL Workbench, you can also check the value by navigating to the “System Variables” section of the “Options File” tab.

Updating the max_connections

You can use SET command to modify the max_connections value for the current session. To set the max_connections value temporarily, use the following sql statement:

SET GLOBAL max_connections = <new_value>; 

Replace <new_value> with the desired value for max_connections.

Note that this command sets the max_connections value globally, meaning it affects all sessions connected to the MySQL server. Be careful not to set the value too high, as this can cause performance issues.

Updating the max_connections Permanently

To modify the max_connections value permanently, you need to modify the MySQL configuration file. The location of this file can vary depending on your operating system and installation method.

For example, on a Linux system with a default installation of MySQL, the configuration file is usually located at /etc/my.cnf or /etc/mysql/my.cnf. On a Windows system, the configuration file is usually located at C:\Program Files\MySQL\MySQL Server 8.0\my.ini.

Once you have located the configuration file, open it in a text editor and find the [mysqld] section. If there is no existing entry for max_connections, add the following line:

Here 300 is the new value, You can Replace this with the desired value for max_connections.

If there is already an entry for max_connections, modify the value to the desired value.

Restart the MySQL Server

After modifying the configuration file, you need to restart the MySQL server for the changes to take effect. You can do this using the following command:

sudo systemctl restart mysql 

On a Windows system, you can restart the MySQL service using the Services tool.

Checking the Updated max_connections Value

To confirm that the max_connections value has been updated, you can use the `SHOW VARIABLES` command as described earlier. Alternatively, you can use the following command:

SELECT @@max_connections; 

This command will display the current value of max_connections.

Output
+-------------------+
| @@max_connections |
+-------------------+
|               300 |
+-------------------+
1 row in set (0.04 sec)

Reset the max_connections Value (Optional)

If you want to reset the max_connections value back to its default or previous value, use the following command:

SET GLOBAL max_connections = DEFAULT; 

This command sets the max_connections value back to its default value or the value it was set to before you changed it.

If you have updated the max_connections value by editing the configuration file, also revert the default value to 151.

Conclusion

`max_connections` is an important configuration parameter in MySQL that determines the maximum number of concurrent connections that can be established with the database server. In this article, we have discussed how to check and update the max_connections value in MySQL. By following these steps, you can ensure that your MySQL server is configured to handle the appropriate number of connections for your application’s needs.

Sometimes MySQL server may give “Too many connections” error message when you try to connect to a MySQL database. Here’s how to increase max connections in MySQL to fix this problem.

What causes MySQL Too Many Connections?

If you get “Too many connections” error message while connecting to MySQL database it means all available connections have been used by the various clients and your MySQL Server cannot open any new connections until any of the existing connections is closed.

How Many Connections can MySQL handle?

By default, MySQL 5.5+ can handle up to 151 connections. This number is stored in server variable called max_connections. You can update max_connections variable to increase maximum supported connections in MySQL, provided your server has enough RAM to support the increased connections.

Here are the steps to increase max connections in MySQL.

1. Check the default max connections

Log into MySQL command line tool and run the following command to get the current default max connections supported by your database server.

mysql> show variables like "max_connections";

You will see the following output.

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

2. Increase Max Connections

There are two ways to increase MySQL connections. Here’s the command to update max connections to 200 via MySQL command line tool without restart.

mysql> set global max_connections = 200;

The above command will increase max connections without restart but once the server is restarted, the changes will be gone. This method is useful to increase available connections in a production system such as a website/app, without having to restart your database server.

To permanently increase max connections, open my.cnf file,

$ sudo vi /etc/my.cnf

Depending on your Linux distribution and type of installation, my.cnf file may be located at any of the following locations.

• /etc/my.cnf
• /etc/mysql/my.cnf
• $MYSQL_HOME/my.cnf
• ~/.my.cnf

Add the following line under [mysqld] section.

max_connections = 200

Now if you restart MySQL server, the changes will persist.

3. Restart MySQL Server

Restart MySQL Server to apply changes

$ sudo service mysql restart

Hopefully, the above tutorial will help you increase max connections in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it for free!

Материал из BiTel WiKi

Перейти к: навигация, поиск

База MySQL данных должна быть настроена с поддержкой транзакций.
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.

Настройка MySQL

Рекомендуемая конфигурация, разбор параметров после. Пример сделан для ОС *NIX типа, как наиболее подходящей для высоконагруженных серверов БД. При необходимости его можно адаптировать для Windows системы, изменив соответственно пути файловой системы и пропустив секцию mysqld.safe.

Кодировка 1251 базы данных использовалась по-умолчанию до версии биллинга 6.0 — исторически сложившаяся и не изменяемая как не несущая в себе существенных неудобств. Но при большой необходимости (например, требования хранения в БД символов других алфавитов) может быть изменена на UTF-8 или иную, при этом необходимо также скорректировать .properties файлы приложений биллинга на предмет кодировки БД.
С версии 6.1 и выше по-умолчанию кодировка используется UTF-8 (для БД «character_set» везде «utf8», настройки «collation» везде «utf8_unicode_ci» (не «utf8_general_ci»))

[mysqld]
datadir = /var/lib/mysql
tmpdir = /tmp
socket = /var/lib/mysql/mysql.sock
user = mysql
log-slow-queries =/var/log/slowquery.log
skip-name-resolve
sql_mode=
#
default-storage-engine = innodb
default-character-set = cp1251
default-collation = cp1251_general_ci
#
wait_timeout = 28800
max_allowed_packet = 64M
max_connections = 1000
#
memlock
table_cache = 4096
thread_cache_size = 64
thread_concurrency = 2
#
# InnoDb
innodb_file_per_table
#
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
#
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
#
innodb_thread_concurrency = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
#
# MyIsam
#key_buffer_size = 1G
#myisam_recover
#myisam_repair_threads = 1
#myisam_data_pointer_size = 6
#
# Replication
#server-id = 1
#log-bin=/var/lib/mysql-binlog/mysql-bin
#expire_logs_days = 3
#replicate-do-db=bgbilling
#relay-log-space-limit = 10G
#
[mysqld_safe]
open-files-limit = 32000
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#
[client]
default-character-set = cp1251
#
[mysqldump]
max_allowed_packet = 50M
default-character-set = cp1251

Опции mysqld — общие параметры:

• datadir — каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
• tmpdir — каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.
• socket — файловый сокет, возможность подключения консольным клиентом.
• user — пользователь ОС, под которым запускается БД.
• log-slow-queries — логирование запросов, выполняющихся длительное время.
• skip-name-resolve — отключает DNS резолвинг для устанавливаемых соединений.
• default-storage-engine — тип хранилища таблиц по-умолчанию, InnoDB — транзакционный тип хранилища.
• default-character-set — кодировка по-умолчанию для вновь создаваемых таблиц.
• default-collation — collation (порядок букв, используется при сортировке) по-умолчнию для вновь создаваемых таблиц.
• sql_mode — запрещается установка режимов STRICT_TRANS_TABLES и STRICT_ALL_TABLES, это приведёт к неработоспособности некоторых компонентов биллинговой системы.
• max_allowed_packet — максимальный размер пакета с запросом, принимаемого сервером.
• max_connections — ограничение на максимальное число соединений с БД.
• memlock — запрет на перенос процесса mysqld в свап.
• table_cache — число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение.
• thread_cache_size — размер кэша потоков для предотвращения их повторного создания.
• thread_concurrency — число одновременно работающих потоков, рекомендуется ставить 2 * количество CPU + количество дисков.

Запрещается установка опции skip-networking, т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.

Опции mysqld — InnoDB:

• innodb_file_per_table — для возможности использования Backup базы с помощью snapshot’ов (Linux, LVM) в ОС LINUX.
• innodb_log_group_home_dir — путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.
• innodb_buffer_pool_size — размер буфера под все нужды, он должен составлять порядка 70-80 % от общей памяти сервера БД.
• innodb_additional_mem_pool_size — параметр можно не изменять, размер буфера под доп. цели.

• innodb_log_files_in_group — количество файлов журналов транзакций в группе журналов; InnoDB производит запись в файлы по круговому способу; увеличение ускоряет запись но тормозит восстановление информации в случае сбоя.
• innodb_log_file_size — размер каждого файла журнала в группе журналов (указывается в мегабайтах).
• innodb_log_buffer_size — размер буфера, который в InnoDB используется для записи информации файлов журналов на диск.
• innodb_lock_wait_timeout — время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать прекращения блокировки прежде, чем будет произведен откат.

• innodb_thread_concurrency — должно совпадать с thread_concurrency; число одновременно работающих потоков, рекомендуется ставить 2 * количество CPU + количество дисков.
• innodb_flush_log_at_trx_commit — 2 отменяет сброс данных на диск при каждой транзакции, ускорение работы.
• innodb_flush_method — O_DIRECT отключает двойную буферизацию (самим mysql и ОС).

Опции mysqld -MyIsam, если используется (например, для некритичных таблиц):

• key_buffer_size — размер кэша для хранения индексов;
• myisam_recover -восстановление битых таблиц при старте сервера.
• myisam_repair_threads — число потоков восстановления.
• myisam_data_pointer_size — возможность создания больших первичных ключей в таблицах.

Опции mysqld — репликации, если используется:

• server-id — идентификатор сервера.
• log-bin — место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
• expire_logs_days — автоматическое удаление старых bin-логов.
• replicate-do-db — реплицируемая БД.
• relay-log-space-limit — ограничение на объём bin-логов.

Опции mysqld_safe:

• open-files-limit — лимит количества открытых файлов для ОС Linux.
• log-error — файл для логирования ошибок.
• pid-file — PID файл процесса.

Опции mysqldump:

• max_allowed_packet — установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
• default-character-set — кодировка по-умолчанию при снятии дампов БД.

Опции client:

• default-character-set — кодировка по-умолчанию при подключении консольным клиентом.

В качестве примера конфигурации вы также можете использовать примеры конфигурации MySQL (/usr/share/mysql/my-innodb-heavy-4G.cnf).

Ссылки:

• http://mysql.ru/docs/man/ — общее руководство по администрированию.
• http://mysql.ru/docs/man/InnoDB_start.html — параметры настройки InnoDb.
• http://mysql.ru/docs/man/Replication_Options.html — параметры настройки репликации.
• http://maxq.ru/lib/53/ — рекомендации по настройке InnoDb базы, MyIsam базы.
• http://mysqltuner.pl/mysqltuner.pl — скрипт автоматической настройки параметров БД.

Настройка MySQL для Windows 7

Ниже Вы можете ознакомиться с примерами файла my.ini, настроенного в соответствии с вышеописанными рекомендациями под ОС Windows 7 x32. По разным причинами, связанными в основном с обновлением программного обеспечения MySQL, некоторые переменные и методы в файле конфигурации могут вызывать конфликты при запуске службы сервера MySQL.

Стоит отметить, что начиная с версии 5.6 служба может быть занесена в реестр не как MySQL, а как MySQL56. Также, если в вашей системе остались какие-либо данные о предыдущих установках MySQL, имя службы может принять дополнительный порядковый номер, как например MySQL56_1. Присвоенные имена служб можно проверить через services.msc или диспетчер задач. Будьте внимательны, не все службы можно отследить через msconfig.exe! В случае, если служба называется не MySQL, стоит подправить строку dependencies в server.ini, который находится в установочной директории сервера BGBilling, и исправить название службы на действительное.

Итак, ниже приведены два примера my.ini. Первый использовался для запуска MySQL версии 5.1, второй — 5.6.

MySQL 5.1

[client]
port=3306
default-character-set=cp1251

[mysql]
default-character-set=cp1251

[mysqld]
port=3306
basedir="C:/mysql"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data"
tmpdir = "C:/tmpserver"
socket="C:/mysql/mysql.sock"
user=mysql
log-slow-queries=C:/mysql/log/slowquery.log
skip-name-resolve
#
character-set-server=cp1251
default-storage-engine = INNODB
default-character-set=cp1251
default-collation=cp1251_general_ci
sql-mode=
#
max_allowed_packet=50M
max_connections=800
#
memlock
table_cache=4096
thread_cache_size=300
thread_concurrency=2
#
# InnoDb
innodb_file_per_table
#
innodb_log_group_home_dir = "C:/mysql/lib/"
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
#
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
#
innodb_thread_concurrency = 2
innodb_flush_log_at_trx_commit = 2
#innodb_flush_method = O_DIRECT
#
# MyIsam
#key_buffer_size = 1G
#myisam_recover
#myisam_repair_threads = 1
#myisam_data_pointer_size = 6
#
# Replication
#server-id=1
#log-bin=/var/lib/mysql-binlog/mysql-bin
#expire_logs_days=3
#replicate-do-db=bgbilling
#relay-log-space-limit = 10G
#
#
#
[mysqldump]
max_allowed_packet=50M
default-character-set=cp1251

MySQL 5.6

[client]
port=3306
default-character-set=cp1251

[mysql]
default-character-set=cp1251

[mysqld]
port=3306
basedir="C:/MySQL/MySQL Server 5.6"
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data"
tmpdir = "C:/tmpserver"
socket="C:/mysql/mysql.sock"
user=mysql
#log-slow-queries="C:/MySQL/MySQL Server 5.6/slowquery.log"
skip-name-resolve
#
character-set-server=cp1251
default-storage-engine = INNODB
#default-character-set=cp1251
#default-collation=cp1251_general_ci
sql-mode=
#
max_allowed_packet=50M
max_connections=800
#
memlock
#table_cache=4096
thread_cache_size=300
thread_concurrency=2
#
# InnoDb
innodb_file_per_table
#
innodb_log_group_home_dir = "C:/MySQL/MySQL Server 5.6/lib/"
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
#
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
#
innodb_thread_concurrency = 2
innodb_flush_log_at_trx_commit = 2
#innodb_flush_method = O_DIRECT
#
# MyIsam
#key_buffer_size = 1G
#myisam_recover
#myisam_repair_threads = 1
#myisam_data_pointer_size = 6
#
# Replication
#server-id=1
#log-bin=/var/lib/mysql-binlog/mysql-bin
#expire_logs_days=3
#replicate-do-db=bgbilling
#relay-log-space-limit = 10G
#
#
#
[mysqldump]
max_allowed_packet=50M
#default-character-set=cp1251

Набор Perl утилит для работы с MySQL

В архиве Медиа: mysql_perl_utilites.zip размещены файлы:

• access.pm — параметры доступа к MySQL для остальных скриптов.
• extract_myisam.pl — выбор MyIsam таблиц из базы и вывод в файл tables.
• convert.pl — конвертация таблиц из файла tables в InnoDb.
• drop.pl — удаление таблиц, перечисленных в файле tables.

Конвертацию можно выполнять только после включения в my.cnf поддержки InnoDb. Следует учитывать, что конвертация больших таблиц может быть очень длительной, поэтому оптимальным будет исключить из конвертации помесячные таблицы с сессиями, наработками и т.п.
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере.

Выберите MyIsam таблицы в файл tables с помощью скрипта extract_myisam.pl, скорректируйте файл tables, оставив только последние месячные и постоянные таблицы.
Затем выполните скрипт convert.pl.

Алгоритм конвертации с репликацией

1. Включить поддержку InnoDb на slave-сервере.
2. Включить поддержку InnoDb на master-сервере.
3. Запустить скрипт конвертации в InnoDB (см. ранее) для master-сервера.