Mysql настройки: Настройка MySQL (Конфигурирование сервера)

Что нужно настроить в mySQL сразу после установки? / Хабр

WASD42

10 авг 2009 в 15:41

Время на прочтение
5 мин

Количество просмотров

138K

MySQL *

Перевод

Автор оригинала:

peter

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.

Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

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

• key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
• innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т. к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size, однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
• innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
• innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
• innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.
• innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
• table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
• thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
• query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Как Вы можете видеть, это — глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение sort_buffer_size, даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

Теги:

• mysql
• настройка

Хабы:

• MySQL

Настройка сервера баз данных MySQL в операционной системе Windows

После завершения загрузки запустите программу установки следующим образом.

1. Щелкните правой кнопкой мыши загруженный установочный файл (например, mysql-installer-community-5.6.14.0.msi ) и выберите пункт ‘Выполнить’.
   Запустится программа установки MySQL.

2. На панели приветствия выберите ‘Установить продукты MySQL’.

3. На панели информации о лицензии ознакомьтесь с лицензионным соглашением, установите флажок принятия и нажмите кнопку ‘Далее’.

4. На панели ‘Найти последние продукты’ нажмите кнопку ‘Выполнить’.
   После завершения операции нажмите кнопку ‘Далее’.

5. На панели «Тип настройки» выберите параметр «Пользовательская», а затем нажмите кнопку «Далее».

6. На панели ‘Выбор компонентов обеспечения’ убедитесь, что выбран MySQL Server 5.6.x, и нажмите кнопку ‘Далее’.

7. На панели ‘Проверить требования’ нажмите кнопку ‘Далее’.

8. На панели ‘Установка’ нажмите кнопку ‘Выполнить’.
   После успешного завершения установки сервера на панели ‘Установка’ отображается информационное сообщение. Нажмите кнопку «Далее».

9. На странице ‘Настройка’ нажмите кнопку ‘Далее’.

10. На первой странице конфигурации сервера MySQL (1/3) установите следующие параметры:

    • Тип конфигурации сервера. Выберите вариант ‘Компьютер для разработки’.

    • Включите поддержку сети TCP/IP. Убедитесь, что флажок установлен, и задайте следующие параметры ниже:

    • Номер порта. Укажите порт подключения. По умолчанию установлено значение 3306; не следует изменять его без необходимости.

    • Откройте порт брандмауэра для доступа к сети. Выберите исключение добавления брандмауэра для указанного порта.

    • Расширенная настройка. Выберите флажок ‘Показать расширенные параметры’ для отображения дополнительной страницы конфигурации для настройки расширенных параметров для экземпляра сервера (если требуется).

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

Нажмите кнопку «Далее».

11. На второй странице конфигурации сервера MySQL (2/3) установите следующие параметры:

    • Пароль учетной записи root.

    • Пароль root для MySQL. Введите пароль пользователя root.

    • Повторите ввод пароля. Повторно введите пароль пользователя root.

Примечание. Пользователь root — это пользователь, который имеет полный доступ к серверу баз данных MySQL — создание, обновление и удаление пользователей и так далее. Запомните пароль пользователя root (администратора) – он понадобится вам при создании примера базы данных.

• Учетные записи пользователя MySQL. Нажмите кнопку ‘Добавить пользователя’ для создания учетной записи пользователя. В диалоговом окне ‘Сведения о пользователе MySQL’ введите имя пользователя, роль базы данных и пароль (например, !phpuser ). Нажмите кнопку «ОК».
  Нажмите кнопку «Далее».

  1. На третьей странице конфигурации сервера MySQL (3/3) установите следующие параметры:

• Имя службы Windows. Укажите имя службы Windows, которая будет использоваться для экземпляра сервера MySQL.

• Запустите сервер MySQL при запуске системы. Не снимайте этот флажок, если сервер MySQL требуется для автоматического запуска при запуске системы.

• Запуск службы Windows в качестве. Возможны следующие варианты.

• Стандартная системная учетная запись. Рекомендуется для большинства сценариев.

• Нестандартный пользователь. Существующая учетная запись пользователя рекомендуется для сложных сценариев.
  Нажмите кнопку «Далее».

  1. На странице ‘Обзор конфигурации’ нажмите кнопку ‘Далее’.

  2. После успешного завершения настройки на панели ‘Завершение’ появляется информационное сообщение. Нажмите кнопку «Завершить».

Примечание. Для проверки успешности настройки запустите диспетчер задач. Если MySQLd-nt. exe присутствует в списке ‘Процессы’, сервер базы данных запущен.

В начало

Отправить отзыв по этому учебному курсу

Параметры конфигурации MySQL

Параметры конфигурации MySQL

Обновите конфигурации параметров MySQL несколькими простыми щелчками через настройки администратора консоли ScaleGrid для развертываний MySQL.

Предложить изменения

Сервер MySQL имеет множество рабочих параметров, которые можно установить в файле конфигурации или файле опций (/etc/my.cnf). Также возможно изменить многие параметры во время выполнения. Консоль ScaleGrid предлагает простой пользовательский интерфейс (UI) для настройки некоторых важных параметров развертывания MySQL.

Вот шаги для доступа к настройкам конфигурации MySQL:

1. Войдите в консоль ScaleGrid.
2. Перейдите на свою страницу MySQL, затем щелкните кластер MySQL, который вы хотите настроить.
3. Выберите вкладку «Администрирование» вверху.
4. Щелкните пункт меню «Конфигурация» слева.

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

📘

Нередактируемые значения полей

Поле значений некоторых параметров затенено серым, что указывает на то, что это не редактируемые параметры для развертывания.

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

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

После нажатия кнопки «Сохранить» вы увидите диалоговое окно подтверждения, показывающее сводку внесенных вами изменений, как показано ниже.

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

Некоторые параметры конфигурации в MySQL вступают в силу только после перезапуска сервера. Одним из таких параметров является «innodb_log_file_size». Если вы установите этот параметр и нажмете кнопку Сохранить — в диалоговом окне подтверждения появится предупреждение о том, что значение конфигурации потребует перезапуска MySQL.

Если вы подтвердите, то конфигурация будет применена к вашему развертыванию MySQL последовательно, один сервер за раз, и сервер будет перезапущен.

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

Это задание можно отслеживать на вкладке «Задания», как показано ниже:

Check out the list of MySQL parameters available to review and configure at ScaleGrid:

Parameter	Description	Editable
port	MySQL server port	No
skip_name_resolve	Отключить разрешение имен DNS	Нет
пользователь	Учетная запись пользователя, которая запускает службу mysql.	№
pid_file	PID-файл	Нет
сокет	Файл сокета	Нет
мандаты защищенных соединений	Нет
ssl	Включить SSL на сервере.	Нет
log_bin	Базовое имя с путем к двоичным файлам журнала.	Нет
expire_logs_days	Количество дней для автоматического удаления двоичного файла журнала.	Да
sync_binlog	Количество групп фиксации двоичного журнала, которые необходимо собрать перед синхронизацией двоичного журнала с диском.	Нет
binlog_group_commit_sync_delay	Количество микросекунд, в течение которых фиксация двоичного журнала ожидает перед синхронизацией файла двоичного журнала с диском.	Да
max_connect_errors	Максимальное количество ошибок подключения с хоста до того, как этот сервер заблокирует его от дальнейших подключений.	Да
max_allowed_packet	Максимальный размер пакета (в байтах), который сервер может отправить и получить.	Да
max_heap_table_size	Максимальный размер, до которого разрешено увеличивать созданные пользователем таблицы ПАМЯТИ.	Нет
max_connections	Максимально допустимое количество одновременных клиентских подключений.	Да
max_user_connections	Максимально допустимое количество одновременных клиентских подключений.	Нет
thread_cache_size	Количество потоков, которые сервер должен кэшировать для повторного использования.	Да
open_files_limit	Количество файловых дескрипторов, доступных для mysqld.	Да
table_open_cache	Количество открытых таблиц для всех потоков.	Нет
table_definition_cache	Количество определений таблиц, которые можно сохранить в кэше определений.	Нет
relay_log	Базовое имя с путем к файлам журнала ретрансляции.	Нет
gtid_mode	Включить ведение журнала на основе GTID	Нет
force_gtid_consistency	Сервер разрешает выполнение только операторов, которые могут быть безопасно зарегистрированы с помощью GTID.	Нет
binlog_format	Двоичный формат журнала	Нет
log_slave_updates	Определяет, должны ли обновления, полученные подчиненным сервером от главного сервера, регистрироваться в собственном двоичном журнале подчиненного.	Нет
slave_net_timeout	Количество секунд ожидания дополнительных данных от соединения ведущий/ведомый перед прекращением чтения.	Да
master_info_repository	Чтобы определить, хранится ли главная информация в файле или таблице.	Нет
relay_log_info_repository	Чтобы определить, хранятся ли журналы реле в виде файла или таблицы.	Нет
sync_master_info	Количество событий, после которых ведомое устройство обновляет позицию потока ввода-вывода в своей главной таблице репозитория информации.	Нет
sync_relay_log	Количество событий в журнал ретрансляции, после которых ведомое устройство сбрасывает журналы ретрансляции на диск.	№
relay_log_recovery	Включить автоматическое восстановление журнала ретрансляции сразу после запуска сервера.	Нет
slave_parallel_workers	Количество подчиненных потоков приложения для параллельного выполнения транзакций репликации.	Да
slave_preserve_commit_order	Убедитесь, что фиксация транзакций происходит в том же порядке, в котором они появляются в журнале подчиненного реле.	Нет
slave_parallel_type	Политика, определяющая выполнение параллельных транзакций.	Нет
rpl_semi_sync_master_timeout	Время в миллисекундах, по истечении которого мастер переходит в асинхронный режим репликации, если он не получил ответа от слейва.	Да
rpl_semi_sync_master_wait_for_slave_count	Количество ведомых подтверждений, которые ведущий должен получить за транзакцию перед продолжением.	Нет
rpl_semi_sync_master_wait_no_slave	Определяет, будет ли ведущий ожидать истечения периода тайм-аута, настроенного rpl_semi_sync_master_timeout, даже если количество ведомых устройств упадет до значения, меньшего, чем количество ведомых устройств, настроенное rpl_semi_sync_master_wait_for_slave_count в течение периода тайм-аута.	Нет
relay_log_space_limit	Верхний предел общего размера (в байтах) всех журналов ретрансляции на подчиненном устройстве. Значение 0 означает «без ограничений».	Да
max_relay_log_size	Максимальный размер (в байтах) журнала ведомого реле, после которого он сменяется. Значение 0 означает, что этот размер будет таким же, как max_binlog_size.	Да
max_binlog_size	Максимальный размер (в байтах) бинарного журнала, после которого он сменяется.	Да
каталог данных	Путь к каталогу данных сервера MySQL.	Нет
general_log	Включить общий журнал	Нет
general_log_file	General Log File	No
log_error	Error log file	No
default_storage_engine	Storage Engine for MySQL	No
innodb_log_files_in_group	Number of log files in log_group.	Нет
innodb_flush_log_at_trx_commit	Частота транзакций, при которой буфер журнала InnoDB записывается в журнал и сбрасывается на диск.	Нет
innodb_flush_method	Метод, используемый для сброса данных в файлы данных и журналов InnoDB.	Нет
innodb_file_per_table	Определяет, будет ли каждая вновь созданная таблица иметь свои данные и индекс как часть отдельного файла ibd.	Нет
innodb_log_file_size	Определяет размер (в байтах) каждого файла журнала в log_group.	Да
innodb_buffer_pool_size	Размер (в байтах) памяти, используемой InnoDB для кэширования таблицы и данных индекса.	Да
innodb_io_capacity	Верхний предел количества операций ввода-вывода, выполняемых в секунду фоновыми задачами InnoDB.	Да
innodb_adaptive_hash_index	Указывает, включены или отключены адаптивные хеш-индексы InnoDB.	Да
innodb_lock_wait_timeout	Продолжительность времени в секундах, в течение которого транзакция InnoDB ожидает блокировки строки, прежде чем сдаться.	Да
log_queries_not_using_indexes	Записывает запросы, которые должны получить все строки, в журнал медленных запросов.	Да
log_slow_admin_statements	Включить медленные административные операторы в операторы, записываемые в журнал медленных запросов.	Да
log_throttle_queries_not_using_indexes	Переменная для ограничения количества медленных запросов в минуту, которые могут быть записаны в журнал медленных запросов.	Да
long_query_time	Если запрос занимает больше указанного количества секунд, он считается медленным.	Yes
slow_query_log	Generate slow query log files	No
slow_query_log_file	Name of the slow query log file	No
symbolic_links	Enable symbolic link support	No
interactive_timeout	Количество секунд, в течение которых сервер ожидает активности в интерактивном соединении, прежде чем закрыть его.	Да
div_precision_increment	Количество секунд, в течение которых сервер ожидает активности в интерактивном соединении перед его закрытием.	Да
sql_mode	Набор опций, определяющий синтаксис, поддерживаемый MySQL, а также выполняемые проверки проверки данных.	Да
event_scheduler	State of the Event Scheduler	Yes

Updated 7 months ago

Setting Up the MySQL Database Server in the Windows Operating System

• Начало загрузки
• Запуск установки

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

В этом документе рекомендуется выполнить последовательность шагов для настройки сервера базы данных MySQL версии 5.6 в операционной системе Windows. В нем не рассматриваются детали конфигурации MySQL, а описывается последовательность необходимых шагов.

Информацию об установке и настройке сервера баз данных MySQL для других операционных систем см. в документации по установке и обновлению MySQL.

Начало загрузки

1. Перейдите по адресу http://dev.mysql.com/downloads/installer/.

2. Нажмите кнопку «Загрузить».

3. Сохраните файл установщика в своей системе.

Начало установки

После завершения загрузки запустите программу установки следующим образом:

1. Щелкните правой кнопкой мыши загруженный установочный файл (например, mysql-installer-community-5.6.14.0.msi ) и нажмите «Выполнить».
   Запускается установщик MySQL.

2. На панели приветствия выберите Установить продукты MySQL.

3. На панели «Информация о лицензии» просмотрите лицензионное соглашение, установите флажок «Принять» и нажмите «Далее».

4. На панели «Поиск последних продуктов» нажмите «Выполнить».
   По завершении операции нажмите Далее.

5. На панели «Тип установки» выберите «Пользовательский» и нажмите «Далее».

6. Убедитесь, что на панели Feature Selection выбран MySQL Server 5.6.x, и нажмите Next.

7. На панели «Проверка требований» нажмите «Далее».

8. На панели «Установка» нажмите «Выполнить».
   После успешного завершения установки сервера на панели Установка появится информационное сообщение. Нажмите «Далее.

9. На панели «Конфигурация» нажмите «Далее».

10. На первой странице конфигурации сервера MySQL (1/3) установите следующие параметры:

    • Тип конфигурации сервера . Выберите вариант «Машина разработки».

    • Включить сеть TCP/IP . Убедитесь, что флажок установлен, и укажите параметры ниже:

    • Номер порта . Укажите порт подключения. Значение по умолчанию — 3306 — оставьте без изменений, если нет особых причин его менять.

    • Открыть порт брандмауэра для доступа к сети . Выберите, чтобы добавить исключение брандмауэра для указанного порта.

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

Щелкните Далее.

1. На второй странице конфигурации сервера MySQL (2/3) установите следующие параметры:

   • Пароль корневой учетной записи .

   • Корневой пароль MySQL . Введите пароль пользователя root.

   • Повторить пароль . Повторно введите пароль пользователя root.

• Учетные записи пользователей MySQL . Щелкните Добавить пользователя, чтобы создать учетную запись пользователя. В диалоговом окне «Сведения о пользователе MySQL» введите имя пользователя, роль базы данных и пароль (например, !phpuser ). Нажмите «ОК».

Щелкните Далее.

1. На третьей странице конфигурации сервера MySQL (3/3) установите следующие параметры:

   • Имя службы Windows . Укажите имя службы Windows, которое будет использоваться для экземпляра сервера MySQL.

   • Запустите сервер MySQL при запуске системы . Оставьте флажок установленным, если сервер MySQL должен автоматически запускаться при запуске системы.

   • Запустить службу Windows как . Выберите любой:

   • Стандартная системная учетная запись . Рекомендуется для большинства сценариев.

   • Пользовательский . Существующая учетная запись пользователя рекомендуется для расширенных сценариев.
     Нажмите «Далее.

2. На странице Обзор конфигурации нажмите Далее.

3. После успешного завершения настройки на панели Complete появится информационное сообщение.