InnoDB или MyISAM?

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

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

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

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

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

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

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

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

  Как поменять тип таблиц на InnoDB

Поменять тип таблиц на 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_open_cache=4096

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

 set-variable = key_buffer_size=16M 
 set-variable = sort_buffer_size=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