100% CPU when open Closed Tab

Exactly

Hello,

I've got a problem with my osticket, when i click on closed tab my osticket is freeze and CPU goes 100%.
I enable MySQL slow log and i don't know where is a problem, can u check this and help me?

/usr/sbin/mariadbd, Version: 10.6.7-MariaDB-2ubuntu1.1-log (Ubuntu 22.04). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time		    Id Command	Argument
# Time: 221022 15:21:47
# User@Host: root[root] @ localhost []
# Thread_id: 185  Schema: ost  QC_hit: No
# Query_time: 59.164716  Lock_time: 0.000430  Rows_sent: 50  Rows_examined: 121294
# Rows_affected: 0  Bytes_sent: 8078
use ost;
SET timestamp=1666444907;
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` = 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, 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` = 'closed' AND A1.`closed` BETWEEN '2022-01-01 00:00:00' AND '2022-12-31 23:59:59' 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 (9) OR B0.`team_id` IN (9) OR A6.`object_type` = 'C' AND B1.`team_id` IN (9))) OR A1.`dept_id` IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 44, 45) OR B2.`id` IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 44, 45) OR A6.`object_type` = 'C' AND B3.`id` IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 44, 45)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`lastupdate` DESC LIMIT 50;

KevinTheJedi

Exactly

There are a few things to try:

Convert all tables that are MyISAM to InnoDB.
Set ROW_FORMAT=DYNAMIC on tables that do not have this set.
After completing the above run mysqlcheck -o database_name to re-optimize the tables.

If you need help with this do some googling or refer to MySQL/MariaDB docs.

Cheers.

Exactly

@KevinTheJedi thanks for answered!

All tables is InnoDB
All tables is Dynamic
Example:
When i run this script i received
Example:
ost.ost_user_email
note : Table does not support optimize, doing recreate + analyze instead
status : OK

KevinTheJedi

Exactly

Okay, there are other things you can do as well. You can refer to MariaDB optimization guide here:

https://mariadb.com/kb/en/optimization-and-tuning/

Cheers.

ntozier

As a side note You haven't given us much information about your environment.
Version of osTicket, MySQL, PHP, etc.
Webserver software?
Hardware (processors, cores. ram, etc)?
How many closed tickets do you have?

The more information that you can give us the more likely we will be able to help you.

pcjkollmorgen

If you have no luck with any optimisations, you may want to change to using official MySQL packages instead of MariaDB.

There are other forum posts about this, it looks to be something in the newer versions of MariaDB and more recent OsTicket code which introduces some slowness not seen when using other combinations.

Exactly

This virtual server stay on 8vcpu (main machine 20 CPUs x Intel(R) Xeon(R) CPU E5-2670 v2 @ 2.50GHz) / 30 GB ram
Ubuntu 22.04
OsTicket Version 1.17, 10.6.7-MariaDB, Apache/2.4.52,
20k closed ticket

jeandebogue

I have the same issue with mariadb 10.6.

Can you downgrade to mariadb 10.5 or lower and/or use mysql instead?

Jean

Exactly

jeandebogue Yes, I can.
Wouldn't it be better to upgrade MariaDB to 10.9 Stable Version?

jeandebogue

I tried 10.9 and it was also slow on my side. Maybe you will have better luck. Let us know what works best for you please. Downgrade to 10.5 or upgrade to 10.9
Thanks
Jean

ntozier

Let me apologize for the wall of text up front.

I recently migrated from
Windows Server 2019
running IIS, Mariadb 10.4, and PHP 7.3.19
6 cpu (Intel(R) Xeon(R) CPU E7- 8837 @ 2.67GHz)
32gb ram

to
Ubuntu 20.0x
Apache, MariaDB 10.6.7, and PHP 8.0.24
8 cpu (Intel(R) Xeon(R) CPU E7- 8837 @ 2.67GHz)
16gb ram

Ticket System Stats
open tickets: 192
closed tickets: 27243
Active Agents: 11
Departments: 8

When I first setup the new server and migrated everything it was extremely slow to log me in. So I had to do some work to get it working. We do run the Authentication :: LDAP and AD plugin and have an on prem DC which it talks to.

Here is list of most of what I did.

adjusted my mariadb conf.
I added innodb_file_per_table = on
I made sure that innodb_buffer_pool_size was a sane value. In my case its 12G which is about 75% of the Ram the server has.
I upped max_connections from the default 100 to 200.
restarted mariadb
I ran a query to make sure that all my tables were using InnoDB.

Here is the query i ran

SET @DATABASE_NAME = 'YOURdatabaseNAME';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

This gives you a list of the commands that oyu need to run for each table.

example:

| ALTER TABLE `ost__search` ENGINE=InnoDB;         |
| ALTER TABLE `ost_ticket_priority` ENGINE=InnoDB; |

You can then copy and paste it, remove the | and paste them to run the commands.

I ran another query for the same thing to see which tables were not set to ROW_FORMAT=DYNAMIC.

Here is that query

# this gets a list of all tables that do not have ROW_FORMAT=DYNAMIC set
SET @DATABASE_NAME = 'YOURdatabaseNAME';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ROW_FORMAT=DYNAMIC;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ROW_FORMAT` != 'DYNAMIC'
ORDER BY table_name DESC;

Like before it gives you a list of the commands that you need to run for each table.

example

| ALTER TABLE `ost_team_member` ROW_FORMAT=DYNAMIC;      |
| ALTER TABLE `ost_reports_agents` ROW_FORMAT=DYNAMIC;   |

You can then copy and paste it, remove the | and paste them to run the commands.

Lastly I ran the following command from the command prompt
mysqlcheck -o YOURdatabaseNAME
during troubleshooting slowness I ended up tweaking my long_query_time and disabled log-queries-not-using-indexes in my mariadb conf file and restarted mariadb.
I noticed while testing that MariaDB was pegging out all my processors. I upped the number of processors from 4 to 8 which helped a lot.

Some additional notes that may or may not be relevant to your experience.

Logging in was taking impossibly long so I applied this patch:
https://github.com/osTicket/osTicket/blob/1.17.x/include/class.search.php#L995-L1000
To try to further speed up logging into the system I changed the following setting in osTicket:
go to Admin panel -> Settings -> Tickets
disable Top-Level Ticket Counts:
Save Changes.
If you are running the Authentication :: LDAP and AD plugin
see this thread
https://forum.osticket.com/d/101553-ldap-not-enter-whit-user/15
make sure that you are not using a self signed cert. I could not get this working without huge lags until I added a real cert (letsencrypt for the win)