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

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

(Различия между версиями)
Перейти к: навигация, поиск
(Секция mysqld - общие параметры)
(Настройка MySQL)
Строка 3: Строка 3:
== Настройка MySQL ==
== Настройка MySQL ==
-
Рассмотрим рекомендуемую конфигурацию по фрагментам. Полная конфигурация получается объединением фрагментов.
+
Рекомендуемая конфигурация, разбор параметров после.
-
 
+
-
=== Секция mysqldump ===
+
-
<pre>
+
-
[mysqldump]
+
-
max_allowed_packet=50M
+
-
default-character-set=cp1251
+
-
</pre>
+
-
 
+
-
Опции:
+
-
* '''max_allowed_packet''' - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
+
-
* '''default-character-set''' - кодировка по-умолчанию при снятии дампов БД.
+
-
 
+
-
=== Секция client ===
+
-
<pre>
+
-
[client]
+
-
default-character-set=cp1251
+
-
</pre>
+
-
 
+
-
Опции:
+
-
* '''default-character-set''' - кодировка по-умолчанию при подключении консольным клиентом.
+
-
 
+
-
=== Секция mysqld - общие параметры ===
+
<pre>
<pre>
[mysqld]
[mysqld]
Строка 47: Строка 25:
thread_cache_size=300
thread_cache_size=300
thread_concurrency=2
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
</pre>
</pre>
-
Опции:
+
Опции mysqld - общие параметры:
* '''datadir''' - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
* '''datadir''' - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
* '''tmpdir''' - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.
* '''tmpdir''' - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.
Строка 69: Строка 88:
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
-
=== Секция mysqld - параметры InnoDB ===
+
Опции mysqld - InnoDB:
-
<pre>
+
-
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
+
-
##
+
-
</pre>
+
-
 
+
-
Опции:
+
* '''innodb_file_per_table''' - для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX.
* '''innodb_file_per_table''' - для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX.
* '''innodb_log_group_home_dir''' - путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.
* '''innodb_log_group_home_dir''' - путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.
Строка 103: Строка 103:
* '''innodb_flush_method''' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС).
* '''innodb_flush_method''' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС).
-
=== Секция mysqld - параметры MyIsam если используется (например, для некритичных таблиц) ===
+
Опции mysqld -MyIsam, если используется (например, для некритичных таблиц):
-
<pre>
+
-
key_buffer_size = 1G
+
-
myisam_recover
+
-
myisam_repair_threads = 1
+
-
myisam_data_pointer_size = 6
+
-
</pre>
+
-
 
+
-
Опции:
+
* '''key_buffer_size''' - размер кэша для хранения индексов;
* '''key_buffer_size''' - размер кэша для хранения индексов;
* '''myisam_recover''' -восстановление битых таблиц при старте сервера.
* '''myisam_recover''' -восстановление битых таблиц при старте сервера.
Строка 117: Строка 109:
* '''myisam_data_pointer_size''' - возможность создания больших первичных ключей в таблицах.
* '''myisam_data_pointer_size''' - возможность создания больших первичных ключей в таблицах.
-
=== Секция mysqld - параметры репликации, если используется ===
+
Опции mysqld - репликации, если используется:
-
<pre>
+
-
server-id=1
+
-
log-bin=/var/lib/mysql-binlog/mysql-bin
+
-
expire_logs_days=3
+
-
replicate-do-db=bgbilling
+
-
relay-log-space-limit = 10G
+
-
</pre>
+
-
 
+
-
Опции:
+
* '''server-id''' - идентификатор сервера.
* '''server-id''' - идентификатор сервера.
* '''log-bin''' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
* '''log-bin''' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
Строка 133: Строка 116:
* '''relay-log-space-limit''' - ограничение на объём bin-логов.
* '''relay-log-space-limit''' - ограничение на объём bin-логов.
-
=== Секция mysqld_safe ===
+
Опции mysqld_safe:
-
<pre>
+
-
[mysqld_safe]
+
-
open-files-limit=32000
+
-
log-error=/var/log/mysqld.log
+
-
pid-file=/var/run/mysqld/mysqld.pid
+
-
</pre>
+
-
 
+
-
Опции:
+
* '''open-files-limit''' - лимит количества открытых файлов для ОС Linux.
* '''open-files-limit''' - лимит количества открытых файлов для ОС Linux.
* '''log-error''' - файл для логирования ошибок.
* '''log-error''' - файл для логирования ошибок.
* '''pid-file''' - PID файл процесса.  
* '''pid-file''' - PID файл процесса.  
-
=== Ссылки ===
+
Опции mysqldump:
 +
* '''max_allowed_packet''' - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
 +
* '''default-character-set''' - кодировка по-умолчанию при снятии дампов БД.
 +
 
 +
Опции client:
 +
* '''default-character-set''' - кодировка по-умолчанию при подключении консольным клиентом.
 +
 
В качестве примера конфигурации вы также можете использовать примеры конфигурации MySQL ('''/usr/share/mysql/my-innodb-heavy-4G.cnf''').
В качестве примера конфигурации вы также можете использовать примеры конфигурации MySQL ('''/usr/share/mysql/my-innodb-heavy-4G.cnf''').

Версия 07:52, 17 ноября 2011

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

Настройка MySQL

Рекомендуемая конфигурация, разбор параметров после.

[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
#
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
#
# 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).

Ссылки:

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

Личные инструменты