Рекомендации по настройке MySQL
Материал из BiTel WiKi
Amir (Обсуждение | вклад) |
Admin (Обсуждение | вклад) |
||
Строка 1: | Строка 1: | ||
База MySQL данных должна быть настроена с поддержкой транзакций. | База MySQL данных должна быть настроена с поддержкой транзакций. | ||
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций. | Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций. | ||
- | + | ||
+ | Рассмотрим рекомендуемую конфигурацию по фрагментам. Полная конфигурация получается объединением фрагментов. | ||
+ | |||
+ | == Секция mysqldump == | ||
<pre> | <pre> | ||
- | [ | + | [mysqldump] |
- | default- | + | max_allowed_packet=50M |
+ | default-character-set=cp1251 | ||
</pre> | </pre> | ||
- | + | Опции: | |
+ | * '''max_allowed_packet''' - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump. | ||
+ | * '''default-character-set''' - кодировка по-умолчанию при снятии дампов БД. | ||
+ | |||
+ | == Секция client == | ||
<pre> | <pre> | ||
- | [ | + | [client] |
- | + | default-character-set=cp1251 | |
</pre> | </pre> | ||
- | + | Опции: | |
+ | * '''default-character-set''' - кодировка по-умолчанию при подключении консольным клиентом. | ||
+ | |||
+ | == Секция mysqld - общие параметры == | ||
<pre> | <pre> | ||
- | [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_allowed_packet=50M | ||
max_connections=1000 | max_connections=1000 | ||
+ | # | ||
+ | table_cache=4096 | ||
+ | thread_cache_size=384 | ||
+ | thread_concurrency=12 | ||
</pre> | </pre> | ||
- | + | Опции: | |
+ | * '''datadir''' - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости. | ||
+ | * '''tmpdir''' - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости. | ||
+ | * '''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''' - ограничение на максимальное число соединений с БД. | ||
+ | * '''table_cache''' - максимальное количество описаний структур открытых таблиц в кэше. | ||
+ | |||
+ | Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет. | ||
+ | |||
+ | == Секция mysqld - параметры InnoDB == | ||
+ | <pre> | ||
+ | innodb_file_per_table | ||
+ | # | ||
+ | innodb_log_group_home_dir = /storage/mysql/ | ||
+ | innodb_buffer_pool_size = 1G | ||
+ | innodb_additional_mem_pool_size = 16M | ||
+ | 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 | ||
+ | # | ||
+ | innodb_thread_concurrency = 2 | ||
+ | innodb_flush_log_at_trx_commit = 2 | ||
+ | ##Khadimulin | ||
+ | #innodb_flush_method = O_DIRECT | ||
+ | ## | ||
+ | </pre> | ||
+ | |||
+ | Опции: | ||
+ | * innodb_file_per_table - для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX. | ||
+ | * innodb_buffer_pool_size - он должен составлять порядка 70-80 % от общей памяти сервера, это размер буфера под все нужды. | ||
+ | * innodb_thread_concurrency - число потоков, рекомендуется ставить 2 * <количество CPU> + количество дисков. | ||
+ | * | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | innodb_flush_method = O_DIRECT | ||
+ | которая отключает двойную буферизацию (самим mysql и ОС). | ||
+ | |||
+ | |||
+ | == Секция mysqld - параметры MyIsam если используется (например, для некритичных таблиц) == | ||
+ | <pre> | ||
+ | key_buffer_size = 5G | ||
+ | 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 | ||
+ | myisam_repair_threads = 1 | ||
+ | myisam_recover | ||
+ | myisam_data_pointer_size = 6 | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | |||
+ | == Секция mysqld_safe == | ||
<pre> | <pre> | ||
[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 | ||
</pre> | </pre> | ||
- | + | Опции: | |
+ | * '''open-files-limit''' - лимит количества открытых файлов для ОС Linux. | ||
+ | * '''log-error''' - файл для логирования ошибок. | ||
+ | * '''pid-file''' - PID файл процесса. | ||
+ | |||
+ | ********************************************************************* | ||
+ | |||
+ | Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре | ||
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет. | Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет. | ||
Возможный вариант конфигурации: | Возможный вариант конфигурации: | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
sort_buffer_size=8M | sort_buffer_size=8M | ||
join_buffer_size=8M | join_buffer_size=8M | ||
- | + | ||
- | + | ||
#x0.25 | #x0.25 | ||
query_cache_size = 64M | query_cache_size = 64M | ||
Строка 59: | Строка 141: | ||
max_heap_table_size = 512M | max_heap_table_size = 512M | ||
#x16 | #x16 | ||
- | + | ||
wait_timeout = 28800 | wait_timeout = 28800 | ||
- | |||
- | |||
# replication | # replication | ||
Строка 74: | Строка 154: | ||
relay-log=mysqld-relay-bin | relay-log=mysqld-relay-bin | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | + | ||
# misc | # misc | ||
default-character-set=cp1251 | default-character-set=cp1251 | ||
Строка 144: | Строка 193: | ||
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере. | Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере. | ||
- | == | + | == Пример конвертации. == |
- | Пример конвертации. == | + | |
Задача сконвертировать базу, имеется два сервера с настроенной репликацией. | Задача сконвертировать базу, имеется два сервера с настроенной репликацией. | ||
Строка 155: | Строка 203: | ||
Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты. | Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты. | ||
Скрипт конвертирует только в master базе, а в slave изменения произойдут из-за настроенного реплицирования (см документацию по репликации). | Скрипт конвертирует только в master базе, а в slave изменения произойдут из-за настроенного реплицирования (см документацию по репликации). | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- |
Версия 06:24, 17 ноября 2011
База MySQL данных должна быть настроена с поддержкой транзакций. Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
Рассмотрим рекомендуемую конфигурацию по фрагментам. Полная конфигурация получается объединением фрагментов.
Содержание |
Секция 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 # table_cache=4096 thread_cache_size=384 thread_concurrency=12
Опции:
- datadir - каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
- tmpdir - каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости.
- 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 - ограничение на максимальное число соединений с БД.
- table_cache - максимальное количество описаний структур открытых таблиц в кэше.
Запрещается установка опции skip-networking, т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
Секция mysqld - параметры InnoDB
innodb_file_per_table # innodb_log_group_home_dir = /storage/mysql/ innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 16M 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 # innodb_thread_concurrency = 2 innodb_flush_log_at_trx_commit = 2 ##Khadimulin #innodb_flush_method = O_DIRECT ##
Опции:
- innodb_file_per_table - для возможности использования Backup базы с помощью snapshot'ов (Linux, LVM) в ОС LINUX.
- innodb_buffer_pool_size - он должен составлять порядка 70-80 % от общей памяти сервера, это размер буфера под все нужды.
- innodb_thread_concurrency - число потоков, рекомендуется ставить 2 * <количество CPU> + количество дисков.
</pre>
innodb_flush_method = O_DIRECT
которая отключает двойную буферизацию (самим mysql и ОС).
Секция mysqld - параметры MyIsam если используется (например, для некритичных таблиц)
key_buffer_size = 5G 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 myisam_repair_threads = 1 myisam_recover myisam_data_pointer_size = 6
Секция 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 файл процесса.
Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре Запрещается установка опции skip-networking, т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
Возможный вариант конфигурации:
sort_buffer_size=8M
join_buffer_size=8M
- 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
wait_timeout = 28800
- 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
- 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 </pre>
В качестве примера конфигурации вы также можете использовать примеры конфигурации MySQL (/usr/share/mysql/my-innodb-heavy-4G.cnf).
Ссылки с рекомендациями по настройке 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 изменения произойдут из-за настроенного реплицирования (см документацию по репликации).