Removig old tickets with Generic Agent too slow

Moderator: crythias

Post Reply
atom
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Removig old tickets with Generic Agent too slow

Post by atom »

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
Znuny superhero
Posts: 888
Joined: 15 Dec 2016, 15:13
Znuny Version: All
Real Name: Emin
Company: Efflux GmbH
Contact:

Re: Removig old tickets with Generic Agent too slow

Post by zzz »

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
Professional OTRS, Znuny & OTOBO services: efflux.de | efflux.de/en/

Free and premium add-ons: German | English
atom
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom »

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
christianclavet
Znuny newbie
Posts: 75
Joined: 27 Aug 2018, 13:50
Znuny Version: Community
Real Name: Christian Clavet
Company: Tact Group

Re: Removig old tickets with Generic Agent too slow

Post by christianclavet »

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
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom »

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
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Removig old tickets with Generic Agent too slow

Post by root »

Hi,

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

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

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

Do you want to contribute or want to know where it goes ?
atom
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom »

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
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom »

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
Znuny newbie
Posts: 14
Joined: 06 Nov 2018, 13:03
Znuny Version: OTRS5
Real Name: Ahmet

Re: Removig old tickets with Generic Agent too slow

Post by atom »

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