Item9808: Support DBI
Priority: Enhancement
Current State: Closed
Released In: n/a
Target Release: n/a
It's been a long standing desire that we support DBI-based data stores. Well, this is a big step on the road. The
DBIStoreContrib implements query and search algorithms using SQL, searching a database cache of the topics stored in RCS.
Next step is (hopefully) to eliminate the RCS bit of this.
This
only works on trunk.
--
CrawfordCurrie - 07 Oct 2010
Basic port to Foswiki <= 1.1, using plugin handlers. Will not behave correctly on delRev or repRev. I don't think I broke it on trunk, but haven't tested that yet.
--
CrawfordCurrie - 20 Nov 2010
Hi Crawford,
This seems to work really well (although the initial load process hammers disk i/o even more than
MongoDBPlugin and
DBCacheContrib), but I couldn't make it run on my trunk checkout until I made the following modification:
diff --git a/DBIStoreContrib/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm b/DBIStoreContrib/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm
index 4a06766..552d4ab 100644
--- a/DBIStoreContrib/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm
+++ b/DBIStoreContrib/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm
@@ -264,6 +264,9 @@ SQL
sub query {
my ($session, $sql) = @_;
+ if (not $db) {
+ $db = Foswiki::Contrib::DBIStoreContrib::Listener->new();
+ }
$db->_connect($session);
print STDERR "$sql\n" if MONITOR;
my $names = $db->{handle}->selectcol_arrayref($sql);
The error I was getting was:
| 2010-12-04T07:32:57Z warning | Can't call method "_connect" on an undefined value at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm line 267.
at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Contrib/DBIStoreContrib/Listener.pm line 267
Foswiki::Contrib::DBIStoreContrib::Listener::query('Foswiki=HASH(0x8a05710)', 'SELECT tid FROM topic WHERE EXISTS(SELECT * FROM FORM WHERE F...') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Store/QueryAlgorithms/DBIStoreContrib.pm line 154
Foswiki::Store::QueryAlgorithms::DBIStoreContrib::query('Foswiki::Query::Node=HASH(0x91e19b8)', undef, 'Foswiki=HASH(0x8a05710)', 'HASH(0x90f2e98)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Store/VC/Store.pm line 491
Foswiki::Store::VC::Store::query('Foswiki::Store::RcsWrap=HASH(0x8a343f8)', 'Foswiki::Query::Node=HASH(0x91e19b8)', undef, 'Foswiki=HASH(0x8a05710)', 'HASH(0x90f2e98)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Meta.pm line 836
Foswiki::Meta::query('Foswiki::Query::Node=HASH(0x91e19b8)', undef, 'HASH(0x90f2e98)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Func.pm line 2790
Foswiki::Func::query('form.name~\'*GlossaryForm\'', undef, 'HASH(0x90f2e98)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Plugins/JQGridPlugin/GRID.pm line 598
Foswiki::Plugins::JQGridPlugin::GRID::count('Foswiki::Plugins::JQGridPlugin::GRID=HASH(0x90d24d8)', 'Lauries/GlossaryData', 'form.name~\'*GlossaryForm\'') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Plugins/JQGridPlugin/GRID.pm line 533
Foswiki::Plugins::JQGridPlugin::GRID::restGridConnectorSearch('Foswiki::Plugins::JQGridPlugin::GRID=HASH(0x90d24d8)', 'Foswiki::Request=HASH(0x89e24a0)', 'Foswiki::Response=HASH(0x8a346c8)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Plugins/JQGridPlugin/GRID.pm line 406
Foswiki::Plugins::JQGridPlugin::GRID::restGridConnector('Foswiki::Plugins::JQGridPlugin::GRID=HASH(0x90d24d8)', 'JQGridPlugin', 'gridconnector', 'Foswiki::Response=HASH(0x8a346c8)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Plugins/JQGridPlugin.pm line 65
Foswiki::Plugins::JQGridPlugin::restGridConnector('Foswiki=HASH(0x8a05710)', 'JQGridPlugin', 'gridconnector', 'Foswiki::Response=HASH(0x8a346c8)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Func.pm line 667
Foswiki::Func::__ANON__('Foswiki=HASH(0x8a05710)', 'JQGridPlugin', 'gridconnector', 'Foswiki::Response=HASH(0x8a346c8)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/UI/Rest.pm line 242
Foswiki::UI::Rest::rest('Foswiki=HASH(0x8a05710)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/UI.pm line 316
Foswiki::UI::__ANON__() called at /usr/share/perl5/Error.pm line 416
eval {...} called at /usr/share/perl5/Error.pm line 408
Error::subs::try('CODE(0x84d1948)', 'HASH(0x8a05430)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/UI.pm line 435
Foswiki::UI::_execute('Foswiki::Request=HASH(0x89e24a0)', 'CODE(0x89e2230)', 'rest', 1) called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/UI.pm line 277
Foswiki::UI::handleRequest('Foswiki::Request=HASH(0x89e24a0)') called at /usr/local/src/gitsvn.foswiki.org/core/lib/Foswiki/Engine/CGI.pm line 37
Foswiki::Engine::CGI::run('Foswiki::Engine::CGI=HASH(0x86c0e70)') called at /usr/local/src/gitsvn.foswiki.org/core/bin/rest line 29.
--
PaulHarvey - 04 Dec 2010
Many thanks for the feedback, Paul - I was getting worried this work was being ignored.
Are you sure you have the Listeners defined correctly in
LocalSite.cfg? The rest function should only be called after the Store object is created, and it is the creation of that object the should initialise the listener......
--
CrawfordCurrie - 04 Dec 2010
You're right - adding it solved the problem.
Here's some timing trying to load a 25,000 topic web:
real 276m44.611s
user 1m37.998s
sys 1m56.575s
This only got 6432 of the 25k loaded. I'm assuming this is more sqlite's end (fsync on every update?) because my HDD light wasn't going this crazy with Mongo (takes 7 minutes) and DBCache (takes 9 minutes).
[Maybe this is the problem:
http://www.sqlite.org/faq.html#q19 --
BobBagwill - 14 Dec 2010]
setEmbeddedStore seems to be a really massive overhead processing results in
MongoDB currently. Even if the DB can return a result in a fraction of a second, Foswiki seems to take forever parsing each topic returned from the DB. I'm assuming DBIContrib has the same issue (still waiting for NYTProf to finish)
--
PaulHarvey - 04 Dec 2010
Ok, got the NYTProf results back - it's true: even if I limit="25" in a
SEARCH, it seems we run getEmbeddedStore over all 25k topics which happen to match the query at hand.
I don't think I will ever render 25k topics - we want to page, skip/limit & filter. Is there a way to get the query/search algorithm to take care of skip & limit?
--
PaulHarvey - 04 Dec 2010
I don't understand where this is coming from; I don't get
any calls to
getEmbeddedStoreForm
searching with
DBIStoreContrib.Perhaps if you shared you
SEARCH statement I could comment more. Possibly your query isn't being fully hoisted, or you have a
raw
field search that is being triggered.
With respect to parsing topics from the DB; yes, it's time consuming. I
think it's down to the permissions checks - I can't think of any other reason it would want to parse the topic.
--
CrawfordCurrie - 05 Dec 2010
Oops. As discussed on IRC, the problem is setEmbeddedStoreForm, not get. And we established that this is because every topic must be parsed for its preferences (ACL checks).
--
PaulHarvey - 05 Dec 2010
Every
matched topic, not every
searched topic. presumably this is due to the presentation algorithm.
--
CrawfordCurrie - 05 Dec 2010
[Maybe this is the problem:
http://www.sqlite.org/faq.html#q19
--
BobBagwill - 14 Dec 2010
I'm sure it doesn't help - though I suspect it's the loading of the existing topic that's the real problem.
I will follow the suggestions in that posting, thanks Bob.
--
CrawfordCurrie - 15 Dec 2010
I don't thing checking ACLs on foswiki level instead of adding it to the DB query is a good idea. Imagine a 10K web where a user only has got access to 1% of the topics. Without checking ACLs in the query right away foswiki will have to wade thru an almost endless stream of pseudo-hits the user doesn't have access to ...
--
MichaelDaum - 16 Dec 2010
Exactly, but right now it's worse than that - foswiki has to
parse and Wade through 10k hits. My own testing on a 25k web and several 4k-ish webs has me believe that this parse overhead (setEmbeddedStoreForm) is well over 50% CPU time expended; the actual ACL check is small by comparison (but agree delegating ACLs to the DB would be the next step, after figuring out smarter skip/limit/paging)
--
PaulHarvey - 16 Dec 2010
Ideally foswiki should only be involved formatting the result, not constructing the hit set
at all.
--
MichaelDaum - 16 Dec 2010
DBIStoreContrib is in production use, so closing this.
--
CrawfordCurrie - 18 Jul 2014