Moving attachments to the file system

[oldengine]

My attachment statistics are now 37MB, the data 75MB and the index 28MB. I am on my own server (Dell dual Xeon, 1G mem and 2-73G drives.) Average attachment size 52K, total attachments 731, total downloads 76,170.

Question one:
What are the alarm points on attachments remaining inside the database that would necessitate my making a move from the database to the file system?

=========

I made an attempt at moving attachments from the database to the file system. Safe mode off, directory chmod 777, directory owned by user. All went well. It reported 731 attachments in the database and 731 attachments in the file system and 38 that had 0 bytes. I completed the moving process and optimized the attachment table.

Everything was looking great and making the move was very speedy. I looked at several attachments and they came up fine. When I later returned to the board, it was RED X time and none of them were there. The files (666) were definitely in the directories (777) and they could be brought up directly on a browser by their url address. I can't say they were previously coming from cache because I was checking different photos each time, however, "properties" on each did show as attachment.php rather than the actual file url. I also did an optimize/check of all tables without incident, but no change.

Question two:
Did I miss a step? Any suggestions?

[Freddie Bingham]

You will need to submit a support ticket for us to determine what the cause of this is.

[oldengine]

I have resolved question #2 by setting up a /testvb/ system on another domain and importing my main database to it. It successfully moved the 731 attachments to file space and this time, there were 0 files with 0 bytes. I expect that it will work fine on the active board, so no need for a ticket.

Is there an answer for my #1 question?

[Zachery]

For number 1,

If you are storing alot of attachments then it is best to store them in the file system, MySQL is not ment for storing masses of biniary data. Moving them into the file system should show some improvement if you have a large busy attachment table.

However not all hosts allow for this.

[oldengine]

For number 1, If you are storing a lot of attachments then it is best to store them in the file system, MySQL is not ment for storing masses of biniary data. Moving them into the file system should show some improvement if you have a large busy attachment table.

From the top of my first post: My attachment statistics are now 37MB, the data 75MB and the index 28MB. And my attachment count when moved is 731. I have a dedicated server.

What are the alarm points? Are my statistics small enough that I don't need to be concerned about having attachments in the database? What size do these grow to where I should be concerned? Total attachment file size? Total attachment file count?

Just asking, what is a lot and what is large?

[Freddie Bingham]

You need not worry, you are still small in that area. Since you have a dedicated server, I see no reason for you to not go ahead and move them to the filesystem though.

[oldengine]

Thank you Freddie.

Trying this move again worked absolutely fine. My first failed attempt may have been because I placed the attachment directory outside of the forum directory, although I did enter the full path correctly. Or, it could have been simply a one-time problem.

Anyway, I created an attachment directory under the forum directory and the conversion worked smoothly and the photos now show fine on the board.

The main reason that I am adding to this thread is to comment on an added feature. I was concerned about having an unprotected chmod 777 directory. I have found that this attachment directory can be .htaccess password protected because the photos are served by SQL and there is no need for the end user to directly access the directory.

This should put to rest a lot of concern about the writable directory AND outside link access to the photos.