[Building Sakai] Site Stats 2 Performance

[Earle Nietzel]

Thanks for the links, very helpful.

Earle



From:
Nuno Fernandes <nuno at ufp.edu.pt>
To:
Earle Nietzel <Earle.Nietzel at marist.edu>
Cc:
sakai-dev at collab.sakaiproject.org
Date:
12/29/2009 11:55 AM
Subject:
Re: [Building Sakai] Site Stats 2 Performance
Sent by:
sakai-dev-bounces at collab.sakaiproject.org



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/20091229/6d046afa/attachment.html