Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit36ab831

Browse files
committed
Fix pg_stat_io buffer reuse test instability
The stats regression test attempts to ensure that Buffer Access Strategy"reuses" are being counted in pg_stat_io by vacuuming a table which is largerthan the size of the strategy ring. However, when shared buffers are insufficiently high demand, another backend could evict one of the blocks in thestrategy ring before the first backend has a chance to reuse the buffer. Thebackend using the strategy would then evict another shared buffer and add thatbuffer to the strategy ring. This counts as an eviction and not a reuse inpg_stat_io. Count both evictions and reuses in the test to ensure it does notfail incorrectly.Reported-by: Jeff Davis <pgsql@j-davis.com>,Author: Melanie Plageman <melanieplageman@gmail.com>Reviewed-by: Alexander Lakhin <exclusion@gmail.com>Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>Discussion:https://postgr.es/m/CAAKRu_bNG27AxG9TdPtwsL6wg8AWbVckjmTL2t1HF=miDQuNtw@mail.gmail.com
1 parent6050b6a commit36ab831

File tree

2 files changed

+32
-17
lines changed

2 files changed

+32
-17
lines changed

‎src/test/regress/expected/stats.out

Lines changed: 20 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1385,37 +1385,47 @@ SELECT :io_sum_local_new_tblspc_writes > :io_sum_local_after_writes;
13851385

13861386
RESET temp_buffers;
13871387
-- Test that reuse of strategy buffers and reads of blocks into these reused
1388-
-- buffers while VACUUMing are tracked in pg_stat_io.
1388+
-- buffers while VACUUMing are tracked in pg_stat_io. If there is sufficient
1389+
-- demand for shared buffers from concurrent queries, some buffers may be
1390+
-- pinned by other backends before they can be reused. In such cases, the
1391+
-- backend will evict a buffer from outside the ring and add it to the
1392+
-- ring. This is considered an eviction and not a reuse.
13891393
-- Set wal_skip_threshold smaller than the expected size of
13901394
-- test_io_vac_strategy so that, even if wal_level is minimal, VACUUM FULL will
13911395
-- fsync the newly rewritten test_io_vac_strategy instead of writing it to WAL.
13921396
-- Writing it to WAL will result in the newly written relation pages being in
13931397
-- shared buffers -- preventing us from testing BAS_VACUUM BufferAccessStrategy
13941398
-- reads.
13951399
SET wal_skip_threshold = '1 kB';
1396-
SELECT sum(reuses) AS reuses, sum(reads) AS reads
1400+
SELECT sum(reuses) AS reuses, sum(reads) AS reads, sum(evictions) AS evictions
13971401
FROM pg_stat_io WHERE context = 'vacuum' \gset io_sum_vac_strategy_before_
13981402
CREATE TABLE test_io_vac_strategy(a int, b int) WITH (autovacuum_enabled = 'false');
13991403
INSERT INTO test_io_vac_strategy SELECT i, i from generate_series(1, 4500)i;
14001404
-- Ensure that the next VACUUM will need to perform IO by rewriting the table
14011405
-- first with VACUUM (FULL).
14021406
VACUUM (FULL) test_io_vac_strategy;
1403-
-- Use the minimum BUFFER_USAGE_LIMIT to cause reuseswith the smallest table
1404-
-- possible.
1407+
-- Use the minimum BUFFER_USAGE_LIMIT to cause reusesor evictions with the
1408+
--smallest tablepossible.
14051409
VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) test_io_vac_strategy;
14061410
SELECT pg_stat_force_next_flush();
14071411
pg_stat_force_next_flush
14081412
--------------------------
14091413

14101414
(1 row)
14111415

1412-
SELECT sum(reuses) AS reuses, sum(reads) AS reads
1416+
SELECT sum(reuses) AS reuses, sum(reads) AS reads, sum(evictions) AS evictions
14131417
FROM pg_stat_io WHERE context = 'vacuum' \gset io_sum_vac_strategy_after_
1414-
SELECT :io_sum_vac_strategy_after_reads > :io_sum_vac_strategy_before_reads,
1415-
:io_sum_vac_strategy_after_reuses > :io_sum_vac_strategy_before_reuses;
1416-
?column? | ?column?
1417-
----------+----------
1418-
t | t
1418+
SELECT :io_sum_vac_strategy_after_reads > :io_sum_vac_strategy_before_reads;
1419+
?column?
1420+
----------
1421+
t
1422+
(1 row)
1423+
1424+
SELECT (:io_sum_vac_strategy_after_reuses + :io_sum_vac_strategy_after_evictions) >
1425+
(:io_sum_vac_strategy_before_reuses + :io_sum_vac_strategy_before_evictions);
1426+
?column?
1427+
----------
1428+
t
14191429
(1 row)
14201430

14211431
RESET wal_skip_threshold;

‎src/test/regress/sql/stats.sql

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -678,7 +678,11 @@ SELECT :io_sum_local_new_tblspc_writes > :io_sum_local_after_writes;
678678
RESET temp_buffers;
679679

680680
-- Test that reuse of strategy buffers and reads of blocks into these reused
681-
-- buffers while VACUUMing are tracked in pg_stat_io.
681+
-- buffers while VACUUMing are tracked in pg_stat_io. If there is sufficient
682+
-- demand for shared buffers from concurrent queries, some buffers may be
683+
-- pinned by other backends before they can be reused. In such cases, the
684+
-- backend will evict a buffer from outside the ring and add it to the
685+
-- ring. This is considered an eviction and not a reuse.
682686

683687
-- Set wal_skip_threshold smaller than the expected size of
684688
-- test_io_vac_strategy so that, even if wal_level is minimal, VACUUM FULL will
@@ -687,21 +691,22 @@ RESET temp_buffers;
687691
-- shared buffers -- preventing us from testing BAS_VACUUM BufferAccessStrategy
688692
-- reads.
689693
SET wal_skip_threshold='1 kB';
690-
SELECTsum(reuses)AS reuses,sum(reads)AS reads
694+
SELECTsum(reuses)AS reuses,sum(reads)AS reads,sum(evictions)AS evictions
691695
FROM pg_stat_ioWHERE context='vacuum' \gset io_sum_vac_strategy_before_
692696
CREATETABLEtest_io_vac_strategy(aint, bint) WITH (autovacuum_enabled='false');
693697
INSERT INTO test_io_vac_strategySELECT i, ifrom generate_series(1,4500)i;
694698
-- Ensure that the next VACUUM will need to perform IO by rewriting the table
695699
-- first with VACUUM (FULL).
696700
VACUUM (FULL) test_io_vac_strategy;
697-
-- Use the minimum BUFFER_USAGE_LIMIT to cause reuseswith the smallest table
698-
-- possible.
701+
-- Use the minimum BUFFER_USAGE_LIMIT to cause reusesor evictions with the
702+
--smallest tablepossible.
699703
VACUUM (PARALLEL0, BUFFER_USAGE_LIMIT128) test_io_vac_strategy;
700704
SELECT pg_stat_force_next_flush();
701-
SELECTsum(reuses)AS reuses,sum(reads)AS reads
705+
SELECTsum(reuses)AS reuses,sum(reads)AS reads,sum(evictions)AS evictions
702706
FROM pg_stat_ioWHERE context='vacuum' \gset io_sum_vac_strategy_after_
703-
SELECT :io_sum_vac_strategy_after_reads> :io_sum_vac_strategy_before_reads,
704-
:io_sum_vac_strategy_after_reuses> :io_sum_vac_strategy_before_reuses;
707+
SELECT :io_sum_vac_strategy_after_reads> :io_sum_vac_strategy_before_reads;
708+
SELECT (:io_sum_vac_strategy_after_reuses+ :io_sum_vac_strategy_after_evictions)>
709+
(:io_sum_vac_strategy_before_reuses+ :io_sum_vac_strategy_before_evictions);
705710
RESET wal_skip_threshold;
706711

707712
-- Test that extends done by a CTAS, which uses a BAS_BULKWRITE

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp