Главная
Блог разработчиков phpBB
 
+ 17 предустановленных модов
+ SEO-оптимизация форума
+ авторизация через соц. сети
+ защита от спама

Пулы соединений к БД — для чего и отчего

Anna | 4.06.2014 | нет комментариев
Когда Ваш план начинает пользуется популярностью и всякая миллисекунда обработки запроса от пользователя становится скептической, доводится искать тесные места в системе. Зачастую огромнее каждого времени занимает выполнение SQL запроса из приложения к базе данных. Испробуем разобраться, что дозволено оптимизировать в программе при работе с БД.

Теория

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

  1. Открытие соединения к БД и отправка запроса серверу.
  2. Сервер парсит SQL запрос.
  3. Сервер оптимизирует запрос исходя из правил, а так же из статистики по таблицам. В итоге строится план выполнения запроса.
  4. Сервер исполняет запрос в соответствии с ранее построенном планом и отправляет итоги пользователю.

На чем же дозволено сэкономить время?
1-й шаг открытия соединения с сервером, является достаточно длинным и мы можем его исключить предварительно подготовив пул теснее открытых соединений и предоставляя из него соединения приложению по мере необходимости.

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

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

Метод измерения

Для тестов используем вольно распространяемую СУБД PostgreSQL, а заказчик напишем на JAVA. В БД сотворим небольшую таблицу test.test_table (около 10 строк), состоящую из первичного ключа id и строкового значения value. Пускай у нас заказчики параллельно исполняют запросы к БД, для этого сотворим потоки, которые будут делать примитивные запросы поиска по первичному ключу в этой таблице. При создании потоков мы будем указывать разную реализацию пулов соединений, что дозволит нам сравнить эффективность, т.к. поток будет считать суммарное время потраченное им на выполнение 100 запросов.

    class TestThread extends Thread {
        private DBPool pool;   
        private long workTime = 0;
        private long foundStr = 0;

        @Override
        public void run() {
            workTime = System.currentTimeMillis(); // Засекаем время
            Connection con = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            Random rnd = new Random();// будем применять как первичный ключ в запросе
            for (int i = 0; i < 100; i  ) {
                try {
                    con = pool.getConnection();// получем соединение к БД
                    // отправляем запрос на парсинг и построение плана выполнения
                    st = con.prepareStatement("SELECT a.*  FROM test.test_table a WHERE id =?");
                    st.setObject(1, rnd.nextInt(10));
                    rs = st.executeQuery();// исполняем запрос
                    if (rs.next()) {
                        String tmp = (rs.getString(2));   // обрабатываем итог
                        if (tmp != null) {
                            foundStr  ;
                        }
                    }
                } catch (SQLException ex) {
                    //ошибка при выполнении, выводим в консоль
                    System.out.println("Pool "   pool   " exeption "   ex);
                } finally {
                    // и в конце, старательно закрываем все использованные нами объекты
                    try {
                        if (rs != null)
                            rs.close();
                    } catch (SQLException e) {
                        //ignore
                    }
                    try {
                        if (st != null)
                            st.close();
                    } catch (SQLException e) {
                        //ignore
                    }
                    try {
                        if (con != null)
                            pool.putConnection(con); // кладем соединение обратно в пул
                    } catch (SQLException e) {
                        //ignore
                    }
                }
            }
            workTime = System.currentTimeMillis() - workTime; // получаем потраченное время
        }
    }

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

    class DBPool {
        private String url, user, password;

        DBPool(String url, String user, String password) throws ClassNotFoundException {
            this.url = url;
            this.user = user;
            this.password = password;
            Class.forName("org.postgresql.Driver");
        }

        public Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url, user, password);
        }

        public void putConnection(Connection connection) throws SQLException {
            connection.close();
        }
    }

Вторым будет, использующий особый кеширующий источник данных класс из JDBC драйвера к PostgreSQL — PGPoolingDataSource. Тот, что разрешает задать размер пула соединений, а так же исходное число соединений. Помимо того в настройках у PreparedStatement есть настройка setPrepareThreshold — отвечающая за число выполнений запроса, позже которого запрос кешируется и не требует парсинга и построения плана выполнения.

    class DBPoolCache extends DBPool {
        private PGPoolingDataSource source;

        DBPoolCache(String host, String database, String user, String password) {
            source = new PGPoolingDataSource();
            source.setDataSourceName("A Data Source");
            source.setServerName(host);
            source.setDatabaseName(database);
            source.setUser(user);
            source.setPassword(password);
            source.setMaxConnections(20);//Максимальное значение
            source.setInitialConnections(20);//Сколько соединений будет сразу открыто
        }

        public Connection getConnection() throws SQLException {
            return source.getConnection();
        }

        public void putConnection(Connection connection) throws SQLException {
            connection.close();
        }
    }

Ну и в конце нашу реализацию пулов, когда мы сами кешируем соединения к БД а также итоги разбора SQL запроса (PreparedStatement).

    class DBPoolCacheMy extends DBPool {
        private String url, user, password;
        private PGSimpleDataSource source;
        private BlockingQueue<Connection> connections = new ArrayBlockingQueue<Connection>(20);

        DBPoolCacheMy(String host, String database, String user, String password) throws SQLException {
            source = new PGSimpleDataSource();
            source.setServerName(host);
            source.setDatabaseName(database);
            source.setUser(user);
            source.setPassword(password);
            for (int i = 0; i < 20; i  ) {//Подготавливаем соединения
                connections.add(new MyConnection(source.getConnection()));
            }
        }

        public Connection getConnection() throws SQLException {
            try {  // пробуем получить свободное соединение
                return connections.poll(2, TimeUnit.SECONDS);
            } catch (InterruptedException e) {
                return null;
            }
        }

        public void putConnection(Connection connection) throws SQLException {
            connections.add(connection);
        }
    }

Так же придётся реализовать свой класс соединения с БД, тот, что будет осуществлять кеширование PreparedStatement.

 class MyConnection implements Connection {
        private Connection connection;

        protected MyConnection(Connection connection) {
            this.connection = connection;
        }

        private ConcurrentHashMap<String, PreparedStatement> statements = new ConcurrentHashMap<String, PreparedStatement>();

        public PreparedStatement prepareStatement(String sql) throws SQLException {
            PreparedStatement statement = statements.get(sql);
            if (statement == null) {
                statement = new MyStatement(connection.prepareStatement(sql));
                statements.put(sql, statement);
            }
            return statement;
        }
.....
  }

Плюс свой класс реализующей интерфейс PreparedStatement, и не реагирующий на закрытие

    class MyStatement implements PreparedStatement {
        private PreparedStatement statement;

        MyStatement(PreparedStatement statement) throws SQLException {
            this.statement = statement;
            ((PGStatement) statement).setPrepareThreshold(1);
        }

        public void close() throws SQLException {
            //ignore
        }
.....
}

Завершение

Ну и наконец сравним продуктивность 3 разных пулов соединений, запустим тесты с числом параллельных потоков от 1 до 10, для разных реализаций. В итоге получился дальнейшая связанность всеобщего времени выполнения задачи от числа потоков.

Из графика видно, что кешировать соединения с БД очевидно необходимо, это даёт существенный приход продуктивности системы. А вот писать самописную реализацию кеширования соединений и PreparedStatement не даёт ощутимой выгоды.

Источник: programmingmaster.ru

Оставить комментарий
Форум phpBB, русская поддержка форума phpBB
Рейтинг@Mail.ru 2008 - 2017 © BB3x.ru - русская поддержка форума phpBB