Уровень 3.0

Предусловие: пройдены предыдущие практические задания: «Клиентское подключение», «Работа в среде psql», «Конфигурация сервера». Предусловие: пройдены предыдущие практические задания: «Клиентское подключение», «Работа в среде psql», «Конфигурация сервера».

Практика. Архитектура

1. Подключитесь к базе данных под пользователем postgres.

2. Подключитесь к базе данных под пользователем postgres.

   [student@ServerName ~]$ sudo su - postgres
   [student@ServerName ~]$ sudo su - postgres

   [postgres@ServerName ~]$ psql
   [postgres@ServerName ~]$ psql

   psql (15.5)
   Type "help" for help.

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

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

   postgres@postgres=# SELECT pid, backend_start, backend_type FROM pg_stat_activity;

   postgres@postgres=# SELECT pid, backend_start, backend_type FROM pg_stat_activity;

     pid  |         backend_start         |         backend_type
   -------+-------------------------------+------------------------------
    32292 | 2025-05-19 20:23:50.133245+03 | autounite launcher
    32295 | 2025-05-19 20:23:50.134435+03 | logical replication launcher
    32293 | 2025-05-19 20:23:50.134921+03 | integrity check launcher
    32291 | 2025-05-19 20:23:50.137111+03 | autovacuum launcher
    33049 | 2025-05-19 21:20:42.79377+03  | client backend
    32287 | 2025-05-19 20:23:50.128779+03 | background writer
    32286 | 2025-05-19 20:23:50.126141+03 | checkpointer
    32290 | 2025-05-19 20:23:50.1327+03   | walwriter
   (8 rows)

   Обратите внимание на время старта процесса в поле backend_start, которое показывает время запуска каждого процесса. Фоновые процессы (checkpointer, walwriter и другие) запускаются при старте экземпляра, а клиентский процесс (client backend) появляется только при подключении клиента.

3. Получите только PID процессов из того же представления и передайте их команде ОС ps -fp для отображения подробной информации: Обратите внимание на время старта процесса в поле backend_start, которое показывает время запуска каждого процесса. Фоновые процессы (checkpointer, walwriter и другие) запускаются при старте экземпляра, а клиентский процесс (client backend) появляется только при подключении клиента.

4. Получите только PID процессов из того же представления и передайте их команде ОС ps -fp для отображения подробной информации:

   postgres@postgres=# SELECT pid FROM pg_stat_activity \g (tuples_only=on) | xargs ps -fp

   postgres@postgres=# SELECT pid FROM pg_stat_activity \g (tuples_only=on) | xargs ps -fp

     UID          PID    PPID  C STIME TTY      STAT   TIME CMD
     postgres   32286   32282  0 20:23 ?        Ss     0:00 postgres: checkpointer
     postgres   32287   32282  0 20:23 ?        Ss     0:00 postgres: background writer
     postgres   32290   32282  0 20:23 ?        Ss     0:00 postgres: walwriter
     postgres   32291   32282  0 20:23 ?        Ss     0:00 postgres: autovacuum launcher
     postgres   32292   32282  0 20:23 ?        Ss     0:00 postgres: autounite launcher
     postgres   32293   32282  0 20:23 ?        Ss     0:00 postgres: integrity check launcher
     postgres   32295   32282  0 20:23 ?        Ss     0:00 postgres: logical replication launcher
     postgres   33049   32282  0 21:20 ?        Ss     0:00 postgres: postgres postgres [local] idle

     UID          PID    PPID  C STIME TTY      STAT   TIME CMD
     postgres   32286   32282  0 20:23 ?        Ss     0:00 postgres: checkpointer
     postgres   32287   32282  0 20:23 ?        Ss     0:00 postgres: background writer
     postgres   32290   32282  0 20:23 ?        Ss     0:00 postgres: walwriter
     postgres   32291   32282  0 20:23 ?        Ss     0:00 postgres: autovacuum launcher
     postgres   32292   32282  0 20:23 ?        Ss     0:00 postgres: autounite launcher
     postgres   32293   32282  0 20:23 ?        Ss     0:00 postgres: integrity check launcher
     postgres   32295   32282  0 20:23 ?        Ss     0:00 postgres: logical replication launcher
     postgres   33049   32282  0 21:20 ?        Ss     0:00 postgres: postgres postgres [local] idle

   Разбор команды:

   • \g – метакоманда, которая позволяет записать строки результата выполненного запроса в файл или передать через конвейер на обработку команде ОС;
   • (tuples_only=on) – отключает вывод заголовков и служебных строк;
   • xargs принимает из потока ввода PID процессов. Получив строку с PID, команда xargs подставляет этот PID команде ps -fp;
   • ps -fp – выводит информацию о процессе в подробном формате.

   Обратите внимание на поле STIME в выводе ps -f — это время запуска процесса, которое совпадает с данными в поле backend_start из pg_stat_activity.

   Разбор команды:

   • \g – метакоманда, которая позволяет записать строки результата выполненного запроса в файл или передать через конвейер на обработку команде ОС;
   • (tuples_only=on) – отключает вывод заголовков и служебных строк;
   • xargs принимает из потока ввода PID процессов. Получив строку с PID, команда xargs подставляет этот PID команде ps -fp;
   • ps -fp – выводит информацию о процессе в подробном формате.

   Обратите внимание на поле STIME в выводе ps -f — это время запуска процесса, которое совпадает с данными в поле backend_start из pg_stat_activity.

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

1. Посмотрим, какой тип разделяемой памяти использует экземпляр. Для этого выведем значение параметра dynamic_shared_memory_type:

   postgres@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)

   Этот параметр определяет механизм работы с разделяемой памятью.

2. Чтобы узнать все возможные значения этого параметра, обратитесь к представлению pg_settings, где в поле enumvals перечислены допустимые варианты:

   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}

   Поддерживаются следующие типы:

   • posix;
   • sysv;
   • windows (только в соответствующей ОС);
   • mmap.

3. shared_buffers – это область в разделяемой памяти, куда считываются страницы данных с диска. Определите текущий объем выделенной памяти для буферов: Поддерживаются следующие типы:

   • posix;
   • sysv;
   • windows (только в соответствующей ОС);
   • mmap.

4. shared_buffers – это область в разделяемой памяти, куда считываются страницы данных с диска. Определите текущий объем выделенной памяти для буферов:

   postgres@postgres=# SHOW shared_buffers;

   shared_buffers
   ----------------
   128MB
   (1 row)

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

   postgres@postgres=# SELECT name, setting, unit, context FROM pg_settings WHERE name ~ '^sh.*rs$';

         name      | setting | unit |  context
   ----------------+---------+------+------------
    shared_buffers | 16384   | 8kB  | postmaster
   (1 row)

   Контекст postmaster означает, что изменение этого параметра требует рестарта экземпляра. Столбец unit показывает, что размер буфера измеряется количеством 8 Кб страниц, так как каждый буфер предназначен для размещения одной страницы, считанной с диска. По умолчанию таких буферов 16384, что дает в результате 128 Мб. Это очень малое значение.

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

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

   postgres@postgres=# ALTER SYSTEM SET shared_buffers = '512MB';

   ALTER SYSTEM

   postgres@postgres=# \q

   [postgres@ServerName ~] exit

   [student@ServerName ~]$ sudo systemctl restart postgresql
   postgres@postgres=# \q

   [postgres@ServerName ~] exit

   [student@ServerName ~]$ sudo systemctl restart postgresql

   [student@ServerName ~] sudo su - postgres

   [postgres@ServerName ~]$ psql -U postgres -h localhost

   psql (15.5)
   Type "help" for help.

   postgres@postgres=# SHOW shared_buffers;

   shared_buffers
   ----------------
   512MB
   (1 row)

   В данном случае перезапуск выполнен командой systemctl restart, поскольку для экземпляра СУБД Pangolin ранее был создан и запущен сервис systemd.

   Примечание

   Если экземпляр СУБД Pangolin был запущен командой pg_ctl start, перезапуск выполнялся бы от имени пользователя postgres командой pg_ctl restart.

2. Проверьте, существует ли зарегистрированная роль student:

   postgres@postgres=# \du student

            List of roles
    Role name | Attributes | Member of
   -----------+------------+-----------
    student   |            | {}

   Если такой роли нет, создайте ее командой

   CREATE USER student PASSWORD 'student';

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

   postgres@postgres=# \x \l+ student \x

   Expanded display is on.
   List of databases
   -[ RECORD 1 ]-----+------------
   Name              | student
   Owner             | student
   Encoding          | UTF8
   Collate           | en_US.UTF-8
   Ctype             | en_US.UTF-8
   ICU Locale        |
   Locale Provider   | libc
   Access privileges |
   Size              | 9462 kB
   Tablespace        | pg_default
   Description       |
   
   Expanded display is off.

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

   CREATE DATABASE student OWNER student;

4. Переключитесь в сеанс student и создайте в БД student таблицу для проверки кеширования:

   postgres@postgres=# \c student student localhost

   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

5. Получите план выполненного запроса:

   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=21
   (4 rows)

   Обратите внимание на строку Buffers: shared read=541 – это означает, что 541 считана с диска.

   Выполним тот же запрос еще раз:

   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 hit=541 – все страницы были найдены в кеше буферов, чтения с диска не потребовалось.

6. Проверьте, останутся ли страницы в кеше после переподключения (в другой сессии). Метакоманда \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)

   Видно, что страницы остались в кеше буферов (разделяемая память), так как он не очищается при завершении сессии.

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

1. Перезагрузите экземпляр для очистки разделяемой памяти:

   student@student=> \q

   [postgres@ServerName ~] exit

   [student@ServerName ~]$ sudo systemctl restart postgresql

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

   [student@ServerName ~] sudo su - postgres

   [student@ServerName ~] sudo su - postgres

   [postgres@ServerName ~]$ psql student student

   psql (15.5)
   Type "help" for help.

3. Подготовьте оператор для такого же запроса, как и в предыдущем случае (SELECT * FROM rndtab):

   student@student=> PREPARE preprndtab AS SELECT * FROM rndtab;

   PREPARE

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

   student@student=> SELECT * FROM pg_prepared_statements \gx

   -[ RECORD 1 ]---+--------------------------------------------
   name            | preprndtab
   statement       | prepare preprndtab as select * from rndtab;
   prepare_time    | 2025-05-19 21:52:11.776894+03
   parameter_types | {}
   from_sql        | t
   generic_plans   | 0
   custom_plans    | 0

   Поле generic_plans = 0 означает, что план еще не построен.

5. Выполните подготовленный оператор с получением плана и информации о буферах:

   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.030..13.662 rows=100000 loops=1)
      Buffers: shared read=541
    Planning:
      Buffers: shared hit=15 read=8
    Planning Time: 0.693 ms
    Execution Time: 17.909 ms
   (6 rows)

   Так как после рестарта сервера разделяемая память пуста и в кеше буферов еще нет страниц, то при выполнении запроса с диска были считаны 541 страница (shared read=541), как и без предварительной подготовки. Но это и ожидалось, так как в локальной памяти запоминается лишь план подготовленных операторов. Время, затраченное на планирование, составило 0.693 ms.

6. Теперь план построен и сохранен в локальной памяти процесса. Проверьте pg_prepared_statements:

   student@student=> SELECT * FROM pg_prepared_statements \gx

   -[ RECORD 1 ]---+--------------------------------------------
   name            | preprndtab
   statement       | prepare preprndtab as select * from rndtab;
   prepare_time    | 2025-05-19 21:52:11.776894+03
   parameter_types | {}
   from_sql        | t
   generic_plans   | 1
   custom_plans    | 0

   generic_plans = 1 – план построен и запомнен в локальной памяти.

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

   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.051..8.323 rows=100000 loops=1)
       Buffers: shared hit=541
    Planning Time: 0.013 ms
    Execution Time: 12.450 ms
   (4 rows)

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

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

8. Что изменилось в 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.

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

   Поле generic_plans показывает сколько раз запрос был выполнен в соответствии с общим планом. Для подготовленных запросов без параметров есть только общие планы.

10. Проверьте, останется ли в локальном кеше подготовленный оператор после рестарта сессии:

    student@student=> \c

    You are now connected to database "student" as user "student".

    student@student=> SELECT * FROM pg_prepared_statements \gx

    (0 rows)

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

11. Отключитесь от psql:

     student@student=> \q

Самопроверка

Вопрос 1

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

Seq Scan on test_table (actual rows=100000 loops=1)
Buffers: shared hit=229 read=441
Planning:
Buffers: shared hit=60 read=9
Planning Time: 5.220 ms
Execution Time: 11.434 ms

Сколько страниц данных понадобилось обслуживающему процессу для выполнения запроса в соответствии с указанным планом?

Вопрос 2

Каким образом может быть увеличен размер буферного кеша до 1 GB? Рассмотрите следующие варианты:

• Вариант 1. Путем последовательного выполнения следующих команд:
  • в psql: ALTER SYSTEM SET shared_buffers='1GB';
  • в psql: SELECT pg_reload_conf();
• Вариант 2. Путем последовательного выполнения следующих команд:
  • в psql: ALTER SYSTEM SET shared_buffers='1024MB';
  • в shell: sudo systemctl restart postgresql
• Вариант 3. Путем последовательного выполнения следующих команд:
  • в psql: ALTER SYSTEM SET shared_buffers='512MB';
  • в psql: ALTER SYSTEM SET temp_buffers='512MB';
  • в shell: sudo systemctl restart postgresql
• Вариант 4. Путем последовательного выполнения следующих команд:
  • в psql: ALTER SYSTEM SET shared_buffers=131072;
  • в shell: sudo systemctl restart postgresql

Выберите верные варианты.

Вариант 1

Вариант 2

Вариант 3

Вариант 4

Вопрос 3

Открыто два сеанса psql. Первый сеанс обслуживается процессом с pid=13646, второй — процессом с pid=13917. У каждого из сеансов в начале приглашения указывается pid обслуживающего процесса. В указанных сеансах последовательно выполняются следующие команды:

13646 test_db=# PREPARE pre_statement AS SELECT * FROM test_table;
13646 test_db=# EXECUTE pre_statement;
13917 test_db=# PREPARE pre_statement AS SELECT * FROM test_table;

Какие этапы обработки запроса будут выполнены последней командой?

Разбор

Переписывание

Планирование

Выполнение

Никакие из перечисленных

Вопрос 4

Какие из перечисленных процессов могут записывать «грязные» страницы в файловую систему?

checkpointer

background writer

walwriter

autovacuum launcher

logical replication launcher

client backend