Повышение производительности MySQL - InnoDB

Question

Я работаю с Linux CentOS и MySQL, до сих пор это содержимое my.cnf:

sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"
max_connections = 750
max_connect_errors = 750
connect_timeout = 28800
max_allowed_packet = 500M
character_set_server = utf8
query_cache_size = 500M

Я пытаюсь повысить производительность MySQL с помощью этих параметров, я читал, что некоторые из них устарели, поэтому я выбрал InnoDB в качестве движка и попытался установить его следующим образом:

default_storage_engine = INNODB
innodb_buffer_pool_size=9G
innodb_buffer_pool_instances=64

Итак, последний параметр my.cnf:

sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"
max_connections = 750
max_connect_errors = 750
connect_timeout = 28800
character_set_server = utf8
default_storage_engine = INNODB
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=64

Но в конце сервер перегружается без использования всей доступной памяти (3 ГБ /8 ГБ, а не 6 ГБ /8 ГБ, как я ожидал от использования innodb_buffer_pool_size), и я должен перезапустить службу MySQL, чтобы восстановить и нормализовать состояние службы.

Как я могу реально улучшить производительность MySQL? или какие рекомендации вы можете дать мне?

Заранее спасибо.

Answer 1

Попробуйте установить innodb_flush_log_at_trx_commit=2Дополнительную информацию можно найти здесь https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Answer 2

query_cache_size = 500M убивает производительность.

Каждая запись в таблицу очищает все записи в КК для этой таблицы. И он выполняет эту задачу очень медленно для очень большого КК.

50M - это практический максимум для этого параметра.

И QC забирает память у buffer_pool InnoDB, где это может быть более полезным.

Если у вас нет других приложений на машине, и вы снизили QC, тогда 6G для buffer_pool на машине 8 ГБ должно быть в порядке. Следите за обменом - это ужасно для производительности.

Кроме того, только 6 для innodb_buffer_pool_instances - 1G за экземпляр является рекомендацией.

connect_timeout относится к отказу от установления соединения, а не к тому, как долго оно может оставаться рядом. 60 или менее разумно.

Но... Как правило, низкая производительность происходит из-за плохих индексов (особенно из-за отсутствия "составных" индексов, когда это необходимо) и скрытия индексированных столбцов внутри вызовов функций (например, WHERE DATE(created_at) = '...').

Итак, давайте посмотрим, какие запросы медленные.