[Building Sakai] Site Stats 2 Performance
Nuno Fernandes
nuno at ufp.edu.pt
Wed Dec 30 03:55:35 PST 2009
Alan,
Thank you for your update and... wish you a *happy new year*!
Thanks,
Nuno
On Wed, Dec 30, 2009 at 11:17 AM, Berg, A.M. <A.M.Berg at uva.nl> wrote:
> 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.organd 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
>
>
--
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/d2156568/attachment.html
More information about the sakai-dev
mailing list