DBCacheContrib BerkeleyDB performance

I'm tring to test the performance of s foswiki site usign DBCacheContrib with BerkeleyDB. For my test i have:

• Intel Core 2 duo 1.84 GHz
• 2 GB RAM
• 250 IDE hard drive 5400 rpms
• Ubuntu 9.10 i386
• Foswiki 1.0.9 - CGI Engine
• BerkeleyBD-5.0.21
• DBCacheContrib(from configure extension installer)

I create a subweb in Sandbox name DBCacheTest and create through the bash shell 35000(35 thousand) topics with the following data:

%<nop>META:TOPICINFO{author="BaseUserMapping_333" date="1274820765" format="1.1" version="1.7"}%
%<nop>META:TOPICPARENT{name="Sandbox.HexaRestFormTopicPlugin"}%
---+ System.HexaRestFormTopicPlugin Test Topic



%<nop>META:FORM{name="Sandbox.HexaRestFormTopicTestForm"}%
%<nop>META:FIELD{name="CreateBy" title="<nop>CreateBy" value="Main.PepePerez"}%
%<nop>META:FIELD{name="State" title="State" value="Old"}%

Then i try to VIEW then WebHome of the Sandbox.DBCacheTest and i been waiting for more than 1 hour and the get request doesn't finish yet because the DBCachePluginDB file in the working_area still growing.

The .../data/Sandbox/DBCacheTest directory size is 139 MB and the DBCachePluginDB size is about 80 MB after 1 hour.

This is normal? someone is using DBCacheContrib with this amount of topics?

-- JuanPabloGaviria - 26 May 2010

Finally the DBCacheContrib finish it job. I got a 84 MB db file zise name DBCachePluginDB afeter 1 hour 20 min aprox.

I'm testing the performance with %DBQUERY{...}% of DBCachePlugin vrs %SEARCH{...}% and for that i create the following querys:

• With DBQUERY:

%DBQUERY{"form.name =~ 'HexaRestFormTopicTestForm'" web="Sandbox.DBCacheTest" separator="" limit="%URLPARAM{"limit" default="50"}%" skip="%URLPARAM{"skip"}%"  format="{create: '$expand(CreateBy)', status: '$expand(State)'}"}%

• With SEARCH:

%SEARCH{"form.name='Sandbox.HexaRestFormTopicTestForm'" nonoise="on" type="query" limit="%URLPARAM{"limit" default="50"}%" skip="%URLPARAM{"skip"}%" footer="$ntopics " format="{create: '$formfield(CreateBy)', status: '$formfield(State)'}"}%

NOTE: total topics with HexaRestFormTopicTestForm attached 35.000.

TYPE/TOPICVIEWS(sec)	1	2	3
DBQUERY	61	18	18
SEARCH	64	19	19

And every save action in that subweb takes about 1 min.

Those times were form firebug profiling tool.

It can't see the diferences, so i working with 35.000 topic with and attached form with 2 formfields and no text, the BerkeleyDB and the creation of the Web database took about 2 and half hour and i got the same result.

Please Tell what am i doing wrong?

-- JuanPabloGaviria - 26 May 2010

1. Do NOT use the berkeley db backend. This is too unstable and too slow ... as you've found out yourself. Instead use the Storable backend.
2. You are HIGHLY recommended to use mod_perl or fastcgi as DBCachePlugin can keep caches in memory for several responses.
3. Do NOT enable AlwaysUpdateCache as it crawls all of the web by default on each save. That's why you get these horror numbers during save.

So try these:

$Foswiki::cfg{DBCacheContrib}{Archivist} = 'Foswiki::Contrib::DBCacheContrib::Archivist::Storable';
$Foswiki::cfg{DBCache}{MemoryCache} = 1;
$Foswiki::cfg{DBCacheContrib}{AlwaysUpdateCache} = 0;
$Foswiki::cfg{DBCacheContrib}{LoadFileLimit} = 0;

(Yes, the defaults of DBCacheContrib suck...)

Do NOT install DBCacheContrib / DBCachePlugin into a big running site as each request from that point on hitting a web that hasn't got a cache yet will trigger creating a cache db. Instead pre-seed caches for each web offline, that is

• disable your webserver
• for each web triggering a view on the commandline using something like cd bin; ./view topic=Main.WebHome refresh=on
• enable your webserver

From that point on the cache will be kept in sync automatically.

Note, when you've changed txt files or attachments using other means than foswiki, e.g. by hacking the txt files in an editor, the cache will inevitably become out of sync. Use refresh=on to manually trigger an update of the cache db of a web.

Either use LORI instead of firebug to time responses and disable firebug as it eats considerable cpu.

Or use time ./view topic=Sandbox.TestSearch on the commandline to eliminate all webserver, browser and network times, thus getting the raw computing time of foswiki. Drawback: you won't profit from fastcgi or mod_perl when measuring from the cmdline, which makes a big difference, i.e. when using DBCacheContrib / DBCachePlugin with Storable.

Try 10 SEARCHes / DBQUERY per page, not only one. Load the page repeatedly to illustrate the effect of mod_perl / fastcgi benefit.

-- MichaelDaum - 26 May 2010

MichaelDaum,

Thanks for your answer.

I just made all change you suggest:

• run foswiki with mod_perl
• change to Storable
• set to 0 {DBCacheContrib}{LoadFileLimit}, {DBCacheContrib}{AlwaysUpdateCache}
• set to 1 {DBCache}{MemoryCache}

Creating the DBCacheContrib file for the 35.000 topics took about: 3min 30sec much better than BerkeleyDB(1 hour 2o min)!!!

With all of this enhancement the DBQUERY improbe its performace to:

TYPE/TOPICVIEWS(sec)	1	2	3
DBQUERY	4.67	4.8	4.8
SEARCH	48	19.42	19.4

But, with VIEW, EDIT and SAVE are slower with DBCachePlugin enabled(in web with the 35.000 topics):

• VIEW a topic: 1.7sec
• EDIT a topic: 1.2 sec
• SAVE a topic:
  • save: 24.6 sec
  • view after save: 22 sec

I did my best to trace this issue and i found that the thing that takes the 24 sec in save and 22 sec in view after save is a function name loadTopic($web, $topic) in line 117 of DBCachePlugin/Core.pm. This function is declared in .../Contribs/DBCacheContrib/Core.pm line 503, and this calls _onReload( [$topic] ) and then {archivist}->sync( $this->{_cache} ):

• _onReload( [$topic] ) takes about: 5-7 sec
• {archivist}->sync( $this->{_cache} ) take about: 18 sec

The slower part is in sync when call the function:

• $this->{root}->setArchivist(undef)
• $this->{root}->setArchivist($this)

I don't understand those functions, but those functions makes SAVE and VIEW very slow. If someone can check them and make them better or faster i be thanksfull.

Without DBCachePlugin:

• VIEW a topic: 1.8sec
• EDIT a topic: 1.6 sec
• SAVE a topic: * save: 122 ms * view after save: 1.7 sec

Thanks,

-- JuanPabloGaviria - 26 May 2010

Interesting findings. Please try fastcgi instead of mod_perl. The latter seems to render {DBCache}{MemoryCache} useless, which means every view will drag the cache file for your large web into memory yet again. It still seems odd that the archivist is synced to disk so often.

-- MichaelDaum - 07 Jun 2010

Done some test of my own that confirm most of your findings. What I wasn't able to reproduce was VIEW and EDIT times for dbcache+fastcgi and initial indexing. A web of 35k topics took about 39s on an Intel Core 2 3.16 Ghz. I used time ./view topic=BigWeb >/dev/null to measure this.

My results using skin=text are:

	VIEW		EDIT
no fastcgi	3.118s		3.339s
fastcgi	3.338s	0.094s	3.511s	0.267s

Fastcgi shows times for firsthit vs second hit. Second hit is always dramatically lower as the cache is loaded into memory.

SAVE times are comparably low, that is above 15s. That's not acceptable. It stems from the fact that the cache is updated during save and then synced back to disk. My dbcache file for 35k topics is about 178MB size. It simply takes time to create a file of that size from scratch on every SAVE and write it to disk and other . This is innate to how the Storable archivist works.

To work around this is decoupling cache maintenance from SAVE might be a good thing to do, that is disable any cache maintenance during SAVE. Instead a cronjob could take over to do that. Even better would be using iwatch on linux to automatically trigger a script on any inode change on the filesystem below data or pub. Drawback is: you will see caching artefacts where the update is visible after a short time period.

Another idea is to implement a SharedMemoryArchivist that keeps the Storable in a shared memory segment. This has got a few other advantages as well.

-- MichaelDaum - 23 Jul 2010