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

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

(Различия между версиями)
Перейти к: навигация, поиск
Строка 1: Строка 1:
База MySQL данных должна быть настроена с поддержкой транзакций.  
База MySQL данных должна быть настроена с поддержкой транзакций.  
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
Для биллинга версий от 5.2 и старше это требование обязательно, т.к. транзакции используются для отката результатов неудавшихся операций.
-
Хранилище по-умолчанию рекомендуется InnoDb, для этого в конфигурационном файле должна быть установлена опция:
+
 
 +
Рассмотрим рекомендуемую конфигурацию по фрагментам. Полная конфигурация получается объединением фрагментов.
 +
 
 +
== Секция mysqldump ==
<pre>
<pre>
-
[mysqld]  
+
[mysqldump]
-
default-storage-engine = innodb
+
max_allowed_packet=50M
 +
default-character-set=cp1251
</pre>
</pre>
-
Для возможности использования [[Database_backup | Backup базы с помощью snapshot'ов (Linux, LVM)]] в ОС LINUX установите опцию:
+
Опции:
 +
* '''max_allowed_packet''' - установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
 +
* '''default-character-set''' - кодировка по-умолчанию при снятии дампов БД.
 +
 
 +
== Секция client ==
<pre>
<pre>
-
[mysqld]  
+
[client]
-
innodb_file_per_table
+
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>
-
Для LINUX, повышения ограничения на число открытых файлов.
+
Опции:
 +
* '''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>
-
Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре '''sql-mode''' режимов '''STRICT_TRANS_TABLES''' и '''STRICT_ALL_TABLES''', это приведёт к неработоспособности некоторых компонентов биллинговой системы.
+
Опции:
 +
* '''open-files-limit''' - лимит количества открытых файлов для ОС Linux.
 +
* '''log-error''' - файл для логирования ошибок.
 +
* '''pid-file''' - PID файл процесса.  
 +
 
 +
*********************************************************************
 +
 
 +
Внимательно проверяйте по документации все устанавливаемые опции в my.cnf, запрещается установка в параметре
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
Запрещается установка опции '''skip-networking''', т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.
Возможный вариант конфигурации:
Возможный вариант конфигурации:
-
<pre>
 
-
[mysqld]
 
-
datadir=/var/lib/mysql
 
-
socket=/var/lib/mysql/mysql.sock
 
-
user=mysql
 
-
max_allowed_packet=50M
 
-
tmpdir = /storage/mysql-tmp
 
-
old_passwords=1
 
-
##master-master preparations
 
-
#auto_increment_increment=2
 
-
#auto_increment_offset=1
 
-
# modified parameters
+
 
-
#-500 by historical maximum on bill-1(8Gb) 248 on febr2009
+
-
max_connections=500
+
-
#x2
+
-
table_cache=4096
+
sort_buffer_size=8M
sort_buffer_size=8M
join_buffer_size=8M
join_buffer_size=8M
-
thread_cache_size=384
+
 
-
thread_concurrency=12
+
#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
-
key_buffer_size = 5G
+
 
wait_timeout = 28800
wait_timeout = 28800
-
# log slow queries
 
-
log-slow-queries=/var/log/slowquery.log
 
# replication
# replication
Строка 74: Строка 154:
relay-log=mysqld-relay-bin
relay-log=mysqld-relay-bin
-
# myisam
 
-
read_buffer_size = 2M
 
-
read_rnd_buffer_size = 16M
 
-
bulk_insert_buffer_size = 64M
 
-
myisam_sort_buffer_size = 1G
 
-
myisam_max_sort_file_size = 10G
 
-
##unused since 5.0???
 
-
#myisam_max_extra_sort_file_size = 10G
 
-
##
 
-
myisam_repair_threads = 1
 
-
myisam_recover
 
-
myisam_data_pointer_size = 6
 
-
# bdb
 
-
skip-bdb
 
-
# innodb
+
 
-
#skip-innodb
+
-
innodb_file_per_table
+
-
innodb_additional_mem_pool_size = 16M
+
-
#x8
+
-
innodb_buffer_pool_size = 9G
+
-
#A recommended value is 2 times the number of CPUs plus the number of disks.
+
-
innodb_thread_concurrency = 8
+
-
innodb_flush_log_at_trx_commit = 2
+
-
innodb_log_group_home_dir = /storage/mysql/
+
-
innodb_log_buffer_size = 8M
+
-
innodb_log_file_size = 128M
+
-
innodb_log_files_in_group = 2
+
-
innodb_max_dirty_pages_pct = 90
+
-
innodb_lock_wait_timeout = 120
+
-
##Khadimulin
+
-
#innodb_flush_method = O_DIRECT
+
-
##
+
# misc
# misc
default-character-set=cp1251
default-character-set=cp1251
Строка 144: Строка 193:
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере.
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере.
-
==  
+
== Пример конвертации. ==
-
Пример конвертации. ==
+
Задача сконвертировать базу, имеется два сервера с настроенной репликацией.
Задача сконвертировать базу, имеется два сервера с настроенной репликацией.
Строка 155: Строка 203:
Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты.
Использование скрипта на реальной базе показало, что есть объемные таблицы которые конвертируются более 1 минуты.
Скрипт конвертирует только в master базе, а в slave изменения произойдут из-за настроенного реплицирования (см документацию по репликации).
Скрипт конвертирует только в master базе, а в slave изменения произойдут из-за настроенного реплицирования (см документацию по репликации).
-
--------------------------------------------------------------------------------------------
 
-
--skyb
 
-
 
-
Немного пояснений к конфигу которые опытным путем выяснились
 
-
 
-
key_buffer_size = 5G
 
-
это для таблиц myisam - если используете, то оставьте.
 
-
Аналог для innodb это
 
-
innodb_buffer_pool_size
 
-
он должен составлять порядка 70-80 % от общей памяти, ещё важна опция
 
-
innodb_flush_method = O_DIRECT
 
-
которая отключает двойную буферизацию (самим mysql и ОС).
 

Версия 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

  1. x0.25

query_cache_size = 64M query_cache_limit = 2M query_cache_type = 1 memlock tmp_table_size = 1G max_heap_table_size = 512M

  1. x16

wait_timeout = 28800


  1. 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


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

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