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

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

(Различия между версиями)
Перейти к: навигация, поиск
м (→Настройка MySQL)
 
(28 промежуточных версий не показаны.)
Строка 1: Строка 1:
База MySQL данных должна быть настроена с поддержкой транзакций.  
База MySQL данных должна быть настроена с поддержкой транзакций.  
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
-
Хранилище по-умолчанию рекомендуется InnoDb, для этого в конфигурационном файле должна быть установлена опция:
 
-
<pre>
 
-
[mysqld]
 
-
default-storage-engine = innodb
 
-
</pre>
 
-
Для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX установите опцию:
+
== Настройка MySQL ==
-
<pre>
+
Рекомендуемая конфигурация, разбор параметров после. Пример сделан для ОС *NIX типа, как наиболее подходящей для высоконагруженных серверов БД. При необходимости его можно адаптировать для Windows системы, изменив соответственно пути файловой системы и пропустив секцию mysqld.safe.
-
[mysqld]
+
-
innodb_file_per_table
+
-
</pre>
+
-
Для возможности передачи больших запросов и установки большого количества подключений к БД:
+
Кодировка 1251 базы данных использовалась по-умолчанию до версии биллинга 6.0 - исторически сложившаяся и не изменяемая как не несущая в себе существенных неудобств. Но при большой необходимости (например, требования хранения в БД символов других алфавитов) может быть изменена на UTF-8 или иную, при этом необходимо также скорректировать .properties файлы приложений биллинга на предмет кодировки БД.
-
<pre>
+
С версии 6.1 и выше по-умолчанию кодировка используется UTF-8 (для БД "character_set" везде "utf8", настройки "collation" везде "utf8_unicode_ci" (не "utf8_general_ci"))
-
[mysqld]
+
-
max_allowed_packet=50M
+
-
max_connections=1000
+
-
</pre>
+
-
 
+
-
Для LINUX, повышения ограничения на число открытых файлов.
+
<pre>
<pre>
 +
[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]
[mysqld_safe]
-
open-files-limit=32000  
+
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>
-
Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре '''sql-mode''' режимов '''STRICT_TRANS_TABLES''' и '''STRICT_ALL_TABLES''', это приведёт к неработоспособности некоторых компонентов биллинговой системы.
+
Опции 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 протокола, а не через файловый сокет.
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
-
Возможный вариант конфигурации:
+
Опции mysqld - InnoDB:
-
<pre>
+
* '''innodb_file_per_table''' - для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX.
-
[mysqld]
+
* '''innodb_log_group_home_dir''' - путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.
-
datadir=/var/lib/mysql
+
* '''innodb_buffer_pool_size''' - размер буфера под все нужды, он должен составлять порядка 70-80 % от общей памяти сервера БД.
-
socket=/var/lib/mysql/mysql.sock
+
* '''innodb_additional_mem_pool_size''' - параметр можно не изменять, размер буфера под доп. цели.
-
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
+
* '''innodb_log_files_in_group''' - количество файлов журналов транзакций в группе журналов; InnoDB производит запись в файлы по круговому способу; увеличение ускоряет запись но тормозит восстановление информации в случае сбоя.
-
#-500 by historical maximum on bill-1(8Gb) 248 on febr2009
+
* '''innodb_log_file_size''' - размер каждого файла журнала в группе журналов (указывается в мегабайтах).
-
max_connections=500
+
* '''innodb_log_buffer_size''' - размер буфера, который в InnoDB используется для записи информации файлов журналов на диск.
-
#x2
+
* '''innodb_lock_wait_timeout''' - время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать прекращения блокировки прежде, чем будет произведен откат.
-
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
+
* '''innodb_thread_concurrency''' - должно совпадать с '''thread_concurrency'''; число одновременно работающих потоков, рекомендуется ставить 2 * '''количество CPU''' + количество дисков.
-
log-slow-queries=/var/log/slowquery.log
+
* '''innodb_flush_log_at_trx_commit''' - 2 отменяет сброс данных на диск при каждой транзакции, ускорение работы.
 +
* '''innodb_flush_method''' - O_DIRECT отключает двойную буферизацию (самим mysql и ОС).
-
# replication
+
Опции mysqld -MyIsam, если используется (например, для некритичных таблиц):
-
server-id=1
+
* '''key_buffer_size''' - размер кэша для хранения индексов;
-
replicate-same-server-id=0
+
* '''myisam_recover''' -восстановление битых таблиц при старте сервера.
-
log-bin=/var/lib/mysql-binlog/mysql-bin
+
* '''myisam_repair_threads''' - число потоков восстановления.
-
expire_logs_days=3
+
* '''myisam_data_pointer_size''' - возможность создания больших первичных ключей в таблицах.
-
replicate-do-db=bgbilling
+
-
relay-log-space-limit = 10G
+
-
relay-log=mysqld-relay-bin
+
-
# myisam
+
Опции mysqld - репликации, если используется:
-
read_buffer_size = 2M
+
* '''server-id''' - идентификатор сервера.
-
read_rnd_buffer_size = 16M
+
* '''log-bin''' - место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
-
bulk_insert_buffer_size = 64M
+
* '''expire_logs_days''' - автоматическое удаление старых bin-логов.
-
myisam_sort_buffer_size = 1G
+
* '''replicate-do-db''' - реплицируемая БД.
-
myisam_max_sort_file_size = 10G
+
* '''relay-log-space-limit''' - ограничение на объём bin-логов.
-
##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
+
Опции mysqld_safe:
-
#skip-innodb
+
* '''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/] - общее руководство по администрированию.
 +
* [http://mysql.ru/docs/man/InnoDB_start.html http://mysql.ru/docs/man/InnoDB_start.html] - параметры настройки InnoDb.
 +
* [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://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 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_file_per_table
 +
#
 +
innodb_log_group_home_dir = "C:/mysql/lib/"
 +
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
innodb_additional_mem_pool_size = 16M
-
#x8
+
#
-
innodb_buffer_pool_size = 9G
+
-
innodb_thread_concurrency = 2
+
-
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_log_files_in_group = 2
-
innodb_max_dirty_pages_pct = 90
+
innodb_log_file_size = 128M
 +
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
innodb_lock_wait_timeout = 120
-
##Khadimulin
+
#
 +
innodb_thread_concurrency = 2
 +
innodb_flush_log_at_trx_commit = 2
#innodb_flush_method = O_DIRECT
#innodb_flush_method = O_DIRECT
-
##
+
#
-
# misc
+
# 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
default-character-set=cp1251
-
default-collation=cp1251_general_ci
+
</pre> 
-
 
+
-
[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
+
 +
MySQL 5.6
 +
<pre>
[client]
[client]
 +
port=3306
default-character-set=cp1251
default-character-set=cp1251
-
</pre>
 
-
Ссылки с рекомендациями по настройке InnoDb базы:
+
[mysql]
-
* [http://maxq.ru/lib/53/ http://maxq.ru/lib/53/]
+
default-character-set=cp1251
-
* [http://www.mysql.ru/docs/man/InnoDB_start.html http://www.mysql.ru/docs/man/InnoDB_start.html]
+
-
Если вы обновляете базу биллинга с MyIsam таблицами, вы можете произвести конвертацию в ОС LINUX по описанной [http://highervisibilitywebsites.com/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time здесь] методике.
+
[mysqld]
-
<source lang="bash">
+
port=3306
-
mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
+
basedir="C:/MySQL/MySQL Server 5.6"
-
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
+
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data"
-
</source>
+
tmpdir = "C:/tmpserver"
-
 
+
socket="C:/mysql/mysql.sock"
-
После этого можно посмотреть и почистить файл с перечнем таблиц для конвертации alter_table.sql.
+
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>
-
<source lang="bash">
+
== Набор Perl утилит для работы с MySQL ==
-
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql
+
В архиве [[Медиа: mysql_perl_utilites.zip]] размещены файлы:
-
</source>
+
* '''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-сервере.
-
*1. Включить поддержку InnoDb на slave-сервере.
+
# Включить поддержку InnoDb на master-сервере.
-
*2. Включить поддержку InnoDb на master-сервере.
+
# Запустить скрипт конвертации в InnoDB (см. ранее) для master-сервера.
-
*3. Запустить [[скрипт]] конвертации для master-сервера.
+
-
Скрипт выгружает таблицы для конвертации (кроме помесячных) в файл и потаблично конвертирует, запрашивая подтверждение на выполнение операции.
+
-
Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты.
+

Текущая версия на 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).

Ссылки:

Настройка 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-сервера.
Источник — «http://wiki.bitel.ru/index.php/%D0%A0%D0%B5%D0%BA%D0%BE%D0%BC%D0%B5%D0%BD%D0%B4%D0%B0%D1%86%D0%B8%D0%B8_%D0%BF%D0%BE_%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B9%D0%BA%D0%B5_MySQL»
Личные инструменты