Hello,

I see that our version is in need of upgrading, as we're running 1.12.x, but I need to trim down our database size first.

Our web host temporarily shut down our account because the mysql database has gotten too large, and now that I look at it, I see that the two tables, thread_entry and thread_entry_email are huge, 858MB, and 630MB, respectively. The _search table is also large-ish at 255MB.

All of the other tables are significantly smaller, which I would expect, because we have the Archiver plugin set to remove all messages over 6 months old.

What do I need to do to clean up those two tables? Any ideas?

  • So, I discovered that cascading deletes via the database structure alone aren't a thing, so I used the following three deletes to clean up the old threads in my database; the first line deletes all threads without matching tickets in the ticket table, and the other two deletes take care of the cascading orphans:

    DELETE FROM `db29brts8zgjh4`.`soft_thread` WHERE `object_type` = 'T' AND `object_id` NOT IN (SELECT `ticket_id` FROM `soft_ticket`);
DELETE FROM `db29brts8zgjh4`.`soft_thread_entry` WHERE `thread_id` NOT IN (SELECT id FROM soft_thread);
DELETE FROM `db29brts8zgjh4`.`soft_thread_entry_email` WHERE `thread_entry_id` NOT IN (SELECT id FROM soft_thread_entry);

    After deleting this data, you might have a lot of wasted space in your MySql database, so be sure to go back and optimize the db:

    https://www.siteground.com/tutorials/phpmyadmin/repair-optimize-database/

    After running all of this, OST is running fine, lean and clean with no ill-effects detectable so far. If there are any, I'm sure I'll hear about it soon from the users!

    I'll schedule the software upgrade for next weekend, and backups will be much smaller and easier to manage.

So not to nitpick, but you mention three (3) tables and then ask how to clean up two (2).

The only way that I can think of to reduce the first two mentioned is to reduce the number of tickets that you have in the system. So I guess lower time limits on your Archiver plugin.

The ost__search table is for searching ticket data like subject and such. You could reduce the size of the database temporarily by truncating it, resetting the index to 1, and then search for something and it should slowly rebuild the table. But his isn't a fix its a temporary band aid. (Its a index of searchable stuff to speed up searches)

    ntozier , thanks for the reply!

    The Archiver plugin does delete tickets, so the oldest closed ticket in the system is from April of this year, but when I look at the data in the two tables I mentioned, the data goes back to 2019. I'm guessing those are orphaned records.

    I presumed that the search table was either a related issue, or at the least, could be dealt with after the two big tables were handled.

      ntozier, any ideas? How can I prune or otherwise reduce the size of those two tables?

      I've been trying to explore the data, but I need more information about the schema before I can really tell for sure what the data is that is in there, and whether it could be safe to drop.

        OK, so I found the schema for tickets here:

        https://docs.osticket.com/en/latest/_static/images/erd_Tickets.pdf

        Looking at that, I was able to walk up the structure another table, and identify the orphaned threads like this:

        SELECT count(*) FROM soft_thread
where object_type = 'T' and object_id NOT IN (SELECT ticket_id FROM soft_ticket)
order by created desc;

        which gives me a count of 66,887 and I can follow the relationships down to see the corresponding data in the related tables.

        But now the question is, what would be the best way to go about removing this data? Will the database cascade deletes?

        I can see by the diagram that it cannot cascade deletes from tickets to threads due to database design, hence this problem, but maybe it can cascade from here on down?

        Or, some other way to clean up orphaned threads?

        So, I discovered that cascading deletes via the database structure alone aren't a thing, so I used the following three deletes to clean up the old threads in my database; the first line deletes all threads without matching tickets in the ticket table, and the other two deletes take care of the cascading orphans:

        DELETE FROM `db29brts8zgjh4`.`soft_thread` WHERE `object_type` = 'T' AND `object_id` NOT IN (SELECT `ticket_id` FROM `soft_ticket`);
DELETE FROM `db29brts8zgjh4`.`soft_thread_entry` WHERE `thread_id` NOT IN (SELECT id FROM soft_thread);
DELETE FROM `db29brts8zgjh4`.`soft_thread_entry_email` WHERE `thread_entry_id` NOT IN (SELECT id FROM soft_thread_entry);

        After deleting this data, you might have a lot of wasted space in your MySql database, so be sure to go back and optimize the db:

        https://www.siteground.com/tutorials/phpmyadmin/repair-optimize-database/

        After running all of this, OST is running fine, lean and clean with no ill-effects detectable so far. If there are any, I'm sure I'll hear about it soon from the users!

        I'll schedule the software upgrade for next weekend, and backups will be much smaller and easier to manage.

        Write a Reply...