Рекомендации по настройке MySQL

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

Текущая версия на 04:59, 12 сентября 2017

База 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-сервера.