public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v6 5/7] Invalidate parent index cluster on attach
5+ messages / 5 participants
[nested] [flat]
* [PATCH v6 5/7] Invalidate parent index cluster on attach
@ 2020-11-06 01:11 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Justin Pryzby @ 2020-11-06 01:11 UTC (permalink / raw)
---
src/backend/commands/indexcmds.c | 21 +++++++++++++++++++++
src/test/regress/expected/cluster.out | 14 ++++++++++++++
src/test/regress/sql/cluster.sql | 5 +++++
3 files changed, 40 insertions(+)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 0a65698c28..ffc809822b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -4006,6 +4006,27 @@ IndexSetParentIndex(Relation partitionIdx, Oid parentOid)
/* set relispartition correctly on the partition */
update_relispartition(partRelid, OidIsValid(parentOid));
+ /*
+ * If the attached index is not clustered, invalidate cluster mark on
+ * any parents
+ */
+ if ((OidIsValid(parentOid) && get_index_isclustered(parentOid)) ||
+ get_index_isclustered(partRelid))
+ {
+ Relation indrel;
+
+ /* Make relispartition visible */
+ CommandCounterIncrement();
+
+ indrel = table_open(IndexGetRelation(partRelid, false),
+ ShareUpdateExclusiveLock);
+ mark_index_clustered(indrel,
+ get_index_isclustered(partRelid) ? partRelid : InvalidOid,
+ true);
+ table_close(indrel, ShareUpdateExclusiveLock);
+
+ }
+
if (fix_dependencies)
{
/*
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 6d88978387..f0c962db75 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -567,6 +567,20 @@ Indexes:
"clstrpart_idx" btree (a)
Number of partitions: 3 (Use \d+ to list them.)
+-- Check that attaching an unclustered index marks the parent unclustered:
+ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
+CREATE TABLE clstrpart5 (LIKE clstrpart INCLUDING INDEXES);
+ALTER TABLE clstrpart ATTACH PARTITION clstrpart5 FOR VALUES FROM (40)TO(50);
+\d clstrpart
+ Partitioned table "public.clstrpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: RANGE (a)
+Indexes:
+ "clstrpart_idx" btree (a)
+Number of partitions: 4 (Use \d+ to list them.)
+
-- Test CLUSTER with external tuplesorting
create table clstr_4 as select * from tenk1;
create index cluster_sort on clstr_4 (hundred, thousand, tenthous);
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index c9bb204a93..ff7ffed6e4 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -239,6 +239,11 @@ CLUSTER clstrpart1 USING clstrpart1_idx_2;
ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
ALTER TABLE clstrpart1 SET WITHOUT CLUSTER;
\d clstrpart
+-- Check that attaching an unclustered index marks the parent unclustered:
+ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;
+CREATE TABLE clstrpart5 (LIKE clstrpart INCLUDING INDEXES);
+ALTER TABLE clstrpart ATTACH PARTITION clstrpart5 FOR VALUES FROM (40)TO(50);
+\d clstrpart
-- Test CLUSTER with external tuplesorting
--
2.17.0
--FsscpQKzF/jJk6ya
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v6-0006-Preserve-indisclustered-on-children-of-clustered-.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Identify transactions causing highest wal generation
@ 2024-03-08 14:50 Gayatri Singh <[email protected]>
2024-03-08 15:40 ` Re: Identify transactions causing highest wal generation Tomas Vondra <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Gayatri Singh @ 2024-03-08 14:50 UTC (permalink / raw)
To: [email protected]
Hello Team,
Can you help me with steps to identify transactions which caused wal
generation to surge ?
Regards,
Gayatri.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Identify transactions causing highest wal generation
2024-03-08 14:50 Identify transactions causing highest wal generation Gayatri Singh <[email protected]>
@ 2024-03-08 15:40 ` Tomas Vondra <[email protected]>
2024-03-08 16:32 ` Re: Identify transactions causing highest wal generation Euler Taveira <[email protected]>
2024-03-08 16:38 ` Re: Identify transactions causing highest wal generation Bharath Rupireddy <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Tomas Vondra @ 2024-03-08 15:40 UTC (permalink / raw)
To: Gayatri Singh <[email protected]>; [email protected]
On 3/8/24 15:50, Gayatri Singh wrote:
> Hello Team,
>
> Can you help me with steps to identify transactions which caused wal
> generation to surge ?
>
You should probably take a look at pg_waldump, which prints information
about WAL contents, including which XID generated each record.
I don't know what exactly is your goal, but sometimes it's not entirely
direct relationship. For example, a transaction may delete a record,
which generates just a little bit of WAL. But then after a checkpoint a
VACUUM comes around, vacuums the page to reclaim the space of the entry,
and ends up writing FPI (which is much larger). You could argue this WAL
is also attributable to the original transaction, but that's not what
pg_waldump will allow you to do. FPIs in general may inflate the numbers
unpredictably, and it's not something the original transaction can
affect very much.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Identify transactions causing highest wal generation
2024-03-08 14:50 Identify transactions causing highest wal generation Gayatri Singh <[email protected]>
2024-03-08 15:40 ` Re: Identify transactions causing highest wal generation Tomas Vondra <[email protected]>
@ 2024-03-08 16:32 ` Euler Taveira <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Euler Taveira @ 2024-03-08 16:32 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; Gayatri Singh <[email protected]>; [email protected]
On Fri, Mar 8, 2024, at 12:40 PM, Tomas Vondra wrote:
> On 3/8/24 15:50, Gayatri Singh wrote:
> > Hello Team,
> >
> > Can you help me with steps to identify transactions which caused wal
> > generation to surge ?
> >
>
> You should probably take a look at pg_waldump, which prints information
> about WAL contents, including which XID generated each record.
You can also use pg_stat_statements to obtain this information.
postgres=# select * from pg_stat_statements order by wal_bytes desc;
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
userid | 10
dbid | 16385
toplevel | t
queryid | -8403979585082616547
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 238260
total_exec_time | 4642.599296000018
min_exec_time | 0.011094999999999999
max_exec_time | 0.872748
mean_exec_time | 0.01948543312347807
stddev_exec_time | 0.006370786385582063
rows | 238260
.
.
.
wal_records | 496659
wal_fpi | 19417
wal_bytes | 208501147
.
.
.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Identify transactions causing highest wal generation
2024-03-08 14:50 Identify transactions causing highest wal generation Gayatri Singh <[email protected]>
2024-03-08 15:40 ` Re: Identify transactions causing highest wal generation Tomas Vondra <[email protected]>
@ 2024-03-08 16:38 ` Bharath Rupireddy <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Bharath Rupireddy @ 2024-03-08 16:38 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Gayatri Singh <[email protected]>; [email protected]
On Fri, Mar 8, 2024 at 9:10 PM Tomas Vondra
<[email protected]> wrote:
>
> On 3/8/24 15:50, Gayatri Singh wrote:
> > Hello Team,
> >
> > Can you help me with steps to identify transactions which caused wal
> > generation to surge ?
> >
>
> You should probably take a look at pg_waldump, which prints information
> about WAL contents, including which XID generated each record.
Right. pg_walinspect too can help get the same info for the available
WAL if you are on a production database with PG15 without any access
to the host instance.
> I don't know what exactly is your goal,
Yeah, it's good to know the use-case if possible.
> but sometimes it's not entirely
> direct relationship.For example, a transaction may delete a record,
> which generates just a little bit of WAL. But then after a checkpoint a
> VACUUM comes around, vacuums the page to reclaim the space of the entry,
> and ends up writing FPI (which is much larger). You could argue this WAL
> is also attributable to the original transaction, but that's not what
> pg_waldump will allow you to do. FPIs in general may inflate the numbers
> unpredictably, and it's not something the original transaction can
> affect very much.
Nice. If one knows the fact that there can be WAL generated without
associated transaction (no XID), there won't be surprises when the
amount of WAL generated by all transactions is compared against the
total WAL on the database.
Alternatively, one can get the correct amount of WAL generated
including the WAL without XID before and after doing some operations
as shown below:
postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/52EB488
(1 row)
postgres=# create table foo as select i from generate_series(1, 1000000) i;
SELECT 1000000
postgres=# update foo set i = i +1 where i%2 = 0;
UPDATE 500000
postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/D2B8000
(1 row)
postgres=# SELECT pg_wal_lsn_diff('0/D2B8000', '0/52EB488');
pg_wal_lsn_diff
-----------------
134007672
(1 row)
postgres=# SELECT pg_size_pretty(pg_wal_lsn_diff('0/D2B8000', '0/52EB488'));
pg_size_pretty
----------------
128 MB
(1 row)
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-03-08 16:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-06 01:11 [PATCH v6 5/7] Invalidate parent index cluster on attach Justin Pryzby <[email protected]>
2024-03-08 14:50 Identify transactions causing highest wal generation Gayatri Singh <[email protected]>
2024-03-08 15:40 ` Re: Identify transactions causing highest wal generation Tomas Vondra <[email protected]>
2024-03-08 16:32 ` Re: Identify transactions causing highest wal generation Euler Taveira <[email protected]>
2024-03-08 16:38 ` Re: Identify transactions causing highest wal generation Bharath Rupireddy <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox