Настройка базы данных MySQL

Настройка базы данных MySQL

Оптимизация работы с базой данных для MySQL-версии продукта является одной из важнейших стратегий в оптимизации системы в целом, так как продукт активно работает с базой данных.

Простой формат данных MyISAM хранит каждую таблицу с данными или индекс в отдельном файле. В целом, на небольших по нагрузке сайтах данный формат является наиболее быстрым, хотя и не обеспечивает полной целостности и надежного хранения данных за счет отсутствия транзакций.

Основным недостатком MyISAM с точки зрения производительности является блокировка на уровне таблицы при выполнении тех или иных операций. В результате, при большой нагрузке MySQL именно MyISAM таблицы становятся основным узким местом в системе, мешая увеличивать утилизацию машины и число обрабатываемых запросов. Это также приводит к увеличению времени работы страницы за счет ожидания используемых таблиц на уровне MySQL.

Рекомендуется переводить все таблицы проекта в формат данных InnoDB. Формат InnoDB, начиная с версии MySQL 4.0, входит в стандартную поставку продукта и обеспечивает надежное хранение данных, транзакционность и блокирование данных на уровне строки.

Два важных момента, которые дают основание предпочесть таблицы InnoDB перед MyISAM:

1. Надежность. В MyISAM высока вероятность сбоя таблиц, особенно больших, особенно при высокой посещаемости, особенно часто изменяемых. Есть риск потерять несколько (десятков, сотен) записей и целостность данных. В InnoDB чинить отдельные таблицы не придется. Если упадет, так все сразу. Но на практике это - исключительное явление, практически не встречаемое. Благодаря транзакционности, риск нарушения целостности минимальный.

   Недостатки InnoDB: нужно внимательно следить за свободным местом на диске; накапливающаяся фрагментация данных (лечится периодическим переводом таблиц из InnoDB в MyISAM и обратно).

2. Скорость. На невысокой посещаемости MyISAM ведет себя быстрее, как на модификацию, так и на чтение. Однако, при росте посещаемости достаточно быстро сказывается отсутствие транзакций и блокировка на уровне таблиц. При некоторой величине посещаемости проект просто реально умирает. В InnoDB запись будет медленнее (транзакции же), зато при высокой посещаемости блокировки наступят намного, намного позже, чем для MyISAM.

Поменять тип таблиц на InnoDB можно следующим образом:

1. В административном меню Настройки системы > Инструменты > SQL запрос выполнить команду:

    SHOW TABLES 

2. В результате вы получите список всех текущих таблиц продукта. Для каждой таблицы необходимо выполнить команду:

    ALTER TABLE <ИМЯ ТАБЛИЦЫ>, type=InnoDB 

   В FAQ приведен пример для создания скрипта для перевода таблиц в InnoDB.

3. После перевода таблиц вашей базы в InnoDB надо добавить в файл /bitrix/php_interface/dbconn.php нижеследующий код:

    define("MYSQL_TABLE_TYPE", "InnoDB"); 

Переход на тип таблиц InnoDB позволяет избежать возникновения узкого участка в производительности при работе с базой данных и в полном объеме использовать системные ресурсы.

Наибольшее внимание следует обратить на следующие параметры и примеры:

 set-variable = innodb_buffer_pool_size=250M 
 set-variable = innodb_additional_mem_pool_size=50M 
 set-variable = innodb_file_io_threads=8 
 set-variable = innodb_lock_wait_timeout=50 
 set-variable = innodb_log_buffer_size=8M 
 set-variable = innodb_flush_log_at_trx_commit=0 

Если MyISAM уже не используется активно, можно высвободить память в пользу InnoDB параметров.

Желательно, чтобы кэш данных вмещал в себя основной объем данных, используемых продуктом в работе. Обычно для работы базы данных выделяется порядка 60-80% свободной памяти в системе.

Пример рекомендуемых настроек для сервера с 2 Гб оперативной памяти, работающего с операционной системой FreeBSD/Linux:

 set-variable = table_cache=4096

В составе продукта около 250 таблиц, поэтому рекомендуется увеличивать кэш для заголовков таблиц.

 set-variable = key_buffer_size=16M 
 set-variable = sort_buffer=8M 
 set-variable = read_buffer_size=16M 

Эти параметры используются только для MyISAM. Если в базе нет таблиц MyISAM, то лучше установить минимальные значения.

set-variable = query_cache_size=64M 
 set-variable = query_cache_type=1

Кэширование результатов запросов. Обычно бывает достаточно 32 Мб (смотреть на статус Qcache_lowmem_prunes). Максимальный размер результата по умолчанию - 1 Мб, его можно регулировать.

 set-variable = innodb_buffer_pool_size=780M 

Основной буфер - чем больше, тем лучше.

set-variable = innodb_additional_mem_pool_size=20M

Вспомогательный буфер на внутренние структуры, большой делать не имеет смысла.

 set-variable = innodb_log_file_size=100M 
 set-variable = innodb_log_buffer_size=16M

Чем больше размер лог-файла, тем реже надо будет записывать в основной файл данных. Суммарный размер лог-файла может быть сопоставим с величиной innodb_buffer_pool_size (по умолчанию ведется два лога).

 set-variable = innodb_flush_log_at_trx_commit=0 

Отложенная фиксация транзакций, раз в секунду

 set-variable = tmp_table_size=32m

Размер временных таблиц рекомендуется увеличивать до 32 Мб.

Рекомендуется так же увеличивать join_buffer_size до 2 Мб, это существенно влияет на скорость выполнения ряда запросов.

set-variable = join_buffer_size = 2M

Временная папка

При наличии достаточного количества ОЗУ рекомендуется выносить временную папку MySQL на ramdisk в памяти.

Для этого:

• Убедитесь, что в ядре реализована поддержка tmpfs.
• Создайте новую точку монтирования и дайте все права на использование:

      # mkdir /mnt/tmpfs/
      # chmod 777 /mnt/tmpfs/

• Дайте команду (от рута или через sudo):

      # mount -t tmpfs -o size=1024M tmpfs /mnt/tmpfs/
      или
      $ sudo mount -t tmpfs -o size=1024M tmpfs /mnt/tmpfs/

  где 1024M есть размер RAMdisk в Мегабайтах.
  Внимание! К размеру папки нужно подходить осторожно: если вы попросите создать ramdisk больше, чем имеете оперативной памяти, система начнёт сгружать всё в swap-файл и реальный результат подключения временной папки может быть отрицательным.

Список ссылок по теме:

• Частный опыт настройки MySQL