Bug #2152
closed
Metacat Performace: Reduce size of xml_nodes table
Added by Saurabh Garg about 19 years ago.
Updated about 19 years ago.
Description
xml_nodes is huge (6.8x106 records), and grows with every insert and
update by many thousands of records. By design we don't want to search
deleted docs nor old revisions anyway, so these could and should be
moved to their own table (xml_nodes_revisions) that parallels the
xml_revisions table. This would mean a massive reduction in the
xml_nodes table size (xml_nodes has 6,885,702 records now, would reduce
to 2,861,435 records) and a much slower growth rate for that
table. This alone could speed up query time substantially.
While implementing this, have to check whats the limit on the sequence numbers
that are being assigned to xml_nodes table. This will be a problem if the
sequence number start rotating.
From Postgres documentation, the limits on the sequence...
"Sequences are based on bigint arithmetic, so the range cannot exceed the range
of an eight-byte integer (-9223372036854775808 to 9223372036854775807). On some
older platforms, there may be no compiler support for eight-byte integers, in
which case sequences use regular integer arithmetic (range -2147483648 to
+2147483647)."
From Oracle documentation, the limits on the sequence...
"an ascending sequence starts at one and increments up to the greatest allowed
bigint value3, a negative sequence starts at –1 and decrements to the least
allowed bigint value."
Currently on KNB we are at 7354460 for xml_nodes and 7354457 for xml_index.
Hence long way to go.
This has been fixed. A new table is created in the Metacat schema where nodes
from old document revisions and deleted documents are stored. This feature is
currently being used by servers like ecoinfo2 and sbcdata and is running fine.
Closing the bug.
Original Bugzilla ID was 2152
Also available in: Atom
PDF