pureXML and bufferpools

[Mark A]

There is some general confusion about pureXML and DB2 Bufferpools. The word seems to be that
pureXML uses DB2 bufferpools, but when we get down to specifics things become less clear, or at
least a little bit suspicious.

The first thing I notice is that pureXML has some eerie similarities to LOB columns:

A. INLINE specification can be used for pureXML (please don’t explain INLINE, because I understand
exactly how it works), which seems unnecessary if pureXML is always cached in bufferpools to begin
with. The main purpose of INLINE for LOB columns is because they otherwise cannot be stored in
bufferpools.

B. pureXML columns cannot be accessed in read only mode on a HADR standby database. The same
restriction is true for LOB columns.

C. Based on my tests, pureXML performance is not as good as regular data (unless INLINE is used),
such as when comparing against same size columns (XML data stored in a fairly large VARCHAR). This
obviously assumes that a VARCHAR column can be created that is large enough to hold the needed
XML data. If pureXML always uses bufferpools, then I would not expect to see this discrepancy in
performance.

In addition, there are some other questions:

1. Exactly what bufferpool is used when a pureXML column is defined for a table?

2. Is a DBA defined bufferpool used for pureXML, some automatic bufferpool used, or is it some other
memory heap that is not a Bufferpool defined in syscat.bufferpools?

3. What are all the details of pureXML caching and how can it be measured and tuned (as compared to
regular BP hit ratio calculations)?

4. Did any of this behind the scenes use of bufferpools for pureXML change in DB2 10.1?

[Lennart Jonsson]

On 03/27/2013 06:59 PM, Mark A wrote:
> There is some general confusion about pureXML and DB2 Bufferpools. The word seems to be that
> pureXML uses DB2 bufferpools, but when we get down to specifics things become less clear, or at
> least a little bit suspicious.

Mark, do you have a reference that says that XML is handled by BP? My
impression is (as you indicate below) that only the inline portion of
XML is handled by BP. If I remember correctly Matthias Nicola's advise
during his IDUG session on XML in Berlin, was to inline as much as
possible of the XML. Seems odd if it is handled by the BP anyhow.
Perhaps Helmut can shed some light on this?


/Lennart

[Mark A]

On Wed, 27 Mar 2013 21:39:57 +0100, Lennart Jonsson wrote:

> Mark, do you have a reference that says that XML is handled by BP? My
> impression is (as you indicate below) that only the inline portion of
> XML is handled by BP. If I remember correctly Matthias Nicola's advise
> during his IDUG session on XML in Berlin, was to inline as much as
> possible of the XML. Seems odd if it is handled by the BP anyhow.
> Perhaps Helmut can shed some light on this?
>
> /Lennart

I have never seen any official documentation that says pureXML is handled by a bufferpool (always,
not just with INLINE). However, a lot of IBM marketing people have claimed that. When I ask for
specifics, I never get it.

[nicola....@gmail.com]

Hi Mark, Hi Lennart,

great questions! Maybe I can provide some clarification:

XML columns are always buffered in the buffer pool, regardless of whether inlining is used or not.

The buffer pool being used is the one that is assigned to the tablespace that holds the table with the XML data.

There is no behind-the-scenes buffer pool for XML and none of this has changed in DB2 10.1.

If you don't use inlining *and* specify the LONG IN clause in the CREATE TABLE statement, then the XML data will be stored in the table space specified in the LONG IN clause. In that case, the XML is buffered by whatever buffer pool is assigned to that table space.

For XML that is inlined, the buffer pool hit ratio, logical reads, physical reads, etc. is included in the monitoring for regular data, i.e. there are no separate counters for inlined XML because inlined XML is part of each relational row.

For XML that is not inlined, you will find separate counters for XDA logical reads, XDA physcial reads, and so on.

The potential benefits of XML inlining are not related to buffering at all, since XML is buffered regardless of inlining. The main benefits of inlining include:

* Faster access to inlined documents – no redirection via the XML regions index.
* The XML regions index has no entries for inlined documents. If a large percentage of your documents are inlined then this reduces the space and maintenance cost associated with the regions index.
* Better prefetching since inlined XML documents are prefetched as part of the row they belong to.

Some of these benefits may explain the difference between non-inlined XML and Varchar that you have seen. XML parsing and XML serialziation are additional factors that play into this comparison.

Also, XML columns and Varchar columns offer very different capabilities. For example, with XML indexes you can efficiently index individual pieces of XML documents in an XML column, but you cannot index substrings in a Varchar column (unless you use text search), with great implications for search performance.

Does this help?

Thanks,

Matthias

[Mark A]

Matthias, thank you very much for participating in this discussion. Your information is somewhat
helpful, but does not completely answer my questions. It is not necessary to explain the benefits of
pureXML vs VARCHAR (or CLOB) because that misses the point entirely. I am not an idiot and I do
understand the benefits of pureXML compared to VARCHAR or CLOB. I also completely understand
INLINE.

The problem is performance, so let's stick to that subject, please.

1. What is the difference between XDA bufferpool area and bufferpool areas for data and indexes, in
terms of structure and performance?

2. Do CLOB columns use XDA bufferpool areas?

3. Why is it that neither CLOB (or any LOB) columns and pureXML columns can be accessed in a HADR
Standby read-only enabled database? That sounds suspicious to me, and makes me think that both
LOB and pureXML have a lot more similarities in the way they are physically cached (or not cached)
than IBM is leading us to believe.

[Henrik Loeser]

Mark,

1) The general bufferpool structure is the same (and hence overall performance). However, in DB2 we distinguish between data pages, index pages, XDA (Xml DAta) pages. As Matthias mentioned, we have different counters for logical/physical reads for all types. There are different operations on row data, index structures, and XML structures.

2) LOBs (CLOBs and BLOBs) are not XML structures and do not use bufferpools. The exception is when LOBs are inlined because then the data is part of the row and would be stored in a data page (see 1).

3) For both LOBs and XML data that is not inlined references are to the data is stored in the row. You don't want to copy megabytes or gigabytes of data around for different processing steps. Integration of that kind of data into the RoS requires more work.

BTW: Someone posted similar questions to a related article in my blog:
http://blog.4loeser.net/2012/06/db2-purexml-and-bufferpools-revisited.html

I answered by pointing out how someone could turn from a (non-)believer to someone who knows. There are tools to look inside...

Henrik

[nicola....@gmail.com]

One more clarification in addition to what Henrik wrote. A given bufferpool, such as the IBMDEFAULTBP or any bufferpool that the DBA creates, can buffer data pages, index pages, and XDA pages that contain non-inlined XML, all at the same time. To the buffer pool all of these types of pages look the same. The BP is not really aware that some pages do or do not contain XML.

In this post and my previous post I'm trying to be comprehensive for the benefit of the wider audience, which might include people who are not fully familiar with all the details of pureXML, inlining, etc.

Thanks,

- Matthias

[TheBoss]

nicola....@gmail.com wrote in
news:85321092-5885-473d...@googlegroups.com:

If you ask me: you (and Henrik!) have been more than generous in answering
the questions and putting them in a broader perspective. I also think that
Mark could have been more generous in showing his appreciation...

Thanks!

--
Jeroen

[Mark A]

On Thu, 28 Mar 2013 02:46:19 -0700, Henrik Loeser wrote:

> Mark,
>
> 1) The general bufferpool structure is the same (and hence overall
> performance). However, in DB2 we distinguish between data pages, index
> pages, XDA (Xml DAta) pages. As Matthias mentioned, we have different
> counters for logical/physical reads for all types. There are different
> operations on row data, index structures, and XML structures.
>
> 2) LOBs (CLOBs and BLOBs) are not XML structures and do not use
> bufferpools. The exception is when LOBs are inlined because then the
> data is part of the row and would be stored in a data page (see 1).
>
> 3) For both LOBs and XML data that is not inlined references are to the
> data is stored in the row. You don't want to copy megabytes or gigabytes
> of data around for different processing steps. Integration of that kind
> of data into the RoS requires more work.
>
> BTW: Someone posted similar questions to a related article in my blog:
> http://blog.4loeser.net/2012/06/db2-purexml-and-bufferpools-revisited.html
>
> I answered by pointing out how someone could turn from a (non-)believer
> to someone who knows. There are tools to look inside...
>
> Henrik

Henrik,

Thank you for the info. But I still don't understand item 3 above. If XML data is cached in bufferpools,
would that be worse performance than regular data/indexes cached in bufferpools. If so, why (other
than the amount of data).

If someone uses pureXML, presumably they sometimes need to access the data for reporting, and so it
should be available for RoS also, otherwise it makes RoS a complete non-option for databases with
pureXML. The general suspciion is that there is some structural reason in common as to why LOB and
pureXML are not available for RoS (and are structurally different than data/index bufferpool cache),
and the reason is not simply IBM trying to police the performance of it customer's database by refusing
them access to those columns for RoS. If performance of LOB and pureXML is not good enough for RoS,
it is likely not good enough for HADR Primary database, hence my confusion.

[Mark A]

On Fri, 29 Mar 2013 00:20:32 +0000, TheBoss wrote:

> If you ask me: you (and Henrik!) have been more than generous in
> answering the questions and putting them in a broader perspective. I
> also think that Mark could have been more generous in showing his
> appreciation...

I do appreciate their participation in this discussion. But if I ask a used car salesman about the
gasoline economy of a car (in the US we call it miles per gallon, maybe kilometers per liter in most
other countries), and the salesman doesn't answer the question directly but starts telling me about all
the great features of the car (or great features of pureXML over VARCHAR/CLOB), then I get suspicious.

I interviewed someone recently for a DBA job, and the person has been teaching DB2 LUW classes as
an IBM employee for the last 10 years, and he claimed that pureXML and LOB columns have more in
common that IBM would like us to believe. Given the RoS limitation of both LOB and pureXML columns,
that adds to my suspicion.

[Mark A]

On Wed, 27 Mar 2013 21:39:57 +0100, Lennart Jonsson wrote:

> Mark, do you have a reference that says that XML is handled by BP? My
> impression is (as you indicate below) that only the inline portion of
> XML is handled by BP. If I remember correctly Matthias Nicola's advise
> during his IDUG session on XML in Berlin, was to inline as much as
> possible of the XML. Seems odd if it is handled by the BP anyhow.
> Perhaps Helmut can shed some light on this?
> /Lennart

OK, let's take a different approach based on what Lennart posted above. Why is it so highly
recommended to use INLINE for pureXML if pureXML is cached the same way that VARCHAR is cached
in bufferpools? Obviously, we are assuming that in both cases the XML will fit in the VARCHAR or
INLINE quantity.

BTW, I am not in any way questioning the INLINE advice, since I believe it is accurate. The question is
why would that be the case if both (pureXML and VARCHAR) are already using bufferpools and both
are the same length?

[Helmut Tessarek]

On 28.03.13 23:38 , Mark A wrote:
> I interviewed someone recently for a DBA job, and the person has been teaching DB2 LUW classes as
> an IBM employee for the last 10 years, and he claimed that pureXML and LOB columns have more in
> common that IBM would like us to believe. Given the RoS limitation of both LOB and pureXML columns,
> that adds to my suspicion.

There are 2 things I do not touch: XML and Java. That's why I asked Matthias
to answer your questions. Matthias is one of the architects (next to Don) for
the XML engine, so you won't find anybody else who'd be more qualified to
answer any questions wrt XML.

There is a big difference in XML documents defined as a LOB field and XML as
an XML column. We are using a relational engine and an XML engine in DB2 and
depending how it is defined, the type has either more or less similarities
with a LOB type. (Even when a document was defined as a LOB, it was still not
100% the same as a LOB.) Don't forget that a lot of people do not use
exclusively XQuery to access XML data, but use a mix of SQL and XQuery. There
are situations where the optimizers (SQL and XML engine) might not be able to
access the data as it was supposed to be.
Please elaborate what this person told you about how XML and LOB have more in
common than IBM wants you to believe.
I'm sure there are situations where this is the case, but they should be
rather the exception.

But I am definitely not the person to talk about XML, since the only thing I
know about XML is how you spell it. :-)

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

[Mark A]

On Fri, 29 Mar 2013 03:48:35 -0400, Helmut Tessarek wrote:

> There are 2 things I do not touch: XML and Java. That's why I asked
> Matthias to answer your questions. Matthias is one of the architects
> (next to Don) for the XML engine, so you won't find anybody else who'd
> be more qualified to answer any questions wrt XML.
>
> There is a big difference in XML documents defined as a LOB field and
> XML as an XML column. We are using a relational engine and an XML engine
> in DB2 and depending how it is defined, the type has either more or less
> similarities with a LOB type. (Even when a document was defined as a
> LOB, it was still not 100% the same as a LOB.) Don't forget that a lot
> of people do not use exclusively XQuery to access XML data, but use a
> mix of SQL and XQuery. There are situations where the optimizers (SQL
> and XML engine) might not be able to access the data as it was supposed
> to be. Please elaborate what this person told you about how XML and LOB
> have more in common than IBM wants you to believe. I'm sure there are
> situations where this is the case, but they should be rather the
> exception.
>
> But I am definitely not the person to talk about XML, since the only
> thing I know about XML is how you spell it. :-)

I think there is apparently confusion here. I know the feature/functional differences of storing XML in
CLOB vs pureXML. I don't care about that. I don't care about whether one can use XQuery with
pureXML and cannot with CLOB/Varchar. My only concern is performance and how data in pureXML is
cached. The comparison between CLOB and pureXML is not being made because I am thinking about
storing XML in a CLOB, it is because they appear to have a lot of things in common in terms of the way
they are handled in DB2. Here are few examples:

1. Both LOB and pureXML can be INLINE'd. We certainly know why that is the case for LOB columns,
because LOB’s don't use bufferpools, and INLINE improves performance dramatically. But we are being
told that pureXML is stored in bufferpools, but at the same time we are being told that one should
INLINE pureXML whenever possible to improve performance. This makes no sense unless there is
something fundamentally and materially different about pureXML caching in bufferpools as compared
to data/index caching in bufferpools.

2. Both LOB and pureXML columns cannot be accessed in a HADR Standby database enabled for read-
only access (RoS). There must be some architectural reason (or limitation) why that is the case, and it
is reasonable to assume (although I don't have proof) that LOB and pureXML columns have something
in common in the way they are processed internally within DB2 that has so far prevented IBM from
making them available for RoS.

As to what the IBM employee (who is a DB2 LUW instructor, or at least was when I talked to him) said
about the similarities of LOB columns and pureXML, I don't have anything other to add besides what he
said (that LOB and pureXML columns have more in common than IBM wants you to believe). It was
only a 30 minute job interview, and I didn’t have time to go into more detail on this with him.

Please, please, stop talking about the advantages of pureXML over storing XML in VARCHAR or CLOB
columns. That has nothing to do with what I am asking about, and I fully understand the advantage of
pureXML (and also INLINE).

[Helmut Tessarek]

On 29.03.13 4:28 , Mark A wrote:
> Please, please, stop talking about the advantages of pureXML over storing XML in VARCHAR or CLOB
> columns. That has nothing to do with what I am asking about, and I fully understand the advantage of
> pureXML (and also INLINE).

Ok, I'm on vacation the next 10 days, but after that I can look at the code
and talk to the component owners.

But Matthias can explain for sure why INLINE improves performance and what the
internals are.

I do understand your concern about why XML (although already buffered in
bufferpools) are apparently not as performant as when they are inlined.
As I said, I don't know anything about XML, so I will have to look into it.

As for HADR, which I'm more familiar with than with XML, I also have to find
out why we have the restrictions you mentioned.

I'll look into it as soon as I'm back from my vacation.

[Mark A]

On Fri, 29 Mar 2013 05:04:33 -0400, Helmut Tessarek wrote:

> Ok, I'm on vacation the next 10 days, but after that I can look at the
> code and talk to the component owners.
>
> But Matthias can explain for sure why INLINE improves performance and
> what the internals are.
>
> I do understand your concern about why XML (although already buffered in
> bufferpools) are apparently not as performant as when they are inlined.
> As I said, I don't know anything about XML, so I will have to look into
> it.
>
> As for HADR, which I'm more familiar with than with XML, I also have to
> find out why we have the restrictions you mentioned.
>
> I'll look into it as soon as I'm back from my vacation.

Sounds great. Thank you very much.

[Henrik Loeser]

> 1. Both LOB and pureXML can be INLINE'd. We certainly know why that is the case for LOB columns,
>
> because LOB’s don't use bufferpools, and INLINE improves performance dramatically. But we are being
>
> told that pureXML is stored in bufferpools, but at the same time we are being told that one should
>
> INLINE pureXML whenever possible to improve performance. This makes no sense unless there is
>
> something fundamentally and materially different about pureXML caching in bufferpools as compared
>
> to data/index caching in bufferpools.

There are two reasons for that and they have been explained since INLINING of XML data was supported in DB2 9.5:
1) For inlined XML there is no descriptor to point from the row to the XML data. Hence it avoids an indirection and is faster.
2) The descriptor/indirection is resolved using the XML region index. For each XML fragment that is stored in the XDA (XML storage) there is one entry in the region index. By avoiding the indirection and the entry, the region index is smaller and hence faster and more efficient for the rest of the XML docs plus we avoid region index access for the inlined XML data.

Henrik
(I was on vacation)

[Helmut Tessarek]

On 29.03.13 4:28 , Mark A wrote:

> 1. Both LOB and pureXML can be INLINE'd. We certainly know why that is the case for LOB columns,
> because LOB’s don't use bufferpools, and INLINE improves performance dramatically. But we are being
> told that pureXML is stored in bufferpools, but at the same time we are being told that one should
> INLINE pureXML whenever possible to improve performance. This makes no sense unless there is
> something fundamentally and materially different about pureXML caching in bufferpools as compared
> to data/index caching in bufferpools.

Henrik already answered this question last week.

> 2. Both LOB and pureXML columns cannot be accessed in a HADR Standby database enabled for read-
> only access (RoS). There must be some architectural reason (or limitation) why that is the case, and it
> is reasonable to assume (although I don't have proof) that LOB and pureXML columns have something
> in common in the way they are processed internally within DB2 that has so far prevented IBM from
> making them available for RoS.

I talked to HADR development and this was the answer:

"this is due to extreme complexity .. and that's probably the only thing in
common between LOB and XML"

Sorry that I don't have any better news.