KevinTheJedi
Yes. Its only this one query that is killing me. Its taking anywhere between 180 to 600 sec
SELECT A1.`number` AS `number`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`ticket_id` AS `ticket_id`, A1.`isoverdue` AS `isoverdue`, B5.`name` AS `user__name`, A1.`dept_id` AS `dept_id`, B6.`name` AS `dept__name`, A1.`isanswered` = '0' AS `2GkBZxz`, (SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count, (SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count, B7.`expire` > NOW() AND NOT B7.`staff_id` = 1 AS `_locked`, (SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) LEFT JOIN `ost_ticket__cdata` B4 ON (A1.`ticket_id` = B4.`ticket_id`) LEFT JOIN `ost_user` B5 ON (A1.`user_id` = B5.`id`) LEFT JOIN `ost_department` B6 ON (A1.`dept_id` = B6.`id`) LEFT JOIN `ost_lock` B7 ON (A1.`lock_id` = B7.`lock_id`) WHERE A2.`state` = 'open' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR A1.`dept_id` IN (1, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26) OR B2.`id` IN (1, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`lastupdate` DESC LIMIT 30
If we are able to fix this, then its all great.
I tried mysqltuner.pl also. Based on the suggestions I added the following parameters and restarted mysql:
[mysqld]
innodb_buffer_pool_size = 512M
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
max_connections = 20
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 5
I get a very strong feeling that we are not looking at the right place for the rot cause & I'm clueless where is the right place to look for the root cause.