<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 129.75pt 1.0in 129.7pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoPlainText">Hi.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I upgraded my test environment from 4.2.11 to 4.4.0rc2<o:p></o:p></p>
<p class="MsoPlainText">I have not tested everything yet, but so far it seems to have addressed any issues I saw in rc1 (thank you!)<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">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).<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">When I ran the externalize-attachments script, it seems to have dumped attachments to the file system:<o:p></o:p></p>
<p class="MsoPlainText"># du -sk /opt/rt4/var/attachments/<o:p></o:p></p>
<p class="MsoPlainText"> 478424 /opt/rt4/var/attachments/<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Where I would have thought it would be (very roughly) 470MB or so smaller, it now seems to be about 300MB larger.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I’m assuming I’m just missing something obvious – running the command incorrectly, bad SQL (sizing), etc.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Has anyone else tried this and been successful with it? <o:p>
</o:p></p>
<p class="MsoPlainText">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.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Here is what I use to check DB size (MySQL 5.6.27)<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%">mysql> SELECT table_schema "rt4",
<o:p></o:p></span></p>
<p class="MsoPlainText" style="text-indent:.5in"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB",
<o:p></o:p></span></p>
<p class="MsoPlainText" style="text-indent:.5in"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> sum( data_free )/ 1024 / 1024 "Free Space in MB"
<o:p></o:p></span></p>
<p class="MsoPlainText" style="text-indent:.5in"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> FROM information_schema.TABLES GROUP BY table_schema;
<o:p></o:p></span></p>
<p class="MsoPlainText" style="text-indent:.5in"><span style="font-family:"Courier New""><o:p> </o:p></span></p>
<p class="MsoNormal">And for table size:<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%">mysql> SELECT table_name AS `Table`,
<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> -> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> -> FROM information_schema.TABLES WHERE table_schema = "rt4"<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";color:#1F4E79;mso-style-textfill-fill-color:#1F4E79;mso-style-textfill-fill-alpha:100.0%"> -> AND table_name = "Attachments";<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">This is the “before”:<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| rt4 | Database Size in MB | Free Space in MB |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| rt4 | 1809.51562500 | 119.00000000 |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| Table | Size in MB |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| Attachments | 987.84 |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">And the “after”:<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| rt4 | Database Size in MB | Free Space in MB |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| rt4 | 2273.68750000 | 156.00000000 |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+--------------------+---------------------+------------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| Table | Size in MB |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">| Attachments | 1335.91 |<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">+-------------+------------+<o:p></o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">In RT_SiteConfig.pm I set <o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">Set(%ExternalStorage,<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New""> Type => 'Disk',<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New""> Path => '/opt/rt4/var/attachments',<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">);<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">Set($ExternalStorageCutoffSize, '10');<o:p></o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">(I didn’t originally set CutoffSize, then tried 500, 100, and finally 10)<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I modified two things:<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"> 1) In sbin/rt-externalize-attachments<o:p></o:p></p>
<p class="MsoPlainText"> * Added a "force" option (so I could keep re-running it with new CutoffSizes):<o:p></o:p></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">GetOptions( \%opts,<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""> "help|h", "verbose|v", "force|f"<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">);<o:p></o:p></span></p>
<p class="MsoPlainText"> * Per previous post from Shawn :<o:p></o:p></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""># pull out the previous high-water mark for each object type<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">my $last = RT->System->FirstAttribute("ExternalStorage");<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">if ($opts{'force'}) {<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""> # IGNORE PREVIOUS RUNS OF THE PROGRAM AND<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""> # (RE)PROCESS ALL ATTACHMENTS<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""> $last = {};<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">} else {<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New""> $last = $last ? $last->Content : {};<o:p></o:p></span></p>
<p class="MsoPlainText" style="margin-left:.5in"><span style="font-family:"Courier New"">}<o:p></o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">2) lib/RT/Attachment.pm (well, copied it to local/lib/RT and modified that one)<o:p></o:p></p>
<p class="MsoPlainText"> * I modified the sub “ShouldStoreExternally” to skip text (just store images)<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New""> elsif ($type =~ m{^(text|message)/}) {<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New""> # If textual, we only store externally if it's _large_<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New""> return (0, "attachment is text");<o:p></o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Thanks!<o:p></o:p></p>
<p class="MsoPlainText">Brent<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
</div>
</body>
</html>