+7 495 008 8452 пн.-пт. 10:00 – 17:00
Загрузка...
Если у вас возникли какие либо вопросы которые вы не смогли решить по нашим публикациям самостоятельно,
то ждем ваше обращение в нашей службе тех поддержки.


Настройка базы данных 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 позволяет избежать возникновения узкого участка в производительности при работе с базой данных и в полном объеме использовать системные ресурсы.

Внимание! Обязательно конфигурируйте InnoDB. Для лучшей производительности базы данных при работе с InnoDB рекомендуется настроить my.cnf для MySQL в разделе параметров для 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 
Рекомендуется: Для сокращения времени ответа сервера можно использовать отложенные транзакции и, в частности, устанавливать переменную set-variable = innodb_flush_log_at_trx_commit=0.

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

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

Рекомендуется: Производить многопотоковую (multithreading) сборку MySQL для улучшения производительности системы и возможностей по параллельной обработке запросов.

Пример рекомендуемых настроек для сервера с 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

Совет администратору Переход на InnoDB может привести к значительному замедлению некоторых масштабных операций записи и обновления данных. Это связано с тем, что все операции по изменению данных начинают выполняться с использованием транзакций. Кроме того, в отличие от MyISAM для кэширования таблиц InnoDB не используется кэш операционной системы, а все кэшированные данные хранятся в кэше БД, определяемом параметром innodb_buffer_pool_size. Вы должны сами определить оптимальность перехода вашего проекта на формат данных InnoDB.
Внимание! Если по каким-то причинам вы решили продолжить работу с типом данных MyISAM, обязательно проведите конфигурирование MySQL для увеличения объемов кэшируемой информации, областей сортировки и минимизации числа дисковых операций. Использование для базы данных 60-80% оперативной памяти может ускорить работу стандартного проекта в несколько раз.

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

При наличии достаточного количества ОЗУ рекомендуется выносить временную папку 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-файл и реальный результат подключения временной папки может быть отрицательным.

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



Назад в раздел

Подписаться на новые материалы раздела:
Загрузка...