Рекомендации по настройке MySQL
Материал из BiTel WiKi
База MySQL данных должна быть настроена с поддержкой транзакций. Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
Настройка MySQL
Рассмотрим рекомендуемую конфигурацию по фрагментам. Полная конфигурация получается объединением фрагментов.
Секция mysqldump
[mysqldump] max_allowed_packet=50M default-character-set=cp1251
Опции:
- max_allowed_packet - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
- default-character-set - кодировка по-умолчанию при снятии дампов БД.
Секция client
[client] default-character-set=cp1251
Опции:
- default-character-set - кодировка по-умолчанию при подключении консольным клиентом.
Секция mysqld - общие параметры
[mysqld] datadir=/var/lib/mysql tmpdir = /tmp socket=/var/lib/mysql/mysql.sock user=mysql log-slow-queries=/var/log/slowquery.log # default-storage-engine = innodb default-character-set=cp1251 default-collation=cp1251_general_ci sql-mode= # max_allowed_packet=50M max_connections=1000 # memlock table_cache=4096 thread_cache_size=300 thread_concurrency=2
Опции:
- datadir - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
- tmpdir - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.
- socket - файловый сокет, возможность подключения консольным клиентом.
- user - пользователь ОС, под которым запускается БД.
- log-slow-queries - логирование запросов, выполняющихся длительное время.
- 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 # 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 ##
Опции:
- 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 = 1G myisam_recover myisam_repair_threads = 1 myisam_data_pointer_size = 6
Опции:
- key_buffer_size - размер кэша для хранения индексов;
- myisam_recover -восстановление битых таблиц при старте сервера.
- myisam_repair_threads - число потоков восстановления.
- myisam_data_pointer_size - возможность создания больших первичных ключей в таблицах.
Секция mysqld - параметры репликации, если используется
server-id=1 log-bin=/var/lib/mysql-binlog/mysql-bin expire_logs_days=3 replicate-do-db=bgbilling relay-log-space-limit = 10G
Опции:
- server-id - идентификатор сервера.
- log-bin - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
- expire_logs_days - автоматическое удаление старых bin-логов.
- replicate-do-db - реплицируемая БД.
- relay-log-space-limit - ограничение на объём bin-логов.
Секция mysqld_safe
[mysqld_safe] open-files-limit=32000 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Опции:
- open-files-limit - лимит количества открытых файлов для ОС Linux.
- log-error - файл для логирования ошибок.
- pid-file - PID файл процесса.
Ссылки
В качестве примера конфигурации вы также можете использовать примеры конфигурации 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 базы.
Конвертация MyIsam БД в InnoDb
Если вы обновляете базу биллинга с MyIsam таблицами, вы можете произвести конвертацию в ОС LINUX по примерно такой методике:
mysql -u [USER_NAME] -p -e "SHOW TABLES IN bgbilling;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
После этого можно посмотреть и почистить файл с перечнем таблиц для конвертации alter_table.sql.
mysql -u [USER_NAME] -p bgbilling < alter_table.sql
Конвертацию можно выполнять только после включения в my.cnf поддержки InnoDb. Следует учитывать, что конвертация больших таблиц может быть очень длительной, поэтому оптимальным будет исключить из конвертации помесячные таблицы с сессиями, наработками и т.п. Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере.
Пример конвертации
Задача сконвертировать базу, имеется два сервера с настроенной репликацией. Решение:
- 1. Включить поддержку InnoDb на slave-сервере.
- 2. Включить поддержку InnoDb на master-сервере.
- 3. Запустить скрипт конвертации в InnoDB для master-сервера.
Скрипт выгружает таблицы для конвертации (кроме помесячных) в файл и потаблично конвертирует, запрашивая подтверждение на выполнение операции. Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты. Скрипт конвертирует только в master базе, а в slave изменения произойдут из-за настроенного реплицирования (см документацию по репликации).
