I don't believe primary key is the problem.

MariaDB [osticket]> describe 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)

    [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]#

    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

      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)

        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.

          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.

          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.

          6 days later

          Thanks,

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

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

          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.

          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?

            dmtml

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

            Cheers.

              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.

              6 months later

              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)

              Write a Reply...