Соединений без индексов: 5568

Joins performed without indexes: 91. Скорее всего, это главный виновник вашей производительности.

В вашей базе данных нет надлежащих индексов для столбцов, которые используются в запросах SQL, которые объединяют данные из нескольких таблиц.

Это означает, что вместо просмотра только данных индекса MySQL необходимо сканировать всю таблицу, чтобы получить строки, соответствующие ключам запроса.

Итак, вам нужно изучить свои SQL-запросы и добавить правильные индексы в таблицы / столбцы, используемые в соединениях.

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

• Превышено max_connections. Максимальное использование подключения - 4, и вы выделили 440 подключений, что привело к чрезмерному потреблению памяти (каждое подключение выделяет 18,5 МБ, умноженное на 440, используется 8 ГБ памяти). Уменьшить max_connections до 20 и регулярно контролировать использование соединения.
• Превышено innodb_buffer_pool_size. Ваш набор данных составляет 254,3 МБ, и вы выделили 4G. Уменьшите его до 1 Гб и просмотрите позже, когда ваша база данных будет работать под нагрузкой без перезапуска в течение как минимум пары дней. Также сбросить innodb_buffer_pool_instances к 1.

Из-за перераспределения памяти (12,6 ГБ выделено для MySQL + 1,3 ГБ памяти для других процессов> 8 ГБ системной памяти) ваша система, вероятно, перешла в режим подкачки, что вызвало высокую загрузку ЦП.

Извините за неподходящий первый ответ - очевидно, вчера я слишком устал и неправильно понял ваш вопрос.

Хотя использование пулов соединений поможет повысить общую производительность приложения, скорее всего, в первую очередь следует принять другие меры:

MysqlTuner предлагает допустимые параметры для установки:

query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 1.0M, or always use indexes with JOINs)
innodb_log_file_size should be (=528M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)

Также убедитесь, что mysqld.log регулярно меняются и проверяют наличие там предупреждений / ошибок. Вы можете начать с его удаления и перезапуска mysqld, а затем посмотреть, какие ошибки / предупреждения будут напечатаны оттуда.

Вы можете проверить, снижает ли innodb_buffer_pool_size до 1G (1073741824) и innodb_buffer_pool_instances 1 отрицательно влияет на производительность. Согласно mysqltuner, в буфере всего 254,3 МБ данных, поэтому у вас будет достаточно места для роста.

Предложение @tero-kilkanen тоже стоит того. Хотя индекс соответствия отсутствовал только в 58 из 19 тыс. Запросов, я не думаю, что это значительно повысит производительность. Но все равно проверьте индексы на их работоспособность и включенные ключи.

Чтобы избавиться от предупреждения о том, что ваш mysqld может использовать больше, чем установленный объем ОЗУ, вы должны уменьшить max_connections на разумное число: 20 - 30. Ваше приложение использовало не более 4 во время работы mysqld.
Максимальный объем памяти, назначаемый mysqld, приблизительно вычисляется путем умножения max_connections (440) с кешем, назначенным для каждого соединения (в основном sort_buffer_size плюс несколько меньших), а затем добавлением глобальных буферов (т.е. innodb_buffer_pool_size) к нему. Хотя 18,5 МБ на соединение звучит не слишком много (это не так), умножение на 440 дает 8 ГБ.

Поскольку ваше приложение в основном использует операторы чтения (99%), добавление кеширования на стороне приложения может снизить общую нагрузку на базу данных и заметно повысить производительность на стороне интерфейса.
Значения, которые регулярно запрашиваются, которые редко меняются вообще (например, пользовательские таблицы внешнего интерфейса), могут быть кэшированы. Затем вы можете сделать кеш недействительным при редактировании учеников / родителей и их разрешений.

Если вы по-прежнему сталкиваетесь с проблемами, запустите mysqld и приложение на более длительное время, прежде чем запускать mysqltuner. 1-3 дня должны дать лучшие предложения из-за более точного профиля использования.

Предложения для вашего раздела my.cnf [mysqld]. Скорость в секунду = RPS.

УДАЛИТЕ первую строку с помощью open_files_limit (их 2 в вашем my.cnf) И УДАЛИТЕ только строку с innodb_open_files = 800000, чтобы позволить по умолчанию работать для вашего экземпляра.

following could be change or ADD line to my.cnf

thread_cache_size=100  # for CAP of 100 per V5.7 refman to reduce threads_created
query_cache_type=0  # for NO query cache to conserve CPU cycles
query_cache_size=0  # from 1M to conserve RAM for more useful purpose
innodb_io_capacity=15000  # from 200 to use more of SSD IOPS capacity
sort_buffer_size=2M  # from 6M to conserve RAM per CONNECTION
read_buffer_size=128K  # from 1M to reduce handler_read_next RPS
join_buffer_size=256K  # from 2M per table join OPS per CONNECTION
table_open_cache=10000  # from 524288 for a practical LIMIT
table_definition_cache=1000  # from 2000 since you have less than 100 tables today
open_files_limit=65536  # from 1049026 million for a practical LIMIT