Hi,

After many months of trying, we have managed to get attachments on the filesystem installed and working correctly. We then rang the command to migrate attachments from the Database to the filesystem.

Great news! We reduced the database size from 5.44GB to 1.88GB (cpanel figure), however this still seems very large and I need your help to understand why?

I have checked the osti_file table and all of the rows have a F value in the bk column, so this shows they are all on the filesystem rather than in the database, however in the system information (see image at top of post), it still shows the Space Used as 1927.90MiB and space for attachments to be 1647.67 MiB.

The only significant table is the osti_file_chunk table which shows 1.6GiB in PHPMyAdmin, all of the other tables are just small numbers of MiB or KiB.

Is there anything I can do to reduce this table down further or is that what it should be now?

We were ideally trying to get the database to less than 1GB?

Kind Regards

@clarkies

You moved the Files to the filesystem but you did not delete the files from the database. The best thing to do is to backup the ost_file_chunk table (so you can add it back later on if you need to) and then delete all records in the same table. Don't touch any other tables, just the ost_file_chunk table.

Edit:
Now that I think about it, if just migrating attachments made the size go down then it should've moved all files...I assume some files were not migrated for some reason. Try running the file migration again to see if the size continues to go down. If not, check ost_file table for any records with bk = 'D'. If you do find records then these are still in the database.

Cheers.

    Hi,

    KevinTheJedi

    I have checked the osti_file table and all of the records show bk = F. There are no D records in the bk column.

    Do you think it is worth running the migration command again? We were doing them in blocks of 1000 to ease server load, however the last one we did only processed just over 400 and so I assume they are all done.

    I have attached a screen shot of the PHPMyAdmin just in case you can see any issues from there.

    Any other suggestions would be greatly appreciated.

    Many Thanks & Kind Regards

      @clarkies

      Okay, so my original comment still stands then:
      You moved the Files to the filesystem but you did not delete the files from the database. The best thing to do is to backup the ost_file_chunk table (so you can add it back later on if you need to) and then delete all records in the same table. Don't touch any other tables, just the ost_file_chunk table.

      Edit:
      Nvm, I see now where we unlink (delete) the db attachments after they are moved. I'm not sure why there are still chunks in the table. Do you see any records in the ost_file_chunk table? If so, grab the file_id from the ost_file_chunk table, do a lookup in the ost_file table where id = file_id, and post the matching record here (copy/paste, screenshot, etc.).

      Cheers.

        Hi,

        KevinTheJedi

        The number of rows in the osti_file_chunk table did reduce from about 18000 down to 5431 when we ran the migration. As previous, the database size also went down from 5.44GB to 1.88GB.

        The lowest ID numbers in the osti_file table are 1 & 3450 (which are logos) and then it jumps to 8718. The ID's in the osti_file_chunk start at 4 and go upto 8705. (I sorted them by ID number in the screenshots)

        So I am right in thinking I can delete all of the records from the osti_file_chunk table as the ID's in this table aren't present in the osti_file table?

        Thank you

          Personally I would try running the migrate command again to move the rest.

            Hi,

            ntozier
            KevinTheJedi

            We have run the command again as suggested and the output as is follows 👎️

            The result of the provided command:

            php setup/cli/manage.php file migrate --backend D --to F
            #!/usr/bin/env php
            Migrated 0 files

            Any suggestions now please?

            Thank you

                @clarkies

                Yes, delete all ost_file_chunk records that do not match any records in the ost_file table. Just make sure to back up the ost_file_chunk table first to ensure you can revert if anything goes wrong.

                Cheers.

                  Hi,

                  KevinTheJedi

                  Thank you so much. There aren't any records that match the ost_file table, so do you think it would be ok if I just either press the 'empty' table button in the PHPMyAdmin database view or go into the table, select operations and press the truncate button?

                  Which do you think might be best in this case?

                  I have backed up the table already.

                  Thank you

                    @clarkies

                    You can do either or. Truncating removes all records in one click whereas selecting all then deleting is more steps.

                    Cheers.

                      Hi,

                      Massive thanks to KevinTheJedi and ntozier

                      I have emptied the table and the database is now down to about 200MB which is fantastic. I have tested it and everything appears to be working ok.

                      Final question, do you think I should Optimise the Table for those tables that have Overhead or just leave it now.

                      Kind Regards

                          @clarkies

                          Really up to you man. Since you are trying to reclaim storage though Optimizing the tables will be a good idea.

                          Cheers.

                          Write a Reply...