Removig old tickets with Generic Agent too slow

Moderator: crythias

Post Reply
atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Removig old tickets with Generic Agent too slow

Post by atom » 06 Nov 2018, 13:23

Hi :-)

We have ~3,500,000 old tickets we dont need anymore.
Because of this we have created a generic agent. But it only deletes 4000 tickets once (runned manually) and needs too long for deletion.
Is there a way to speed up the process?
Maybe more agents? Running by command line?

Appreciate any help, thank you in advance :-)
Ahmet

zzz
OTRS superhero
Posts: 216
Joined: 15 Dec 2016, 15:13
OTRS Version?: 3.x - 6.0.x
Real Name: Emin
Company: maxence business consulting GmbH
Contact:

Re: Removig old tickets with Generic Agent too slow

Post by zzz » 06 Nov 2018, 14:16

Hallo Ahmet,

you can define the limit in the SystemConfiguration under 'Ticket::GenericAgentRunLimit'.
*Try not to exaggerate.

Can't help with the speed but you could also consider archiving these tickets.

Best regards
Emin

atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom » 06 Nov 2018, 15:24

Hi Emin,
thank you very much. This would solve one of our problems and we could remove 10k or 100k tickets with one run.
Unfortunately, OTRS is removing only 1 ticket per minute. This would mean that we need more than 6.5 years for this process :-S
Any idea how we can speed up the deletion?
Regards,
Ahmet

User avatar
christianclavet
OTRS expert
Posts: 75
Joined: 27 Aug 2018, 13:50
OTRS Version?: Community
Real Name: Christian Clavet
Company: Tact Group

Re: Removig old tickets with Generic Agent too slow

Post by christianclavet » 07 Nov 2018, 17:48

Hi,

I can delete 30 tickets in less than a second here. Could mean you have tons on tickets on your DB and the search is taking more time. I think as your DB will have less tickets it should take less time.

Still 1 minute per ticket is really long!
----------------------------------------------
OTRS Community 6.0.11
Debian 9.0

MariaDB

atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom » 08 Nov 2018, 11:56

Hi,
Thank you both for your efforts :-)

Are there anything that we can speed up at the config site?
Or maybe running any agent from command line?
Right now, we are getting more than 200 Tickets a day, we don't need. Deleting 1440 deleting - 200 Tickets = 1240 Ticket deletions a day. This cannot be the solution :shock:

root
Moderator
Posts: 1314
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: Removig old tickets with Generic Agent too slow

Post by root » 08 Nov 2018, 14:03

Hi,

Did you ever tried to filter these (I think) email that they not become a ticket? Way more efficient.

- Roy
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out https://www.znuny.com/

atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom » 14 Nov 2018, 14:02

Hi there,

thanks for your replies...
Did you ever tried to filter these (I think) email that they not become a ticket? Way more efficient.
sure, you are right.
We have reduced those emails that are not relevant for OTRS significantly meanwhile, and developed a bounce mail checker, that filters those messages and also deactivate sending further emails.

But from the time before we still have 3.5M (closed) Tickets. Our current OTRS Installation is very slow. Opening some Queues, Opening tickets, etc. takes several seconds (between 3s up to 20s).

We also have around 5000 open tickets (most of them are "away from office" messages which we want to reduce be generic agent filtering)

Our current installation parameters are
OTRS 5
Ubuntu 16.04 LTS
MariaDB 10.1 (shared with other dbs) using InnoDB Engine
Ticket::SearchIndexModule is StaticDB
Ticket::IndexModule: StaticDB
Ticket::StorageModule is ArticleStorageFS

AdminSupportDataCollector shows a warning for
Query Cache Size (1073741824)
The setting 'query_cache_size' should be used (higher than 10 MB but not more than 512 MB).
We are using staticDB setting

We are planning to upgrade to OTRS6. But due to large amounts of data we assumed to better reduce database size before.

Any help is appreciated.

Regards

atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom » 14 Nov 2018, 14:42

Investigated a little further

Code: Select all

"DELETE FROM ticket_history WHERE article_id = <someid>"
takes almost 60s each.

These are the current indices on that table
Bildschirmfoto 2018-11-14 um 13.39.16.png
You do not have the required permissions to view the files attached to this post.

atom
OTRS newbie
Posts: 6
Joined: 06 Nov 2018, 13:03
OTRS Version?: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom » 14 Nov 2018, 17:40

in OTRS version 6 this has been fixed

https://dev.ib.pl/ib/otrs/commit/cc5789 ... 73939aaf1b

strange but true, for oracle db driver an article index on the history table is already available.

As for now, we have added the index, and deletion is working fast now.

Post Reply