Практикум
Очистка
-
Подключитесь ролью postgres к одноименной БД:
[postgres@p620 ~]$ psql psql (15.5)
Type "help" for help.
postgres=# -
Создайте БД
monman_dbи подключитесь к ней:postgres=# CREATE DATABASE monman_db; CREATE DATABASE
postgres=# \c monman_db
You are now connected to database "monman_db" as user "postgres". -
Создайте таблицу
fatab:monman_db=# CREATE TABLE fatab ( id serial PRIMARY KEY, dte date);
CREATE TABLE
monman_db=# \d+ fatab
Table "public.fatab"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+-----------------------------------+---------+-------------+--------------+-------------
id | integer | | not null | nextval('fatab_id_seq'::regclass) | plain | | |
dte | date | | | | plain | | |
Indexes:
"fatab_pkey" PRIMARY KEY, btree (id)
Access method: heapОба столбца таблицы используют метод хранения plain, то есть, отсутствует TOAST для чистоты эксперимента.
-
Запретите автоочистке обрабатывать таблицу
fatab:monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = off); ALTER TABLE -
Вставьте в таблицу сто тысяч строк:
monman_db=# INSERT INTO fatab(dte) SELECT current_date + g.i FROM generate_series(1,100000) AS g(i); INSERT 0 100000 -
Получите суммарный размер таблицы:
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
5776 kB
(1 row) -
Измените 50% строк таблицы и снова про верьте ее размер. Изменяйте строки с четными id:
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0; UPDATE 50000
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
9744 kB
(1 row)Размер таблицы увеличился, так как у 50% строк появились новые версии. Старые, уже ненужные версии все так же находятся на своих местах.
-
Теперь повторите то же самое для нечетных строк и снова измерьте размер таблицы:
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1; UPDATE 50000
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
10 MB
(1 row)Размер снова увеличился, хоть и не настолько, как в прошлый раз. Это связано с внутристраничной очисткой, но 50% строк все-равно имеют неактуальные версии, которые можно очистить.
-
Выполните очистку в режиме подробного информирования:
monman_db=# VACUUM VERBOSE fatab;
INFO: vacuuming "monman_db.public.fatab"
INFO: finished vacuuming "monman_db.public.fatab": index scans: 1
pages: 0 removed, 758 remain, 758 scanned (100.00% of total)
tuples: 50000 removed, 100000 remain, 0 are dead but not yet removable, oldest xmin: 838
removable cutoff: 838, which was 0 XIDs old when operation ended
new relfrozenxid: 836, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 443 pages from table (58.44% of total) had 71222 dead item identifiers removed
index "fatab_pkey": pages: 551 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.216 MB/s, avg write rate: 0.541 MB/s
buffer usage: 2519 hits, 2 misses, 5 dirtied
WAL usage: 2196 records, 5 full page images, 660831 bytes
system usage: CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.07 s
VACUUM
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
10 MB
(1 row)Команда очистки отработала, было очищено 50000 версий строк, а также был очищен индекс. Но размер таблицы остался без изменений. Так и должно быть: задача очистки состоит в освобождении места на страницах для вставки новых версий строк.
-
Несколько раз повторите эксперимент из пункта 1.8, но каждый раз выполняйте очистку. Затем измерьте размер таблицы, он должен перестать увеличиваться:
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1; VACUUM fatab; UPDATE 50000
VACUUM
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0; VACUUM fatab; UPDATE 50000
VACUUM
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1; VACUUM fatab; UPDATE 50000
VACUUM
monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0; VACUUM fatab; UPDATE 50000
VACUUM
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
10 MB
(1 row)
- Получите имя файла данных таблицы, перестройте ее командой
VACUUM FULL, проверьте получившийся размер и снова получите имя файла таблицы:
monman_db=# SELECT pg_relation_filepath('fatab'); pg_relation_filepath
----------------------
base/16441/16457
(1 row)
monman_db=# VACUUM FULL fatab ;
VACUUM
monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab')); pg_size_pretty
----------------
5752 kB
(1 row)
monman_db=# SELECT pg_relation_filepath('fatab');
pg_relation_filepath
----------------------
base/16441/16463
(1 row)
Размер таблицы уменьшился, поскольку все актуальные версии строк были физически скопированы в другой файл. Старый файл назывался 16457, новый - 16463:
monman_db=# \! ls -l $PGDATA/base/16441/{16463,16457}
-rw------- 1 postgres postgres 0 Nov 10 21:46 /pgdata/06/data/base/16441/16457
-rw------- 1 postgres postgres 3629056 Nov 10 21:46 /pgdata/06/data/base/16441/16463
Это размера файла данных, функция pg_total_relation_size() показывает общий размер таблицы со всеми зависимыми объектами, включая индексы.
Автоочистка
-
Включите автоочистку для таблицы
fatab:monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = on); ALTER TABLE -
Повторите
UPDATEтаблицыfatab, с проверкой ее размера:monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0 \; SELECT pg_size_pretty(pg_total_relation_size('fatab'));
UPDATE 50000
pg_size_pretty
----------------
9744 kB
(1 row)Эти две команды выполняются в одной транзакции за счет конструкции
\;разделяющей команды. -
Проверьте наличие выполненных команд в буфере
psql:monman_db=# \p
UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0 ; SELECT pg_size_pretty(pg_total_relation_size('fatab')); -
Запустите циклическое повторения этих команд через 10 секунд, используя
\watch 10. Остановите выполнение нажатиемCtrl+Cтогда, когда размер таблицы стабилизируется и не будет более увеличиватьс я:monman_db=# \watch 10
UPDATE 50000
Sun 10 Nov 2024 10:04:40 PM MSK (every 10s)
pg_size_pretty
----------------
9752 kB
(1 row)
...
UPDATE 50000
Sun 10 Nov 2024 10:07:20 PM MSK (every 10s)
pg_size_pretty
----------------
18 MB
(1 row)
UPDATE 50000
Sun 10 Nov 2024 10:07:30 PM MSK (every 10s)
pg_size_pretty
----------------
18 MB
(1 row)
Статистика планировщика
-
Добавьте в таблицу
fatabстолбец типа float:monman_db=# ALTER TABLE fatab ADD nme float; ALTER TABLE
monman_db=# \d fatab
Table "public.fatab"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('fatab_id_seq'::regclass)
dte | date | | |
nme | double precision | | |
Indexes:
"fatab_pkey" PRIMARY KEY, btree (id) -
Отключите автоочистку для таблицы
fatab:monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = off); ALTER TABLE -
Заполните добавленное поле во всех строках таблицы
fatabслучайными значениями, генерируемыми функциейrandom ( )- эти значения в диапазоне от 0 до 1:monman_db=# UPDATE fatab SET nme = random(); UPDATE 100000 -
Обновите приблизительно 1% строк в таблице, увеличив значение в столбце
nmeв 10 раз:monman_db=# UPDATE fatab SET nme = nme*10 WHERE random() < 0.01; UPDATE 1010 -
Добавьте индекс к таблице по полю
nme:monman_db=# UPDATE fatab SET nme = nme*10 WHERE random() < 0.01;
UPDATE 1010
monman_db=# CREATE INDEX ON fatab (nme);
CREATE INDEX
monman_db=# \d fatab
Table "public.fatab"
Column | Type | Collation | Nullable | Default
--------+------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('fatab_id_seq'::regclass)
dte | date | | |
nme | double precision | | |
Indexes:
"fatab_pkey" PRIMARY KEY, btree (id)
"fatab_nme_idx" btree (nme) -
Выгрузите таблицу в отсортированном виде во внешний файл и очистите ее. Затем загрузите ее снова:
monman_db=# COPY (SELECT * FROM fatab ORDER BY nme) TO '/tmp/fatab.sql'; COPY 100000
monman_db=# TRUNCATE fatab;
monman_db=# COPY fatab FROM '/tmp/fatab.sql'; COPY 100000 -
Получите план выполнения запроса, возвращающего строки, где значение поля
nme> 1:monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1; QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on fatab (cost=674.60..1632.62 rows=33362 width=16)
Recheck Cond: (nme > '1'::double precision)
-> Bitmap Index Scan on fatab_nme_idx (cost=0.00..666.26 rows=33362 width=0)
Index Cond: (nme > '1'::double precision)
(4 rows)В плане видно значительную ошибку оценки количества строк, которое будет возвращено запросом, а также выбранный метод сканирования по индексу с применением битовой карты, которая нужна в случае хаотичности данных в столбце, по которому осуществляется фильтрация. На самом деле все строки после загрузки упорядочены по этому столбцу и битовая карта не нужна:
-
Выполните анализ, собрав статистику планировщика по таблице, и снова получите тот же план запроса:
monman_db=# ANALYZE fatab;
ANALYZE
monman_db=# EXPLAIN SELECT * FROM fatab WHERE nme > 1;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using fatab_nme_idx on fatab (cost=0.29..38.79 rows=1000 width=16)
Index Cond: (nme > '1'::double precision)
(2 rows)Теперь оценка количества строк, которые вернет запрос, гораздо точнее и используется сканирование по индексу без битовой карты, т ак как данные в столбце nme упорядочены.
-
Включите автоочистку таблицы:
monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = on); ALTER TABLE
Мониторинг производительности
-
Включите сбор данных по нагрузке на подсистему ввода-вывода:
monman_db=# ALTER SYSTEM SET track_io_timing TO on; ALTER SYSTEM
monman_db=# SELECT pg_reload_conf(); pg_reload_conf
----------------
t
(1 row) -
Подготовьте таблицы для проведения нагрузочного тестирования утилитой
pgbench:monman_db=# \! pgbench -i monman_db
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 (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.18 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.38 s (drop tables 0.01 s, create tables 0.02 s, client-side generate 0.25 s, vacuum 0.03 s, primary keys 0.07 s).
monman_db=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | fatab | table | postgres
public | fatab_id_seq | sequence | postgres
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(6 rows) -
Сбросьте накопленную прежде кумулятивную статистику:
monman_db=# SELECT pg_stat_reset(); pg_stat_reset
---------------
(1 row)
monman_db=# SELECT pg_stat_reset_shared('bgwriter'); pg_stat_reset_shared
----------------------
(1 row) -
Запустите нагрузочное тестирование на тридцать секунд:
monman_db=# \! pgbench -T30 monman_db pgbench (15.5)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)> scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 9985
number of failed transactions: 0 (0.000%)
latency average = 3.003 ms
initial connection time = 21.177 ms
tps = 333.035253 (without initial connection time) -
Получите собранную статистику по строкам одной из таблиц:
monman_db=# SELECT * FROM pg_stat_all_tables
monman_db-# WHERE relid = 'pgbench_tellers'::regclass \gx
-[ RECORD 1 ]-----------+------------------------------
relid | 16485
schemaname | public
relname | pgbench_tellers
seq_scan | 9985
seq_tup_read | 99850
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 9985
n_tup_del | 0
n_tup_hot_upd | 9985
n_live_tup | 10
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum | 2024-11-11 09:05:49.584207+03
last_autovacuum | 2024-11-11 09:07:02.217142+03
last_analyze
last_autoanalyze | 2024-11-11 09:07:02.217678+03
vacuum_count | 1
autovacuum_count | 2
analyze_count | 0
autoanalyze_count | 2 -
Получите статистику по вводу выводу для этой же таблицы. Ввод-вывод осуществляется постранично:
monman_db=# SELECT * FROM pg_statio_all_tables WHERE relid = 'pgbench_tellers'::regclass \gx
-[ RECORD 1 ]-----+----------------
relid | 16485
schemaname | public
relname | pgbench_tellers
heap_blks_read | 0
heap_blks_hit | 29981
idx_blks_read | 1
idx_blks_hit | 3
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit | -
Получите статистику по всей БД:
monman_db=# SELECT * FROM pg_stat_database
WHERE datname = 'monman_db' \gx
-[ RECORD 1 ]-----------------+------------------------------
datid | 16441
datname | monman_db
numbackends | 1
xact_commit | 10091
xact_rollback | 2
blks_read | 373
blks_hit | 165602
tup_returned | 160090
tup_fetched | 22585
tup_inserted | 9991
tup_updated | 29976
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures | 0
checksum_last_failure |
blk_read_time | 6.067
blk_write_time | 0
session_time | 1178339.251
active_time | 22034.291
idle_in_transaction_time | 4911.254
sessions | 2
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | 2024-11-11 08:58:34.874476+03 -
Измените все строки таблицы
fatabи снова получите из кумулятивной статистики количество измененных строк в БД:monman_db=# UPDATE fatab SET nme = 0; UPDATE 100000
monman_db=# SELECT tup_updated FROM pg_stat_database WHERE datname = 'monman_db' ;
tup_updated
-------------
129979
(1 row)Прежнее значение количества измен енных строк было
29979.
Журнал отчета
-
Определите настройки параметра включения сборщика отчетов:
monman_db=# \dconfig+ logging*
List of configuration parameters
Parameter | Value | Type | Context | Access privileges
-------------------+-------+------+------------+-------------------
logging_collector | off | bool | postmaster |
(1 row)При изменении параметра потребуется перезапуск экземпляра.
-
Включите сборщик:
monman_db=# ALTER SYSTEM SET logging_collector TO on;
ALTER SYSTEM
monman_db=# ALTER SYSTEM SET log_directory = '/pgerrorlogs/06';
ALTER SYSTEM
monman_db=# \q
[postgres@p620 ~]$ exit
logout
[student@p620 ~]$ sudo systemctl restart postgresql
[student@p620 ~]$ ps f -C postgres
PID TTY STAT TIME COMMAND
61867 ? Ss 0:00 /usr/pangolin-6.2.0/bin/postgres -D /pgdata/06/data
61891 ? Ss 0:00 \_ postgres: logger
61892 ? Ss 0:00 \_ postgres: checkpointer
61893 ? Ss 0:00 \_ postgres: background writer
61895 ? Ss 0:00 \_ postgres: idle sessions terminator
61896 ? Ss 0:00 \_ postgres: walwriter
61897 ? Ss 0:00 \_ postgres: license checker
61898 ? Ss 0:00 \_ postgres: autovacuum launcher
61899 ? Ss 0:00 \_ postgres: autounite launcher
61900 ? Ss 0:00 \_ postgres: integrity check launcher
61901 ? Ss 0:00 \_ postgres: logical replication launcher -
Включите журналирование входов в сеанс:
[student@p620 ~]$ sudo -iu postgres
[postgres@p620 ~]$ psql
postgres=# ALTER SYSTEM SET log_connections TO on; ALTER SYSTEM
monman_db=# SELECT pg_reload_conf(); pg_reload_conf
----------------
t
(1 row) -
В соседнем терминале проверьте наличие журнала отчета и включите его мониторинг с помощью
tail -f. В режиме мониторинга командаtail -fвернет командную строку при ее остановкеCtrl+C:[student@TERM1 ~]$ sudo ls -ltrh /pgerrorlogs/06
-rw------- 1 postgres postgres 1.4K Nov 11 09:30 postgresql-2024-11-11_092534.log
[student@TERM1 ~]$ sudo tail -f /pgerrorlogs/06/postgresql-2024-11-11_092534.log -
Сделайте несколько успешных и неуспешных попыток войти в сеанс:
monman_db=# \c monman_db
You are now connected to database "monman_db" as user "postgres".
monman_db=# \c "dbname=student user=student password=student"
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for
user "student"
Previous connection kept
monman_db=# \c "dbname=student user=student password=student host=localhost"
You are now connected to database "student" as user "student" on host "localhost" (address "::1") at port "5432". -
Проверьте, что записалось в журнал отчета (см. пункт 5.4 для определения местоположения журнала отчета):
2024-11-11 09:40:46.843 MSK [62196] LOG: connection received: host=[local]
2024-11-11 09:40:46.847 MSK [62196] LOG: connection authenticated: identity="postgres" method=peer
(/pgdata/06/data/pg_hba.conf:89)
2024-11-11 09:40:46.847 MSK [62196] LOG: provided user name (student) and authenticated user name
(postgres) do not match
2024-11-11 09:40:46.847 MSK [62196] LOG: AUDIT: SESSION, CONNECTION, FAILED,database = student,user
= student
2024-11-11 09:40:46.847 MSK [62196] FATAL: Peer authentication failed for user "student"
2024-11-11 09:40:46.847 MSK [62196] DETAIL: Connection matched pg_hba.conf line 89: "local all
all peer"
2024-11-11 09:40:59.062 MSK [62200] LOG: connection received: host=::1 port=39996
2024-11-11 09:40:59.081 MSK [62200] LOG: connection authenticated: identity="student" method=scram-
sha-256 (/pgdata/06/data/pg_hba.conf:93)
2024-11-11 09:40:59.081 MSK [62200] LOG: connection authorized: user=student database=student
application_name=psql -
Сбросьте настройки в
postgresql.auto.conf. Остановите мониторинг:
postgres=# ALTER SYSTEM RESET ALL; ALTER SYSTEM
postgres=# SELECT pg_reload_conf(); pg_reload_conf
----------------
t
(1 row)