Item9808: Support DBI

pencil
Priority: Enhancement
Current State: Closed
Released In: n/a
Target Release: n/a
Applies To: Extension
Component: DBIStoreContrib
Branches: trunk master
Reported By: CrawfordCurrie
Waiting For:
Last Change By: CrawfordCurrie
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

 

ItemTemplate edit

Summary Support DBI
ReportedBy CrawfordCurrie
Codebase
SVN Range
AppliesTo Extension
Component DBIStoreContrib
Priority Enhancement
CurrentState Closed
WaitingFor
Checkins DBIStoreContrib:508713762091 DBIStoreContrib:9b87862e9a7f DBIStoreContrib:c9a13a6f74f5 DBIStoreContrib:d3f33829de6c DBIStoreContrib:282853c18ea3 DBIStoreContrib:a6f376b03568 DBIStoreContrib:d967689d702b DBIStoreContrib:2006fe161d67 DBIStoreContrib:244e03e97de2 DBIStoreContrib:098aedaa0afd DBIStoreContrib:d36b05f86577 DBIStoreContrib:6b7da3406767 DBIStoreContrib:d6eca6a80f98 DBIStoreContrib:44438ec3abc5 DBIStoreContrib:6f72b69bdcc1 DBIStoreContrib:7536be13749d DBIStoreContrib:b323f7c6ca5f DBIStoreContrib:422bb5f804f8 DBIStoreContrib:9be8b5c43996 DBIStoreContrib:65ec15697c52 DBIStoreContrib:cd9eeb7ecfb9 distro:9146beed1bf9 DBIStoreContrib:4c3e80f003e8 distro:508713762091 distro:9b87862e9a7f distro:c9a13a6f74f5 distro:d3f33829de6c distro:282853c18ea3 distro:a6f376b03568 distro:d967689d702b distro:2006fe161d67 distro:244e03e97de2 distro:098aedaa0afd distro:d36b05f86577 distro:6b7da3406767 distro:d6eca6a80f98 distro:44438ec3abc5 distro:6f72b69bdcc1 distro:7536be13749d distro:b323f7c6ca5f distro:422bb5f804f8 distro:9be8b5c43996 distro:65ec15697c52 distro:cd9eeb7ecfb9 distro:4c3e80f003e8
TargetRelease n/a
ReleasedIn n/a
CheckinsOnBranches trunk master
trunkCheckins DBIStoreContrib:508713762091 DBIStoreContrib:9b87862e9a7f DBIStoreContrib:c9a13a6f74f5 DBIStoreContrib:d3f33829de6c DBIStoreContrib:282853c18ea3 DBIStoreContrib:a6f376b03568 DBIStoreContrib:d967689d702b DBIStoreContrib:2006fe161d67 DBIStoreContrib:244e03e97de2 DBIStoreContrib:098aedaa0afd DBIStoreContrib:d36b05f86577 DBIStoreContrib:6b7da3406767 DBIStoreContrib:d6eca6a80f98 DBIStoreContrib:44438ec3abc5 DBIStoreContrib:6f72b69bdcc1 DBIStoreContrib:7536be13749d DBIStoreContrib:b323f7c6ca5f DBIStoreContrib:422bb5f804f8 DBIStoreContrib:9be8b5c43996 DBIStoreContrib:65ec15697c52 DBIStoreContrib:cd9eeb7ecfb9 distro:9146beed1bf9 DBIStoreContrib:4c3e80f003e8
masterCheckins distro:508713762091 distro:9b87862e9a7f distro:c9a13a6f74f5 distro:d3f33829de6c distro:282853c18ea3 distro:a6f376b03568 distro:d967689d702b distro:2006fe161d67 distro:244e03e97de2 distro:098aedaa0afd distro:d36b05f86577 distro:6b7da3406767 distro:d6eca6a80f98 distro:44438ec3abc5 distro:6f72b69bdcc1 distro:7536be13749d distro:b323f7c6ca5f distro:422bb5f804f8 distro:9be8b5c43996 distro:65ec15697c52 distro:cd9eeb7ecfb9 distro:4c3e80f003e8
Release01x01Checkins
Topic revision: r40 - 28 Nov 2014, CrawfordCurrie
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy