[Building Sakai] Site Stats 2 Performance

[Nuno Fernandes]

Hi Earle!!

Thank you very much for sharing your results!

Unfortunately, some bug with MySQL + Hibernate combination prevents that
hibernate creates the proper table indexes when using auto.ddl (I believe
this bug stills occurring).

You may also check other useful SiteStats indexes that will speed up other
queries:

*MySQL:*
https://source.sakaiproject.org/contrib/ufp/sitestats/trunk/schema/mysql_schema-export.sql

*Oracle:*
<https://source.sakaiproject.org/contrib/ufp/sitestats/trunk/schema/mysql_schema-export.sql>
https://source.sakaiproject.org/contrib/ufp/sitestats/trunk/schema/oracle_schema-export.sql

Thank you,
Nuno


On Tue, Dec 29, 2009 at 4:05 PM, Earle Nietzel <Earle.Nietzel at marist.edu>wrote:

> Site Statistics 2 was showing some long queries against table
> SST_RESOURCES.
>
> select SITE_ID,
>       USER_ID,
>       sum(resourcest0_.RESOURCE_COUNT) as col3
> from SST_RESOURCES
> where SITE_ID='33964def-1fd2-4384-97ae-5194b7ed4f02'
>       and RESOURCE_ACTION='read'
>       and (RESOURCE_REF like
> '/content/group/33964def-1fd2-4384-97ae-5194b7ed4f02/%')
>       and RESOURCE_DATE>='2009-10-21'
>       and RESOURCE_DATE<'2009-12-29'
> group by SITE_ID,
>       USER_ID
> order by col3 DESC;
>
> After profiling this query:
>
>
> +------+----------------------+----------+---------+---------+---------+---------+
> | seq  | state                | numb_ops | sum_dur | avg_dur | sum_cpu |
> avg_cpu |
>
> +------+----------------------+----------+---------+---------+---------+---------+
> |    1 | starting             |        1 | 0.00006 | 0.00006 | 0.00000 |
> 0.00000 |
> |    2 | Opening tables       |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |    3 | System lock          |        1 | 0.00000 | 0.00000 | 0.00000 |
> 0.00000 |
> |    4 | Table lock           |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |    5 | init                 |        1 | 0.00003 | 0.00003 | 0.00000 |
> 0.00000 |
> |    6 | optimizing           |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |    7 | statistics           |        1 | 0.00002 | 0.00002 | 0.00000 |
> 0.00000 |
> |    8 | preparing            |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |    9 | Creating tmp table   |        1 | 0.00004 | 0.00004 | 0.00000 |
> 0.00000 |
> |   10 | Sorting for group    |        1 | 0.00000 | 0.00000 | 0.00000 |
> 0.00000 |
> |   11 | executing            |        1 | 0.00000 | 0.00000 | 0.00000 |
> 0.00000 |
> |   12 | Copying to tmp table |        1 | 5.50261 | 5.50261 | 5.50816 |
> 5.50816 |
> |   13 | Sorting result       |        1 | 0.00003 | 0.00003 | 0.00000 |
> 0.00000 |
> |   14 | Sending data         |        1 | 0.00004 | 0.00004 | 0.00000 |
> 0.00000 |
> |   15 | end                  |        3 | 0.00001 | 0.00000 | 0.00000 |
> 0.00000 |
> |   16 | removing tmp table   |        1 | 0.00002 | 0.00002 | 0.00000 |
> 0.00000 |
> |   19 | query end            |        1 | 0.00000 | 0.00000 | 0.00000 |
> 0.00000 |
> |   20 | freeing items        |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |   21 | closing tables       |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
> |   22 | logging slow query   |        1 | 0.00000 | 0.00000 | 0.00000 |
> 0.00000 |
> |   23 | cleaning up          |        1 | 0.00001 | 0.00001 | 0.00000 |
> 0.00000 |
>
> +------+----------------------+----------+---------+---------+---------+---------+
>
> Step 12 is the culprit which leads to believe that there is possibly no
> index on field used in GROUP BY.
>
> CREATE TABLE `SST_RESOURCES` (
>   `ID` bigint(20) NOT NULL auto_increment,
>   `USER_ID` varchar(99) NOT NULL,
>   `SITE_ID` varchar(99) NOT NULL,
>   `RESOURCE_REF` varchar(255) NOT NULL,
>   `RESOURCE_ACTION` varchar(12) NOT NULL,
>   `RESOURCE_DATE` date NOT NULL,
>   `RESOURCE_COUNT` bigint(20) NOT NULL,
>   PRIMARY KEY  (`ID`),
>   KEY `SST_RESOURCES_USER_ID_I` (`USER_ID`)
> )
>
> hmmm no index on SITE_ID.
>
> Adding the following index:
>
> CREATE INDEX SST_RESOURCES_SITE_ID_I ON SST_RESOURCES (SITE_ID);
>
> Reduced the query from 5.5 seconds to .07 seconds.
>
> Nice speed up,
> Earle
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>



-- 
Nuno Fernandes

Profile    | http://facebook.com/nfgrilo | http://linkedin.com/in/nfgrilo
Web      | http://codingwithcoffee.com | http://twitter.com/nfgrilo
Work     | Analyst/Programmer @ UFP-UV [http://elearning.ufp.pt]
             | Analyst/Programmer @ Sakai Foundation [
http://sakaiproject.org]
             | Sakai Fellow 2008 @ Sakai Foundation [
http://confluence.sakaiproject.org//x/6oCTAQ]
Address | Universidade Fernando Pessoa  [http://www.ufp.pt]
             | Praça 9 de Abril, 349    | 4249-004 Porto
             | tel: + 351 22 507 13 00 | fax: + 351 22 550 82 69
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091229/da7dadeb/attachment.html