• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Advise on storage

Ive upgrade an application I worked on a long time ago, which is working fine but storage is running out of control.

When I took over a few years ago files were stored in a database but kept falling over when the database file hit 2Gb, so I stored the files in a folder instead (sub folder for each record as one record could hold 0-~ files). After a few years the storage has gone from 2Gb being an issue to now 780Gb, however its using 10.2Tb of disc space due to the cluster size of the server and the majority of the files being very small (1-3Kb in size).

I cant do anything about the cluster size on the server, so need to look into alternative ways.

So I'm revisiting the idea of storing the files in a database, and looking at using SQLite to store the files in however slightly worried about the write locking (when SQLite writes it locks the database). I've been looking at splitting the file into segments and over multiple database files. so I can keep the the SQLite files around 200Mb each which I think would be a good size and reducing the loss of storage in the clusters.

Does anyone have any advise or previous experience in storing files for web apps?

Thank you
  • 2
1 Solution
Not sure what to recomend for this? I use SQLite all the time, and you can have problems if you try and use it like MySQL for an entire site, all pages, every thing, Database storage. BUT if you use it on a Per Page (or web site section) database, where for a single page (or just 3, 4, 5, or 6 pages) uses a separate data file, then there is never the traffic to block effective usage. But the SQLite, can also suffer from conjestion for file storage, and is recommended that files are stored on disk.
It sounds to me like you should try and use one of PHP file compression for storage.These can be fast for file collections of less than a thousand or so files. But with 780Gb o files, this might get less than easy to find a balance between speed and disk space?
tonelm54Author Commented:
I've been reading up on this using this guide 'http://www.dummies.com/how-to/content/storing-data-with-php-flat-file-or-database.html' however there doesn't seem any down side to using the files in a database.

Any reason why I cant store in database

Thank you
OK, I will give you some of my opinions, and there are other opinions I have read.
 There are several many considerations when someone considers disk file storage, and or database file storage. I never said that you can not put disk files into a data base.
When using DB engines like MySQL, MSSQL, , they store each TABLE and its data inserts in a separate disk file from other Table files, they MUST have all columns defined as a specific amount of Bytes, if you place disk files in the table as some sort of "Blob" column with undefined byte size, then another file is created and used for storage of all "Blob" type columns, and this works, you can place any files in a table that you like. And if you have little site traffic, and not much data base activity, then this works well for storing and getting the files. BUT, if you have moderate or heavy site traffic and are pushing these DB stored files through the PHP to Database connection transfer, then your site can become SLOW in response time for any page that uses the Database, even if it does not get any files. The PHP to Database connection can only transfer so many packets per second, this transfer DB rate is usually much slower than the Operating System disk file access speed. So it is recommended that you store files on disk by PHP methods, and just place the file path, as a defined byte size column in the Database, so you do not overload the database connection transfers with large "Blob" byte size transfers.

As to using SQLite, it does NOT use a separate Database engine and data transfer connection, so the database files for SQLite are not divided in to separate Table files, but any and ALL data for the DB file is in one single file. SQLite works very well (speed of DB) up to a certain point of database usage, but when there are many, very many php pages using the same SQLite DB file every second, then SQLite can become Slow. The newer SQLite3 is more able to handle Loads than the old SQLite2, which had a reputation for only being able to do light weight data traffic loads.

Your idea of making enough separate SQLite DB files at about 200-300 megs, for file storage, just may work for you, I have never tried anything at all like that strategy for file storage, so I can not say much one way or another about it. But I can say that direct PHP flat-File access times, are faster, and take less operations than using a SQLite DB access for files, and so it is recommended to not place many files in the SQLite.

That's all I have to inform you on database storage, but the web is super loaded with info about MySQL,  SQLite, and PHP.
Unfortunately, there's going to be some difficulties in dealing with 780Gb of of file size Data, no matter what you do, and I have no experience in dealing with that much items to deal with.
I suppose you have considered a way to Archive half of those 780Gb, that are less or never accessed files to compressed files?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now