[Building Sakai] Site Stats 2 Performance

Wed Dec 30 07:26:45 PST 2009

Another place where this came up recently is SAK-16557. It appeared to  
be specific to Oracle, but this may not be the case. We found 50+ OSP  
indexes that were not created by Hibernate. Most of them are on  
foreign key relationships where their absence came as a surprise.

Thanks,
-Noah

On Dec 29, 2009, at 11:55 AM, Nuno Fernandes <nuno at ufp.edu.pt> wrote:

> 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/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
> _______________________________________________
> 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"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091230/9618ebdd/attachment.html 