Re: [WWdevel] speed of WeBWorK/mysql

[John J. <jj...@as...>]

Hi all,

I was looking at mysql speed today during set assignment. It was slowing 
down mainly by having lots of calls asking if items exist in the 
database.  So, I looked at the indexing again and I think that is where 
the problem lies after all.  There are enough indexes being kept in 
mysql, but I don't think the queries get to use them.

Because of another bug, the mysql select commands have the modifier 
"binary" added when specifying the key information (e.g., the set_id, 
etc.).  This made that information checked in a case sensitive way.  The 
problem is that the indexing in mysql seems to be case insensitive.  So, 
when we try to look something up in mysql specifying case sensitive 
matches, I think it ignores the index and walkd linearly through all of 
the records.  The bigger the course, the longer this takes, and it of 
course, will get worse.

If you want to test this theory at home, login to your server (in a unix 
shell).  Then connect to mysql (e.g., mysql -uroot -p).  Then, do 
something like this:

> mysql> use webwork;
> Database changed
> mysql> select count(*) from Jones_MAT_117_Spring_2005_problem_user 
> where user_id="jjones" and set_id="Homework_3" and problem_id="2";
> +----------+
> | COUNT(*) |
> +----------+
> |        1 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from Jones_MAT_117_Spring_2005_problem_user 
> where BINARY user_id="jjones" and BINARY set_id="Homework_3" and 
> BINARY problem_id="2";
> +----------+
> | COUNT(*) |
> +----------+
> |        1 |
> +----------+
> 1 row in set (0.08 sec)

The same query, but using the binary modifier goes from ~ 0 secs to 0.08 
secs.

I looked at the mysql documentation for the create index command, and 
did not see a way of specifying the entries so that they would be 
treated as binary.  Maybe if the original database types where binary 
(if that is possible), it would work.

Alternatively, webwork could remove the binary modifier, get its result, 
and then sift out the ones which match in a case sensitive way.  I will 
look at doing that when I have a chance.  All changes are probably just 
to the file DB/Schema/SQL.pm, which is nice.

John


Arnold Pizer wrote:

> At 10:43 PM 3/2/2005, Sam Hathaway wrote:
>
>> \On Mar 2, 2005, at 10:20 PM, Arnold Pizer wrote:
>>
>>> At 09:45 PM 3/2/2005, Sam Hathaway wrote:
>>>
>>>> On Mar 2, 2005, at 10:35 AM, Arnold Pizer wrote:
>>>>
>>>>> My students in MTH 162 are seeing a significant slowdown in 
>>>>> WeBWorK this semester and I'm not sure what is going on.  I'm 
>>>>> wondering if any of you have seen similar things or if the 
>>>>> symptoms bring to mind what the problem might be.
>>>>
>>>>
>>>> If someone's feeling ambitious, they could write a custom version 
>>>> of DB.pm that makes SQL calls directly. I think we could get some 
>>>> savings if we avoided the "Schema" abstraction.
>>>> -sam
>>>
>>>
>>> Hi Sam,
>>>
>>> The slow downs I'm seeing are significant --- e.g. 40 times slower 
>>> than what we are used to.  Could the "Schema" abstraction have that 
>>> great an overhead?
>>
>>
>> It can't account for all of the slowdown, but I think there's room 
>> for significant improvement. I'll take a closer look once devel is 
>> back up.
>> -sam
>
>
> Thanks,
>
> Arnie
>
>
>> -------------------------------------------------------
>> SF email is sponsored by - The IT Product Guide
>> Read honest & candid reviews on hundreds of IT Products from real users.
>> Discover which products truly live up to the hype. Start reading now.
>> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
>> _______________________________________________
>> OpenWeBWorK-Devel mailing list
>> Ope...@li...
>> https://lists.sourceforge.net/lists/listinfo/openwebwork-devel
>
>
>
> Prof. Arnold K. Pizer
> Dept. of Mathematics
> University of Rochester
> Rochester, NY 14627
> (585) 275-7767
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> OpenWeBWorK-Devel mailing list
> Ope...@li...
> https://lists.sourceforge.net/lists/listinfo/openwebwork-devel