So far we've added a couple of indexes which has helped some specific queries, and added a SQL script to remove old job info from jobs and job_arguments.
I'm expecting the next scalability issue to be the user_activity table, which accumulates lots of data (I think we have 1.2 million+ records already) and is used for statistics views. It's structure is sub-optimal (e.g. the 'type' field should be split into type + additional data rather than concatenate e.g. resize with the resolution you're resizing to), plus I think the timline view statistics probably needs some type of periodic aggregation or get put into a separate table. Regards Stephen Stephen Marquard, Learning Technologies Co-ordinator Centre for Educational Technology, University of Cape Town http://www.cet.uct.ac.za Email/IM/XMPP: [email protected] Phone: +27-21-650-5037 Cell: +27-83-500-5290 >>> Tobias Wunden 08/31/12 6:15 PM >>> Hi Stephen, thanks a lot for sharing your insights and lessons learned! Entwine is at the moment looking into the database structure as right now the database can be considered somewhat "unoptimized". We had discussed that issue about half a year ago but there was not a lot of feedback other than "works for me". Now we start seeing an increasing number of installations with database performance issues. Tobias On 31.08.2012, at 16:13, Stephen Marquard wrote: > Hi all, > > This turned out to be a side-effect of contention on the SAN storage > used by the mysql database, which was the same (slow, SATA) storage > volume being used for ingesting mediapackages. This caused excessively > slow response times to SQL queries (> 30s) when mediapackages were being > pushed up, which presumably caused the CA updates to time out and log > the error. > > We moved the mysql files to faster different storage and the problem > disappeared. > > Regards > Stephen > > Stephen Marquard, Learning Technologies Co-ordinator > Centre for Educational Technology, University of Cape Town > http://www.cet.uct.ac.za > Email/IM/XMPP: [email protected] > Phone: +27-21-650-5037 Cell: +27-83-500-5290 > > >>>> Stephen Marquard 8/28/2012 9:21 AM >>>> > Hi all, > > Has anyone else seen intermittent failures with CA updates e.g. like > this: > > 2012-08-23 13:16:51 INFO (AgentStateJob:190) - #15221 - State push to > org.opencastproject.capture.impl.jobs.AgentStateJob@1769e87 to > http://media.uct.ac.za/capture-admin/agents/zoo-1 failed with code > 401. > 2012-08-23 13:16:54 INFO (AgentConfigurationJob:121) - #15067 - > Capabilities push to > http://media.uct.ac.za/capture-admin/agents/zoo-1/configuration > failed with code 401. > > http 401 is authorization required, which is usually the first step in > the 2-stage digest auth process. So this suggests something is going > wrong with digest auth, but most of the time it works. > > Any ideas, or is there an existing JIRA for this? These are 1.3.x > capture agents (not quite head of 1.3.x branch but close). > > Regards > Stephen > > > Stephen Marquard, Learning Technologies Co-ordinator > Centre for Educational Technology, University of Cape Town > http://www.cet.uct.ac.za > Email/IM/XMPP: [email protected] > Phone: +27-21-650-5037 Cell: +27-83-500-5290 > > > > > > ### > > UNIVERSITY OF CAPE TOWN > > This e-mail is subject to the UCT ICT policies and e-mail disclaimer > published on our website at > http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable > from > +27 21 650 9111. This e-mail is intended only for the person(s) to > whom > it is addressed. If the e-mail has reached you in error, please notify > the author. If you are not the intended recipient of the e-mail you > may > not use, disclose, copy, redirect or print the content. If this e-mail > is not related to the business of UCT it is sent by the sender in the > sender's individual capacity. > > ### > > > _______________________________________________ > Matterhorn mailing list > [email protected] > http://lists.opencastproject.org/mailman/listinfo/matterhorn > > > To unsubscribe please email > [email protected] > _______________________________________________ > > > > > > ### > > UNIVERSITY OF CAPE TOWN > > This e-mail is subject to the UCT ICT policies and e-mail disclaimer > published on our website at > http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from > +27 21 650 9111. This e-mail is intended only for the person(s) to whom > it is addressed. If the e-mail has reached you in error, please notify > the author. If you are not the intended recipient of the e-mail you may > not use, disclose, copy, redirect or print the content. If this e-mail > is not related to the business of UCT it is sent by the sender in the > sender's individual capacity. > > ### > > > _______________________________________________ > Matterhorn mailing list > [email protected] > http://lists.opencastproject.org/mailman/listinfo/matterhorn > > > To unsubscribe please email > [email protected] > _______________________________________________ _______________________________________________ Matterhorn mailing list [email protected] http://lists.opencastproject.org/mailman/listinfo/matterhorn To unsubscribe please email [email protected] _______________________________________________ ### UNIVERSITY OF CAPE TOWN This e-mail is subject to the UCT ICT policies and e-mail disclaimer published on our website at http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from +27 21 650 9111. This e-mail is intended only for the person(s) to whom it is addressed. If the e-mail has reached you in error, please notify the author. If you are not the intended recipient of the e-mail you may not use, disclose, copy, redirect or print the content. If this e-mail is not related to the business of UCT it is sent by the sender in the sender's individual capacity. ### _______________________________________________ Matterhorn mailing list [email protected] http://lists.opencastproject.org/mailman/listinfo/matterhorn To unsubscribe please email [email protected] _______________________________________________
