Version 1.7 much slower than 1.6

eboehnisch

After upgrading to osTicket 1.7 the whole ticket system appears to be much slower than version 1.6.x. Especially deleting multiple tickets (e.g. generated by incoming spam email messages) takes ages. Did anyone else notice this performance drop after the upgrade? Anything we could do to make it zippier again?:

Eric

ntozier

I didn't notice a performance change between the versions. That being said I also didnt sit with a stop watch and perform benchmarking of certain tasks either. What sort of data do you have to quantify and help support your claim(s)? I've sure that the devs would like to see it.

mlungu

the opposite for me

Just my 2cts, I find 1.7 works faster. Not a huge difference but it feels more responsive.

Cheers

Theo

eboehnisch

That’s strange. I have just 27,000 tickets in the system, altogether about 2 GB in size including the attachments that have been moved into the database with this update.

Any other ideas why e.g. mass deleting a few tickets takes ten seconds or more where it took just the blink of an eye in v1.6?

Eric

com_tom

It seems some of my last posts are gone with the new forum update. OK, here I will post my current issue again.

Since I have upgraded to 1.7 (currently 1.7.3) osticket is very slow. Sometimes we have to wait 10 seconds and more. Sometimes even 30 sec. and more. This is very frustrating. Here are some queries from mysql taking a long time:

# Time: 131209 8

# User@Host: wticket @ localhost

# Query_time: 16.504771 Lock_time: 0.000127 Rows_sent: 0 Rows_examined: 78143119

SET timestamp=1386574219;

DELETE FROM ost_file WHERE id NOT IN (SELECT DISTINCT(file_id) FROM (SELECT file_id FROM ost_ticket_attachment UNION ALL SELECT file_id FROM ost_canned_attachment UNION ALL SELECT file_id FROM ost_faq_attachment) still_loved) AND `ft` = "T";

# Time: 131209 8

# User@Host: wticket @ localhost

# Query_time: 35.784333 Lock_time: 0.000135 Rows_sent: 0 Rows_examined: 78143119

SET timestamp=1386574298;

# Time: 131209 8

# User@Host: wticket @ localhost

# Query_time: 35.315714 Lock_time: 0.000136 Rows_sent: 0 Rows_examined: 78143119

use wticket;

SET timestamp=1386575497;

# Time: 131209 8

# User@Host: wticket @ localhost

# Query_time: 12.595162 Lock_time: 0.000188 Rows_sent: 1 Rows_examined: 234558

SET timestamp=1386575656;

SELECT count(open.ticket_id) as open, count(answered.ticket_id) as answered ,count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned, count(closed.ticket_id) as closed FROM ost_ticket ticket LEFT JOIN ost_ticket open

ON (open.ticket_id=ticket.ticket_id

AND open.status='open'

AND open.isanswered=0

) LEFT JOIN ost_ticket answered

ON (answered.ticket_id=ticket.ticket_id

AND answered.status='open'

AND answered.isanswered=1) LEFT JOIN ost_ticket overdue

ON (overdue.ticket_id=ticket.ticket_id

AND overdue.status='open'

AND overdue.isoverdue=1) LEFT JOIN ost_ticket assigned

ON (assigned.ticket_id=ticket.ticket_id

AND assigned.status='open'

AND assigned.staff_id=2) LEFT JOIN ost_ticket closed

ON (closed.ticket_id=ticket.ticket_id

AND closed.status='closed' ) WHERE (ticket.staff_id=2 OR ticket.dept_id IN(1,3,4) );

# Time: 131209 8

# User@Host: wticket @ localhost

# Query_time: 10.094246 Lock_time: 0.000217 Rows_sent: 1 Rows_examined: 220038

SET timestamp=1386575693;

ON (open.ticket_id=ticket.ticket_id

AND open.status='open'

AND open.isanswered=0

) LEFT JOIN ost_ticket answered

ON (answered.ticket_id=ticket.ticket_id

AND answered.status='open'

AND answered.isanswered=1) LEFT JOIN ost_ticket overdue

ON (overdue.ticket_id=ticket.ticket_id

AND overdue.status='open'

AND overdue.isoverdue=1) LEFT JOIN ost_ticket assigned

ON (assigned.ticket_id=ticket.ticket_id

AND assigned.status='open'

AND assigned.staff_id=8) LEFT JOIN ost_ticket closed

ON (closed.ticket_id=ticket.ticket_id

AND closed.status='closed' ) WHERE (ticket.staff_id=8 OR ticket.dept_id IN(3) );

Any help is much appreciated.

CotterPin

There were two things I had to do to get things to be responsive.

1) Make sure host resolution is working. I'm on RedHat, so I had to make sure /etc/resolv.conf and /etc/nsswitch.conf was correctly configured.

2) Disable IPv6 if you're not using it.

Between those two things, my osTicket 1.7.3 installation is very quick.

jared@osticket.com

@[deleted], the DELETE query was removed in 1.7.3; I'm not sure why it's in your slow log. The SELECT query with the LEFT JOIN's was corrected in the develop branch and will be released with 1.7.4 soon. We're working on making a nightly build available, so until it's release, you could grab and apply a patch from https://github.com/osTicket/osTicket-1.7/pull/862.patch

@[deleted], which version of 1.7 are you using currently?

Grizly

Delete will be slower, as it has to delete attachment chunks from the DB instead of allowing FS to defer etc.. means large volumes of attachments are also slower for same reason, instead of allowing apache to cache/shunt, requires DB load for each request. I'm kind of irritated about that actually, but it works. (and I figured out how to bypass it)

com_tom

@[deleted], thanks for pointing out. Indeed, in my class.file.php still the old code was present. Probably a manual upgrade issue. However, the change speed up osticket dramatical. Thank you!

I have also grab the patch as suggested. Thank you!

com_tom

I have to say, it is a pleasure to work with osticket now. Such a small change with deep impact.There a still some performance-blocking-candidates. If you do not mind, I will post it here1) In staff panel searching for a ticket number with a space after the number (probably a copy and past input) will make a full search instead of simply searching for ticket with corresponding ticket id. I think it make sense to do a trim on users input, do it? 2) Also there a still some slow queries in the mysql log, but I do not know what action in osticket is responsible. Here a a log sample# Time: 131212 9# User@Host: wticket @ localhost # Query_time: 9.933025 Lock_time: 0.000569 Rows_sent: 50 Rows_examined: 238188use wticket;SET timestamp=1386837681;SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,ticket.team_id ,ticket.subject,ticket.name,ticket.email,dept_name ,ticket.status,ticket.source,isoverdue,isanswered,ticket.created, ticket.lastmessage,pri.* ,count(attach.attach_id) as attachments ,count(DISTINCT thread.id) as thread_count ,IF(ticket.duedate IS NULL,IF(sla.id IS NULL, NULL, DATE_ADD(ticket.created, INTERVAL sla.grace_period HOUR)), ticket.duedate) as duedate ,IF(ticket.reopened is NULL,IF(ticket.lastmessage is NULL,ticket.created,ticket.lastmessage),ticket.reopened) as effective_date ,CONCAT_WS(" ", staff.firstname, staff.lastname) as staff, team.name as team ,IF(staff.staff_id IS NULL,team.name,CONCAT_WS(" ", staff.lastname, staff.firstname)) as assigned ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(" / ", ptopic.topic, topic.topic)) as helptopic FROM ost_ticket ticket LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id LEFT JOIN ost_ticket_priority pri ON (ticket.priority_id=pri.priority_id) LEFT JOIN ost_ticket_lock tlock ON (ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW() AND tlock.staff_id!=2) LEFT JOIN ost_ticket_attachment attach ON (ticket.ticket_id=attach.ticket_id) LEFT JOIN ost_ticket_thread thread ON ( ticket.ticket_id=thread.ticket_id) LEFT JOIN ost_staff staff ON (ticket.staff_id=staff.staff_id) LEFT JOIN ost_team team ON (ticket.team_id=team.team_id) LEFT JOIN ost_sla sla ON (ticket.sla_id=sla.id AND sla.isactive=1) LEFT JOIN ost_help_topic topic ON (ticket.topic_id=topic.topic_id) LEFT JOIN ost_help_topic ptopic ON (ptopic.topic_id=topic.topic_pid) WHERE ( ticket.staff_id=2 OR ticket.dept_id IN (1,3,4) ) AND status='closed' GROUP BY ticket.ticket_id ORDER BY ticket.closed DESC, ticket.created DESC LIMIT 0,50;

drjoeblack

We too are very unhappy with the performance when upgrading to 1.7.3In our case, our problem is with the 1500 email addresses in our black listThe filter functions are BADLY designed. The comments in the code state: * It would seem that this would not work; however, bear in mind that * this logic is completely backwards from the database design. Rather * than determining if the email matches the rules, we're determining if * the rules *might* apply to the email. This is a "quick" method,It DOESN'T work. The query is horribly inefficient and takes almost 10s to run, per email address, examining EVERY record in the filter_rule table It is so slow, in fact, that it causes another problem - the regular cron job does not complete before it is called again, and the same emails are processed again, and again, and again, and again.

ntozier

So you aren't running the current version (1.8) and haven't applied patch 856?https://github.com/osTicket/osTicket-1.7/pull/856

drjoeblack

our core problem is with class.filter.php, not class.ticket.php as shown in patch 856.The latest 1.8 rc2 still has the same broken code; we have no plans to upgrade to this untested beta software

ntozier

drjoeblack,1. I do not see a bug report on github about your problem. (You might want to open one.)2. We ask that people start their own threads when experiencing issues and that they follow the guidelines provided in this thread.3. You may not have noticed, but 1.8 stable was released a few days ago after about two months of testing.

jared@osticket.com

@[deleted], I agree with you concerning the code in the filter. I think we should just pull all the records from the database and run the filter in PHP. However, it's the same code in 1.7.0, so I'm not sure that upgrading to 1.7.3 would have triggered the sluggishness on account of the filter code only

Grizly

It is so slow, in fact, that it causes another problem - the regular cron job does not complete before it is called again, and the same emails are processed again, and again, and again, and again.

You might be calling cron too often if you get that problem.. every few minutes should be enough.

drjoeblack

https://github.com/osTicket/osTicket-1.8/pull/301That's our problem.. and a very simple solution. we hadn't upgraded for well over a year. It is finally good to see some updates for the product - it certainly needed them.Cron was once per minute on the old version. We have a good workaround for that.