[WWdevel] Re: relative speed of sql_single and gdbm

[William H. W. <wh...@in...>]

Dear Arnie (et al),

Thanks for your message.

I can provide some important information about gdbm,
but I can't help with timing comparisons to sql_single
and sql, because I'm using BerkeleyDB instead of MySQL.

Last semester, I used gdbm for all of our courses.
That was not successful, because gdbm locks the entire
database when it needs to write a record.  Although
that locking strategy was not a problem in WeBWorK1
(because there were only 2 accesses to webwork-database
per student submission), that locking strategy is impractical
in WeBWork2 where webwork-database is used
to simulate several tables.  At periods of peak demand last semester,
the wait time in the queue to access webwork-database
would exceed 60 seconds, Apache children would proliferate,
RAM memory would be exhausted, and the server would
slow to a crawl as it spent most of its time swapping
memory pages.

So this semester, I swapped to BerkeleyDB.  There has been
a big, sometimes painful learning curve, but now things
are running smoothly.  The execution times are somewhat less
than wth GDBM.  But the crucial improvement is
that BerkeleyDB does page locking rather than file locking.
So there are no queues to access webwork-database
even at peak demand.

I've also grown to appreciate and like BerkeleyDB's
logging and recovery procedures and its tuning features.

There are some subtleties to using BerkeleyDB in the
WeBWorK environment (not least because the documentation
for BerkeleyDB.pm has many holes).  On several occasions, after
making mistakes with the BerkeleyDB utilities, I had to rebuild
a few webwork-databases from the transaction.log files.

But things are going smoothly now.

Best wishes.

Sincerely,

Bill

--------------------------------------------------------------------------------



On Fri, 4 Feb 2005, Arnold Pizer wrote:

> Date: Fri, 04 Feb 2005 13:50:13 -0500
> From: Arnold Pizer <ap...@ma...>
> To: ga...@ma..., sh...@ma..., jj...@as...,
>      wh...@in..., ope...@li...
> Cc: ap...@ma...
> Subject: relative speed of sql_single and gdbm
>
> Hi,
>
> I'm trying to get some idea of the relative speed of sql_single and gdbm and
> have been looking at our timing log.   This is prompted by some of my students
> reaction that WeBWorK is slower this semester than last.  Last semester all
> courses (except MTH 162 which used sql) used gdbm.  This semester all courses
> are using sql_single. It seems gdbm is the fastest, then sql_single, and then
> sql.  Also sql and sql_single have many more times that are long compared to
> gdbm.  The details are below but here is a snap shot:
>
> For gdbm
>
> 68% of the gdbm entires took 0.0 seconds (the log rounds to 1 second)
> 29% of the gdbm entires took 1.0 seconds
> 0.01% of the gdbm entires took 10.0 seconds
>
> For sql_single the percentages were:
>
> 52%
> 34%
> 1.7%
>
> and for sql
>
> 40%
> 31%
> 3%
>
> This data came from our timing log which covers Oct 16, 2004 through Feb 3,
> 2005
> (data before Oct 16 were in a different format)
>
> I'm not sure what we can do with this data but we should be aware of
> it.  Also I
> don't know how BerkelyDB would compare. Bill Wheeler is using that for both
> WW1.9 and WW2 at Indiana.
>
> Arnie
>
> Here's the actual data:
>
> There were 702291 total timings for gdbm, 441585 for sql and
> 290963 for sql_single.
>
> grep '0.0 sec gdbm' timing.02_03_05 | wc -l gives the first entry, etc.
>
> 476652  0.0 sec gdbm
> 205360  1.0 sec gdbm
>   10644  2.0 sec gdbm
>    4936          3.0 sec gdbm
>    1058          4.0 sec gdbm
>    1177          5.0 sec gdbm
>    1784          6.0 sec gdbm
>     404          7.0 sec gdbm
>     153          8.0 sec gdbm
>     123          9.0 sec gdbm
>      69          10.0 sec gdbm
>      58          11.0 sec gdbm
>      26          12.0 sec gdbm
>      20          13.0 sec gdbm
>      23          14.0 sec gdbm
>      46          15.0 sec gdbm
>      24          16.0 sec gdbm
>      14          17.0 sec gdbm
>      12          18.0 sec gdbm
>       5          19.0 sec gdbm
>      14          20.0 sec gdbm
>
>
> grep '0.0 sec sql_single' timing.02_03_05 | wc -l gives the first entry, etc.
>
> 150926  0.0 sec sql_single
> 100426  1.0 sec sql_single
>   22972  2.0 sec sql_single
>    7179          3.0 sec sql_single
>    3358          4.0 sec sql_single
>    2082          5.0 sec sql_single
>    1486          6.0 sec sql_single
>    1053          7.0 sec sql_single
>     840          8.0 sec sql_single
>     641          9.0 sec sql_single
>     484          10.0 sec sql_single
>     374          11.0 sec sql_single
>     289          12.0 sec sql_single
>     207          13.0 sec sql_single
>     190          14.0 sec sql_single
>     162          15.0 sec sql_single
>     145          16.0 sec sql_single
>     129          17.0 sec sql_single
>     129          18.0 sec sql_single
>      89          19.0 sec sql_single
>      94          20.0 sec sql_single
>
>
> grep '0.0 sec sql' timing.02_03_05 | wc -l gives the first entry, etc.
>
>
>    175395        0.0 sec sql
>    135452        1.0 sec sql
>     35487        2.0 sec sql
>     35450        3.0 sec sql
>     30331        4.0 sec sql
>     12301        5.0 sec sql
>      6750        6.0 sec sql
>      4583        7.0 sec sql
>      3286        8.0 sec sql
>      2550        9.0 sec sql
>      1657        10.0 sec sql
>      1306        11.0 sec sql
>       992        12.0 sec sql
>       758        13.0 sec sql
>       682        14.0 sec sql
>       565        15.0 sec sql
>       530        16.0 sec sql
>       479        17.0 sec sql
>       397        18.0 sec sql
>       325        19.0 sec sql
>       271        20.0 sec sql
>
>
> Prof. Arnold K. Pizer
> Dept. of Mathematics
> University of Rochester
> Rochester, NY 14627
> (585) 275-7767
>
>
>