Very slow performance after migration/update

dmtml

Our org. has had OSTicket running on CentOS 6 for a number of years, and it has been great. However, CentOS 6 is very old and EOL, CentOS was version 14.3, and it was time for an upgrade.

I spun up an Ubuntu 22 VM, and migrated the database and everything over to the new server. Everything is working, however, it seems to be MUCH slower. Creating tickets/looking at current open tickets is pretty fast, but when trying to look at all closed tickets or anything with a larger dataset, it is VERY slow. When I click on "Closed" to see all closed tickets (we have about 7500), it takes a good 30 seconds for the page to load. I see using "top" that mysql mariadb is using 100% CPU during these operations. We originally had mySQL on the old server, and I switched to MariaDB with the new server.

Both servers have 1 vCPU and 2GB RAM. I tried adding a 2nd vCPU to the new server, but it didn't seem to make much difference. I'm having a hard time figuring out why sql queries are apparently now so slow. On the old server, it was basically instantaneous, but now is very hard to look at closed tickets as loading takes 30 seconds or so for every new page load.

Any ideas?

dmtml

I set up a slow query log, and the below appears to be the query that is regularly taking 25-30 seconds when clicking "Closed" to access all closed tickets.

SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.closed AS closed, B4.subject AS cdata__subject, A1.isoverdue AS isoverdue, B5.name AS user__name, B6.firstname AS staff__firstname, B6.lastname AS staff__lastname, B7.name AS team__name, A1.team_id AS team_id, 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, B8.expire > NOW() AND NOT B8.staff_id = 4 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, COALESCE(B6.firstname, B6.lastname, B7.name, 'zzz') AS assignee 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_staff B6 ON (A1.staff_id = B6.staff_id) LEFT JOIN ost_team B7 ON (A1.team_id = B7.team_id) LEFT JOIN ost_lock B8 ON (A1.lock_id = B8.lock_id) WHERE A2.state = 'closed' AND ((A2.state = 'open' AND (A1.staff_id = 4 OR A5.staff_id = 4 OR A6.object_type = 'C' AND A8.staff_id = 4 OR A1.team_id IN (1, 2, 3) OR B0.team_id IN (1, 2, 3) OR A6.object_type = 'C' AND B1.team_id IN (1, 2, 3))) OR A1.dept_id IN (1) OR B2.id IN (1) OR A6.object_type = 'C' AND B3.id IN (1)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY A1.closed DESC LIMIT 40;

Here is a screenshot of an EXPLAIN command on that command. Anything there that might provide some clues?

KevinTheJedi

dmtml

I noticed that A1 and B4 are not using keys and are scanning 7,000+ rows. With that being said, MySQL should be able to handle 7,000 rows. I guess your system doesn't have many resources allocated to MySQL or MySQL is not tuned? Anyways, what's strange is B4 should be using ticket_id as the key and it's even a PRIMARY key in the ticket cdata table. So I'm not sure exactly what's going on in your case.

Maybe check your %TABLE_PREFIX%_ticket__cdata table to see if it has a PRIMARY key and if so, if it's set to ticket_id. If not you can run the following to add it:

ALTER TABLE `%TABLE_PREFIX%_ticket__cdata` ADD PRIMARY KEY (`ticket_id`);

Cheers.

dmtml

KevinTheJedi

When running describe ost_ticket__cdata; I get the following, which looks the same as what @"kwallace" looks like

MariaDB [helpdesk]> describe ost_ticket__cdata;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| ticket_id | int(11) unsigned | NO   | PRI | 0       |       |
| subject   | mediumtext       | YES  |     | NULL    |       |
| priority  | mediumtext       | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

I tried running the ALTER TABLE command you listed, but looks like it already has a Primary key so it just errors out saying "Multiple primary key defined"

kwallace

I don't believe primary key is the problem.

dmtml

kwallace

Mine looks the same as yours for describe ost_ticket__cdata;


MariaDB [helpdesk]> describe ost_ticket__cdata;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| ticket_id | int(11) unsigned | NO   | PRI | 0       |       |
| subject   | mediumtext       | YES  |     | NULL    |       |
| priority  | mediumtext       | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

kwallace

[root@ip-172-31-71-35 mariadb]# mysqldumpslow -a /var/log/mysql-slow.log

Reading mysql slow query log from /var/log/mysql-slow.log
Count: 4 Time=300.79s (1203s) Lock=0.00s (0s) Rows_sent=25.0 (100), Rows_examined=552361.0 (2209444), Rows_affected=0.0 (0), osticket[osticket]@localhost
SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.closed AS closed, B2.subject AS cdata__subject, A1.isoverdue AS isoverdue, B3.name AS user__name, B4.firstname AS staff__firstname, B4.lastname AS staff__lastname, B5.name AS team__name, A1.team_id AS team_id, A1.isanswered = '0' AS 2GkBZxz, (SELECT COUNT(R0.id) AS count FROM thread Q7 JOIN ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN 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 thread Q7 JOIN ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN 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, B6.expire > NOW() AND NOT B6.staff_id = 1 AS _locked, (SELECT COUNT(R0.id) AS count FROM thread Q7 JOIN ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN thread_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs, COALESCE(B4.firstname, B4.lastname, B5.name, 'zzz') AS assignee FROM ticket A1 JOIN ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) LEFT JOIN ticket__cdata B2 ON (A1.ticket_id = B2.ticket_id) LEFT JOIN user B3 ON (A1.user_id = B3.id) LEFT JOIN staff B4 ON (A1.staff_id = B4.staff_id) LEFT JOIN team B5 ON (A1.team_id = B5.team_id) LEFT JOIN lock B6 ON (A1.lock_id = B6.lock_id) WHERE A2.state = 'closed' 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.dept_id IN (1) OR B0.id IN (1) OR A6.object_type = 'C' AND B1.id IN (1)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY A1.closed DESC LIMIT 25

[root@ip-172-31-71-35 mariadb]#

dmtml

I've also read a bit about InnoDB vs MyISAM, at https://forum.osticket.com/d/99249-performance-tuning/3

I did see that about half my talbes were using MyISAM. I was able to convert all of them to InnoDB, but performance is the same.


I also upped System RAM from 2GB to 4GB with no noticeable difference.

KevinTheJedi

dmtml

We have potentially optimizing the queue queries on our todo list. We have much more pressing things to take care of before that however. In the meantime if someone wants to take a crack at it please do so.

Cheers.

kwallace

I'm hosted at AWS. I migrated to v1.16.3 using a Graviton (arm), 2 vCPU, 2 GiB, with MariaDB. Clicking the Closed tab on _28k tickets executed a SQL query that took over 300 seconds to complete. I also tried the same on a beefed up x86 configuration and got the same results. I decided to switch to MySQL 8.0 from MySQL Community (mysql80-community-release-el7-7.noarch.rpm) instead of MariaDB. I assumed I needed to use an x86 CPU. So I chose AMD, 2 vCPU, 2 GiB. Now with v1.16.3, my _28k tickets Closed tickets query takes 2 to 3 seconds. Perhaps there's some tuning that I needed to do to MariaDB but I'm not really an expert at any of this. MySQL 8.0 from MySQL Community out of the box seems to address the issue for me and either implementation of MySQL is fine with me. Again, I did try a beefed up x86 configuration with MariaDB (out of the box) and the problem persisted. So I'm sticking with MySQL 8.0 from MySQL Community from now own.

kwallace

I later did decide to give a Graviton (arm), 2 vCPU, 2 GiB instance a try as it's a tad less expensive than the AMD instance and it was just as fast. So the performance difference for me is due to my switching from MariaDB to MySQL 8.0 from MySQL Community.

dmtml

Thanks,

Anything special I need to know about moving from MariaDB to MySQL?

Just export, uninstall MariaDB, Install MySQL, then Import DB?

pcjkollmorgen

Not really, they are pretty similar and you have the process right, it will require an export and re-import.

Most likely you will also want to rename /var/lib/mysql out of the way in between the uninstall and install as this folder (with the database contents) will be left behind after an uninstall and it likely will upset the launching of the new database server service.

dmtml

Thanks.

Once I got rid of MariaDB and installed MySQL, opening the Closed Tickets queue now takes about 2 seconds instead of the 30 it was taking previously.

Wonder where the problem is with MariaDB?

KevinTheJedi

dmtml

Probably missing a setting or config or just not tuned properly.

Cheers.

pcjkollmorgen

I've tended to stick with Mariadb, all other things being equal, but it does look for the moment that official MySQL is working better for me also. I'll keep an eye on it, perhaps in the future whatever bug is slowing things down will be fixed (it could be anywhere) and these 2 database server platforms will be equal again.

vastavanand

After upgrading osticket to latest version, we faced same issue.
Fixed db slow issue by runngin following optimization command for mariadb

mysqlcheck -o db_name

it said "table does not support optimize, doing recreate + analyze instead", but still fixed the issue as recreate+analyze is equivalent to optimize in case of innodb.

After executing this, query execution time reduced to 1 sec (earlier it was more then 30 sec)