Настройка производительности базы данных

Максимальная производительность базы данных AggreGate Serverа - ключевой фактор высокой производительности всей инсталляции AggreGate. Вот краткий список советов по оптимизации общей производительности базы данных:

  • AggreGate Server использует множество запросов ВСТАВИТЬ и ОБНОВИТЬ, поэтому следуйте советам производителя базы данных по повышению производительности запросов ВСТАВИТЬ/ОБНОВИТЬ.

  • Когда значения переменных, содержащие большие двоичные блоки (звуки, изображения, файлы прошивок), хранятся в базе данных, AggreGate Server сохраняет их, используя одну транзакцию ВСТАВИТЬ/ОБНОВИТЬ. Таким образом, максимальный размер данных, переводимых за одну транзакцию, будет не ограничен или ограничен достаточно большим значением (рекомендуем ограничение в 100 Мб).

  • Для больших инсталляций мы рекомендуем отключение синхронного сброса данных ВСТАВИТЬ и ОБНОВИТЬ на диск при помощи подтверждения транзакции. Это принесет значительное повышение производительности.

  • Если сервер базы данных запущен на том же устройстве, что и AggreGate Server, рекомендуем позволить ему использование 25-40% оперативной памяти, оставив остальную часть AggreGateу. Также избегайте замен.

Настройка производительности пулов соединений базы данных

Дополнительный раздел определяет, как AggreGate Server управляет пулом соединений базы данных.

AggreGate Server использует библиотеку пулов соединений Java под названием c3p0, чтобы управлять соединениями базы данных. Настройки по умолчанию библиотеки c3p0 могут быть переопределены добавлением новых значений к файлу hibernate.properties, расположенному в установочной папке AggreGate Serverа.

Вот пример содержимого файла hibernate.properties:

# Удостоверьтесь в правильном подходе к решению пролемы базы данных
hibernate.c3p0.checkoutTimeout=30000

# Слишком большое время простоя вызывает ошибки из-за разъединения с базой данных
hibernate.c3p0.maxIdleTime=300

Настройки конфигурации библиотеки c3p0 описаны здесь: https://www.mchange.com/projects/c3p0/. Все свойства c3p0 должны иметь префикс "hibernate.".

Регулировка размера пула соединения с БД

В настройках БД AggreGate Server есть две опции, которые в значительной степени воздействуют на  производительность системы:

  • Минимальный размер пула соединений

  • Максимальный размер пула соединений

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

Существует несколько правил для регулировки размера пула:

  • Максимальный размер пула соединений должен быть больше количества одновременно подключенных устройств с высокой скоростью передачи данных (т.е. частые синхронизации или большое количество генерируемых устройством событий). Например, если отслеживаются 2000 устройств, которые опрашиваются один раз в час, можно назначить для максимального размера пула значение, равное 200, но если у Вас 500 устройств, синхронизируемых с сервером каждые 5 минут, лучше увеличить максимальный размер пула до 500.

  • Максимальный размер пула должен быть ниже общего количества соединений, разрешенных БД для того, чтобы избежать генерируемых базой данных ошибок, таких как "слишком много соединений".

  • Для крупных инсталляций, где пиковая активность значительно выше средней активности, рекомендуется увеличить минимальный размер пула до 20-50% от максимального размера пула.

Очистка базы данных

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

База данных AggreGate Server содержит преимущественно конфигурации и события. В то время как обычно объем данных конфигурации небольшой, и не увеличивается с течением времени, число событий (включая события изменения значений) может увеличиваться достаточно быстро, в следствии чего размер базы данных может составлять несколько гигабайтов.

Корректировка периодов хранения событий окажет влияние с течением времени, поэтому в некоторых случаях очистка базы данных вручную имеет смысл.

Чтобы установить объем и структуру базы данных, запустите действие Посмотреть статистику базы данных из корневого контекста. Это действие покажет Статистику события, т.е. количество событий в каждой таблице контейнеров событий и их распределение по типам и контекстам источника, а также общее число событий в каждой таблице.

Основная концепция очистки базы данных заключается в следующем:

Очистка любой таблицы базы данных AggreGate Server, которая содержит события в то время как сервер не работает, не помешает нормальной работе сервера.

Это делает возможным очистку почти любой таблицы в базе данных AggreGate Server кроме ag_properties и других "специальных" таблиц, описанных в разделе Схема и взаимодействие базы данных. Настоятельно рекомендуем остановить сервер и сделать резервную копию базы данных перед очисткой любой таблицы событий. Еще одной опцией являются таблицы очистки с помощью действия Выполнить прямой запрос к СУБД без остановки сервера, т.е. применив DELETE * FROM ag_xxx_change или похожий запрос обновления.

Очистка postgresql

Рекомендуемый цикл очистки для БД PostgreSQL DB - это выполнение vacuumlo() каждый час и vacuumdb() каждый день. Помогает сохранить оптимальный размер БД PostgreSQL:

#!/bin/bash
export PATH=$PATH:/usr/pgsql-9.4/bin/ export PATH=$PATH:/usr/pgsql-9.4/bin/
TIME=`date +'%Y%m%d-%H%M'`
echo $TIME" executing vacuumlo"
vacuumlo -U postgres -v -w password
TIME=`date +'%Y%m%d-%H%M'`
echo $TIME" stop vacuumlo"
#!/bin/bash
export PATH=$PATH:/usr/pgsql-9.4/bin/ export PATH=$PATH:/usr/pgsql-9.4/bin/
TIME=`date +'%Y%m%d-%H%M'`
echo $TIME" executing vacuumdb"
vacuumdb -fav -w
TIME=`date +'%Y%m%d-%H%M'`
echo $TIME" stop vacuumdb"

Очистка Postgresql BLOB

Есть два варианта удаления устаревших BLOB данных из базы данных.

Первый вариант - использовать команду:

vacuumlo -U <USER> <DATABASE>

Заметьте, что в процессе очистки показатели производительности значительно упадут.

Второй вариант применяется к системам 24/7. Необходимо создать триггерную функцию и набор триггеров для таблиц:

CREATE OR REPLACE FUNCTION "BlobDel"()
RETURNS trigger AS
$BODY$
BEGIN
delete from pg_catalog.pg_largeobject_metadata
where pg_catalog.pg_largeobject_metadata.oid = OLD.ag_data;
delete from pg_catalog.pg_largeobject
where pg_catalog.pg_largeobject.loid = OLD.ag_data;
return OLD;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "BlobDel"()
OWNER TO postgres;

Который будет активирован триггером:

CREATE TRIGGER ag_info
BEFORE DELETE
ON ag_info
FOR EACH ROW
EXECUTE PROCEDURE "BlobDel"();

Эта триггерная функция удалит все потерянные BLOB из таблиц pg.largeobject_metadata и pg.largeobject после операции очистки в таблице ag_info. Есть возможность добавления таких триггеров как этот в другие таблицы вашей базы данных.