Тестирование базы на ошибки postgresql

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?

In the Microsoft SQL Server world there’s a command that you can execute DBCC CHECKDB that will tell you if there’re issues. Here’s a link if your intrested in learning more on the command. DBCC CHECKDB (Transact-SQL)

I’m a paranoid database integrity minded person (which anyone who works with the database in a DBA type role should be) and this type of stuff makes it hard for me to sleep well at night. A utility like this is a must! Searches on google have found a few attempts at tools like this and in my opinion unless it’s an official accepted tool by the PostgreSQL project I won’t trust it for something this important.

Here are some links to people asking similar questions with what I consider no real definitive answer. And in my opinion shows that PostgreSQL needs to have some tools in place that Oracle and Microsoft SQL Server seem to have.

The first link is the most interesting I have found on this subject. I think a comment on the article that probably sums it up states: «Postgres is pretty lame when it comes to identifying database corruption and repairing it. The only way to detect it is by dumping the database or select * from every table in the database.»

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption — Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

I believe there is a chance 9.3 might have some corruption checking features. It appears there may be hope to having page files check summed if one chooses. So things are looking bright if you consider using ZFS and/or a future version of Postgres with page check summing.
https://commitfest.postgresql.org/action/patch_view?id=759

UPDATE: 14-JAN-2012 — Seems like using a file system based on ZFS can detect corruption by check summing each block of data. I will have to look into this further and see if this is a work around to allow one to sleep well at night knowing their database data is not silently going corrupt.

UPDATE: 17-JAN-2012 — How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.3

Источник

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption — Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

UPDATE: 17-JAN-2012 — How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.3

Источник

Оглавление

1 Параметры
- 1.1 Databases
- 1.2 CheckCommands
- 1.3 PhysicalOnly
- 1.4 NoIndex
- 1.5 ExtendedLogicalChecks
- 1.6 TabLock
- 1.7 FileGroups
- 1.8 Objects
- 1.9 MaxDOP
- 1.10 AvailabilityGroups
- 1.11 AvailabilityGroupReplicas
- 1.12 Updateability
- 1.13 LockTimeout
- 1.14 LogToTable
- 1.15 Execute
2 Примеры использования вложенной процедуры
- - 2.0.1 A. Проверка целостности во всех пользовательских базах
  - 2.0.2 B. Проверка физической целостности данных во всех пользовательских базах
  - 2.0.3 C. Проверка целостности данных во всех пользовательских базах, с использованием опции исключающей проверку не кластеризованных индексов
  - 2.0.4 D. Проверка целостности данных во всех пользовательских базах, с использованием опции упрощенной проверки логической структуры
  - 2.0.5 E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks
  - 2.0.6 F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks
  - 2.0.7 G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks
  - 2.0.8 H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks
  - 2.0.9 I. Проверка дискового места во всех пользовательских базах
3 Выполнение

Процедура от Ola Hallengren — DatabaseIntegrityCheck, позволяет проверить базу, файловую группу или таблицу. Если используется полноценный SQL Server, то можно использовать его в Maintains Paln. Если SQL Express, то можно использовать данную процедуру в планировщике, так как агент-SQL в этой редакции отсутствует. Проверка необходима перед архивацией базы. Нет смысла архивировать разрушенную базу!!! поэтому перед архивацией необходим проверить целостность данных. В качестве параметров можно указать список баз данных, время блокировки, объекты проверки и некоторые другие параметры. Данную процедуру удобно использовать при большом количестве баз данных или при обслуживании баз разработчиков, которые постоянно плодят новые ветки и заливают в новые базы. Полный список параметров и примеры использования ниже

Параметры

В качестве аргументов, отвечающих за выбор объектов проверки можно передать SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES и AVAILABILITY_GROUP_DATABASES, если поддерживается. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

Databases

Переменная	Описание
SYSTEM_DATABASES	Все системные базы (master, msdb, and model)
USER_DATABASES	Все пользовательские базы
ALL_DATABASES	Все базы
AVAILABILITY_GROUP_DATABASES	Все базы и группы доступности
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	Все пользовательские базы, исключая группы доступности
Db1	Ваза Db1
Db1, Db2	Базы Db1 и Db2
USER_DATABASES, -Db1	Все пользовательские базы, за исключением Db1
%Db%	Все базы, имеющие в названии Db
%Db%, -Db1	Все базы имеющие в названии Db, за исключением базы Db1
ALL_DATABASES, -%Db%	Все базы, за исключением тех, которые имеют в названии Db

CheckCommands

Использование данного параметра может ускорить скорость проверки путем исключения не нужных объектов или разделить задание на два этапа.

Переменная	Описание
CHECKDB	Проверяет логическую и физическую целостность всех объектов. По умолчанию
CHECKFILEGROUP	Проверяет распределение и структуру целостности всех таблиц и индексированных представлений в файловой группе
CHECKTABLE	Проверяет целостность всех страниц и структур, составляющих таблицу или индексированное представление
CHECKALLOC	Проверяет согласованность структур выделения места на диске
CHECKCATALOG	Проверяет согласованность каталогов в указанной базе данных
CHECKALLOC,CHECKCATALOG	Совмещает два параметра — проверку таблиц и представлений
CHECKFILEGROUP,CHECKCATALOG	Проверяет представления и таблицы для файловых групп
CHECKALLOC,CHECKTABLE,CHECKCATALOG	Проверяет параметры свободного места для таблиц и индексов, а также согласованность каталогов

При составлении команд использовалось описание для SQL Server: DBCC CHECKDB проверка баз данных, DBCC CHECKFILEGROUP проверка фаловых групп, DBCC CHECKTABLE проверка таблиц и индексных представлений, DBCC CHECKALLOC проверка свободного места в таблицах и DBCC CHECKCATALOG проверка согласованности каталогов.

PhysicalOnly

Ограничиться только физической проверкой структуры базы

Переменная	Описание
Y	Ограничиться только физической проверкой структуры базы
N	Не ограничиться только физической проверкой структуры базы. По умолчанию

Параметр PHYSICAL_ONLY использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

NoIndex

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

Переменная	Описание
Y	Не проверять
N	Проверять. По умолчанию

Параметр NoIndex использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

ExtendedLogicalChecks

Упрощенная проверка логической структуры

Переменная	Описание
Y	Провести простую проверку
N	Не проводить простую проверку. По умолчанию

Параметр ExtendedLogicalChecks использует опции следующих команд EXTENDED_LOGICAL_CHECKS и команды SQL Server DBCC CHECKDB

Нельзя комбинировать параметры PhysicalOnly и ExtendedLogicalChecks.

TabLock

Позволяет использовать внешний снапшот

Переменная	Описание
Y	Использовать блокировку, для проверки
N	Использовать снапшот. По умолчанию

Параметр TabLock использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

FileGroups

Выбор файловых групп. Данный параметр поддерживает выборку по словам. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_FILEGROUPS	Все файловые группы
Db1.FileGroup1	Файловая группа FileGroup1 в базе Db1
Db1.FileGroup1, Db2.FileGroup2	Файловая группа FileGroup1 в базе Db1 и фаловая группа FileGroup2 в базе Db2
ALL_FILEGROUPS, -Db1.FileGroup1	Все файловые группы, за исключением FileGroup1 в базе Db1
Db1.%FileGroup%	Все файловые группы в базе Db1 имеющие в своем имени “FileGroup”

Используются только специфические опции CHECKFILEGROUPS.

Objects

Выбор объектов. Параметр ALL_OBJECTS поддерживает выбор по маске. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_OBJECTS	Все объекты
Db1.Schema1.Tbl1	Объект Schema1.Tbl1 в базе Db1
Db1.Schema1.Object1, Db2.Schema2.Object2	Объект Schema1.Tbl1 в базе Db1 и объект Schema2.Tbl2 б базе данных Db2
ALL_OBJECTS, -Db1.Schema1.Object1	Все объекты, кроме Schema1.Object1 в базе Db1
Db1.Schema1.%	Все объекты в схеме Schema1 находящейся в базе Db1

Данный параметр можно использовать только совместно с параметром CHECKTABLE

MaxDOP

Количество ядер процессора выделенных для проверки базы, файловой группы или таблицы. Если данный параметр не задан, будут использоваться максимальное значение степени параллелизма.

Данный параметр может быть использован совместно с параметрами DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

AvailabilityGroups

выбор групп доступности. Параметр ALL_AVAILABILITY_GROUPS разрешает использование маски. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_AVAILABILITY_GROUPS	Все группы доступности
AG1	Группа доступности AG1
AG1, AG2	Группа доступности AG1 и AG1
ALL_AVAILABILITY_GROUPS, -AG1	Все группы доступности, за исключением группы AG1
%AG%	Все группы доступности содержащие в своем имени “AG”
%AG%, -AG1	Все группы доступности содержащие в своем имени “AG” за исключением группы AG1
ALL_AVAILABILITY_GROUPS, -%AG%	Все группы доступности не содержащие в своем имени “AG”

AvailabilityGroupReplicas

Выбор реплики для необходимости проверки.

Переменная	Описание
ALL	Проверяжтся все реплики. По умолчанию
PRIMARY	Проверяется только первичная реплика
SECONDARY	Проверяется только вторичная реплика

Updateability

Задает режим для базы данны READ_ONLY/READ_WRITE

Переменная	Описание
ALL	READ_ONLY и READ_WRITE — база данных. До умолчанию
READ_ONLY	READ_ONLY — только чтение
READ_WRITE	READ_WRITE — чтение запись

Переменная READ_ONLY в sys.databases используется если база в режиме READ_ONLY или READ_WRITE (Трудности перевода, пока не разбирался с данной переменной)

LockTimeout

Устанавливает время блокировки, после которого необходимо освободить захваченный объект. По умолчанию лимит отсутствует.

Параметр LockTimeout в хранимой процедуре IndexOptimize использует SET LOCK_TIMEOUT в терминологии SQL Server.

LogToTable

Логирование команд в таблицу dbo.CommandLog

Переменная	Описание
Y	Логировать команды в таблицу
N	Не логировать команды. По умолчанию

Execute

Выполнение команд. По умолчанию команды отправляются на выполнение. Если установлено значение N, то команды будут выведены без выполнения.

Переменная	Описание
Y	Выполнять команды. По умолчанию
N	Только вывеси команды

Примеры использования вложенной процедуры

A. Проверка целостности во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’

B. Проверка физической целостности данных во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@PhysicalOnly = ‘Y’

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

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@NoIndex = ‘Y’

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

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@ExtendedLogicalChecks = ‘Y’

E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘AdventureWorks.PRIMARY’

F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘ALL_FILEGROUPS, -AdventureWorks.PRIMARY’

G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘AdventureWorks.Production.Product’

H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘ALL_OBJECTS, -AdventureWorks.Production.Product’

I. Проверка дискового места во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKALLOC’

Выполнение

Для выполнения хранимой процедуры, необходимо использовать sqlcmd и опцию -b. Вызов процедуры происходит по имени

sqlcmd —E —S $(ESCAPE_SQUOTE(SRVR)) —d master —Q «EXECUTE dbo.DatabaseIntegrityCheck @Databases = ‘USER_DATABASES'» —b

Загрузка…

Источник

Asked
12 years, 5 months ago

Viewed
28k times

Is there a way to check PostgreSQL database(s) integrity and consistency? I know about SQL Server DBCC CHECKDB and wonder if there is something similar to PostgreSQL.

asked Aug 29, 2010 at 15:46

In 12 version of pgsql, there is pg_checksums.

answered Nov 26, 2019 at 13:16

What do you really want to achieve?

The database itself guarantees its integrity. You do not need tools to fiddle with.

answered Aug 29, 2010 at 18:08

cstamascstamas

6,64724 silver badges42 bronze badges

answered Aug 29, 2010 at 20:26

alerootaleroot

3,1705 gold badges28 silver badges37 bronze badges

Источник

20 марта, 2019 12:10 пп
14 208 views
| Комментариев нет

Cloud Server, VPS

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

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

Требования

Управляемая БД PostgreSQL.
Клиентская машина с экземпляром PostgreSQL. По умолчанию установка PostgreSQL содержит утилиту тестирования производительности pgbench и клиент psql, которые мы будем использовать в этом мануале. Чтобы узнать, как установить PostgreSQL, читайте мануал Установка и использование PostgreSQL в Ubuntu 18.04.

1: Создание и инициализация БД benchmark

Прежде чем создать пул соединений для базы данных, создайте базу данных benchmark в вашем кластере PostgreSQL и заполните ее фиктивными данными, на которых pgbench будет выполнять свои тесты. Утилита pgbench многократно запускает серию из пяти SQL команд (включая запросы SELECT, UPDATE и INSERT), используя несколько потоков и клиентов, и вычисляет показатель полезной производительности под названием «транзакции в секунду» (TPS, transactions per second). TPS – это показатель пропускной способности базы данных, который показывает количество транзакций, обработанных базой данных за одну секунду. Чтобы узнать больше о командах, выполняемых pgbench, обратитесь к официальной документации pgbench.

Итак, подключитесь к кластеру PostgreSQL и создайте БД benchmark.

Процесс создания будет зависеть от вашего провайдера.

При этом вам понадобятся такие данные (мы используем условные данные, которые вы должны заменить):

Имя администратора: myadmin
your_password
Пароль: your_password
Конечная точка кластера: укажите свою точку
Порт: 25060
База данных: defaultdb
Режим SSL: require (чтобы использовать зашифрованное соединение SSL)

Эти параметры вам понадобятся в дальнейшей работе с клиентом psql и инструментом pgbench, потому запомните или запишите их.

Подключитесь к кластеру с помощью клиента psql (для этого вам может понадобиться строка подключения).

Вы попадете в командную строку клиента PostgreSQL, если подключение будет удачно создано.

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. defaultdb=>

Создайте БД benchmark:

CREATE DATABASE benchmark; CREATE DATABASE

Выйдите из кластера:

q

Перед запуском тестов pgbench необходимо заполнить эту тестовую базу данных несколькими таблицами и фиктивными данными.

Для этого мы запустим pgbench со следующими флагами:

-h: конечная точка кластера PostgreSQL
-p: порт кластера PostgreSQL
-U: имя пользователя базы данных
-i: инициализирует базу данных benchmark с помощью таблиц и их фиктивных данных.
-s: устанавливает масштабный коэффициент 150, который умножит размеры таблицы на 150. Стандартный масштабный коэффициент 1 создает таблицу следующих размеров:

table # of rows --------------------------------- pgbench_branches 1 pgbench_tellers 10 pgbench_accounts 100000 pgbench_history 0

Масштабный коэффициент 150 создаст таблицу pgbench_accounts в 15 000 000 строк.

Примечание: Чтобы избежать чрезмерного количества заблокированных транзакций, убедитесь, что значение масштабного коэффициента как минимум равно количеству одновременно работающих клиентов, которые используются в тестировании. В этом мануале мы протестируем максимум 150 клиентов, поэтому мы установили здесь значение -s 150. Чтобы узнать больше, ознакомьтесь с этими рекомендациями из официальной документации pgbench.

Запустите команду pgbench:

pgbench -h your_cluster_endpoint -p 25060 -U myadmin -i -s 150 benchmark

После этого вам будет предложено ввести пароль указанного пользователя БД. Введите пароль и нажмите Enter.

dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data... 100000 of 15000000 tuples (0%) done (elapsed 0.19 s, remaining 27.93 s) 200000 of 15000000 tuples (1%) done (elapsed 0.85 s, remaining 62.62 s) 300000 of 15000000 tuples (2%) done (elapsed 1.21 s, remaining 59.23 s) 400000 of 15000000 tuples (2%) done (elapsed 1.63 s, remaining 59.44 s) 500000 of 15000000 tuples (3%) done (elapsed 2.05 s, remaining 59.51 s) . . . 14700000 of 15000000 tuples (98%) done (elapsed 70.87 s, remaining 1.45 s) 14800000 of 15000000 tuples (98%) done (elapsed 71.39 s, remaining 0.96 s) 14900000 of 15000000 tuples (99%) done (elapsed 71.91 s, remaining 0.48 s) 15000000 of 15000000 tuples (100%) done (elapsed 72.42 s, remaining 0.00 s) vacuuming... creating primary keys... done.

На данный момент у вас есть база данных benchmark, заполненная таблицами и данными, необходимыми для запуска тестов pgbench. Теперь можно запустить базовый тест, который позволит нам сравнить производительность до и после включения пула соединений.

2: Запуск базового теста pgbench

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

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

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

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

Откройте панель управления своими базами данных. в меню найдите опцию типа Connection Pools и создайте пул соединений.

Для этого вам понадобятся такие данные:

Pool Name: уникальное имя вашего пула соединений.
Database: База данных, соединения которой вы хотите объединить в пул.
User: пользователь PostgreSQL, с помощью которого пул соединений будет аутентифицироваться
Mode: режим. Эта опция контролирует, как долго пул назначает клиентскому серверу бэкэнд-соединение.

- Session: Клиент удерживает соединение до тех пор, пока оно явно не отключится.
- Transaction: Клиент получает соединение до завершения транзакции, после чего соединение возвращается в пул.
- Statement: Пул агрессивно перезапускает соединения после каждого оператора клиента. В этом режиме транзакции с несколькими операторами не поддерживаются.

Pool Size: количество соединений, которые пул будет держать открытым между собой и базой данных.

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

В этом мануале мы используем 4 ГБ RAM, 2 vCPU, 80 ГБ дискового пространства, управляемую базу данных (только ведущая нода). Вы можете масштабировать параметры теста производительности в соответствии с вашими спецификациями кластера PostgreSQL.

Итак, давайте запустим первый тест pgbench.

Войдите на клиентскую машину. Запустите pgbench, указав конечную точку, порт и пользователя. Также нужно добавить эти опции:

-c: количество одновременных клиентов или сеансов БД, которое нужно симулировать. Мы установим здесь 50, чтобы имитировать количество одновременных соединений, меньшее, чем параметр max_connections данного кластера PostgreSQL.
-j: количество рабочих потоков, которые pgbench будет использовать во время теста. Если вы используете многопроцессорную машину, вы можете увеличить зто значение, чтоб распределить клиентов по потокам. На двухъядерном компьютере нужно установить значение 2.
-P: отображает прогресс и метрики каждые 60 секунд.
-T: запустит тест на 600 секунд (10 минут). Чтобы получить согласованные, воспроизводимые результаты, важно, чтобы тест длился в течение нескольких минут или один цикл контрольных точек.

Также нужно указать, что тест нужно запустить в БД benchmark.

Запустите эту команду:

pgbench -h your_db_endpoint -p 25060 -U myadmin -c 50 -j 2 -P 60 -T 600 benchmark

Нажмите клавишу Enter и введите пароль пользователя myadmin, чтобы начать выполнение теста. Вы должны увидеть подобный вывод (результаты будут зависеть от характеристик вашего кластера PostgreSQL):

starting vacuum...end. progress: 60.0 s, 157.4 tps, lat 282.988 ms stddev 40.261 progress: 120.0 s, 176.2 tps, lat 283.726 ms stddev 38.722 progress: 180.0 s, 167.4 tps, lat 298.663 ms stddev 238.124 progress: 240.0 s, 178.9 tps, lat 279.564 ms stddev 43.619 progress: 300.0 s, 178.5 tps, lat 280.016 ms stddev 43.235 progress: 360.0 s, 178.8 tps, lat 279.737 ms stddev 43.307 progress: 420.0 s, 179.3 tps, lat 278.837 ms stddev 43.783 progress: 480.0 s, 178.5 tps, lat 280.203 ms stddev 43.921 progress: 540.0 s, 180.0 tps, lat 277.816 ms stddev 43.742 progress: 600.0 s, 178.5 tps, lat 280.044 ms stddev 43.705 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 50 number of threads: 2 duration: 600 s number of transactions actually processed: 105256 latency average = 282.039 ms latency stddev = 84.244 ms tps = 175.329321 (including connections establishing) tps = 175.404174 (excluding connections establishing)

Здесь можно видеть, что за 10 минут работы с 50 одновременными соединениями кластер обработал 105 256 транзакций с пропускной способностью примерно 175 транзакций в секунду.

Теперь давайте запустим тот же тест, но на этот раз используем 150 одновременных клиентов. Это значение выше, чем max_connections для этой базы данных, это позволяет симулировать массовый приток клиентских подключений:

pgbench -h your_db_endpoint -p 25060 -U myadmin -c 150 -j 2 -P 60 -T 600 benchmark

Вы получите результат:

starting vacuum...end. connection to database "pgbench" failed: FATAL: remaining connection slots are reserved for non-replication superuser connections progress: 60.0 s, 182.6 tps, lat 280.069 ms stddev 42.009 progress: 120.0 s, 253.8 tps, lat 295.612 ms stddev 237.448 progress: 180.0 s, 271.3 tps, lat 276.411 ms stddev 40.643 progress: 240.0 s, 273.0 tps, lat 274.653 ms stddev 40.942 progress: 300.0 s, 272.8 tps, lat 274.977 ms stddev 41.660 progress: 360.0 s, 250.0 tps, lat 300.033 ms stddev 282.712 progress: 420.0 s, 272.1 tps, lat 275.614 ms stddev 42.901 progress: 480.0 s, 261.1 tps, lat 287.226 ms stddev 112.499 progress: 540.0 s, 272.5 tps, lat 275.309 ms stddev 41.740 progress: 600.0 s, 271.2 tps, lat 276.585 ms stddev 41.221 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 150 number of threads: 2 duration: 600 s number of transactions actually processed: 154892 latency average = 281.421 ms latency stddev = 125.929 ms tps = 257.994941 (including connections establishing) tps = 258.049251 (excluding connections establishing)

Обратите внимание на ошибку FATAL – она указывает на то, что pgbench достиг порогового значения в 100 соединений (max_connections), что привело к отказу в соединении. И все же pgbench смог закончить тест с TPS примерно в 257.

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

3: Создание и тестирование пула соединений

Теперь давайте создадим пул соединений и повторно запустим предыдущий тест pgbench, чтобы посмотреть, сможет ли это улучшить пропускную способность базы данных.

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

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

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

В появившемся окне конфигурации заполните предложенные поля и нажмите Создать.

Скопируйте появившийся URI.

Вы должны заметить, что в результате в URI будет другой порт и, возможно, другая конечная точка и имя базы данных, соответствующее имени пула (в данном случае test-pool).

Теперь, когда мы создали пул соединений test-pool, мы можем снова запустить тест pgbench, который мы запускали ранее.

Повторный запуск pgbench

На своем клиентском компьютере выполните следующую команду pgbench (укажите 150 одновременных клиентов), убедившись, что выделенные значения заменены значениями в URI пула соединений:

pgbench -h pool_endpoint -p pool_port -U myadmin -c 150 -j 2 -P 60 -T 600 test-pool

Здесь мы снова используем 150 одновременных клиентов, запуская тест в двух потоках. Команда будет отображать прогресс каждые 60 секунд, тест будет продолжаться в течение 600 секунд. Имя базы данных должно быть test-pool, как и имя пула соединений.

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

starting vacuum...end. progress: 60.0 s, 240.0 tps, lat 425.251 ms stddev 59.773 progress: 120.0 s, 350.0 tps, lat 428.647 ms stddev 57.084 progress: 180.0 s, 340.3 tps, lat 440.680 ms stddev 313.631 progress: 240.0 s, 364.9 tps, lat 411.083 ms stddev 61.106 progress: 300.0 s, 366.5 tps, lat 409.367 ms stddev 60.165 progress: 360.0 s, 362.5 tps, lat 413.750 ms stddev 59.005 progress: 420.0 s, 359.5 tps, lat 417.292 ms stddev 60.395 progress: 480.0 s, 363.8 tps, lat 412.130 ms stddev 60.361 progress: 540.0 s, 351.6 tps, lat 426.661 ms stddev 62.960 progress: 600.0 s, 344.5 tps, lat 435.516 ms stddev 65.182 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 150 number of threads: 2 duration: 600 s number of transactions actually processed: 206768 latency average = 421.719 ms latency stddev = 114.676 ms tps = 344.240797 (including connections establishing) tps = 344.385646 (excluding connections establishing)

Обратите внимание, мы смогли увеличить пропускную способность базы данных с 257 TPS до 344 TPS при 150 одновременных соединениях (это увеличение на 33%) и не столкнулись с превышением max_connections, как было без пула соединений. Пул соединений позволяет избежать разрыва соединений и значительно увеличить пропускную способность базы данных в среде с большим количеством одновременных соединений.

Если вы запустите этот же тест, но со значением -c 50 (уменьшив количество клиентов), плюсы использования пула соединений станут гораздо менее очевидными:

starting vacuum...end. progress: 60.0 s, 154.0 tps, lat 290.592 ms stddev 35.530 progress: 120.0 s, 162.7 tps, lat 307.168 ms stddev 241.003 progress: 180.0 s, 172.0 tps, lat 290.678 ms stddev 36.225 progress: 240.0 s, 172.4 tps, lat 290.169 ms stddev 37.603 progress: 300.0 s, 177.8 tps, lat 281.214 ms stddev 35.365 progress: 360.0 s, 177.7 tps, lat 281.402 ms stddev 35.227 progress: 420.0 s, 174.5 tps, lat 286.404 ms stddev 34.797 progress: 480.0 s, 176.1 tps, lat 284.107 ms stddev 36.540 progress: 540.0 s, 173.1 tps, lat 288.771 ms stddev 38.059 progress: 600.0 s, 174.5 tps, lat 286.508 ms stddev 59.941 transaction type: <builtin: TPC-B (sort of)> scaling factor: 150 query mode: simple number of clients: 50 number of threads: 2 duration: 600 s number of transactions actually processed: 102938 latency average = 288.509 ms latency stddev = 83.503 ms tps = 171.482966 (including connections establishing) tps = 171.553434 (excluding connections establishing)

Этот вывод говорит, что вы не смогли увеличить пропускную способность с помощью пула соединений. Пропускная способность снизилась до 171 TPS со 175 TPS.

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

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

Заключение

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

Существуют и другие инструменты для нагрузочного тестирования базы данных. Одним из таких инструментов является sysbench-tpcc, разработанный Percona. Еще есть Apache JMeter, который может загружать тестовые базы данных и веб-приложения.

Читайте также:

Управляемые базы данных: преимущества, узкие места, практические рекомендации
Фрагментация базы данных: основные методы и случаи использования
Официальная документация pgbench

Tags: pgbench, PostgreSQL, SQL

Источник

pgbench (инструмент стресс-теста postgresql)

Введение

Postgresql поставляется с легким инструментом стресс-тестирования: pgbench
Вы можете писать свои собственные сценарии и выполнять стресс-тесты производительности для базы данных в соответствии с вашими потребностями.
Многие пользователи сети на форуме postgresql поделились своими собственными инструментами разработки для pgbench, которые значительно расширили его функции.

тестовая среда

Операционная система: Ubuntu 15.10
База данных: postgresql 9.4
Виртуальная машина: VMware Workstation 12
ОЗУ: 2 ГБ
Жесткий диск: 40 ГБ

Эта база данных построила активный и резервный режим кластера pgpool:
pgpool： port=9999
master: port=5433
slave: port=5434

Введение параметра

войти:

pgbench –help

Вернуть подробную информацию:

pgbench is a benchmarking tool for PostgreSQL.

Usage:
  pgbench [OPTIONS]... [DBNAME]

Initialization options:
  -я вызываю режим инициализации
  -F NUM       fill factor        fillfactor
  -s NUM              коэффициент масштабирования количество торговых терминалов

Benchmarking options:
  -c NUM       number of concurrent database clients (default: 1) Количество смоделированных клиентов
  -C       establish new connection for  каждая транзакция настроена на создание нового соединения для каждой транзакции
  -D VARNAME=VALUE        Передайте значение переменной в тестовом скрипте
               define variable for use by custom script
  -f FILENAME  read transaction script from FILENAME          Расположение тестового скрипта
  -j NUM       number of threads (default: 1) Количество запускаемых потоков
  -l запись времени транзакций в файл журнала сиреневый выходной файл журнала
  -M {simple|extended|prepared}
               protocol for submitting queries to server (default: simple)
  -n           do not run VACUUM before tests
  -N           do not update tables "pgbench_tellers" and "pgbench_branches"
  -r           report average latency per command
  -s NUM       report this scale factor in  вывод показывает количество торговых терминалов в результатах тестирования
  -S           perform SELECT-only transactions
  -t NUM       number of transactions each client runs (default: 10) Количество транзакций, выполненных на одного клиента
  -T NUM       duration of benchmark test in  секунд тестовое время
  -v           vacuum all four standard tables before tests

Common options:
  -d           print debugging output
  -h HOSTNAME  database server host or socket directory
  -p PORT      database server port number
  -U USERNAME  connect as specified database user
  --help       show this help, then exit
  --version    output version information, then exit

Report bugs to .

Создайте тестовую среду

Введите postgresql через pgpool:

psql –p 9999 –U postgres

Создайте тестового пользователя:

CREATE ROLE pg_bench LOGIN
  ENCRYPTED PASSWORD 'oracle'
  SUPERUSER INHERIT CREATEDB NOCREATEROLE REPLICATION;

Создать тестовое табличное пространство

Используйте пользователя root для создания каталога табличного пространства:

mkdir /home/ptbs

Предоставьте пользователю права доступа к postgres:

chown –R postgres /home/ptbs

Создать табличное пространство tbs:

CREATE TABLESPACE tbs
  OWNER pg_bench
  LOCATION '/home/ptbs';

Создайте тестовую библиотеку pg_bench:

CREATE DATABASE pg_bench
  WITH OWNER = pg_bench
       ENCODING = 'UTF8'
       TABLESPACE = tbs;

Инициализировать тестовую среду

Используйте команду: pgbench -i [другие-параметры] dbname
, например:

pgbench –p 9999 –U postgres –I pg_bench

После завершения выполнения войдите в тестовую библиотеку pg_bench и обнаружите, что были созданы 4 таблицы:

psql –p 9999 –U postgres –d pg_bench

Поскольку используется тестовая таблица, созданная с параметрами по умолчанию, объем данных каждой таблицы следующий: (Таблица истории представляет собой таблицу записей, которая при создании пуста)

name	size
pgbench_accounts	100000
pg_branches	1
pg_tellers	10

При создании тестовой таблицы вы можете установить количество тестовых данных, таких как:

pgbench –p 9999 –U postgres –i –F 100 –s 500 pg_bench

Данные теста будут расширены в 500 раз, а именно:

name	size
pgbench_accounts	50000000
pg_branches	500
pg_tellers	5000

Тестовый сценарий

Инструмент pg_bench поставляется с тестовым скриптом
Скрипт выглядит следующим образом:

static char *tpc_b = {
 "\set nbranches :scalen"
 "\set ntellers 10 * :scalen"
 "\set naccounts 100000 * :scalen"
 "\setrandom aid 1 :naccountsn"
 "\setrandom bid 1 :nbranchesn"
 "\setrandom tid 1 :ntellersn"
 "\setrandom delta -5000 5000n"
 "BEGIN;n"
 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;n"
 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;n"
 "UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;n"
 "UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;n"
 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);n"
 "END;n"
};
/* -N case */
static char *simple_update = {
 "\set nbranches :scalen"
 "\set ntellers 10 * :scalen"
 "\set naccounts 100000 * :scalen"
 "\setrandom aid 1 :naccountsn"
 "\setrandom bid 1 :nbranchesn"
 "\setrandom tid 1 :ntellersn"
 "\setrandom delta -5000 5000n"
 "BEGIN;n"
 "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;n"
 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;n"
 "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);n"
 "END;n"
};
/* -S case */
static char *select_only = {
 "\set naccounts 100000 * :scalen"
 "\setrandom aid 1 :naccountsn"
 "SELECT abalance FROM pgbench_accounts WHERE aid = :aid;n"
};

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

испытание под давлением

1 сеанс на кластере:

pgbench –p 9999 –U pg_bench –c 1 –T 20 –r pg_bench

30 сеансов на кластере:

pgbench –p 9999 –U pg_bench –c 30 –T 20 –r pg_bench

Выполнить непосредственно на одном узле 5433:

pgbench –p 5433 –U pg_bench –c 30 –T 20 –r pg_bench

Сравнивая tps, очевидно, что tps в среде с одной машиной выше, чем в среде кластера.

Самостоятельный сценарий

Сценарий только для чтения

Сценарии:
переключиться на пользователя postgres
Создать скрипт только для чтения readonly.sql
выполнить:

vim readonly.sql

Напишите:

set naccounts 100000 * :scale
setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

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

pgbench –p 9999 –U pg_bench –c 1 –T 20 –f /var/lib/postgresql/pgbench_script/readonly.sql

Читать и писать скрипт

Конкретный метод использования такой же, как указано выше
Скрипт:

set nbranches :scale
set ntellers 10 * :scale
set naccounts 100000 * :scale
setrandom aid 1 :naccounts
setrandom bid 1 :nbranches
setrandom tid 1 :ntellers
setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CLOCK_TIMESTAMP());
END;

Функциональный скрипт

# В основном для сравнения разницы в производительности между вызовом функции и прямым использованием SQL для того же
 # Создайте следующую функцию
create or replace function pgbench(i_aid int,i_bid int,i_tid int,i_delta int) returns setof int as $BODY$
declare
begin          
UPDATE pgbench_accounts SET abalance = abalance + i_delta WHERE aid = i_aid;
UPDATE pgbench_tellers SET tbalance = tbalance + i_delta WHERE tid = i_tid; 
UPDATE pgbench_branches SET bbalance = bbalance + i_delta WHERE bid = i_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (i_tid, i_bid, i_aid, i_delta, CURRENT_TIMESTAMP);
return query SELECT abalance FROM pgbench_accounts WHERE aid = i_aid;                                              
end;                                                                 
$BODY$ language plpgsql;

Содержание скрипта:

set nbranches :scale
set ntellers 10 * :scale
set naccounts 100000 * :scale
setrandom aid 1 :naccounts
setrandom bid 1 :nbranches
setrandom tid 1 :ntellers
setrandom delta -5000 5000
select pgbench(:aid,:bid,:tid,:delta);

То же, что и выше

Преимущества и недостатки

После тестирования выяснилось, что pg_bench имеет следующие преимущества и недостатки:
Преимущества:
1. Система поставляется с ним, имеет хорошую совместимость с postgresql и проста в настройке.
2. Небольшие инструменты и высокая скорость выполнения
3. Программное обеспечение с открытым исходным кодом, различные функциональные плагины можно найти в Интернете.

Недостатки:
1. Результаты измерений сильно колеблются. По результатам многих экспериментов результаты измерений составляли от 40 до 400 операций в секунду. Статистические данные показывают, что большинство результатов выполнения попадает в диапазон 350–400. Следовательно, при его использовании он должен выполняться несколько раз и принимать соответствующее значение.
2. Нельзя прерывать. Операция тестирования не может быть прервана во время выполнения.Даже если процесс pg_bench убит, его сценарий вошел в postgresql, и postgresql продолжит выполнение теста. Остается только ждать, пока он закончит.

Справка:
PostgreSQL 9.4.17 Documentation
Один день, чтобы узнать о разработке и управлении приложениями PostgreSQL-8 Управление PostgreSQL

Источник

Тестирование базы на ошибки postgresql

Параметры

Databases

CheckCommands

PhysicalOnly

NoIndex

ExtendedLogicalChecks

TabLock

FileGroups

Objects

MaxDOP

AvailabilityGroups

AvailabilityGroupReplicas

Updateability

LockTimeout

LogToTable

Execute

Примеры использования вложенной процедуры

A. Проверка целостности во всех пользовательских базах

B. Проверка физической целостности данных во всех пользовательских базах

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

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

E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks

F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks

G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks

H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks

I. Проверка дискового места во всех пользовательских базах

Выполнение

Требования

1: Создание и инициализация БД benchmark

2: Запуск базового теста pgbench

3: Создание и тестирование пула соединений

Повторный запуск pgbench

Заключение

pgbench (инструмент стресс-теста postgresql)

Введение

тестовая среда

Введение параметра

Создайте тестовую среду

Инициализировать тестовую среду

Тестовый сценарий

испытание под давлением

Самостоятельный сценарий

Сценарий только для чтения

Читать и писать скрипт

Функциональный скрипт

Преимущества и недостатки

А вот еще интересные материалы: