Практикум

Архитектура

В представлении pg_stat_activity можно получить информацию о текущей активности процессов в экземпляре:

[postgres@p620 ~]$ psql psql (15.5)
Type "help" for help.
postgres=# select pid, backend_start, backend_type from pg_stat_activity; pid | backend_start | backend_type
-------+-------------------------------+------------------------------
9706 | 2024-11-08 11:44:40.101847+03 | autovacuum launcher
9707 | 2024-11-08 11:44:40.104828+03 | autounite launcher
9708 | 2024-11-08 11:44:40.109135+03 | integrity check launcher
9709 | 2024-11-08 11:44:40.112181+03 | logical replication launcher
12656 | 2024-11-08 15:37:06.081169+03 | client backend
9701 | 2024-11-08 11:44:40.055098+03 | background writer
9700 | 2024-11-08 11:44:40.05603+03  | checkpointer
9704 | 2024-11-08 11:44:40.09488+03  | walwriter
(8 rows)

Обратите внимание на время старта процесса в поле backend_start.

Используя представление pg_stat_activity, получите один лишь столбец pid, и, передав каждую строку для обработки команде xargs сконструируйте командную строку команды ps - fp <PID процесса из pg_stat_activity>:
```
UID
postgres
postgres
postgres
postgres
postgres
postgres
postgres
postgres   12465    9675  0 15:22 ?
PID    PPID  C STIME TTY      STAT   TIME CMD
9700    9675  0 11:44 ?
9701    9675  0 11:44 ?
9704    9675  0 11:44 ?
9706    9675  0 11:44 ?
9707    9675  0 11:44 ?
9708    9675  0 11:44 ?
9709    9675  0 11:44 ?
Ss     0:00 postgres: checkpointer
Ss     0:00 postgres: background writer
Ss     0:00 postgres: walwriter
Ss     0:00 postgres: autovacuum launcher
Ss     0:00 postgres: autounite launcher
Ss     0:00 postgres: integrity check launcher
Ss     0:00 postgres: logical replication launcher
Ss     0:00 postgres: postgres postgres [local] idle
```
Обратите внимание на поле STIME в выводе ps -f - это время запуска процесса. Это время совпадает с данными в поле backend_start.

В этом примере использована метакоманда psql \g, которая позволяет записать строки результата выполненного запроса в файл или передать через конвейер на обработку команде ОС. Опции в круглых скобках отключают вывод заголовка tuples_only=on и отключают выравнивание format=unaligned. Команда xargs принимает из потока ввода строки, в которых единственный столбец - PID процесса, принадлежащего экземпляру PostgreSQL. Получив строку с PID, команда xargs подставляет этот PID команде ps -fp, которая выводит информацию об этом процессе в подробном формате.

Процессы и структуры в памяти

Получите значение параметра dynamic_shared_memory_type:

postgres=# \dconfig+ dynamic_shared_memory_type
List of configuration parameters
Parameter          | Value | Type |  Context   | Access privileges
----------------------------+-------+------+------------+-------------------
dynamic_shared_memory_type | posix | enum | postmaster |
(1 row)

Этот параметр устанавливает используемую экземпляром разновидность разделяемой памяти. Поддерживаются типы:
- posix
- sysv
- windows (только в соответствующей ОС)
- mmap
См. https://www.postgresql.org/docs/15/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

Проверьте это, обратившись к представлению pg_setting:
```
postgres@postgres=# select name, setting, enumvals from pg_settings where name = 'dynamic_shared_memory_type' \gx
-[ RECORD 1 ]------------------------
name | dynamic_shared_memory_type
setting  | posix
enumvals | {posix,sysv,mmap}
```
Определите объем выделенной памяти для буферов, в которые считываются страницы с диска:
```
postgres=# show shared_buffers; shared_buffers
----------------
128MB
(1 строка)
```
Определите с помощью представления pg_settings можно ли изменить значение этого параметра в экземпляре без его рестарта. Определите также из этого представления, в каких единицах измеряется этот параметр:
```
postgres=# select name, setting, unit, context from pg_settings where name ~ '^sh.*rs$'; name | setting | unit | context
----------------+---------+------+------------
shared_buffers | 16384   | 8kB  | postmaster
(1 строка) postgres=# \q
```
Контекст postmaster означает, что изменение этого параметра требует рестарт экземпляра. Столбец unit показывает, что размер буфера измеряется количеством 8Кб страниц, так как каждый буфер предназначен для размещения одной страницы, считанной с диска. По умолчанию таких буферов 16384, что дает в результате 128Мб. Это очень малое значение.

Влияние размера кеша буферов

Войдите в сеанс ролью postgres из сеанса пользователя ОС student:

[student@p620 ~]$ psql -U postgres -h localhost
Password for user postgres:
psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help.
postgres@postgres=#

Установите размер кеша буферов, равный 512Мб. После перезагрузки экземпляра проверьте результат:

postgres@postgres=# alter system set shared_buffers = '512MB'; ALTER SYSTEM
postgres@postgres=# \q
[student@p620 ~]$ sudo systemctl restart postgresql
[student@p620 ~]$ psql -U postgres -h localhost
Password for user postgres:
psql (15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help.
postgres@postgres=# show shared_buffers; shared_buffers
----------------
512MB
(1 row)

Проверьте, существует ли зарегистрированная роль student:
```
postgres@postgres=# \du student List of roles
Role name | Attributes | Member of
-----------+------------+-----------
student | | {}
```
Если такой роли нет, создайте ее командой:
```
CREATE USER student PASSWORD 'student';
```

Проверьте наличие БД student, принадлежащей роли student:

postgres@postgres=# \x \l+ student \x Expanded display is on.
List of databases
-[ RECORD 1 ]-----+-----------
Name
Owner
Encoding
Collate
Ctype
ICU Locale
Locale Provider
Access privileges |
Size
Tablespace
Description
| 10175 kB
| pg_default
|
| student
| student
| UTF8
| en_US.UTF8
| en_US.UTF8
|
| libc
Expanded display is off.

Если БД нет, то создайте ее командой:

CREATE DATABASE student OWNER student;

В сеансе student создайте в БД student таблицу для проверки кеширования:

postgres@postgres=# \c student student localhost
Password for user student:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "student" as user "student".
student@student=> create table rndtab as select g.i as id, random() as nm from generate_series(1,100000) as g(i);
SELECT 100000

Два раза получите план выполненного запроса:
```
student@student=> explain (analyze,buffers,costs off,timing off,summary off) select * from rndtab ; QUERY PLAN
-------------------------------------------------
Seq Scan on rndtab (actual rows=100000 loops=1)
Buffers: shared read=541
Planning:
Buffers: shared hit=15 read=8
(4 rows)
student@student=> explain (analyze,buffers,costs off,timing off,summary off) select * from rndtab ; QUERY PLAN
-------------------------------------------------
Seq Scan on rndtab (actual rows=100000 loops=1)
Buffers: shared hit=541
(2 rows)
```
Обратите внимание: первый раз в плане запроса было выведено shared read=541, что значит, что с диска были считаны 541 страница. Второй раз в плане такого же запроса было выведено shared hit=541, значит все необходимые страницы были на этот раз найдены в кеше буферов.
Проверьте, что в другой сессии план этого же запроса покажет, что страницы до сих пор находятся в кеше буферов в общей памяти. Закончить предыдущую сессию, а, следовательно, и стереть память обслуживающего процесса, можно метакомандой \c - она разрывает старую и открывает новую сессию:
```
student@student=> \c
You are now connected to database "student" as user "student".
student@student=> explain (analyze,buffers,costs off,timing off,summary off) select * from rndtab ; QUERY PLAN
-------------------------------------------------
Seq Scan on rndtab (actual rows=100000 loops=1)
Buffers: shared hit=541
Planning:
Buffers: shared hit=23
(4 rows)
```
Поскольку в запросе shared hit=541, закешированные страницы остались в кеше буферов.

Локальная память процессов

Перезагрузите экземпляр для очистки разделяемой памяти:
```
student@student=> \q
[student@p620 ~]$ sudo systemctl restart postgresql
```

Откройте новую сессию от имени student:

[student@p620 ~]$ psql psql (15.5)
Type "help" for help.
student@student=>

Подготовьте оператор для такого же запроса, как и в предыдущем случае:
```
student@student=> prepare preprndtab as select * from rndtab; PREPARE
```
Для подготовленного оператора оптимизатором производится разбор запроса, дерево разобранного запроса запоминается в локальной памяти обслуживающего процесса. Далее строится план выполнения запроса, который запоминается в локальной памяти обслуживающего процесса. Однако, происходит это не сразу. Проверьте наличие подготовленного оператора:
```
student@student=> select * from pg_prepared_statements \gx
-[ RECORD 1 ]---+--------------------------------------------
name
statement
prepare_time
parameter_types | {}
from_sql
generic_plans
custom_plans
| t
| 0
| 0
| preprndtab
| prepare preprndtab as select * from rndtab;
| 2024-11-08 19:20:06.647472+03
```
План еще не построен.
Получим план реального выполнения запроса:
```
student@student=> explain (analyze,buffers) execute preprndtab; QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------
Seq Scan on rndtab  (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.043..40.770
rows=100000 loops=1)
Buffers: shared read=541
Planning:
Buffers: shared hit=15 read=8
Planning Time: 0.592 ms
Execution Time: 50.811 ms
(6 rows)
```
Так как после рестарта сервера разделяемая память пуста и в кеше буферов еще нет страниц, то при выполнении запроса с диска были считаны 541 страница, как и без предварительной подготовки. Но это и ожидалось, так как в локальной памяти запоминается лишь план подготовленных операторов. Время, затраченное на планирование составило 0.592 ms.

Что изменилось в pg_prepared_statements?

student@student=> select * from pg_prepared_statements \gx
-[ RECORD 1 ]---------------+--------------------------------------------
name                        | preprndtab
statement                   | prepare preprndtab as select * from rndtab;
prepare_time                | 2024-11-08 19:24:58.201862+03
parameter_types             | {}
from_sql                    | t
generic_plans               | 1
custom_plans                | 0

План построен и запомнен в локальной памяти.

Выполните подготовленный оператор еще раз, получив план реального выполнения:

student@student=> explain (analyze,buffers) execute preprndtab; QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on rndtab  (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.018..16.384
rows=100000 loops=1)
Buffers: shared hit=541
Planning Time: 0.007 ms
Execution Time: 27.569 ms
(4 rows)

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

Также обратите внимание на то, что 541 страница была извлечена из кеша буферов, а он находится в разделяемой памяти.

Что изменилось в pg_prepared_statements теперь?
```
student@student=> select * from pg_prepared_statements \gx
-[ RECORD 1 ]---------------+--------------------------------------------
name                        | preprndtab
statement                   | prepare preprndtab as select * from rndtab;
prepare_time                | 2024-11-08 19:24:58.201862+03
parameter_types             | {}
from_sql                    | t
generic_plans               | 2
custom_plans                | 0
```
План, запомненный в локальной памяти был использован повторно. Поле generic_plans показывает сколько раз запрос был выполнен в соответствии с общим планом. Для подготовленных запросов без параметров есть только общие планы.

https://www.postgresql.org/docs/15/view-pg-prepared-statements.html
Проверим, останется ли в локальном кеше подготовленный оператор после рестарта сессии:
```
student@student=> \c
You are now connected to database "student" as user "student".
student@student=> select * from pg_prepared_statements \gx (0 rows)
```
Нет, не остался, так как при рестарте сессии старый обслуживающий процесс завершается и память его освобождается. Вместо него стартует новый обслуживающий процесс, но его локальная память исходно пуста.

Архитектура​

Процессы и структуры в памяти​

Влияние размера кеша буферов​

Локальная память процессов​

Архитектура

Процессы и структуры в памяти

Влияние размера кеша буферов

Локальная память процессов