public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v23 5/5] doc: Add Collation Versions section.
3+ messages / 3 participants
[nested] [flat]
* [PATCH v23 5/5] doc: Add Collation Versions section.
@ 2020-03-11 02:01 Thomas Munro <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Thomas Munro @ 2020-03-11 02:01 UTC (permalink / raw)
Supply a brief introduction to collation version concepts.
Author: Thomas Munro
Reviewed-by: Julien Rouhaud
Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com
---
doc/src/sgml/charset.sgml | 35 +++++++++++++++++++++++++++++++++++
1 file changed, 35 insertions(+)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 4b4563c5b9..c537bdfc28 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -948,6 +948,41 @@ CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-tr
</tip>
</sect3>
</sect2>
+
+ <sect2 id="collation-versions">
+ <title>Collation Versions</title>
+
+ <para>
+ The ordering defined by a collation is not necessarily fixed over time.
+ If a collation changes for any reason, persistent data structures such as
+ b-trees that depend on a stable ordering of text might be corrupted.
+ <productname>PostgreSQL</productname> defends against this by recording
+ the current version of each referenced collation for any index that
+ depends on it in the
+ <link linkend="catalog-pg-depend"><structname>pg_depend</structname></link>
+ catalog, if the collation provider makes it available. If the provider
+ later begins to report a different version, a warning will be reported
+ when the index is accessed, until either the <xref linkend="sql-reindex"/>
+ or the <xref linkend="sql-alterindex"/> command is used to update the
+ version.
+ </para>
+ <para>
+ Version information is available for collations from the
+ <literal>icu</literal> provider on all operating systems. For the
+ <literal>libc</literal> provider, versions are currently only available
+ on systems using the GNU C library (most Linux systems).
+ </para>
+
+ <note>
+ <para>
+ When using the GNU C library for collations, the C library's version
+ is used as a proxy for the collation version. Many Linux distributions
+ change collation definitions only when upgrading the C library, but this
+ approach is imperfect as maintainers are free to back-port newer
+ collation definitions to older C library releases.
+ </para>
+ </note>
+ </sect2>
</sect1>
<sect1 id="multibyte">
--
2.20.1
--MGYHOYXEY6WxJCY8--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Inefficiency in parallel pg_restore with many tables
@ 2023-07-15 17:47 Tom Lane <[email protected]>
2023-07-24 17:27 ` Re: Inefficiency in parallel pg_restore with many tables Pierre Ducroquet <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-07-15 17:47 UTC (permalink / raw)
To: [email protected]
I looked into the performance gripe at [1] about pg_restore not making
effective use of parallel workers when there are a lot of tables.
I was able to reproduce that by dumping and parallel restoring 100K
tables made according to this script:
do $$
begin
for i in 1..100000 loop
execute format('create table t%s (f1 int unique, f2 int unique);', i);
execute format('insert into t%s select x, x from generate_series(1,1000) x',
i);
if i % 100 = 0 then commit; end if;
end loop;
end
$$;
Once pg_restore reaches the parallelizable part of the restore, what
I see is that the parent pg_restore process goes to 100% CPU while its
children (and the server) mostly sit idle; that is, the task dispatch
logic in pg_backup_archiver.c is unable to dispatch tasks fast enough
to keep the children busy. A quick perf check showed most of the time
being eaten by pg_qsort and TocEntrySizeCompare.
What I believe is happening is that we start the parallel restore phase
with 100K TableData items that are ready to go (they are in the
ready_list) and 200K AddConstraint items that are pending, because
we make those have dependencies on the corresponding TableData so we
don't build an index until after its table is populated. Each time
one of the TableData items is completed by some worker, the two
AddConstraint items for its table are moved from the pending_list
to the ready_list --- and that means ready_list_insert marks the
ready_list as no longer sorted. When we go to pop the next task
from the ready_list, we re-sort that entire list first. So
we spend something like O(N^2 * log(N)) time just sorting, if
there are N tables. Clearly, this code is much less bright
than it thinks it is (and that's all my fault, if memory serves).
I'm not sure how big a deal this is in practice: in most situations
the individual jobs are larger than they are in this toy example,
plus the initial non-parallelizable part of the restore is a bigger
bottleneck anyway with this many tables. Still, we do have one
real-world complaint, so maybe we should look into improving it.
I wonder if we could replace the sorted ready-list with a priority heap,
although that might be complicated by the fact that pop_next_work_item
has to be capable of popping something that's not necessarily the
largest remaining job. Another idea could be to be a little less eager
to sort the list every time; I think in practice scheduling wouldn't
get much worse if we only re-sorted every so often.
I don't have time to pursue this right now, but perhaps someone
else would like to.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/CAEzn%3DHSPXi6OS-5KzGMcZeKzWKOOX1me2u2eCiGtMEZDz9Fqdg%40m...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inefficiency in parallel pg_restore with many tables
2023-07-15 17:47 Inefficiency in parallel pg_restore with many tables Tom Lane <[email protected]>
@ 2023-07-24 17:27 ` Pierre Ducroquet <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Pierre Ducroquet @ 2023-07-24 17:27 UTC (permalink / raw)
To: [email protected]
On Saturday, July 15, 2023 7:47:12 PM CEST Tom Lane wrote:
> I'm not sure how big a deal this is in practice: in most situations
> the individual jobs are larger than they are in this toy example,
> plus the initial non-parallelizable part of the restore is a bigger
> bottleneck anyway with this many tables. Still, we do have one
> real-world complaint, so maybe we should look into improving it.
Hi
For what it's worth, at my current job it's kind of a big deal. I was going to
start looking at the bad performance I got on pg_restore for some databases
with over 50k tables (in 200 namespaces) when I found this thread. The dump
weights in about 2,8GB, the toc.dat file is 230MB, 50 120 tables, 142 069
constraints and 73 669 indexes.
HEAD pg_restore duration: 30 minutes
pg_restore with latest patch from Nathan Bossart: 23 minutes
This is indeed better, but there is still a lot of room for improvements. With
such usecases, I was able to go much faster using the patched pg_restore with
a script that parallelize on each schema instead of relying on the choices
made by pg_restore. It seems the choice of parallelizing only the data loading
is losing nice speedup opportunities with a huge number of objects.
patched pg_restore + parallel restore of schemas: 10 minutes
Anyway, the patch works really fine as is, and I will certainly keep trying
future iterations.
Regards
Pierre
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-07-24 17:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-11 02:01 [PATCH v23 5/5] doc: Add Collation Versions section. Thomas Munro <[email protected]>
2023-07-15 17:47 Inefficiency in parallel pg_restore with many tables Tom Lane <[email protected]>
2023-07-24 17:27 ` Re: Inefficiency in parallel pg_restore with many tables Pierre Ducroquet <[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