ArticleStorageFS migration questions - Database load question

[finalbeta2]

Good day,

I'm currently running latest Znuny stable (6.5.*).
We noticed performance drops and think we traced it to tickets with Email signatures containing many images.
Seems if you have a mail conversation with Emails that contain many images, and replies multiplying this, the database is getting hammered.
Each image seems to be a database connection.

I have a few questions about migrating to ArticleStorageFS.

* I'm wondering if switching to ArticleStorageFS would also include these images, and result in much less database load?
* I found documentation for 7.1 ( https://doc.znuny.org/znuny-7_1/standar ... index.html ), but not for 6.5.*. are the steps the same? Does documentation exist for 6.5?

Thanks for anyone offering insights.

[Johannes]

Hi,

it is suggested to not have the files in the DB, just migrate them to the FS.
It is straight forward and improves loading times and backup times overall.
Just make sure you have enough space for the export.

If things do not improve, check the size of your DB and match it against the configuration.
So "optimize" it, one would say.
For MySQL/MariaDB, use the MySQL tuner as a guideline.

Regards
Johannes

[hkais]

I'm currently running latest Znuny stable (6.5.*).
We noticed performance drops and think we traced it to tickets with Email signatures containing many images.
Seems if you have a mail conversation with Emails that contain many images, and replies multiplying this, the database is getting hammered.
Each image seems to be a database connection.

To understand your environment, can you answer:
- How big is your instance?
- Means how many tickets and articles do you have?
- and how many images and files do you have in database?
- How many active tickets?
- how many concurrent users (real parallel work on znuny, NOT only logged in users)

Of course the Filesystem will be the best performing option, but having in database has also benefits.
We have seldomly filesystem based ArticleStorageFS environments, as long as the database is fully tuned. And here it is often a lack of understanding how execution plans are working and how database threads are handled per specific database - espacially on xLOBs.
The issue is to keep the filesystem and database consistent. And in my past experiences admins do some strange things, since there are only files, not having the required expertise on Znuny/OTRS and kill the "links" of Files to database rows. And you as the expert have to fix the crap from a person, mostly months later. And best situation, the causing person left the company...

So as long as you can tune the DB, stick to it.
Maybe we can share our DB tuning guide for Znuny too on our platform, need to discuss with my team mates.

[finalbeta2]

Hi,

- How big is your instance?
+-50GB (Larger on disk, we will be optimizing some tables)
- Means how many tickets and articles do you have?
483513 Articles; 78154 Tickets; 4135615history entries.
- and how many images and files do you have in database?
908063 attachments in the db.
- How many active tickets?
1200 tickets (not all in open state)
- how many concurrent users (real parallel work on znuny, NOT only logged in users)
10 max logged in. 5 max in parrallel.

[Johannes]

Hi,

then just export the ArticleStorage to the FS an check.
It is a fairly straightforward process.

Set the Sysconfig: Ticket::Article::Backend::MIMEBase::ArticleStorage to > FS
check if there is enough space left on the machine and execute.
bin/otrs.Console.pl Admin::Article::StorageSwitch --target ArticleStorageFS

Everything will be exported to the path specified in
Sysconfig: Ticket::Article::Backend::MIMEBase::ArticleDataDir

Regards

[hkais]

Hi,

- How big is your instance?
+-50GB (Larger on disk, we will be optimizing some tables)
- Means how many tickets and articles do you have?
483513 Articles; 78154 Tickets; 4135615history entries.
- and how many images and files do you have in database?
908063 attachments in the db.
- How many active tickets?
1200 tickets (not all in open state)
- how many concurrent users (real parallel work on znuny, NOT only logged in users)
10 max logged in. 5 max in parrallel.

If your sever is a common fast disk server, the issue is probably something else or a non optimized Database.
This amount is usually no piece of data for a database to worry about.

You could enable the performance logging and check what operation is eating the time. Based on this you can catch the current thread on database to see what is causing the bad performance.

If you do not have a database administrator, who knows how to optimize database, simply share here the long running query. maybe we can help

[finalbeta2]

Small update on this.

Before moving to articleFS we moved to a diferend reverse proxy.
This improved some of our issues where page refreshes failed or where extra slow. (Mostly a know issue with Microsoft Entra ID app proxy where many users complain, but they do not acknowledge it exists).

Most pages load very fast, and sometimes AgentTicketZoom loads fast too. The initial AgentTicketZoom page always loads fast, but displaying the mails (articlecontents) inside can take up to 6 seconds before the complete page is loaded.
During that time, we notice that not the disks or mysql is under pressure, but the CPU from multiple Apache2 processes.

The moment the loading of the content in the article details starts, we have many Apache2 processes go to 100% CPU until the page loaded.
when debugging in the browser. We see that the page loads in under a second, and images take 1 tot 6 seconds to load.

At the moment I simply increased the CPU count (virtual machine) and this has a noticeable effect on page loads.

We will still move some tickets to articlestoragefs in further tests.