Как правильно работать с коннектами из пула HikkariCP?

Question

Прорабатываю серверную часть приложения, в котором каждый из клиентов раз в день скидывает серверу заранее неизвестное количество строк, а сервер уже обрабатывает их и посылает UPDATE в базу данных. Клиентов ~200, количество строк, которые суммарно нужно UPDATE-нуть ~10-15тыс.

Каждое подключение запускает свой отдельный поток, в котором клиент скидывает строки в "свой" ArrayList, строки так же бэкапятся в txt файл, а потом поток пытается свой ArrayList записать в БД.

    public void run() {
        try {
            log.info("Fill list...");
            fillListFromClient();
            backupListFromClientInTxt();
        } catch (IOException e) {
            log.error("List not filled!" + e);
            throw new RuntimeException(e);
        }

        try {
            log.info("Sending list to DB...");
            sendListToDB(DataBaseConnector.getConnection());
        } catch (SQLException e) {
            log.info("Can't send list to DB...");
            throw new RuntimeException(e);
        }
    }

    private void sendListToDB(Connection connection) throws SQLException {
        PreparedStatement statement = null;
        try {
            int updated = 0; // смотрю в логе количество скинутых и добавленных в бд записей
            statement = connection.prepareStatement("UPDATE TRN SET FLAGISPRINTED = ? " +
                    "WHERE TYPE = 55 AND POINT = ? AND DATE = ? AND CHECK = ?");
            for (String line:listFromClient) {
                log.info("Line: " + line);
                String[] arr = convertStrToArr(line);

                statement.setInt(1, Integer.parseInt(arr[3]));
                statement.setInt(2, Integer.parseInt(arr[0]));
                statement.setDate(3, Date.valueOf(arr[2]));
                statement.setInt(4, Integer.parseInt(arr[1]));
                while (statement.executeUpdate() < 1){ // проверяю, что запись добавлена
                                                       // может возникнуть ситуация, 
                                                       // когда клиент скинул нам строки, которые нужно заапдейтить, 
                                                       // но другой его сервис еще не добавил эти строки в БД
                                                       // теоретически - ждем мы в пределах 15 минут
                    log.info("Waiting in DB for: " + line);
                    Thread.sleep(60 * 1000);
                }
                updated++;
            }
            log.info("Updated from list: " + updated + " lines");
        } catch (SQLException | InterruptedException e) {
            throw new RuntimeException(e);
        } finally {
            if (statement != null){
                statement.close();
            }
        }
    }

Проблему с количеством соединений с базой я пытаюсь решить при помощи HikariCP, соединение с БД вынесено в отдельный класс:

public class DataBaseConnector {
    private static String dbDriver, url, login, pass, lc_ctype;
    private static final HikariDataSource dataSource;
    private static final Logger log;

    static {
        log = Logger.getLogger(DataBaseConnector.class);

        loadConnectionProperties();

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(dbDriver + url + "?lc_ctype=" + lc_ctype);
        config.setUsername(login);
        config.setPassword(pass);
        config.addDataSourceProperty("minimumIdle", "5");
        config.addDataSourceProperty("maximumPoolSize", "20");
        config.addDataSourceProperty("connectionTimeout", TimeUnit.SECONDS.toMillis(180L));
        dataSource = new HikariDataSource(config);
    }

    private static void loadConnectionProperties(){
        InputStream input = DataBaseConnector.class.getResourceAsStream(
                "/com/benderje/PCC/DB.properties");
        Properties props = new Properties();
        try {
            log.info("Loading properties...");
            props.load(input);
        } catch (IOException e) {
            log.error("Can't load!");
            throw new RuntimeException(e);
        }
        dbDriver = props.getProperty("dbDriver"); // используется Jaybird для соединения с Firebird DB
        url = props.getProperty("dbUrl");
        login = props.getProperty("dbLogin");
        pass = props.getProperty("dbPass");
        lc_ctype = props.getProperty("dbCharset");
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

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

Может, возникнуть ситуация, когда поток начинает запись в БД, прописывает одну (иногда 2-3) строку и повисает. Судя по логам, следующая запись пишется через 3-5 минут, дальше поток дописывает строки стабильно. Мне кажется это поведение некорректным. Если кто-то знает, подскажите, пожалуйста, как это можно решить. Да, что важно - при этом сообщения в лог об ожидании строчки в БД нет, поток в этот момент "повисает" не на Thread.sleep().
Через пару часов работы выбрасывается вот такой exception:

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30009ms.

Я поднял время ожидания подключения до трех минут (как сейчас указано в DataBaseConnector), но не уверен, что дело именно в этом и что так делать корректно.

Собственно, вопросы...:

Изучая Hikari, я нигде не увидел, чтобы кто-то использовал какой-нибудь метод "вернутьПодключениеНазадВПул"(). Я смотрел в сторону просто connection.close(), но тогда каждое соединение в пул не будет возвращаться, а будет просто закрываться... а тогда и в пуле смысла нет, как и во времени жизни подключения.

1.1) Тогда как правильнее "вернуть" его явно?

1.2) Или когда мы закрываем statement.close(), оно вернется силами Hikari?

1.3) Или оно вернется, когда поток отработает? А если бы потоки не закрывались?..

А как вообще адекватно рассчитывают такие параметры, как

Время жизни соединения в пуле

max и min количество соединений в этом пуле

Таймаут ожидания соединения из пула

Ведь не на глаз же?..

Answer 1

Для начала немного о том как работает пул.

нигде не увидел, чтобы кто-то использовал какой-нибудь метод "вернутьПодключениеНазадВПул"(). Я смотрел в сторону просто connection.close(), но тогда каждое соединение в пул не будет возвращаться, а будет просто закрываться

Такого метода нет, потому что именно close возвращает соединения в пул. hikari возвращает не jdbc соединение, которое вернул jdbc драйвер БД, а особый объект, который:

реализует интерфейс Connection, т.е. выглядит как обычное соединение
внутри содержит ссылку на объект соединения, которое выдал jdbc драйвер БД
перенаправляет все вызовы (кроме close) этому завернутому объекту
при выполенении close возвращает соединение в пул

как вообще адекватно рассчитывают такие параметры, как

Для этого нужно понять зачем используется пул и исходить из этого и ваших сценариев использования. У пула две основных задачи:

не позволить приложению открывать больше определенного количества одновременных соединений у БД. БД обычно не может обрабатывать слишком большое количество одновременных запросов. Причина в том, что у БД ограниченное количество ресурсов (памяти, процессора и пропускной способности диска). При большом количестве одновременных запросов, они одноверменно конкурируют за эти ресурсы и каждый из них выполняется очень медленно, т.к. например, планировщик ОС вытесняет поток или процесс, сохраняет его контекст и это сильно замедляет процесс выполнения конкретного запроса. Если просто подождать и выполнить запрос чуть позже, когда количество одновременных запросов снизилось, то это будет в разы быстрее чем пробовать одновременно их все выполнять.
уменьшить накладные расходы на открытие соединения. По просту говоря, вместо того, чтоб открывать новое соединения (а это долгий процесс), пул держит его уже открытым и возвращает приложению по запросу.

Итого:

max количество соединений в пуле определяется количеством ресурсов сервера БД (помещаются ли все данные в память или при запросах часто читается диск, сколько ядер) и сложностью запросов (долгие они или быстрые, какая часть запроса выполняется в памяти и упирается в количество процессоров, а какая упирается в операции чтения с диска). Например, если все упирается в процессоры, то делать соединений сильно больше чем колиство ядер нет смысла. Если все упирается в диск, то имеет значение суммарное количество данных, которые запросы за единицу времени читают с диска (это можно оценить, но правду покажет только эксперимент с репрезентативными данными), нужно чтоб суммарно соединения не читали сильно больше в единицу времени чем пропускная способность диска.
min количество, как и время жизни соединения в пуле особого смысла ограничивать нет. Разве что, иногда может быть полезно соедининея пересоздавать, чтоб избежать, например, проблем с утечками памяти в драйвере или СУБД (это если они есть).
таймаут соединения из пула. Значение зависит от того, сколько одновременных запросов может быть, и как долго они выполняются. Нужно соблюдать баланс: слишком много поставите и приложение будет долго ждать обратную связь, если что-то совсем не так (клиенты не отпускают соединений, это то, что вы наблюдаете сейчас, об этом ниже), слишком мало - и будут ложные таймауты под нагрузкой. Еще этот параметр зависит от того, как приложение будет обрабатывать ошибку, можно ли ее в принципе обработать. Если это фоновая задача, ее можно перезапустить с какой-то задержкой, а если запрос пришел от пользователся с UI, то тут придется возвращать ответ мол "не удалось, попробуйте позже" и тут таймаут должен согласовываться с тем, как долго мы можем себе позволить пользователю не отвечать на запрос - долгий таймаут увеличит это время.

Теперь конкретно о вашей проблеме.

У вас тут вижу потенциальные и реальные проблемы из того что описано:

Вы не закрываете соединения (не возвращаете в пул)
долгий sleep с захваченным соединением
не ясны границы транзакций

Нужно закрывать соединения

try (Connection connection = DataBaseConnector.getConnection()) {
   sendListToDB(connection);
}

Без этого клиенты, которые закончили все еще могут держать соединения и другие будут простаивать.

sleep

Ожидание в цикле while (statement.executeUpdate() < 1) - это очень плохо. Поток получил соединение из пула и потом спит потенциально очень долго. Это соединение никто другой использовать не может. Количество одновременно работающих потоков снижается. Когда у вас наберется 20 таких ожидающих - вся обработка встанет (и вы получите Connection is not available), хотя какие-то потоки и смогли бы, возможно, делать свое дело.

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

Альтернативный вариант - отпускать соединение, когда поток ждет, чтоб другие могли им попользоваться. Для этого придется структуру кода поменять, чтоб можно было соединение закрыть и потом открыть внутри sendListToDB.

границы транзакций

Обычно в jdbc драйвере включен автоматеческий commit. Это приводит к тому, что после каждой операции (у вас это executeUpdate) делается комит и это может сильно влиять на производительность. Может замедлить выполнение даже в 10 раз.

Может быть и обратная ситуация, что автокомит выключен. Тогда, если клиенты могут присылать данные на обновление тех же самых строк, то они будут один с другим конфликтовать. Один будет ждать, когда закончится транзакция другого. Если она длинная, то он может висеть. Особенно это может усугубиться из-за проблемы со sleep. Поток обновли запись (т.е. захватил на нее блокировку) и потому ушел спать.

Диагностика

поток в этот момент "повисает" не на Thread.sleep()

Чтоб опеределить, что делает поток используйте jstack. Эта утилита покажет, где ваши потоки находятся в данный момент. Выполняете в момент зависания и смотрите stacktrace-ы - так будете знать чем они заняты. Можно делать через каждые 5 секунд, чтоб видеть, какие потоки не продвигаются.

БЛОГ НА HUSL

Как правильно работать с коннектами из пула HikkariCP?

Ответы (1 шт):

Нужно закрывать соединения

sleep

границы транзакций

Диагностика