Slow server

[fsprott]

PHP version: 8.1.29
FreeScout version: 1.8.141
Database: MySQL 8.0.31
Are you using CloudFlare: Yes / No
Are you using non-official modules: No

During some years of using the app we now currently have 3 000 000 Threads and 450 000 Converstations, and having issues with slow APP.

Database is running on GCP with:
vCPUs: 8
Memory: 8 GB
SSD storage: 100 GB

WEB server on GCP standard machine.

We no longer able to use the offical report module due to long load times, and several other general pages and features takes sevel seconds to load (5-15 seconds). Any suggestions on improvements?

We already use faster search module.

Let me know what other information you need to be able to support.

[fsprott]

[freescout-helpdesk]

Obviously there are two approaches - reducing the number of conversations & threads in DB and increasing server performance.

[fsprott]

Obviously, that would be the simplest solution.

But are there any scalable solutions that have been tested? Most of the data will be "old" history and not accessed on a daily basis. However, this slows down the entire application, and deleting history is not always an ideal solution.

Has anyone tried partitioning the threads table based on the created_at date? It seems to be a potential fix, especially considering the queries that are taking the longest. I am considering partitioning the data quarterly or even monthly to reduce the amount of data each query has to process. Has anyone experimented with this approach? This might significantly impact resource usage for larger databases.

Would it make sense to implement this for threads based on the created_at date, and possibly for conversations as well? Any suggestions or known limitations would be greatly appreciated.

[freescout-helpdesk]

Yes, this can also be done. You can simply create a threads_backup table and periodically move there old threads from treads table.

[raramuridesign]

@fsprott We had a similar issue, here is our approach
https://matthew.philogene.co.za/freescout-remove-emails-procedure/

Unless you are keeping the emails / conversations for a specific purpose they are probably not needed, as they should be in the original email box where they are fetched from. So you can use this as the backup.

Hope this helps
M.

[vanderproject]

@raramuridesign Thank you for the tips! We will try to include something simiar as well.

We also addes some indexes. That removed all the slow experience, and as well reports now load also during peak times and are on average 5-10 times faster.

I am guessing the index on created_at on threads made the biggest difference. I belive these was the indexes we added:

conversations
ADD INDEX idx_conversations_mailbox_state_status (mailbox_id, state, status)

notifications
ADD INDEX idx_notif_read_created_notifiable (read_at, created_at, notifiable_id, notifiable_type)

threads
ADD INDEX created_at (created_at)
ADD INDEX idx_threads_conversation_type_action_created (conversation_id, type, action_type, status, created_by_user_id, created_at)

[raramuridesign]

@fsprott Thanks for sharing. I am sure that @freescout-helpdesk will review the indexes and add to the code if it works well.
Then it would not need to be added manually.
Let us know how it goes on the cleanup.
Just one thing to note. the cleanup takes a long while, so let it run its course. Eventually after a few months - it will normalise.
But it keeps freescout clean and working fast.

Our approach was
Checking with the team how far back we should look for tickets. On different projects this varied, from 6 months to a year of conversations. Most of the support or tickets are dealt with and never touched again. If it did come back into the inbox and the original was not there, then it would be opened as a new ticket.

Hope this helps.
M.