Add new comment

Оптимизация сервера базы данных Mysql(Drupal7)

Самые распространенные параметры, которые следует подстроить под нужды Вашего проекта и конфигурацию железа приведены ниже.
/etc/mysql/my.cnf. Debian7

Первым делом узнаем подробною информацию о нашей базе данных

Первый способ.
Если вы можете напрямую авторизоваться на сервере MySQL, другой способ выяснить систему хранения — это запустить следующую MySQL команду внутри вашего MySQL сервера после авторизации.
mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Эта команда покажет тип системы хранения таблицы под названием 'my_table' в базе данных 'my_database'.
Т.е.:
'my_database' — название базы данных, в которой находится интересующая вас таблица
'my_table' — название интересующей вас таблицы

Второй способ

И ещё один метод проверить движок MySQL, это использование mysqlshow (утилиты командной строки, которая показывает информацию о базе данных). mysqlshow поставляется вместе с установкой клиентского пакета MySQL. Для использования mysqlshow вы должны войти в MySQL сервер со своим логином.

Эта команда отобразит информацию о конкретной базе данных. В колонке "Engine" вы увидите систему хранения для каждой таблицы.
mysqlshow -u -p -i

LmG7D5RuRRMZ7m.jpg

key_buffer_size или key_buffer

Если Вы используете только MyIsam таблицы, устанавливайте это значение в 30%...40% всей доступной оперативной памяти на сервере. MyIsam использует кеш операционной системы для данных, поэтому учтите, что оставшаяся свободная память понадобится именно для этого. Если же MyIsam таблиц у Вас немного и их совокупный размер маленький — оставьте это значение в пределах 32M.

innodb_buffer_pool_size

Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кэширует и данные и индексы (а кеш операционной системы не используется), поэтому значение этого ключа стоит устанавливать в 70%...80% доступной памяти.

Проверим сколько свободной памяти
free -m
MAjRlbkfvvVR7A.jpg
Свободная память в linux расчитывается из свободной в текущей момент и памяти в кеше(которая тоже может освободится)

Итого 80% - будет равняться 1340 мб

Если Ваш сервер работает на Линуксе или Юниксе, не забудьте установить параметр innodb_flush_method в значение "O_DIRECT", что-бы избежать избыточного кеширования на уровне ОС.

В нашем примере будут такие значения
innodb_buffer_pool_size = 1340M
innodb_flush_method = O_DIRECT

innodb_log_file_size

Обратите внимание на этот параметр, если у Вас предусматривается большой показатель записей. Чем больше размер этого ключа, тем более эффективно будет происходить запись данных. Но учтите, что при этом увеличится время восстановления системы! Этот параметр обычно устанавливают в 64M-512M.

Также важно ! Изменение параметра innodb_log_file_size InnoDB-движка MySQL
Проблема:
Если файл лога уже присутствует, при попытке поменять параметр innodb_log_file_size при запуске сервера вылезет ошибка вида:

InnoDB: Error: log file .\ib_logfile0 is of different size 0 bytes
InnoDB: than specified in the .cnf file 0 bytes!

Причина:
MySQL видит, что размер существующего лог-файла отличается от указанного в настройках.
Решение:
Остановить сервер, сделать резервную копию файлов ib_logfile, после чего удалить их, изменить значение параметра innodb_log_file_size и запустить сервер. MySQL создаст новый лог-файл указанного в конфигурации размера.
Путь к файлу
/var/lib/mysql/ib_logfile0

innodb_flush_log_at_trx_commit

Этот параметр в значительной степени влияет на скорость работы (записи) innoDB таблиц. Значение "1" означает, что любая завершенная транзакция будет синхронно сбрасывать лог на диск. Значение "2" делает то же самое, только сбрасывает лог не на диск, а в кеш операционной системы. Это значение подойдет в большинстве случаев, т.к. не выполняет дорогой операции записи после каждой транзакции. При этом лог пишется на диск с задержкой в несколько секунд, что весьма безопасно с точки зрения сохранности данных. Значение "0" даст наибольшую производительность. В этом случае буфер будет сбрасывать в лог файл независимо от транзакций. Устанавливайте этот параметр в "0" на свой риск, т.к. в этом случае риск потери данных возрастает.

table_cache

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

thread_cache_size

Этот параметр помогает избежать операций создания/уничтожения потоков при соединении к серверу. Установите этот параметр в 16 и наращивайте по мере потребности. Проверяйте показатель "Threads_created", идеально он должен быть равным нулю:

mysql> show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 1 |
+-----------------+-------+
1 row in set (0.00 sec)

Почти идеально


query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Не увлекайтесь установкой огромных значений. Кеш запросов не должен быть большим, т.к. mysql будет съедать ресурсы на управление данными в кеше. Начните с 32М...128М, и увеличивайте по мере необходимости.

Категория: 
The code has been tested and works

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.