Re: [Quexs-discuss] Performance issues
Web based system for Computer Assisted Telephone Interviewing (CATI)
Brought to you by:
azammitdcarf
Menu
▾
▴
Re: [Quexs-discuss] Performance issues
|
From: Saulius <zi...@ce...> - 2010-03-09 00:14:07
|
Hi Chris, I think I unintentionally misled you. The load reaches 3 (10min average) ..so I assumed that this is for all processors, but you are right if we have 4 proc. cores - 300% server load would be equivalent to 12. I have attached 30min average graph which shows some of the peeks (though not as big as 10min average). When the load shoots up the system cannot take a next case ... operators have to wait for few minutes to get a case assigned to them. I tried to tune the database by removing old sample variables and the performance improved. The sample table is growing fast so im afraid i will run into the same issues again. BTW I noticed that Quexs system refreshes screen every few seconds. Is it really necessary? Would be more efficient only to refresh when the system tries to assign a case? Regards, Saulius Nuo: "Chris Mylonas" Siųsta: 2010 03 08 01:45 Tema: Re: Re: [Quexs-discuss] Performance issues Thanks, So it had a load-average of about 12. Out of interest, was it affecting the quality of the calls? Cheers Chris On Mon, Mar 8, 2010 at 4:39 AM, Saulius zi...@ce...> wrote: Hi Chris, The figure I mentioned is load average during 10min ( http://en.wikipedia.org/wiki/Load_%28computing%29) Regards, Saulius Nuo: "Chris Mylonas" Siųsta: 2010 03 07 05:26 Tema: Re: [Quexs-discuss] Performance issues Hi, That seems very excessive. When you say 300%, where are you getting this figure from? Chris On Sun, Mar 7, 2010 at 7:28 AM, Saulius zi...@ce...> wrote: Hello, We have started a project using Quexs. I noticed that our server's load shoots up to 300% but only 10 operators are working at the same time. The server specs are as follows ( dedicated server with 4 x Xeon processors & 8 GB RAM). I checked MySQL slow sql log and it shows that following SQLs are causing the server load. It seems that the main "offender" is the case SQL as it tries to join so many tables using costly LEFT JOINs. Would be possible to tune this SQL or apply different strategy to select a next available case? Perhaps Operators, Operator skills, shifts, call restrictions checks can be done outside this SQL? ------------------------------------------------------- # Query_time: 27 Lock_time: 0 Rows_sent: 1 Rows_examined: 2203024 SET timestamp=1267623502; SELECT c.case_id as caseid,s.*,apn.*,a.*,sh.*,op.*,cr.*,si.*,CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) as resptime FROM `case` as c LEFT JOIN `call` as a on (a.call_id = c.last_call_id) JOIN (sample as s, sample_import as si) on (s.sample_id = c.sample_id and si.sample_import_id = s.import_id) JOIN (questionnaire_sample as qs, operator_questionnaire as o, questionnaire as q, operator as op, outcome as ou) on (c.questionnaire_id = q.questionnaire_id and op.operator_id = '216' and qs.sample_import_id = s.import_id and o.operator_id = op.operator_id and o.questionnaire_id = qs.questionnaire_id and q.questionnaire_id = o.questionnaire_id and ou.outcome_id = c.current_outcome_id) LEFT JOIN shift as sh on (sh.questionnaire_id = q.questionnaire_id and (CONVERT_TZ(NOW(),'System','UTC') >= sh.start) AND (CONVERT_TZ(NOW(),'System','UTC') CONVERT_TZ(NOW(),'System','UTC'))) LEFT JOIN appointment as apn on (apn.case_id = c.case_id AND apn.completed_call_id is NULL AND (CONVERT_TZ(NOW(),'System','UTC') >= apn.start) AND (CONVERT_TZ(NOW(),'System','UTC') = cr.start and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) Nėra pakitimų pranešimų: pridėti pranešimą') ELSE CONCAT('Rodyti pakitimų pranešimus') END AS link, c.completions as completions, CONCAT('Rodyti operatoriaus darbą') as operform FROM `shift` as s JOIN operator as o on (o.operator_id = '1') LEFT JOIN shift_report as sr on (sr.shift_id = s.shift_id) LEFT JOIN ( SELECT count(*) as completions,sh.shift_id FROM `call` as a, `case` as b, shift as sh WHERE a.outcome_id = '10' AND a.case_id = b.case_id AND b.questionnaire_id = '1' AND sh.start = a.start GROUP BY sh.shift_id) as c on (s.shift_id = c.shift_id) WHERE s.questionnaire_id = '1' GROUP BY shift_id ORDER BY s.start ASC; ----------------------------- # Query_time: 32 Lock_time: 0 Rows_sent: 1 Rows_examined: 905808 SET timestamp=1267518450; SELECT s.sample_id as sample_id,c.case_id as case_id,qs.questionnaire_id as questionnaire_id,CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) as resptime, q.testing as testing FROM sample as s JOIN (questionnaire_sample as qs, operator_questionnaire as o, questionnaire as q, operator as op, sample_import as si, operator_skill as os) on (op.operator_id = '200' and qs.sample_import_id = s.import_id and o.operator_id = op.operator_id and o.questionnaire_id = qs.questionnaire_id and q.questionnaire_id = o.questionnaire_id and si.sample_import_id = s.import_id and os.operator_id = op.operator_id and os.outcome_type_id = 1) LEFT JOIN `case` as c on (c.sample_id = s.sample_id and c.questionnaire_id = qs.questionnaire_id) LEFT JOIN call_restrict as cr on (cr.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= cr.start and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) = sh.start) AND (CONVERT_TZ(NOW(),'System','UTC') http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Quexs-discuss mailing list Que...@li... https://lists.sourceforge.net/lists/listinfo/quexs-discuss -- ====================== Chris Mylonas Web Systems Administrator Omnium Research Group College Of Fine Arts University of NSW www.omnium.net.au Communications, Internetworking, Software Design & Prototyping Consultant www.mrvoip.com.au Open Source Software Projects Founder www.opencsta.org www.nursepaging.com ====================== -- ====================== Chris Mylonas Web Systems Administrator Omnium Research Group College Of Fine Arts University of NSW www.omnium.net.au Communications, Internetworking, Software Design & Prototyping Consultant www.mrvoip.com.au Open Source Software Projects Founder www.opencsta.org www.nursepaging.com ====================== dfghdg |
