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

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

Версия 10:08, 18 октября 2011

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

[mysqld] 
default-storage-engine = innodb

Для возможности использования Backup базы с помощью snapshot'ов (Linux, LVM) в ОС LINUX установите опцию:

[mysqld] 
innodb_file_per_table

Для возможности передачи больших запросов и установки большого количества подключений к БД:

[mysqld] 
max_allowed_packet=50M
max_connections=1000

Для LINUX, повышения ограничения на число открытых файлов.

[mysqld_safe]
open-files-limit=32000 

Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре sql-mode режимов STRICT_TRANS_TABLES и STRICT_ALL_TABLES, это приведёт к неработоспособности некоторых компонентов биллинговой системы. Запрещается установка опции skip-networking, т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.

Возможный вариант конфигурации:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
max_allowed_packet=50M
tmpdir = /storage/mysql-tmp
old_passwords=1
##master-master preparations
#auto_increment_increment=2
#auto_increment_offset=1

# modified parameters
#-500 by historical maximum on bill-1(8Gb) 248 on febr2009
max_connections=500
#x2
table_cache=4096
sort_buffer_size=8M
join_buffer_size=8M
thread_cache_size=384
thread_concurrency=12
#x0.25
query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
memlock
tmp_table_size = 1G
max_heap_table_size = 512M
#x16
key_buffer_size = 5G
wait_timeout = 28800

# log slow queries
log-slow-queries=/var/log/slowquery.log

# replication
server-id=1
replicate-same-server-id=0
log-bin=/var/lib/mysql-binlog/mysql-bin
expire_logs_days=3
replicate-do-db=bgbilling
relay-log-space-limit = 10G
relay-log=mysqld-relay-bin

# myisam
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 1G
myisam_max_sort_file_size = 10G
##unused since 5.0???
#myisam_max_extra_sort_file_size = 10G
##
myisam_repair_threads = 1
myisam_recover
myisam_data_pointer_size = 6
# bdb
skip-bdb

# innodb
#skip-innodb
innodb_file_per_table
innodb_additional_mem_pool_size = 16M
#x8
innodb_buffer_pool_size = 9G
#A recommended value is 2 times the number of CPUs plus the number of disks.
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_group_home_dir = /storage/mysql/
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
##Khadimulin
#innodb_flush_method = O_DIRECT
##
# misc
default-character-set=cp1251
default-collation=cp1251_general_ci

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=32000

[mysql.server]
user=mysql
basedir=/var/lib

[client]
default-character-set=cp1251

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

Ссылки с рекомендациями по настройке InnoDb базы:

• http://maxq.ru/lib/53/
• http://www.mysql.ru/docs/man/InnoDB_start.html

Если вы обновляете базу биллинга с 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 изменения произойдут из-за настроенного реплицирования (см документацию по репликации).

--skyb

Немного пояснений к конфигу которые опытным путем выяснились

key_buffer_size = 5G 

это для таблиц myisam - если используете, то оставьте. Аналог для innodb это

innodb_buffer_pool_size 

он должен составлять порядка 70-80 % от общей памяти, ещё важна опция

innodb_flush_method = O_DIRECT 

которая отключает двойную буферизацию (самим mysql и ОС).