Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как мы можем включить механизм кэширования инструкций драйвера MySQL JDBC, который, к сожалению, не включен по умолчанию.
Независимо от того, какую платформу доступа к данным вы используете, вам все равно необходимо настроить драйвер JDBC, если вы хотите получить максимальную отдачу от ядра базы данных MySQL.
Подготовленное на стороне клиента заявление MySQL JDBC
Как я объяснил в этой статье , по умолчанию драйвер MySQL JDBC эмулирует подготовленные инструкции.
Таким образом, независимо от того , выполняете ли вы обычную Инструкцию или Подготовленную инструкцию , выполнение инструкции SQL будет выглядеть следующим образом:
Итак, при выполнении следующей инструкции:
try(PreparedStatement statement = connection.prepareStatement("""
SELECT balance
FROM account
WHERE iban = ?
""")
) {
statement.setString(1, iban);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()) {
return resultSet.getLong(1);
}
}
По умолчанию вызывается метод ConnectionImpl#clientPrepareStatement , и инструкция готовится только на стороне клиента.
Параметры привязки хранятся локально и в режиме онлайн до выполнения инструкции, поэтому выполняется один цикл обработки базы данных.
Подготовленное заявление на стороне сервера MySQL JDBC- useServerPrepStmts
Чтобы включить подготовленные на стороне сервера инструкции, вам необходимо установить параметр useServerPrepStmts в значение true .
useServerPrepStmts=true
Теперь при вызове подготовленного оператора JDBC Подключения метода вместо него будет создано ServerPreparedStatement , и оператор будет подготовлен на сервере базы данных:
Когда оператор подготовлен на сервере, анализатор создаст AST (Абстрактное синтаксическое дерево) , которое может быть пройдено оптимизатором во время выполнения оператора.
Настройка кэширования инструкций MySQL JDBC – cachePrepStmts
Поскольку рекомендуется использовать пул соединений с базой данных , объекты Connection будут повторно использоваться из одной транзакции в другую, поэтому имеет смысл кэшировать либо обработанные операторы на стороне клиента, либо на стороне сервера.
Это можно сделать как для подготовленных операторов на стороне клиента, так и на стороне сервера, установив для свойства cachePrepStmts конфигурации значение true :
cachePrepStmts=true
Кэширование подготовленных операторов на стороне клиента MySQL JDBC
Для подготовленных на стороне клиента инструкций драйвер MySQL JDBC будет кэшировать объект ParseInfo :
ParseInfo pStmtInfo = this.cachedPreparedStatementParams.get(nativeSql);
Объект ParseInfo предоставляет длину оператора, количество параметров привязки, содержит ли оператор ПРИ ОБНОВЛЕНИИ ДУБЛИКАТА КЛЮЧА .
Кэширование подготовленных операторов на стороне сервера MySQL JDBC
Для подготовленных операторов на стороне сервера, без кэширования подготовленных операторов, мы собираемся выполнять два сетевых обхода каждый раз, когда мы хотим выполнить оператор.
Это делается с помощью вызова метода server Prepare в объекте ServerPreparedStatement .
Таким образом, чтобы уменьшить количество сетевых обходов, имеет смысл включить механизм кэширования операторов при использовании подготовленных на стороне сервера операторов.
Кэширование инструкций MySQL JDBC – prepStmtCacheSize
После того, как вы включили кэширование операторов с помощью свойства cachePrepStmts , вам необходимо увеличить значение размера кэша, потому что размер по умолчанию просто 25 .
Поэтому вам необходимо установить prepStmtCacheSize на достаточно большое значение:
prepStmtCacheSize=500
prepStmtCacheSize может быть даже больше, чем это. Вам необходимо настроить его таким образом, чтобы он соответствовал наиболее распространенным операторам SQL, используемым вашим приложением.
Чтобы определить, какой рабочий набор SQL вашего приложения, используйте Percona PMM и посмотрите в представлении Анализатор запросов, чтобы узнать, какие наиболее распространенные операторы SQL вы используете.
Внутренне драйвер JDBC MySQL будет хранить кэшированные ServerPreparedStatement объекты в LruCache . LruCache расширяет Java LinkedHashMap , которая предоставляет политику замены кэша LRU (наименее недавно использовавшуюся).
Настройка кэширования инструкций MySQL JDBC – prepStmtCacheSqlLimit
По умолчанию операторы SQL, длина которых больше, чем 256 символы не хранятся в кэше. Это делается для ограничения объема оперативной памяти, используемого механизмом кэширования операторов.
Однако, если ваше приложение использует длинные инструкции, вы можете увеличить это ограничение с помощью параметра prepStmtCacheSqlLimit :
prepStmtCacheSqlLimit=1024
Кэширование инструкций MySQL JDBC – Повышение производительности
Чтобы узнать, какой прирост производительности достигается за счет включения кэширования операторов, рассмотрим следующий тестовый случай:
long ttlNanos = System.nanoTime() + getRunNanos();
while (System.nanoTime() < ttlNanos) {
long startNanos = System.nanoTime();
try (PreparedStatement statement = connection.prepareStatement("""
SELECT p.title, pd.created_on
FROM post p
LEFT JOIN post_details pd ON p.id = pd.id
WHERE EXISTS (
SELECT 1 FROM post_comment WHERE post_id = p.id
)
ORDER BY p.id
LIMIT ?
OFFSET ?
"""
)) {
statement.setInt(1, 1);
statement.setInt(2, 100);
try(ResultSet resultSet = statement.executeQuery()) {
queryCount.incrementAndGet();
} finally {
queryTimer.update(
System.nanoTime() - startNanos,
TimeUnit.NANOSECONDS
);
}
}
}
При выполнении приведенного выше тестового примера в течение 1 минуты с использованием драйвера MySQL 8.0.22 Connector/J JDBC для подготовленных инструкций на стороне клиента и сервера мы получаем следующие результаты.
Кэширование инструкций MySQL JDBC На стороне клиента-Повышение производительности
Пропускная способность запросов для подготовленных на стороне клиента операторов выглядит следующим образом:
Чем выше, тем лучше, потому что большая пропускная способность означает, что мы можем выполнять больше запросов в единицу времени.
А время выполнения запроса 99 процентиль выглядит следующим образом:
Чем ниже, тем лучше, потому что более короткое время выполнения запроса означает, что мы также получаем лучшее время отклика на транзакцию.
Кэширование инструкций MySQL JDBC На стороне Сервера-Повышение производительности
Пропускная способность запросов для подготовленных на стороне сервера операторов выглядит следующим образом:
Чем выше, тем лучше, потому что большая пропускная способность означает, что мы можем выполнять больше запросов в единицу времени.
А время выполнения запроса 99 процентиль выглядит следующим образом:
Чем ниже, тем лучше, потому что более короткое время выполнения запроса означает, что мы также получаем лучшее время отклика на транзакцию.
Таким образом, механизм кэширования операторов работает как для подготовленных операторов на стороне клиента, так и на стороне сервера.
Вывод
Для MySQL механизм кэширования операторов демонстрирует некоторые улучшения, но не такие значительные, как в других системах баз данных.
Во всех моих тестах, как на MySQL 8.0.22, так и на 8.0.18, с использованием транзакций с одним оператором или несколькими операторами, подготовленные на стороне клиента операторы выполнялись лучше, чем подготовленные на стороне сервера.
В целом, следующие параметры конфигурации, по-видимому, дают наилучшие результаты:
useServerPrepStmts=false cachePrepStmts=true
Не забудьте увеличить лимиты кэша, так как значения по умолчанию слишком низкие:
prepStmtCacheSize=500 prepStmtCacheSqlLimit=1024