09-08-2011 04:49 AM
Having read the thread on attachments here, we decided to do a little research on our own attachment folder. Our current folder has about 435 000 attachments, which amounts to 44.7GB in file size. These are attachments from 1999 till now. Charting the growth of the attachments on a year to year basis brings up some interesting results though.
As you can see in the below image, the number of attachments is increasing in a pretty linear fashion (getting about 65000 new attachments per year now).
However, when we look at the actual growth in filesize and not filecount we notice something strange. The chart below makes this pretty obvious.
We're noticing a sudden large increase in filesize growth in 2007. Delving a little deeper into this matter I decided to try and find the culprit. Were our users suddenly attaching larger files? This didn't seem to be the case. We found that 9621 files, (2.21% of total) makes up more than 33.1GB (74% of total). Of these 9621 files over 90% appeared to be e-mails. So our users were attaching e-mails with large files attached in them. However, they were sending these kind of e-mails before 2007 aswell. So I decided to see what has changed. Before 2007 Act seemed to use a different mail format. The files carry the extension .DET instead of the current .IMA. Checking these files, they also seemed to contain attachments, but instead of keeping the attachments in the mail, they seemed to link to the files on their original location. This seems to be a better system in my honest opinion. When a user sends an e-mail with a file that's on our network drive (raid-1), which is backed up every evening, why does that file also have to go into the attachments folder. Add to that if he sends that mail 10 times, the file will be there, in the attachment folder, 10 times in the e-mail. Seems very ineficient.
The folder is also becoming too large to handle, we'll soon be at half a million files, accessing the folder (although we don't have to do that often) is becoming a risky business (windows explorer might crash on us). The other thread mentioned earlier calls for people to keep their attachment folders under 10 000 files, but we're growing at a pace 6 to 7 times that per year. We can't go throwing out attachments/history every few months. An add-on which might split it into 10 000 file folders is also mentioned, but we don't want to rely on an add-on for this. Who's to guarantee this would work in future versions of Act.
I'd like to hear some thoughts about this, looking for speed gains using Act we're looking to outfit our server with SSD's, but seeing as storage space comes quite costly with SSD's, we'd love to see a solution to this that does not sacrifice functionality.
09-08-2011 10:50 AM
Here are a few roads you can travel down:
2. You can upgrade the hard drives and it will help. Most of our clients with similar database sizes are running on 15,000RPM SCSI drives and they don't complain about the speed too often. We do have a few on SSD, but as you already mentioned, the cost can become an issue.
3. Evaluate if you need email attachements, or if you can get away with subject + message for recording histories. This would reduce the number of attachements, but it would increase the size of your history table.
Pros and Cons to all options, but those are really the only 3 you have.
Just my $0.02