| 1 |
4830
|
daigle
|
<!--
|
| 2 |
|
|
* query-caching.html
|
| 3 |
|
|
*
|
| 4 |
|
|
* Authors: Michael Daigle
|
| 5 |
|
|
* Copyright: 2009 Regents of the University of California and the
|
| 6 |
|
|
* National Center for Ecological Analysis and Synthesis
|
| 7 |
|
|
* For Details: http://www.nceas.ucsb.edu/
|
| 8 |
|
|
* Created: 2009 March 4
|
| 9 |
|
|
* Version:
|
| 10 |
|
|
* File Info: '$ '
|
| 11 |
|
|
*
|
| 12 |
|
|
*
|
| 13 |
|
|
-->
|
| 14 |
|
|
<html>
|
| 15 |
|
|
<head>
|
| 16 |
|
|
<title>Metacat Query Caching and Indexing Mechanisms</title>
|
| 17 |
|
|
<!-- unfortunately, we have to look for the common css file in the
|
| 18 |
|
|
user docs directory. This is because the user docs deploy to
|
| 19 |
|
|
the top level of the metacat docs on the knb web server -->
|
| 20 |
|
|
<link rel="stylesheet" type="text/css" href="../user/common.css">
|
| 21 |
|
|
<link rel="stylesheet" type="text/css" href="./default.css">
|
| 22 |
|
|
</head>
|
| 23 |
|
|
|
| 24 |
|
|
<body>
|
| 25 |
|
|
<table class="tabledefault" width="100%">
|
| 26 |
|
|
<tr>
|
| 27 |
|
|
<td rowspan="2"><img src="./images/KNBLogo.gif"></td>
|
| 28 |
|
|
<td colspan="7"><div class="title">KNB Software Development Guide: Metacat Query Caching and Indexing Mechanisms</div></td>
|
| 29 |
|
|
</tr>
|
| 30 |
|
|
<tr>
|
| 31 |
|
|
<td><a href="/" class="toollink"> KNB Home </a></td>
|
| 32 |
|
|
<td><a href="/data.html" class="toollink"> Data </a></td>
|
| 33 |
|
|
<td><a href="/people.html" class="toollink"> People </a></td>
|
| 34 |
|
|
<td><a href="/informatics" class="toollink"> Informatics </a></td>
|
| 35 |
|
|
<td><a href="/biodiversity" class="toollink"> Biocomplexity </a></td>
|
| 36 |
|
|
<td><a href="/education" class="toollink"> Education </a></td>
|
| 37 |
|
|
<td><a href="/software" class="toollink"> Software </a></td>
|
| 38 |
|
|
</tr>
|
| 39 |
|
|
</table>
|
| 40 |
|
|
<br>
|
| 41 |
|
|
|
| 42 |
|
|
<table width="100%">
|
| 43 |
|
|
<tr>
|
| 44 |
|
|
<td class="tablehead" colspan="2"><p class="label">Metacat Service Based Architecture</p></td>
|
| 45 |
|
|
<td class="tablehead" colspan="2" align="right">
|
| 46 |
|
|
<!-- a href="add back file here when one exists" -->Back<!-- /a --> | <a href="./index.html">Home</a> |
|
| 47 |
|
|
<a href="./service-base.html">Next</a>
|
| 48 |
|
|
</td>
|
| 49 |
|
|
</tr>
|
| 50 |
|
|
</table>
|
| 51 |
|
|
|
| 52 |
|
|
<div class="header1">Table of Contents</div>
|
| 53 |
|
|
<div class="toc">
|
| 54 |
|
|
<div class="toc1"><a href="#Overview">Overview</a></div>
|
| 55 |
|
|
<div class="toc1"><a href="#XMLNodesIndex">All XML Path Indexing</a></div>
|
| 56 |
|
|
<div class="toc2"><a href="#HowXMLNodesIndexWorks">How All XML Path Indexing Works</a></div>
|
| 57 |
|
|
<div class="toc2"><a href="#XMLNodesIndexDrawbacks">Drawbacks to All XML Path Indexing</a></div>
|
| 58 |
|
|
<div class="toc1"><a href="#MemoryCaching">Query Caching in Memory</a></div>
|
| 59 |
|
|
<div class="toc2"><a href="#HowMemoryCachingWorks">How Memory Caching Works</a></div>
|
| 60 |
|
|
<div class="toc2"><a href="#MemoryCachingDrawbacks">Memory Caching Drawbacks</a></div>
|
| 61 |
|
|
<div class="toc1"><a href="#PathIndexing">Common XML Path Indexing</a></div>
|
| 62 |
|
|
<div class="toc2"><a href="#HowPathIndexingWorks">How Common Path Indexing Works</a></div>
|
| 63 |
|
|
<div class="toc2"><a href="#PathIndexingDrawbacks">Common Path Indexing Drawbacks</a></div>
|
| 64 |
4832
|
daigle
|
<div class="toc1"><a href="#UniqueResultset">Storing Unique Return Fields</a></div>
|
| 65 |
|
|
<div class="toc2"><a href="#HowUniqueResultsetWorks">How Storing Unique Return Fields Works</a></div>
|
| 66 |
|
|
<div class="toc2"><a href="#UniqueResultsetDrawbacks">Drawbacks to Storing Unique Return Fields</a></div>
|
| 67 |
4830
|
daigle
|
</div>
|
| 68 |
|
|
|
| 69 |
|
|
<a name="Overview"></a><div class="header1">Overview</div>
|
| 70 |
|
|
<p>Unfortunately, the act of searching (querying) an xml document that has
|
| 71 |
|
|
been deconstructed and put into a relational database is somewhat slow. The table that
|
| 72 |
|
|
stores xml elements (xml_nodes) becomes very large, very fast. In an
|
| 73 |
|
|
effort to speed up these queries, metacat has implemented several levels of query
|
| 74 |
|
|
caching and indexing:</p>
|
| 75 |
|
|
|
| 76 |
|
|
<ul>
|
| 77 |
|
|
<li>All XML path indexing - store all xml paths in a separate table.</li>
|
| 78 |
|
|
<li>Memory caching - recent query results are held in memory.</li>
|
| 79 |
|
|
<li>Common XML path indexing - store predetermined xml paths in a separate table.</li>
|
| 80 |
|
|
<li>Store unique resultset combinations - unique resultsets are stored in the
|
| 81 |
|
|
database on a per-docid basis.</li>
|
| 82 |
|
|
</ul>
|
| 83 |
|
|
|
| 84 |
|
|
<p>We will discuss each of these caching strategies in turn.</p>
|
| 85 |
|
|
|
| 86 |
|
|
<a name="XMLNodesIndex"></a><div class="header1">All XML Path Indexing</div>
|
| 87 |
|
|
<a name="HowXMLNodesIndexWorks"></a><div class="header2">How All XML Path Indexing Works</div>
|
| 88 |
|
|
<p>Aside from the column indexing that is typical with performance tuning on any
|
| 89 |
|
|
database, an additional table named xml_index was created to improve search
|
| 90 |
|
|
performance against the xml_nodes table. The xml_index table keeps track of the
|
| 91 |
|
|
xml paths for each node in the nodes table. This includes subpaths, so if you
|
| 92 |
|
|
had an element in the xml that looked like: </p>
|
| 93 |
|
|
|
| 94 |
|
|
<div class="code"><a><b><c>value</c></b></a></div>
|
| 95 |
|
|
|
| 96 |
|
|
<p>the paths that would get indexed for this node would be:</p>
|
| 97 |
|
|
|
| 98 |
|
|
<div class="code">c<br>b/c<br>a/b/c</div>
|
| 99 |
|
|
|
| 100 |
|
|
<p>When querying, the query path is first looked up in xml_index and then joined to
|
| 101 |
|
|
the xml_nodes table via the nodeid.
|
| 102 |
|
|
|
| 103 |
|
|
<a name="XMLNodesIndexDrawbacks"></a><div class="header2">Drawbacks to All XML Path Indexing</div>
|
| 104 |
|
|
<p>Indexing all paths improves performance, but is still relatively slow since a join
|
| 105 |
|
|
against the xml_nodes table takes time.</p>
|
| 106 |
|
|
|
| 107 |
|
|
<a name="MemoryCaching"></a><div class="header1">Query Caching in Memory</div>
|
| 108 |
|
|
<a name="HowMemoryCachingWorks"></a><div class="header2">How Memory Caching Works</div>
|
| 109 |
|
|
<p>Metacat keeps a Hashtable of recent queries and their results. This information is only
|
| 110 |
|
|
held for queries that were run by the "public" user. The key is the sql that
|
| 111 |
|
|
that was run for the query. The value is the html that was returned from the query.
|
| 112 |
|
|
This cache is checked first to see if the current sql statement was already run. If so,
|
| 113 |
|
|
the associated result html is returned.</p>
|
| 114 |
|
|
<a name="MemoryCachingDrawbacks"></a><div class="header2">Memory Caching Drawbacks</div>
|
| 115 |
|
|
<p>The first issue is that every time a document is inserted, updated or deleted, the
|
| 116 |
|
|
query cache is potentially out of sync. So anytime any document is changed, the entire
|
| 117 |
|
|
cache is cleared, and the process of filling the cache starts over.</p>
|
| 118 |
|
|
<p>The second issue is that the cache could potentially become large, using a lot of
|
| 119 |
|
|
memory. This could be caused if a large number of different queries are performed
|
| 120 |
|
|
inbetween any documents being inserted, updated or deleted, and many of those queries
|
| 121 |
|
|
return large result sets. Note that this is a corner case, and the cache size is
|
| 122 |
|
|
configurable using the database.queryresultCacheSize property in metacat.properties.<p>
|
| 123 |
|
|
<p>Third, as mentioned before, this caching is only done on searches performed by
|
| 124 |
|
|
the "public" user. Any logged in user searches will not be cached.</p>
|
| 125 |
|
|
|
| 126 |
|
|
<a name="PathIndexing"></a><div class="header1">Common XML Path Indexing</div>
|
| 127 |
|
|
<a name="HowPathIndexingWorks"></a><div class="header2">How Common Path Indexing Works</div>
|
| 128 |
|
|
<p>Metacat queries work in two stages. The first is to get the matching document IDs.
|
| 129 |
|
|
The second is to get the requested return fields for those IDs. As discussed in the
|
| 130 |
|
|
<a href="#XMLNodesIndex">All XML Path Indexing</a> section above, some effort was made to
|
| 131 |
|
|
improve the first step, but it was still slow. Metacat made use of the fact that
|
| 132 |
|
|
there are certain paths in EML that are frequently searched to further speed up queries.</p>
|
| 133 |
|
|
|
| 134 |
|
|
<p>Paths that are determined to be searched frequently are entered as a comma delimited
|
| 135 |
|
|
list in the xml.indexPaths property in metacat.properties. At startup time, metacat determines
|
| 136 |
|
|
if there are any changes to the xml.indexPaths property, scans the xml_nodes table and adds
|
| 137 |
|
|
any nodes with matching paths to the xml_path_index table. In this case, unlike the xml_index
|
| 138 |
|
|
table, the node value is stored. So if a desired value is found for the given path, the
|
| 139 |
|
|
docid is returned and the nodes table is never touched.
|
| 140 |
|
|
|
| 141 |
|
|
<a name="PathIndexingDrawbacks"></a><div class="header2">Common Path Indexing Drawbacks</div>
|
| 142 |
|
|
<p>An obvious issue with the xml_path_index table is that is only holds values for predetermined
|
| 143 |
|
|
paths. Any other path will not benefit from this indexing.</p>
|
| 144 |
|
|
|
| 145 |
|
|
<p>Also, if a wildcard has been used in the search value, the database index for the value
|
| 146 |
|
|
column will not be used. This will slow down the search.</p>
|
| 147 |
|
|
|
| 148 |
|
|
<a name="UniqueResultset"></a><div class="header1">Storing Unique Return Fields</div>
|
| 149 |
|
|
<a name="HowUniqueResultsetWorks"></a><div class="header2">How Storing Unique Return Fields Works</div>
|
| 150 |
|
|
<p>Once the matching docids have been retrieved, metacat makes a second pass and gets the desired
|
| 151 |
|
|
return fields for each document. To help speed this up, metacat databases unique combinations of
|
| 152 |
|
|
query return fields for each document.</p>
|
| 153 |
|
|
|
| 154 |
|
|
<p>This process involves two tables. The first, xml_returnfield, keeps unique return field combinations.
|
| 155 |
|
|
The return fields are a pipe (|) delimited string in the table. The second table, xml_queryresult, keeps
|
| 156 |
|
|
the id for the row in the xml_returnfield that has the appropriate return fields, the docid and the
|
| 157 |
|
|
query result.</p>
|
| 158 |
|
|
|
| 159 |
|
|
<p>If the desired docid and result fields are found in these two tables, the query results are returned
|
| 160 |
4832
|
daigle
|
directly from the xml_queryresult table and the xml_indes and xml_nodes tables are never touched.</p>
|
| 161 |
4830
|
daigle
|
|
| 162 |
4832
|
daigle
|
<a name="UniqueResultsetDrawbacks"></a><div class="header2">Drawbacks to Storing Unique Return Fields</div>
|
| 163 |
|
|
<p>The main issue with storing unique query results is that the query must be performed at least once for
|
| 164 |
|
|
each unique set of results and docid before it is added to the xml_returnfield and xml_queryresult tables.
|
| 165 |
|
|
This first run goes against the xml_index and xml_nodes tables and is slow.</p>
|
| 166 |
4830
|
daigle
|
<br>
|
| 167 |
|
|
<!-- a href="add back file here when one exists" -->Back<!-- /a --> | <a href="./index.html">Home</a> |
|
| 168 |
|
|
<a href="./service-base.html">Next</a>
|
| 169 |
|
|
</ul>
|
| 170 |
|
|
|
| 171 |
|
|
</body>
|
| 172 |
|
|
</html>
|