How to quickly delete tickets in a large db

Moderator: crythias

Post Reply
giacomos
Znuny newbie
Posts: 10
Joined: 28 Sep 2022, 14:51
Znuny Version: 6.0.38
Real Name: Giacomo

How to quickly delete tickets in a large db

Post by giacomos »

I am migrating our otrs server from version 5 to 6.
I am experiencing slowness in consulting the web pages (I opened another thread about this).

In the meantime I also would like to try to shrink our db of tickets.
It is very large:
- not archived tickets = 11782
- not archived and open tickets = 1.4k
- archived tickets = over a million

NB: My system is a virtual machine with Ubuntu 22, 10 GB RAM, and mariadb 10.6, znuny 6.0.38.

My goal is to have a small subset of the tickets; let's say to shrink the number of archived tickets to ~100k (one tenth of the current total).

So, I have tried to delete about 900k archived tickets, but I realized that the delete operation
(both generic agent and the cmd line Maint::Ticket::Attachment::Delete) is very slow:
deleting 20 tickets took more than a minute.
This is surely due to the fact that many tables are involved and db row deletion is slow when tables are big.
I have googled for a while and I didn't come up with a solution.

What can I do to shrink my archived tickets?
Is it possible to add some indexes to the tables?
Johannes
Moderator
Posts: 391
Joined: 30 Jan 2008, 02:26
Znuny Version: All of them ^^
Real Name: Hannes
Company: Znuny|OTTERHUB

Re: How to quickly delete tickets in a large db

Post by Johannes »

Hi,

the generic agent is the way to go here I would say.
But
deleting 20 tickets took more than a minute.
is way too long. A regular instance should delete 4k Tickets in ~2-3 Minutes

I would focus more in the database and IO related performance KPIs to get ahold of this.
Is there a high IO wait?

There has to be something wrong config (Database) or IO wise.
Post Reply