[Building Sakai] Site Stats 2 Performance
Berg, A.M.
A.M.Berg at uva.nl
Wed Dec 30 03:17:05 PST 2009
Hi fellow hard workers,
While I have a chance, I would like to thank a number of colleges for their recent performance related work: Chris Kretler and Nuno Fernandes for their work performance testing site states for the 2.7 release. Chris wrote the grinder scripts and Nuno has been stress testing qa1-nl.sakaiproject.org and updated the web services for provisioning.
http://confluence.sakaiproject.org/display/PERF/SiteStats+Testing
Earles's comments remind me again to look at the slow query logs. The Xmas holiday got in the way.
Further, Seth Theriault (and at the beginning of next year David Howitz for qa1-za), for running long term low level stress tests (over weeks) that hit qa2-us at about 500,000 hits per day via Jmeter running locally. We are hoping to motivate resource leaks to the surface. The stress testing is rather primitive, but is a start point for more sophisticated tests later. The log files are being automatically downloaded and parsed once a day.
http://builds.sakaiproject.org/parsed_qa_logs/
Keep up the good work and a pre-emptive happy new year.
Alan
Alan Berg
Interim QA Director - The Sakai Foundation
Senior Developer / Quality Assurance
Group Education and Research Services
Central Computer Services
University of Amsterdam
http://home.uva.nl/a.m.berg
-----Original Message-----
From: sakai-dev-bounces at collab.sakaiproject.org on behalf of Nuno Fernandes
Sent: Tue 12/29/2009 17:55
To: Earle Nietzel
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Site Stats 2 Performance
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/20091230/fff930ba/attachment.html
More information about the sakai-dev
mailing list