OSticket DB optimization assistance

Roccob

Good day.

Please kindly advise what DB we should be using and what settings/optimizsation we can look at to make OSticket faster. We are experiencing delays when the agent panel loads tickets:

KevinTheJedi

Roccob

MySQL/MariaDB has good optimization guides:

Cheers.

Roccob

Good day Kevin.

Will you be able to provide me with further guidance regarding the below for the MySQL DB optimization:
We need assistance to fix the below:
Table ./performance_schema/processlist has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

Roccob

Hi Kevin, just further context regarding the above:
Amount of tickets - thousands of tickets that are generated daily
DB rows: 2.3 million rows
server specs: Runs on SSD's, 8 core processor, 24GB RAM
It takes 15 to 20 seconds to load tickets on the main dashboard when going to the agent panel, assigning tickets etc
We want to make this work, can you please give us guidance on what optimizations we can do to improve OSticket's performance further.

KevinTheJedi

Roccob

I am no DBA so I cannot assist with database optimization issues. You’ll need to contact a DBA or look at the various resources online.

You need to follow the guide above relevant to your database server to optimize the database. Other than that I can recommend increasing server specs, etc. For setups like yours with millions of tickets you probably want a galera cluster for the database with something like ProxySQL to load balance the requests. You should also probably have multiple web servers with load balancing, etc.

Cheers.