[rt-devel] RT 4.4.0rc2 and ExternalStorage (disk)

[Parish, Brent]

Sorry, my fault!

I needed to run
   mysql> optimize table Attachments;
before I found the space again.  Now I see it:

The database was:
+--------------------+---------------------+------------------+
| rt4                | Database Size in MB | Free Space in MB |
+--------------------+---------------------+------------------+
| rt4                |       2259.43750000 |     153.00000000 |

+--------------------+----------------------------------------------------+------------+
| Database           | Table                                              | Size in MB |
+--------------------+----------------------------------------------------+------------+
| rt4                | Attachments                                        |    1308.91 |


After running optimize, the database now looks like this:
+--------------------+----------------------------------------------------+------------+
| Database           | Table                                              | Size in MB |
+--------------------+----------------------------------------------------+------------+
| rt4                | Attachments                                        |     727.91 |

+--------------------+---------------------+------------------+
| rt4                | Database Size in MB | Free Space in MB |
+--------------------+---------------------+------------------+
| rt4                |       1678.43750000 |      34.00000000 |

Thanks,
Brent



From: rt-devel [mailto:rt-devel-bounces at lists.bestpractical.com] On Behalf Of Parish, Brent
Sent: Friday, December 04, 2015 2:07 PM
To: rt-devel at lists.bestpractical.com
Subject: [rt-devel] RT 4.4.0rc2 and ExternalStorage (disk)


Hi.



I upgraded my test environment from 4.2.11 to 4.4.0rc2

I have not tested everything yet, but so far it seems to have addressed any issues I saw in rc1 (thank you!)



One thing I have not been able to wrap my little brain around is the ExternalStorage (never used it before, when it was an extension).



When I ran the externalize-attachments script, it seems to have dumped attachments to the file system:

# du -sk /opt/rt4/var/attachments/

   478424               /opt/rt4/var/attachments/



Where I would have thought it would be (very roughly) 470MB or so smaller, it now seems to be about 300MB larger.



I’m assuming I’m just missing something obvious – running the command incorrectly, bad SQL (sizing), etc.



Has anyone else tried this and been successful with it?

I’m about to flatten the env and try again but that is such a long process that I thought I’d check with everyone else first.





Here is what I use to check DB size (MySQL 5.6.27)

mysql> SELECT table_schema "rt4",

  sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB",

  sum( data_free )/ 1024 / 1024 "Free Space in MB"

  FROM information_schema.TABLES GROUP BY table_schema;


And for table size:

mysql> SELECT table_name AS `Table`,

    -> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`

    -> FROM information_schema.TABLES WHERE table_schema = "rt4"

    ->  AND table_name = "Attachments";

This is the “before”:

+--------------------+---------------------+------------------+

| rt4                | Database Size in MB | Free Space in MB |

+--------------------+---------------------+------------------+

| rt4                |       1809.51562500 |     119.00000000 |

+--------------------+---------------------+------------------+

+-------------+------------+

| Table       | Size in MB |

+-------------+------------+

| Attachments |     987.84 |

+-------------+------------+



And the “after”:

+--------------------+---------------------+------------------+

| rt4                | Database Size in MB | Free Space in MB |

+--------------------+---------------------+------------------+

| rt4                |       2273.68750000 |     156.00000000 |

+--------------------+---------------------+------------------+

+-------------+------------+

| Table       | Size in MB |

+-------------+------------+

| Attachments |    1335.91 |

+-------------+------------+





In RT_SiteConfig.pm I set

Set(%ExternalStorage,

    Type => 'Disk',

    Path => '/opt/rt4/var/attachments',

);

Set($ExternalStorageCutoffSize, '10');



(I didn’t originally set CutoffSize, then tried 500, 100, and finally 10)



I modified two things:



  1) In sbin/rt-externalize-attachments

        * Added a "force" option (so I could keep re-running it with new CutoffSizes):

GetOptions( \%opts,

     "help|h", "verbose|v", "force|f"

);

        * Per previous post from Shawn :

# pull out the previous high-water mark for each object type

my $last = RT->System->FirstAttribute("ExternalStorage");

if ($opts{'force'}) {

        #       IGNORE PREVIOUS RUNS OF THE PROGRAM AND

        #        (RE)PROCESS ALL ATTACHMENTS

    $last = {};

} else {

    $last = $last ? $last->Content : {};

}



2) lib/RT/Attachment.pm  (well, copied it to local/lib/RT and modified that one)

      * I modified the sub “ShouldStoreExternally” to skip text (just store images)

    elsif ($type =~ m{^(text|message)/}) {

        # If textual, we only store externally if it's _large_

        return (0, "attachment is text");



Thanks!

Brent




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-devel/attachments/20151206/8d8013ef/attachment-0001.html>