[xwiki-devs] Watch Lucene integration: XWATCH-116
Hi devs, my research about Lucene querying for Watch (http://jira.xwiki.org/jira/browse/XWATCH-116) from the past weeks brought me to the following results: First of all, as I already mentioned, since Lucene is a fulltext indexing engine and Watch data is, even if wiki data, quite structured data (and so are its queries), something feels not right in trying to do this. But the query speed improvements are / should be significant so we could try to workaround -- XWiki Lucene plugin indexes object properties in documents as fields so basically, a structured search is plausible (with the right type of Lucene fields, etc). Even like this, not all Watch SQL queries are fully translatable to Lucene queries without having Watch specific Lucene querying code and / or Watch specific Lucene indexing. Second, there is a problem with the Lucene reliability and "response time", as mentioned in the jira issue comments: * there is a delay between the moment a document is created / modified and the moment the change is retrievable through Lucene queries, because first it needs to be indexed by Lucene. This fuzzyness, although acceptable in some situations (for example, retrieving the list of articles to show to the user) it is not desired in situations like article properties updates (star, read, trash) or feed adding / deleting -- "caching" these changes until they are retrievable through Lucene querying is not at all an option. * XWiki Lucene plugin seems quite buggy / unstable: a lot of exceptions (server restart seems to be fatal due to some hanged file lock), duplicate documents in results sometimes, missing results some time, all explainable and acceptable in the "fuzzy" situation of a fulltext search engine, but not when trying to use it for structured search. Despite all these, I devised some code to help me test Lucene and compare it to SQL in the real-watch situation, for the article list retrieval. The results for the time spent on the server (I assumed that the time taken to transport documents and print them in the Reader is the same regardless of the querying technique), for a mysql server with ~60000 articles in ~200 feeds, are: * for the initial loading of the articles, in a newly started server, SQL can take up to 30-40 seconds, Lucene takes "only" up to 20 (15-16) * for the initial load of the interface, in a non-newly started server, it takes ~15 seconds for SQL and ~4-5 on average (but can go up to 10) for Lucene * for a click on the All group, which it's basically the same query as for the initial load of the article list, it can go under a second for Lucene and around 7-8-9 seconds for SQL * for a click on a feed with 1023 articles (therefore loading the list of articles in a specific feed), SQL goes to 3 seconds while Lucene can take from under a second to a couple of seconds, depending on the time took to load the actual documents corresponding to the search results * for pagination navigation, Lucene takes a second on average and SQL 2-3 seconds. Note that Lucene retrieval still uses the database and SQL access because its results are LuceneDocuments (that hold names of XWikiDocuments), not XWikiDocuments. All the tests above were made with a server running on my computer with a 5.0.51a mysql version, a 1.6-SNAPSHOT XWiki version , java 1.5.0_15 version, AMD Turion64 x 2 TL-56, 1.8 GB RAM, but with other applications running too. I feel that, overall, Lucene querying improvements are not so spectacular, especially because it cannot solve all situations and we would still need SQL querying in some cases, and because of its relative instability (which we could think about fixing, though). The other option for performance improvements in Watch would be to have a Watch specialized server (as we've already discussed) which would allow, amongst other things, to use Watch specific SQL queries (as opposed to now when we use generic queries since we have to go through the XWiki GWT API) and optimize as much as possible at that level. I haven't tested yet the amount of improvement but, since I think we might be able to drop some tables from some of the SQL joins we're doing right now, it should be better. Of course, this kind of approach requires heavy refactor, and potentially complete rewriting and rearchitecturing of some pieces. Despite the coding challenge, I'd go for the second approach, WDYT? Happy coding, Anca Luca
Anca Paula Luca wrote:
Hi devs,
my research about Lucene querying for Watch (http://jira.xwiki.org/jira/browse/XWATCH-116) from the past weeks brought me to the following results: First of all, as I already mentioned, since Lucene is a fulltext indexing engine and Watch data is, even if wiki data, quite structured data (and so are its queries), something feels not right in trying to do this. But the query speed improvements are / should be significant so we could try to workaround -- XWiki Lucene plugin indexes object properties in documents as fields so basically, a structured search is plausible (with the right type of Lucene fields, etc). Even like this, not all Watch SQL queries are fully translatable to Lucene queries without having Watch specific Lucene querying code and / or Watch specific Lucene indexing.
Second, there is a problem with the Lucene reliability and "response time", as mentioned in the jira issue comments: * there is a delay between the moment a document is created / modified and the moment the change is retrievable through Lucene queries, because first it needs to be indexed by Lucene. This fuzzyness, although acceptable in some situations (for example, retrieving the list of articles to show to the user) it is not desired in situations like article properties updates (star, read, trash) or feed adding / deleting -- "caching" these changes until they are retrievable through Lucene querying is not at all an option. * XWiki Lucene plugin seems quite buggy / unstable: a lot of exceptions (server restart seems to be fatal due to some hanged file lock), duplicate documents in results sometimes, missing results some time, all explainable and acceptable in the "fuzzy" situation of a fulltext search engine, but not when trying to use it for structured search.
Despite all these, I devised some code to help me test Lucene and compare it to SQL in the real-watch situation, for the article list retrieval. The results for the time spent on the server (I assumed that the time taken to transport documents and print them in the Reader is the same regardless of the querying technique), for a mysql server with ~60000 articles in ~200 feeds, are: * for the initial loading of the articles, in a newly started server, SQL can take up to 30-40 seconds, Lucene takes "only" up to 20 (15-16) * for the initial load of the interface, in a non-newly started server, it takes ~15 seconds for SQL and ~4-5 on average (but can go up to 10) for Lucene * for a click on the All group, which it's basically the same query as for the initial load of the article list, it can go under a second for Lucene and around 7-8-9 seconds for SQL * for a click on a feed with 1023 articles (therefore loading the list of articles in a specific feed), SQL goes to 3 seconds while Lucene can take from under a second to a couple of seconds, depending on the time took to load the actual documents corresponding to the search results * for pagination navigation, Lucene takes a second on average and SQL 2-3 seconds.
Note that Lucene retrieval still uses the database and SQL access because its results are LuceneDocuments (that hold names of XWikiDocuments), not XWikiDocuments.
All the tests above were made with a server running on my computer with a 5.0.51a mysql version, a 1.6-SNAPSHOT XWiki version , java 1.5.0_15 version, AMD Turion64 x 2 TL-56, 1.8 GB RAM, but with other applications running too.
I feel that, overall, Lucene querying improvements are not so spectacular, especially because it cannot solve all situations and we would still need SQL querying in some cases, and because of its relative instability (which we could think about fixing, though).
The other option for performance improvements in Watch would be to have a Watch specialized server (as we've already discussed) which would allow, amongst other things, to use Watch specific SQL queries (as opposed to now when we use generic queries since we have to go through the XWiki GWT API) and optimize as much as possible at that level. I haven't tested yet the amount of improvement but, since I think we might be able to drop some tables from some of the SQL joins we're doing right now, it should be better.
I also did the tests for the specific Watch queries with a Watch server and the results are as follows: * initial load on newly started server: it seems to be taking around 10 seconds (which is even lower than Lucene?) * initial load: around 7 seconds * click "All" group: 4-5-6 on average (as low as 3 but as high as 7 as well) * load large feed with now 1120 articles: under a second (0.7-0.8) * pagination navigation: big variations, from times as low as half of second, to times as high as 3-4 seconds. I'd say 2-3 on average. Indeed, I managed to drop a table from the select, but besides this I didn't otherwise try to optimize it for these tests. The only indices I have in my database are the ones in the database administration guide at: http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration with no indexes whatsoever on the feeds custom mapping tables. Since now there are only two tables left in the articles query, join optimization (with indexes) are possible more than before and they could bring even more improvement to the times -- as we can already see in the times for loading a single feed: that very good time might be a result of the "join where" optimization of mysql. Happy coding, Anca Luca
Of course, this kind of approach requires heavy refactor, and potentially complete rewriting and rearchitecturing of some pieces.
Despite the coding challenge, I'd go for the second approach, WDYT?
Happy coding, Anca Luca _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
Hi, I've summarized Anca's findings below (great testing btw ;-) , I bet we should do this more often) : [snip] | Standard SQL | Lucene | Optimized SQL | Winner initial loading of the articles, in a newly started server | 30-40 seconds | up to 20 (15-16) | around 10 | OSQL initial load of the interface, in a non-newly started server | ~15 seconds | ~4-5 on average (but can go up to 10) | 7 | Lucene click on the All group | around 7-8-9 seconds | 1 second | 5 on average, from 3 to 7 | Lucene click on a feed with 1023 articles | 3 seconds | from under a second to a couple seconds | under a second (0.7-0.8) | OSQL pagination navigation | 2-3 seconds | a second on average | 2-3 on average | Lucene As we can see Lucene still has the edge a majority of times, but Optimized SQL comes close in most cases. As far as my understanding of this issue goes, I'd advise going for SQL optimization instead of Lucene for the following reasons : - It is better suited to handle the highly structured data coming from XWiki Watch - It already offers a good performance and could deliver even more if fully implemented - It goes in the right way in terms of making the XWiki Watch distribution on par with other XWiki products (such as XWS & XEM) in terms of code organization (client-side / server-side) - The Lucene indexing engine integration with XWiki is still error-prone - Lucene doesn't work for real-time actions that are used a lot in XWiki Watch Which is why, on the whole, SQL Optimization seems better than Lucene to me. Please tell me if I've missed something. Guillaume -- Guillaume Lerouge Product Manager - XWiki Skype ID : wikibc http://blog.xwiki.com/
Guillaume Lerouge wrote:
Hi,
I've summarized Anca's findings below (great testing btw ;-) , I bet we should do this more often) :
[snip]
| Standard SQL | Lucene | Optimized SQL | Winner initial loading of the articles, in a newly started server | 30-40 seconds | up to 20 (15-16) | around 10 | OSQL initial load of the interface, in a non-newly started server | ~15 seconds | ~4-5 on average (but can go up to 10) | 7 | Lucene click on the All group | around 7-8-9 seconds | 1 second | 5 on average, from 3 to 7 | Lucene click on a feed with 1023 articles | 3 seconds | from under a second to a couple seconds | under a second (0.7-0.8) | OSQL pagination navigation | 2-3 seconds | a second on average | 2-3 on average | Lucene
As we can see Lucene still has the edge a majority of times, but Optimized SQL comes close in most cases. As far as my understanding of this issue goes, I'd advise going for SQL optimization instead of Lucene for the following reasons :
- It is better suited to handle the highly structured data coming from XWiki Watch - It already offers a good performance and could deliver even more if fully implemented - It goes in the right way in terms of making the XWiki Watch distribution on par with other XWiki products (such as XWS & XEM) in terms of code organization (client-side / server-side) - The Lucene indexing engine integration with XWiki is still error-prone ... or we could improve it. It won't harm having good Lucene indexing, the search on xwiki.org, for example, seems pretty messy... - Lucene doesn't work for real-time actions that are used a lot in XWiki Watch
Which is why, on the whole, SQL Optimization seems better than Lucene to me.
Well, it's not so dramatically better that Lucene, since it has its price to pay (from the hacks standpoint) -- sensibly lower, indeed -- but, if it were for me, I would pay the time & hacks price since it seems to be the technology to use between the two, which might be better as a long-term choice... Happy coding, Anca Luca
Please tell me if I've missed something.
Guillaume
Hi Anca, Great analysis. This gives a lot of good information to make the right choice for XWiki Watch. I suggest we run with 120000 articles to see the trend of query time for all three methods. You should run the tests again with indexes on feed_feedentry because this can bring good improvements. The optimized query for clicking on "All" seems to be still slow (it could be bad with more data). We should also test a text search and a tag or keyword search Ludovic Guillaume Lerouge wrote:
Hi,
I've summarized Anca's findings below (great testing btw ;-) , I bet we should do this more often) :
[snip]
| Standard SQL | Lucene | Optimized SQL | Winner initial loading of the articles, in a newly started server | 30-40 seconds | up to 20 (15-16) | around 10 | OSQL initial load of the interface, in a non-newly started server | ~15 seconds | ~4-5 on average (but can go up to 10) | 7 | Lucene click on the All group | around 7-8-9 seconds | 1 second | 5 on average, from 3 to 7 | Lucene click on a feed with 1023 articles | 3 seconds | from under a second to a couple seconds | under a second (0.7-0.8) | OSQL pagination navigation | 2-3 seconds | a second on average | 2-3 on average | Lucene
As we can see Lucene still has the edge a majority of times, but Optimized SQL comes close in most cases. As far as my understanding of this issue goes, I'd advise going for SQL optimization instead of Lucene for the following reasons :
- It is better suited to handle the highly structured data coming from XWiki Watch - It already offers a good performance and could deliver even more if fully implemented - It goes in the right way in terms of making the XWiki Watch distribution on par with other XWiki products (such as XWS & XEM) in terms of code organization (client-side / server-side) - The Lucene indexing engine integration with XWiki is still error-prone - Lucene doesn't work for real-time actions that are used a lot in XWiki Watch
Which is why, on the whole, SQL Optimization seems better than Lucene to me.
Please tell me if I've missed something.
Guillaume
-- Ludovic Dubost Blog: http://blog.ludovic.org/ XWiki: http://www.xwiki.com Skype: ldubost GTalk: ldubost
If I dare add my grain of salt, a web-page documenting this test can help a lot (maybe it's on the jira issue?). Because depending on the queries, you can make big speed differences. I certainly agree with Ludovic's point of view below, I think this is where Lucene gets correctly. Also, btw, Anca, please make sure your file-system is a local-file- system, for Lucene, at least, this is a performance factor of 2 at writing at least. paul Le 19-août-08 à 14:30, Ludovic Dubost a écrit :
Hi Anca,
Great analysis. This gives a lot of good information to make the right choice for XWiki Watch.
I suggest we run with 120000 articles to see the trend of query time for all three methods.
You should run the tests again with indexes on feed_feedentry because this can bring good improvements.
The optimized query for clicking on "All" seems to be still slow (it could be bad with more data).
We should also test a text search and a tag or keyword search
Ludovic
Guillaume Lerouge wrote:
Hi,
I've summarized Anca's findings below (great testing btw ;-) , I bet we should do this more often) :
[snip]
| Standard SQL | Lucene | Optimized SQL | Winner initial loading of the articles, in a newly started server | 30-40 seconds | up to 20 (15-16) | around 10 | OSQL initial load of the interface, in a non-newly started server | ~15 seconds | ~4-5 on average (but can go up to 10) | 7 | Lucene click on the All group | around 7-8-9 seconds | 1 second | 5 on average, from 3 to 7 | Lucene click on a feed with 1023 articles | 3 seconds | from under a second to a couple seconds | under a second (0.7-0.8) | OSQL pagination navigation | 2-3 seconds | a second on average | 2-3 on average | Lucene
As we can see Lucene still has the edge a majority of times, but Optimized SQL comes close in most cases. As far as my understanding of this issue goes, I'd advise going for SQL optimization instead of Lucene for the following reasons :
- It is better suited to handle the highly structured data coming from XWiki Watch - It already offers a good performance and could deliver even more if fully implemented - It goes in the right way in terms of making the XWiki Watch distribution on par with other XWiki products (such as XWS & XEM) in terms of code organization (client-side / server-side) - The Lucene indexing engine integration with XWiki is still error-prone - Lucene doesn't work for real-time actions that are used a lot in XWiki Watch
Which is why, on the whole, SQL Optimization seems better than Lucene to me.
Please tell me if I've missed something.
Guillaume
-- Ludovic Dubost Blog: http://blog.ludovic.org/ XWiki: http://www.xwiki.com Skype: ldubost GTalk: ldubost
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
Ludovic Dubost wrote:
Hi Anca,
Great analysis. This gives a lot of good information to make the right choice for XWiki Watch.
I suggest we run with 120000 articles to see the trend of query time for all three methods.
You should run the tests again with indexes on feed_feedentry because this can bring good improvements.
The optimized query for clicking on "All" seems to be still slow (it could be bad with more data).
We should also test a text search and a tag or keyword search
Done all, and tried to log the results as good as possible despite the instability of the results. Everything is available at http://watch.xwiki.org/xwiki/bin/view/Development/SpeedingUp , along with some details on how the optimisation was made and a couple of observations. As an overall view, optimised SQL seems to scale pretty well, which, combined with the stability, reliability and extensibility of this method, make me +1, again, for the optimised SQL. Along with this, me and Cati have been working (designing / coding) on the new Watch interface that would, among other things, localize the loading messages so that the slow server requests become a lot more transparent to the user. Happy coding, Anca Luca
Ludovic
Guillaume Lerouge wrote:
Hi,
I've summarized Anca's findings below (great testing btw ;-) , I bet we should do this more often) :
[snip]
| Standard SQL | Lucene | Optimized SQL | Winner initial loading of the articles, in a newly started server | 30-40 seconds | up to 20 (15-16) | around 10 | OSQL initial load of the interface, in a non-newly started server | ~15 seconds | ~4-5 on average (but can go up to 10) | 7 | Lucene click on the All group | around 7-8-9 seconds | 1 second | 5 on average, from 3 to 7 | Lucene click on a feed with 1023 articles | 3 seconds | from under a second to a couple seconds | under a second (0.7-0.8) | OSQL pagination navigation | 2-3 seconds | a second on average | 2-3 on average | Lucene
As we can see Lucene still has the edge a majority of times, but Optimized SQL comes close in most cases. As far as my understanding of this issue goes, I'd advise going for SQL optimization instead of Lucene for the following reasons :
- It is better suited to handle the highly structured data coming from XWiki Watch - It already offers a good performance and could deliver even more if fully implemented - It goes in the right way in terms of making the XWiki Watch distribution on par with other XWiki products (such as XWS & XEM) in terms of code organization (client-side / server-side) - The Lucene indexing engine integration with XWiki is still error-prone - Lucene doesn't work for real-time actions that are used a lot in XWiki Watch
Which is why, on the whole, SQL Optimization seems better than Lucene to me.
Please tell me if I've missed something.
Guillaume
participants (4)
-
Anca Paula Luca -
Guillaume Lerouge -
Ludovic Dubost -
Paul Libbrecht