RDS MySQL Insights: самый популярный запрос "фиксация"

Наконец-то мы смогли решить эту проблему с «фиксацией» нагрузки.

Несколько фактов о нашей системе:

• Около 95% наших SQL-запросов читаются
• Около 5% наших запросов на запись - это записи
• Большинство наших запросов выполняются с автоматической фиксацией, а не с явной транзакцией

После дальнейшего исследования наши выводы следующие:

• Нагрузка на "фиксацию" из анализа производительности возникает из-за времени, которое требуется для записи запроса в binlog на диске.
• Поскольку большинство наших запросов на запись выполняется с автоматической фиксацией, все эти запросы выполняют фиксацию, вызывая некоторую очистку binlog
• Запросы чтения не увеличивают эту "фиксационную" нагрузку, скорее всего, потому, что им не нужно писать в двоичный журнал.
• Таким образом, 5% запросов, выполняющих запись, вызывали большую часть нагрузки базы данных!

Для нас наиболее эффективный способ исправить это - групповая запись в рамках одной транзакции. Многие из выполняемых нами операций записи не нужно сразу же записывать в БД, и некоторые из них можно потерять (большая часть наших операций записи связана со статистикой или отслеживанием). Таким образом, наше текущее решение состоит в том, чтобы сохранять нашу статистику / записи отслеживания в кеш и периодически сбрасывать их в БД в рамках одной транзакции. Это уменьшает количество коммитов, эффективно снижая нагрузку.

Это решение может не работать для всех или может потребовать некоторой настройки. Насколько я понимаю, есть несколько альтернативных решений, например:

• Разделите свою базу данных и переместите таблицы, вызывающие большое количество операций записи, в другой экземпляр
• Уменьшите количество запросов на запись
• Поиграйте с настройками очистки binlog. По умолчанию он сбрасывает двоичный журнал на диск после каждого COMMIT, но его можно настроить по-другому. Однако это может вызвать некоторые проблемы с возможностью восстановления в случае сбоя базы данных!
• Используйте более быстрый диск

Я также ответил на свои первоначальные вопросы ниже.

Надеюсь, это может быть полезно кому-нибудь еще. Я обнаружил, что в Интернете очень мало информации по этому поводу.

Во-первых, у нас включена автоматическая фиксация. Включает ли этот оператор «COMMIT» автоматический оператор COMMIT из конфигурации autocommit, или он является эксклюзивным только для явных операторов COMMIT, когда наше приложение выполняет транзакции?

Этот оператор «COMMIT» включает запросы, выполняемые с автоматической фиксацией.

Кроме того, в нашем контексте ни один из запросов чтения не был включен в этот оператор «COMMIT», скорее всего, потому, что после запросов чтения не требовалось сбросить двоичный журнал на диск.

Я проверил больше, и, похоже, это состояние "init" связано с тем, что база данных сбрасывает двоичный журнал на диск (https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html). Это может иметь смысл, так как на нашем главном сервере есть sync_binlog = 1. Но верна ли эта догадка или есть еще что-то, что может быть связано?

Для нас только сброс binlog вызывал высокую нагрузку во время этого состояния инициализации.

Если это так, я читал о настройке sync_binlog (например, sync_binlog = 0 или sync_binlog> 1), но с большим риском в случае отказа оборудования (https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-2-parameters-related-to-replication/). Что касается этой части, мне неясно, что является хорошей практикой при масштабировании RDS с одним мастером и несколькими репликами для чтения: является ли стандартной практикой трогать этот параметр на мастере, или нам следует больше сосредоточиться на уменьшении количества транзакций, которые мы выполняем выполнение из приложения?

Стандартная практика - сохранить sync_binlog=1 на главном сервере, чтобы избежать проблем с возможностью восстановления после сбоя, особенно если у вас настроены реплики для чтения.

Есть и другие способы решения этой проблемы, перечисленные выше в моем ответе.

Я мог видеть эти операторы «COMMIT», застрявшие в состоянии инициализации при запуске SHOW FULL PROCESSLIST. Однако мне интересно, есть ли способ узнать, какие запросы связаны с этим оператором COMMIT. В противном случае, похоже, нам нужно угадать, какая транзакция является основной причиной.

Это выполнимо, как описано в этом сообщении в блоге: https://www.psce.com/en/blog/2015/01/22/tracking-mysql-query-history-in-long-running-transactions/

Однако для этого необходимо включить events_statements_history. На RDS это отключено и, насколько я знаю, не позволяет включать его.