Устанавливаем необходимые расширения.
=> ALTER SYSTEM SET shared_preload_libraries = 'pg_wait_sampling';
ALTER SYSTEM
=> \q
postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data restart
waiting for server to shut down.... done server stopped waiting for server to start.... done server started
student$ psql
=> CREATE DATABASE locks_memory;
CREATE DATABASE
=> \c locks_memory
You are now connected to database "locks_memory" as user "postgres".
=> CREATE EXTENSION pg_wait_sampling;
CREATE EXTENSION
=> CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
Таблица, как в предыдущих практиках.
=> CREATE TABLE accounts(acc_no integer, amount numeric);
CREATE TABLE
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
INSERT 0 3
Начинаем транзакцию, открываем курсор и выбираем одну строку.
=> BEGIN;
BEGIN
=> DECLARE c CURSOR FOR SELECT * FROM accounts;
DECLARE CURSOR
=> FETCH c;
acc_no | amount
--------+---------
1 | 1000.00
(1 row)
Проверим, закреплен ли буфер:
=> SELECT * FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('accounts') AND relforknumber = 0 \gx
-[ RECORD 1 ]----+------ bufferid | 324 relfilenode | 25288 reltablespace | 1663 reldatabase | 25264 relforknumber | 0 relblocknumber | 0 isdirty | t usagecount | 4 pinning_backends | 1
Да, pinning_backends = 1.
Выполним очистку:
student$ psql locks_memory
=> VACUUM VERBOSE accounts;
INFO: vacuuming "public.accounts" INFO: "accounts": found 0 removable, 0 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 215351 There were 0 unused item pointers. Skipped 1 page due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: vacuuming "pg_toast.pg_toast_25288" INFO: index "pg_toast_25288_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pg_toast_25288": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 215351 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM
Как мы видим, страница была пропущена: Skipped 1 page due to buffer pins.
Очистка не может обработать страницу: если буфер закреплен, из страницы запрещено удалять версии строк. Но очистка не ждет, пока буфер освободится - страница будет обработана при следующей очистке.
Выполняем очистку с заморозкой:
=> VACUUM FREEZE VERBOSE accounts;
Очистка зависает до закрытия курсора. При явно запрошенной заморозке нельзя пропустить ни одну страницу, не отмеченную в карте заморозки - иначе невозможно уменьшить максимальный возраст незамороженных транзакций в pg_class.relfrozenxid.
=> SELECT age(relfrozenxid) FROM pg_class WHERE oid = 'accounts'::regclass;
age ----- 2 (1 row)
=> COMMIT;
COMMIT
INFO: vacuuming "public.accounts" INFO: "accounts": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 215351 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 5.07 s. INFO: vacuuming "pg_toast.pg_toast_25288" INFO: index "pg_toast_25288_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pg_toast_25288": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 215351 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM
=> SELECT age(relfrozenxid) FROM pg_class WHERE oid = 'accounts'::regclass;
age ----- 0 (1 row)
Профиль ожиданий:
=> SELECT p.pid, a.backend_type, a.application_name AS app, p.event_type, p.event, p.count FROM pg_wait_sampling_profile p LEFT JOIN pg_stat_activity a ON p.pid = a.pid ORDER BY p.pid, p.count DESC;
pid | backend_type | app | event_type | event | count -------+---------------------+------+------------+-----------------------+------- 30476 | checkpointer | | Activity | CheckpointerMain | 601 30476 | checkpointer | | IO | DataFileSync | 1 30476 | checkpointer | | IO | ControlFileSyncUpdate | 1 30477 | background writer | | Activity | BgWriterMain | 604 30478 | walwriter | | Activity | WalWriterMain | 604 30479 | autovacuum launcher | | Activity | AutoVacuumMain | 602 30482 | background worker | | Activity | LogicalLauncherMain | 604 30504 | | | Client | ClientRead | 4 30504 | | | IO | CopyFileWrite | 2 30528 | client backend | psql | Client | ClientRead | 557 30528 | client backend | psql | IO | DataFileRead | 1 30738 | client backend | psql | BufferPin | BufferPin | 493 30738 | client backend | psql | Client | ClientRead | 31 (13 rows)
Тип ожидания BufferPin говорит о том, что очистка ждала освобождения буфера.