Рекомендации по настройке MySQL
Материал из BiTel WiKi
Admin (Обсуждение | вклад) (→Секция mysqld - параметры InnoDB) |
Zavndw (Обсуждение | вклад) м (→Настройка MySQL) |
||
(17 промежуточных версий не показаны.) | |||
Строка 2: | Строка 2: | ||
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций. | Для биллинга версий от 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")) | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
<pre> | <pre> | ||
[mysqld] | [mysqld] | ||
- | datadir=/var/lib/mysql | + | datadir = /var/lib/mysql |
tmpdir = /tmp | tmpdir = /tmp | ||
- | socket=/var/lib/mysql/mysql.sock | + | socket = /var/lib/mysql/mysql.sock |
- | user=mysql | + | user = mysql |
- | log-slow-queries=/var/log/slowquery.log | + | log-slow-queries =/var/log/slowquery.log |
+ | skip-name-resolve | ||
+ | sql_mode= | ||
# | # | ||
default-storage-engine = innodb | default-storage-engine = innodb | ||
- | default-character-set=cp1251 | + | default-character-set = cp1251 |
- | default-collation=cp1251_general_ci | + | default-collation = cp1251_general_ci |
- | + | ||
# | # | ||
- | max_allowed_packet= | + | wait_timeout = 28800 |
- | max_connections=1000 | + | max_allowed_packet = 64M |
+ | max_connections = 1000 | ||
# | # | ||
memlock | memlock | ||
- | table_cache=4096 | + | table_cache = 4096 |
- | thread_cache_size= | + | thread_cache_size = 64 |
- | 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 диск. | ||
Строка 53: | Строка 78: | ||
* '''user''' - пользователь ОС, под которым запускается БД. | * '''user''' - пользователь ОС, под которым запускается БД. | ||
* '''log-slow-queries''' - логирование запросов, выполняющихся длительное время. | * '''log-slow-queries''' - логирование запросов, выполняющихся длительное время. | ||
+ | * '''skip-name-resolve''' - отключает DNS резолвинг для устанавливаемых соединений. | ||
* '''default-storage-engine''' - тип хранилища таблиц по-умолчанию, InnoDB - транзакционный тип хранилища. | * '''default-storage-engine''' - тип хранилища таблиц по-умолчанию, InnoDB - транзакционный тип хранилища. | ||
* '''default-character-set''' - кодировка по-умолчанию для вновь создаваемых таблиц. | * '''default-character-set''' - кодировка по-умолчанию для вновь создаваемых таблиц. | ||
* '''default-collation''' - collation (порядок букв, используется при сортировке) по-умолчнию для вновь создаваемых таблиц. | * '''default-collation''' - collation (порядок букв, используется при сортировке) по-умолчнию для вновь создаваемых таблиц. | ||
- | * ''' | + | * '''sql_mode''' - запрещается установка режимов '''STRICT_TRANS_TABLES''' и '''STRICT_ALL_TABLES''', это приведёт к неработоспособности некоторых компонентов биллинговой системы. |
* '''max_allowed_packet''' - максимальный размер пакета с запросом, принимаемого сервером. | * '''max_allowed_packet''' - максимальный размер пакета с запросом, принимаемого сервером. | ||
* '''max_connections''' - ограничение на максимальное число соединений с БД. | * '''max_connections''' - ограничение на максимальное число соединений с БД. | ||
* '''memlock''' - запрет на перенос процесса mysqld в свап. | * '''memlock''' - запрет на перенос процесса mysqld в свап. | ||
- | * '''table_cache''' - | + | * '''table_cache''' - число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение. |
* '''thread_cache_size''' - размер кэша потоков для предотвращения их повторного создания. | * '''thread_cache_size''' - размер кэша потоков для предотвращения их повторного создания. | ||
* '''thread_concurrency''' - число одновременно работающих потоков, рекомендуется ставить 2 * '''количество CPU''' + количество дисков. | * '''thread_concurrency''' - число одновременно работающих потоков, рекомендуется ставить 2 * '''количество CPU''' + количество дисков. | ||
Строка 66: | Строка 92: | ||
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет. | Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет. | ||
- | + | Опции mysqld - InnoDB: | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
* '''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''' - путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму. | ||
Строка 100: | Строка 107: | ||
* '''innodb_flush_method''' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС). | * '''innodb_flush_method''' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС). | ||
- | + | Опции mysqld -MyIsam, если используется (например, для некритичных таблиц): | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
* '''key_buffer_size''' - размер кэша для хранения индексов; | * '''key_buffer_size''' - размер кэша для хранения индексов; | ||
* '''myisam_recover''' -восстановление битых таблиц при старте сервера. | * '''myisam_recover''' -восстановление битых таблиц при старте сервера. | ||
- | * myisam_repair_threads - число потоков восстановления. | + | * '''myisam_repair_threads''' - число потоков восстановления. |
- | * myisam_data_pointer_size - возможность создания больших первичных ключей в таблицах. | + | * '''myisam_data_pointer_size''' - возможность создания больших первичных ключей в таблицах. |
- | + | Опции mysqld - репликации, если используется: | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
* '''server-id''' - идентификатор сервера. | * '''server-id''' - идентификатор сервера. | ||
* '''log-bin''' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму. | * '''log-bin''' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму. | ||
Строка 130: | Строка 120: | ||
* '''relay-log-space-limit''' - ограничение на объём bin-логов. | * '''relay-log-space-limit''' - ограничение на объём bin-логов. | ||
- | + | Опции mysqld_safe: | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
* '''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'''). | ||
Строка 150: | Строка 139: | ||
* [http://mysql.ru/docs/man/Replication_Options.html http://mysql.ru/docs/man/Replication_Options.html] - параметры настройки репликации. | * [http://mysql.ru/docs/man/Replication_Options.html http://mysql.ru/docs/man/Replication_Options.html] - параметры настройки репликации. | ||
* [http://maxq.ru/lib/53/ http://maxq.ru/lib/53/] - рекомендации по настройке InnoDb базы, MyIsam базы. | * [http://maxq.ru/lib/53/ http://maxq.ru/lib/53/] - рекомендации по настройке InnoDb базы, MyIsam базы. | ||
+ | * [http://mysqltuner.pl/mysqltuner.pl 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 - | + | |
- | </ | + | MySQL 5.1 |
+ | <pre> | ||
+ | [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 | ||
+ | </pre> | ||
+ | |||
+ | MySQL 5.6 | ||
+ | <pre> | ||
+ | [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 | ||
+ | </pre> | ||
+ | |||
+ | == Набор Perl утилит для работы с MySQL == | ||
+ | В архиве [[Медиа: mysql_perl_utilites.zip]] размещены файлы: | ||
+ | * '''access.pm''' - параметры доступа к MySQL для остальных скриптов. | ||
+ | * '''extract_myisam.pl''' - выбор MyIsam таблиц из базы и вывод в файл '''tables'''. | ||
+ | * '''convert.pl''' - конвертация таблиц из файла '''tables''' в InnoDb. | ||
+ | * '''drop.pl''' - удаление таблиц, перечисленных в файле '''tables'''. | ||
Конвертацию можно выполнять только после включения в my.cnf поддержки InnoDb. Следует учитывать, что конвертация больших таблиц может быть очень длительной, поэтому оптимальным будет исключить из конвертации помесячные таблицы с сессиями, наработками и т.п. | Конвертацию можно выполнять только после включения в my.cnf поддержки InnoDb. Следует учитывать, что конвертация больших таблиц может быть очень длительной, поэтому оптимальным будет исключить из конвертации помесячные таблицы с сессиями, наработками и т.п. | ||
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере. | Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере. | ||
- | == | + | Выберите MyIsam таблицы в файл '''tables''' с помощью скрипта '''extract_myisam.pl''', скорректируйте файл '''tables''', оставив только последние месячные и постоянные таблицы. |
- | + | Затем выполните скрипт '''convert.pl'''. | |
- | + | ||
- | + | == Алгоритм конвертации с репликацией == | |
- | + | # Включить поддержку InnoDb на slave-сервере. | |
- | + | # Включить поддержку InnoDb на master-сервере. | |
- | + | # Запустить скрипт конвертации в InnoDB (см. ранее) для master-сервера. | |
- | + | ||
- | + |
Текущая версия на 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.
Алгоритм конвертации с репликацией
- Включить поддержку InnoDb на slave-сервере.
- Включить поддержку InnoDb на master-сервере.
- Запустить скрипт конвертации в InnoDB (см. ранее) для master-сервера.